.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

SQL 2005 Merge Replication Issue

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :Sql Server

Ihave merge replication set up between two sites. Everything worked fine up until the subscriber wanted to update the publisher. I get the following error:

The merge process is retrying a failed operation made to article 'X' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.

View Complete Post

More Related Resource Links

merge replication system tables fragmentation issue

I have Merge replication going on in my environment. The problem is that system tables like MSmerge_content, MS merge _tombstone and MSmerge_genhistory are getting fragmented. We are rebuilding indexes time to time daily. But we want some good fix. Just send some good idea guys?

Steps by Step document for configuration Merge Replication in 2005

Hi , Can anyone provide link to configure Merge Replication in 2005 with print screen.Also let me know the difference between Transcation level and Merge Replication. regards Vijay  

SQL Server 2005 Merge Replication

We did the following: We setup a database with all of standard tables, views, etc. We then put in place merge replication for a few of the tables within this database, including a tables called "Areas". We can execute SQL statements to insert rows into this table fine. We then applied an update script for this database to change a few tables by adding fields and changing indexes, etc.  We didn't change the "Areas" table though. After the update to the database structure, we get an error when trying to insert into this "Areas" table. The error message was the following: The insert failed. It conflicted with an identity range check constraint in database 'AETest', replicated table 'dbo.Areas', column 'AREPrimaryId'. 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.  The statement has been terminated.   What would cause such a problem and how do we resolve it?  The structure of the "Areas" table before and after the update is the following: USE   [AETest] GO /****** Object: Table [dbo].[Areas] Script Date: 08/24/2010 17:42:36 ******/ SET   ANSI_NULLS ON GO SET   QUOTED_IDENTIFIER ON GO SET   ANSI_PADDING ON

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!

Identity Range Value (Max Limit Finished) SQL Server 2005 (Merge Replication Problme)


Hi ,

I am using SQL Server 2005 enterprise edition, I Confirured the Merge Replication to my database). I have a replication of 21 database from various locations.

Problem is: When is trying to remove one subriction from the publisher and add a fresh subriciton to it. it is poping any error saying the Identity Range value exceed its Max Limit try to change from int to bigint. is there any solution for it not changing from int to bigint.

Thank you.


How to view merge replication error (sql 2005)


We're using 2005 merge replication and we found that some records deletion cannot synchronize from publisher table to other subscriber table. (This table have very small size(<1000 records). We want to view those replication system tables or any logs for diagnostics this serious problem. Can you suggest any replication system table or logs that can indicate the reasons or hints of records deletion outsync?

Thanks and regards,

2005 Merge Replication Conflicts



We have a republishing merge replication setup of SQL Server 2005 Head Office (HO) publishing to SQL Server 2005 Regional Offices (RO) re-publishing to SQL Server 2005 Express Stores (ST). The problem we are having is that of strange conflicts. They can be grouped into the following types:

1) Update conflicts where the data is the same on both sides and one side hasn't made a change since the last replication event.

2) Delete/Update conflict where the delete is real but the update isn't. Like #1 the update never occured.

3) Delete/Delete confluct where one of the deletes are real but the other isn't which then create a Unique identifer error in the conflict resolver.

We have looked over the SQL Server CU's both nothing describes our problem as we are running SP3. We've also search for other reported problems and can confirm that we don't have filters on DateTime fields only GUID fields and the join filter table hierarchy isn't deep. We've also tried a re-initialisation and verified that the collation is the same over every SQL instance.

Has anyone also come across and found a solution for this?

SQL Server 2005 Schema Replication in Merge Replication


Dear Sir,

I am using SQL Server 2005 Developer Edition for Creating & Testing Database.

I had created Merge Publication WITHOUT FILTERING ROWS and Created Push or Pull Subscription. Now if I Add or Drop column using ALTER TABLE (T-SQL) in Table OR modify stored procedure using ALTER PROCEDURE in Published Database then while Synchronizing It replicate same in Subscriber Database and works very fine.

But when I had created Merge Publication WITH FILTERING ROWS (Parameterize or Static) and Created Push / Pull Subscription and I try to Add or Drop column using ALTER TABLE (T-SQL) in Table  OR modify stored procedure using ALTER PROCEDURE in  Published Database then while Synchronizing It DOESN'T REPLICATE in Table of Subscriber Database but tries Replicate in Stored Procedure and display error.

After Adding Column in Table of Published Database, If I Checked Article in property of Publication then it Adds column but it doesn't selects it. I also checked using sp_enumeratependingschemechenges it displays pending schema changes of stored procedure but

Merge Replication between SQL Server 2005 and SQL Server 2008



We are planning to implement Merge Replication between SQL Server 2008 "Not R2 Version" (Publisher) and SQL Server 2005 (Subscriber). Will this setup work, or there are some limitations ?

SQL 2005 Merge Replication : sysmergepublications unique index 'nc1sysmergepublications' fails

I've got one SQL 2005 SP2 server setup as Publisher and distributor which replicates through Merge pull subscriptions on SQL 2000 SP4 machines. Some of these SQL 2000 machines does republishing too. However, after a columndrop on one of the articles at the SQL 2005 machine by using sp_repldropcolumn the whole subscription sync came down on me with failures all over. All had the same error :

"Cannot insert duplicate key row in object 'dbo.sysmergepublications' with unique index 'nc1sysmergepublications'"
This procedure was called by sp_MScreateglobalreplica.

I have found that there are invalid publications in sysmergepublications, but only on publishers for the SQL 2000 subscription machines(excluding valid republishing publications). Deleting these pubID's does not help at all. With the first sync of any pull subscription (after the delete) they re-appear on the SQL 2005 machine.

