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

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

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

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

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

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).    

Select right type of replication

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

replication; what is: SqlCeReplication.Subscriber?

I am tring to use SQL Server Mobile 2005 to subscribe to a SQL Server 2005 publication from a pocketPC.  In my code I have a SqlCeReplication object.  One of the properties that must be set is: Subscriber, but I have no idea what that should be set to.  When I was setting up the publication I don't remember specifying anything like that, and the class description of this property doesn't help: "Specifies the name of the Subscriber".  Any help will be appreciated.

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

avoid accidental delete on Subscriber tables - transactional replication


Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication


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

Restoring Transactional Replication on the subscriber


We have a situation were  when the subscriber breaks for some reason we need to resume the replication  once the subscriber is fixed. Is there any way we can accomplish this withoout the running the snapshot all over again.?If yes let me what are the steps

Note: We are performing 15 min Log backups on the publisher.

Merge Replication Atomicity - at what point does ALL subscriber data become visible at the publisher



I have a Merge Replication installation going on with about 35 connected devices. When one of the subscribers synchronizes, do the inserted rows appear sporadically on the publication database at different times? Or do they appear all at once?

Can all of these be isolated inside a transaction - this would seem like it should be the default behaviour, in case of a sync being cancelled or failing due to loss of connectivity etc. 

The reason for this question is that another part of the system seems to have queried the publication database during and after a subscriber synchronization with differing results. 

If this is normal, what would be the best solution to ensure data integrity? 

Thank you in advance. 


SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site


I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?



George the DBA

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

Will a ROLLBACK in a subscriber database trigger cause the replication engine to abort?


We have some data retention policy deletes for a table in the publisher database that we do not want to be replicated to the subscriber database.  Our method of implementation is to write a delete trigger for the table in the subscriber database that intercepts the deletes by checking the date associated with the deleted data, and if the date is < 2 months ago we execute a ROLLBACK in the trigger.  The ROLBACK is effective and we get the desired results, but this however produces the message:

    Msg 3609, Level 16, State 1, Line 2 

    The transaction ended in the trigger. The batch has been aborted.

Is this message a problem for replication?  Will the replicaton engine abort?  Is this just an informational message, a warning message, or error message?

Merge Replication - Publisher Identity Range Assignment with Disconnected Subscriber


Hello everyone,

I have a merge replication topology setup as follows...  Publisher - SQL 2008 SP1, Distributor - SQL 2008 R2 (RTM), Subscriber - SQL 2008 SP1.

I recently had my subscriber disconnected for a couple days.  During this time, I was getting the following error on my publisher:

The insert failed. It conflicted with an identity range check constraint in database 'production', replicated table 'dbo.CustomerOrder', column 'CustomerOrderId'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

I understand why I would normally get this error, but if the publisher and distributor are both online, why didn't the publisher attain a new range from the distributor after it hit its threshold?  Is this expected behavior whenever a subscriber is disconnected?

Thanks in advance.


Restart the server that has been configured for subscriber in a transactional replication


Whats the impact on restarting the server that has been configured for subscriber in a transactional replication.

The distributor and the publisher have been set on different servers.

SQL Server 2008 R2 Replication: The process could not connect to Subscriber



We are facing a lot of problem with replication between SQL Server 2008 R2 servers. Let me describe the scenario..

We have 20 branches and one main Server in our organization, all are running SQL Server 2008 R2. The Main server is a Windows 2008 R2 x64 bit computer from which we want to create both way transaction replication (Data inserted in to any branch will be reflected to the main server and from there all other branches will get the same data) to all other branches.  Branch machines are 32 bit XP sp3 or Windows 7. All the machines are under workgroup. Among 20 branches we could successfully establish both way replication on 5 branches only. When we are trying to publish data from main server to say Branch A (with windows 7), it says "The process could not connect to Subscriber 'A'". We can ping to branch A and can login to Branch A from Main server through Management Studio which proves that there is no connectivity issue. Branch A can publish data to Main server without any problem. Even Branch A can subscribe data from another branch Branch B.

I cannot understand why this problem is happening for other 15 branches to subscribe data from Main Server. I have some queries in mind

  1. Is there any upper limit of subscription no?
  2. Is there any problem with windows 7?

Any help in

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.

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
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