.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Select right type of replication

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi. I have this scenario: There exists one database(about 70GB). At the same time, this database will be attached to the two SQL Servers 2005(x64), Standard editions. These servers are a few hundred km far away from each other. Different applications will connect each SQL Server, these applications will modify data in this databases. It means that during this time the databases will have some different data. We need to replicate these changes to these databases on both SQL Servers. So with this replication the databases will have the same data. The data should be visible as soon as possible, so I am thinking about Transaction replication. Is it possible, please, to do it with Transactional replication? What is your opinion on how to solve this? I have tried this: Server A as a publisher of database X and Server B as a subscriber of database X. This worked fine. Server B as a publisher of database X and Server A as a subscriber of database X. I was not able to do this operation. There was an error: I cannot drop a table …. because it is being used for replication. So I suppose SQL Server A cannot apply the first synchronization snapshot. We cannot use Updatable Subscriptions for Transactional Replication because the structure of the tables cannot be changed. We cannot use Peer-to-Peer Transactional Replication because it is only in Enterprise version. Thank you ve

View Complete Post

More Related Resource Links

SQL 2008 geography type replication

Hi, I am attempting to get merge replication going between two SQL 2008 servers.  So far, everything has gone off without a hitch with one exception.  We use spatial data on a daily basis in my organization, so we implemented the geography data type.  This data type seems to be causing all of our replication issues.  I can easily create publications and subscriptions for each article in the entire database except the geography field. The error is as follows: "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription." The real kicker is that the schemas are exactly the same.  If I set the publication to drop and recreate the subscription article, then to convert the geography data type, it works without issue, but then we run into issues with our publication database not matching exactly with our subscription database, which could cause many potential problems with our web front end.   Is there a known issue with replicating the geography data type?  Any help is very much appreciated! - Chad  

Selecting the Appropriate Type of Replication

I have 2 servers 1) Production Server 2) Reporting Server I create seprate tables for reports at production sever. I fill those table from stored procedure thru sql job daily. First i truncate the tables then insert whole data in reporting tables each time. So now i have to implement replication to replicate the reporting tables data to reporting server database. I wan't to know which type of replication is used (Snapshot, Transactional, Merge).    

Cannot select 'SQL Server Compact Edition' from Server Type drop down

I have seen this issue addressed for SQL Server 2005 but not 2008. I have installed these components:SQL Server 2008 (Browser, Management Objects, Native Client, Policies and Setup Support Files)SQL Server Compact 3.5 (for Devies, Server Tools and SP1)In the SQL Server Management Studio, on the Connect to Server dialog, the "Server Type" drop down list is disabled and "Database engine" is the default. Does anyone know what I need to do to make "SQL Server Compact Edition" a choice in the "Server Type" drop down list?

Select all articles that have participated in a merge replication in the last n days/weeks

I have a merge replication set up in MS Server 2000. Some of the published articles are not used. I am trying to write a query that will select these articles as well as the time when they have been last used in the replication. I was know that in the distribution database there is a table [MSmerge_history] however I cannot figure out how to join it to MSarticles. Any help will be much appreciated! Regards, Pencho