However, I need to get the pull subscriptions going again. How can I clean the system tables to prevent any errors like above or duplicate keys in the sysmergesubscription table... Please do not recommend an overhaul. I need step by step procedures to get it going.

Merge replication setup with SQL EXPRESS 2005 on Windows 7 Professional


Hi all,

I have a web app that runs on local and use merge replcation using sql server 2005 standard on the server and sql express 2005 on clients on XP. As you know, XP Pro laptops are getting hard to find. We are trying to change to Windows 7 Pro x64. I installed SQL Express 2005 and I don't see a subscription on "Sync Center" after I created local subscription using SSMS. I normally see the subscription in Synchronize in XP Pro right away. 

I googled about this issue and not really have a good solution. There is one link saying Windows 7 not supporting Express 2005 anymore. http://blogs.msdn.com/b/repltalk/archive/2010/03/24/using-windows-synchronization-manager-on-x64-to-synchronize-replication-revised.aspx 

If so, I would like to know what would be a solution such as can I use Sql Express 2008 R2 instead of Sql Express 2005 for client even if the server is still SQL Server 2005 Standard edition. We are planning to upgrade, but we need to hire 5 to 10 people now. My boss does not want us to keep using XP Pro. 

Any suggestion would be appreciated.



SQL Server 2005 Replication - Replicated Databse Performance Issue




I am struggling to work out a very frustrating replication issue and wondered if anyone could help me.

Here is my situation.


I have a virtual machine with 16gb RAM and 4 quad core 3Ghz processors and 250gb disk. This has Windows Server 2008 64 bit installed.

The machine has a SQL Server 2005 64 bit instance installed with SP3.

I have setup a publication on this SQL Server called pub1 , this publication publishes all tables/sp's /views and UDF's . I have set the article properties for the published tables as below.

- Copy Check Constraints = True

- Copy Clustered Index = True

- Copy Non Clustered Index = True

- Copy Default Value Spec = True

- Copy Collation = True

- Copy Insert, Update and Delete = True

- Copy unique key contraints = True

- Copy XML Indexes = True

Every other setting is set to false.

I have created a subscription to pub1 within the same SQL Server instance. The database has replicated everything I require without an issue.

If I run a query against the databse that I have published, it returns the query in less than 10 secs. If I run the same query against the subscription DB then this query takes over 30 mins to return.

I thought this was an issue with the indexes not replicating. From what I have selecte

SQL 2005 merge replication error


We have one customer that has two subscribers with the one distributor. there are three publications being merged. two of them are fine, but one publication is receiving an error:

The merge process was unable to access row metadata at the 'Subscriber'.

it seems to be from one table because the next error is for that table and to check the permissions. i know they are correct because i can access the table fine from SSMS.

Does anyone have any suggestions to start debugging this?



SQL Server Mobile 2005 Merge replication failing over SSL, but can browse through pocket IE.


Hi All, this system is scheduled to go live in near to a day... but i have hit an issue when switching on SSL.  Any help would be greatly appreciated.


Device Env:

Windows Mobile 5.0 + Messaging and Security Feature Pack

SQL Server Mobile 2005.


Merge replication works fine without SSL.  After running the Configure Web Sync wizzard to setup SSL merge replication no longer works.  Note i checked using pocket IE by browsing to https://mycompany.com/pub/sqlcesa30.dll?diag and all worked fine.  There was no prompt saying the cert was bad etc.


The certificate is a:

Equifax secure global ebusiness CA-1 cert.

Signature algorithm md5RSA

public key RSA(1024 bits)

thumbprint algorithm sha1


Stacktrace given is:

-  ex {"A request to send data to the computer running IIS has failed. For more information, see HRESULT."} System.Data.SqlServerCe.SqlCeException
-  Errors {System.Data.SqlServerCe.SqlCeErrorCollection} System.Data.SqlServerCe.SqlCeErrorCollection
  Count 1 Integer

sql 2005 1 publisher and 2 subscribers for merge replication



We have 3 servers using sql 2005 having merge replication setup. If we use 1 publisher and 2 subscribers approach, can this replication topology handle single point of failure(i.e. when publisher server is down or not available, can the remaining 2 subscribers synchronize with each others?). If this approach cannot handle single point of failure, is there any other replication setup that can handle single point of failure and able to resolve conflicts for 3 servers using sql 2005?

Thanks and regards,



some questions about 2005 merge replication



We have 2 servers with 2005 merge replication setup and we have some questions about it:

1.) The database grew up about 55GB data(since most of them are binary image data) and log alternatively and we want to change from Full recovery mode to Simple recovery mode to reduce log size. Do we need to drop and create replication again or simply change the recovery mode is enough? Any other procedures we need to follow?

2.) The snapshot(run once about 2 weeks) job run about 3 to 4 hours. As this job drew all the resources while it's riunning and severely affect other transactions, we want to reduce it's running time. How about rebuild the index for replication system tables(e.g. msmerge_content) and how much gain by this action? Any other suggestions?

Thanks and regards,


MERGE Replication between SQL Server 2005 to SQL Server 2008 R2


Hi, i have a publisher with 3 publications with below SQL

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)   Nov 24 2008 13:01:59   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

I want to add a subscriber to the server with below SQL

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

Now when i am trying to add subscritpion to the 2008 R2 server on the publication server, i am getting below message

TITLE: New Subscription Wizard

For merge publications, the version of the Subscriber must not exceed the version of the Publisher.



Is merge replication not possible between the 2?? Please advice. I beliwve Microsoft's new releases should support its older versions...

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