Want to. SELECT Field_1 AS [Field_2/@Value] from Table_1 FOR XML PATH (N'), TYPE . where Field_2 i

<!-- /* Font Definitions */ @font-face {font-family:"MS Mincho"; panose-1:2 2 6 9 4 2 5 8 3 4; mso-font-alt:"MS ??"; mso-font-charset:128; mso-generic-font-family:modern; mso-font-pitch:fixed; mso-font-signature:-1610612033 1757936891 16 0 131231 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} @font-face {font-family:"\@MS Mincho"; panose-1:2 2 6 9 4 2 5 8 3 4; mso-font-charset:128; mso-generic-font-family:modern; mso-font-pitch:fixed; mso-font-signature:-1610612033 1757936891 16 0 131231 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; line-height:120%; mso-pagination:widow-orphan; font-size:12.0pt; font-family:Calibri; mso-fareast-font-family:"MS Mincho"; mso-bidi-font-family:"Times New Roman"; mso-fareast-language:JA; mso-bidi-language:AR-SA;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> I'd like to dynamically specify an element name (Field_2) in my select statement ala: SELECT Field_1 AS [Field_2 /@Value] FROM Table_1 FOR XML PATH (N’’), TYPE&

Want to. SELECT Field_1 AS [Field_2/@Value] from Table_1 FOR XML PATH (N'), TYPE . where Field_2 i

I'd like to dynamically specify an element name (Field_2) in my select statement ala: SELECT Field_1 AS [Field_2 /@Value] FROM Table_1 FOR XML PATH (N’’), TYPE  where both fields are in Table_1   Is there a way to do this?   Thanks!   Here's an example:   <Facet Name="ABS Offset">     <Number Value="5.307" />   </Facet>   <Facet Name="Assembly Type">     <String Value="PreSet Hub Assembly" />   </Facet>

Merge replication - table with geography data type



I have a problem with geography data type replication. Any change that is done on a Geography column breaks the replication - the error message is "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber." I double checked compatibility settings that is suggested here: http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/d8e31621-5d43-48cd-bdd3-c02b8a24625c. But both publisher and subscriber are 100RTM.

I checked synchronization logs and found a strange message - attempt to convert varchar to geography. I don't understand from where the varchar data type comes from. Tables at subscriber and publisher has the same format - geometry stored in geography data type.

2010-09-20 18:36:22.499 The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription.
2010-09-20 18:36:22.845 Category:NULL
Source:  Merge Replication Provider
Number:  -2147199398
Message: The

SQL 2K8 ENT - WIN 2K8 ENT - Type of replication



I would like to have some advice or hint to choose the right type of replication using SQL 2008 Ent. and Windows 2008 Server Enterprise.

I made, a couple years ago, a good application using internet replication with Microsoft Access 2000 + Replication Manager 4.0. Since then, the database got bigger and bigger and before everything crash, I'm thinking about using SQL Server 2008 Replication. I also having new client.

My application is a hockey league manager (around 25 tables). The users don't always have access to internet connection (Using laptop). The server hosting the database is also used as a web server wich display the stats and all other stuff from the league on a website (Website is connected to the master database). User can change, delete or add data and conflicts should be rare but they can occur. 

I'm planning to use the SQL Express 2008 on the client side and Windows 7 or XP.

So, with this scenario, what type of replication should I used ? It is possible to used that scenario with internet replication ?


Richard Martin Web Developer / BI Developer

SQL Select problem with bit data type


I have an odd situation.  I wish to select the value of a bit field in my MS SQL query.  I want to know if it is true or false (1 or 0), but my query seems to somehow converting all the responses (true or false) to the letter 'A'.  I want to know what the bit value is: true or false.

Here is the query:

SELECT dbo.roles.pk_role_id, 
FROM   dbo.members 
       INNER JOIN dbo.roles 
         ON dbo.members.pk_id = dbo.roles.fk_member_id 
       INNER JOIN dbo.teams 
         ON dbo.roles.fk_teambar_number = dbo.teams.pk_id 
WHERE  ( dbo.roles.fk_teambar_number = @FK_TEAMBAR_NUMBER ) 
       AND ( dbo.roles.TYPE = 'Player' )  

Why is it converting all TEAMS.ACTIVE date to the letter 'A' for both true and false ???  I bind the results to a gridview and both true and false are listed as an 'A', and I get the same thing where I run the query in my GoDaddy query analyzer.  Does anyone have an idea how to fix this?

Type in first few characters to select items in a large list quicker

is there a way that you can type in the first few characters of an item in a dropdown box eg if you type ‘rad’ in the dropdown box it will take you to the first item starting with ‘rad’.

Is it possible that you could type in rad to get you to radiator for example?

Changing column Data Type when using merge replication: RFC on my SMO code.


I maintain an application for that uses SQL 2005 / 2008 for data persistence.
Some clients use Merge Replication (pull or push) to replicate data.

Some of the data the application stores should be "versioned". All versions of a row containing medical information should be stored, somewhere in a database. This means all updates and deletes to those rows should result in in 2 affected rows:

  • a copy of the row before the change
  • a row containing the change (or the deletion of the row in question)

I plan to achieve this using triggers on all the tables for wich "versioning" is required.
The old versions of a row may be stored in the same database, an other database (or a database on a linked server).

Unfortunalty, the AFTER UPDATE, DELETE triggers do not support TEXT, NTEXt an IMAGE data type columns.
Thus I am required to convert all text, ntext and image columns to their respective "new" data types, varchar(max), nvarchar(max) and varbinary(max).

This is not a big issue when the database is not published, but when it is... most articles (and posts) i've read suggest:

  1. Add a column of the target data type to the table, copy data from source column. rename columns and drop the source column.

  2. Create a temp table (with the s

PRINT vs SELECT of a real type in SQL Management Studio



I know real approximate the value etc. The question from the sample below is why a select in the mangement studio dont use the "nice"/"correct" value like the print function. I tested and for example Reporting Services is clever enouth to use the "correct value".

The problem is that sometimes one want to write a quick manual query and move the output to xls etc.


DECLARE @ConversionFactor decimal(18,10)
DECLARE @Limit real
DECLARE @Report_Meas_Result real

SET @ConversionFactor = 1000
SET @BLDVALUE = '<0.0002'
SET @Limit = cast(RIGHT(@BLDVALUE, LEN(@BLDVALUE) - 1) as real)
SET @Report_Meas_Result = @Limit/2 

type of replication in my subscriber and is activated or not?


Hi All,

I want to know my subscriber is pull/Push type ? and is active/Not ? in sql server merge replication? I am using sql server 2008 as my Publisher and 2008 express as subscriber.

Is it possible to provide ? with a very small SQL Statement or two that will return a simple integer (0 or 1 for ‘false’ or ‘true’) indicating that merge replication with the ‘Orders’ subscription is currently and actively configured to run (‘pull’) at the Store database(subscriber)?

i am able to pull data from Publisher using :


* FROM sysmergesubscriptions WHERE

TreeSelector : playing with generics and type inference

create a generic way to define a selection starting from a tree of objects, the most simpler way as possible.

Let's start with the beginning...
Here is a very simple little interface defining a node of the tree, basically, a value and the references to the child nodes.

AJAX GridView Checkbox Select All in ASP.Net

In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

UnTyped DataSets and Strongly Type DataSets

We all are use datasets as a means of carrier of data from one layer to another. Most of the time we are using weakly typed datasets. In this article I will explain the differences between weakly typed datasets and strongly type datasets

Gmail style multiple Sign-in type combo...is it possible in asp.net?


I am developing a multi-company application and want my users to give the functionality like the following to switch companies.


Is this possible? then how?

Please help


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend