A Different Merge Replication / Primary Key Error

Question

A Different Merge Replication / Primary Key Error

Dan Hickman

Valued Member

Points: 62
More actions
December 9, 2004 at 8:18 am

#165600

I have a C# WinForms application which uses merge replication and MSDE to sync up laptops with a central SQL Server 2000 database. The primary keys have always been set to "Not for Replication", but after experiencing data conflicts when syncing I set all foreign keys to "Not for Replication" as well. This took care of the conflicts, but soon afterwards users started receiving the following error on inserts:

"Violation of PRIMARY KEY constraint 'PK_mytable'. Cannot insert duplicate key in object 'mytable'.

The error occurs right when the INSERT stored procedure is run on the local MSDE database, not when the actual merge occurs. The primary key is an Identity field, so I'm at a loss to explain why this is happening, as it should automatically pick a valid id. Interestingly, the users can try the insert 2 or 3 more times and eventually have success.

Any ideas why this error is happening? I'm relatively new to merge replication, but I tried to put all the relevant facts in this post. Thanks in advance!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

rangark Right there with Babe Points: 738 More actions · Answer 1

The problem happens when a transactions is rolled back on the table where you have the Identity column has the primary key. The identity value does not get resets. When such unused identity values are generated, you get this Pkey violation error message.

You can check this by using dbcc checkident, the current identity value and current column should same. It would be different when such rollbacks happens.

You can reseed these DBCC CHECKIDENT -reseed

This wil be able to solve the problem you are facing

ranga

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Could it be that you do not have identity ranges set for each of your databases? You should specify a range of identities per database to ensure that inserts from one database do not use the same identity values as another database (which would cause primary key violations). By setting "Not for replication" (which you have correctly done), you ensure that replicated values from one database do not cause a reseeding of the identity values - but you must ensure you have the ranges set for identity values.

robertk SSCommitted Points: 1608 More actions · Answer 3

Yes, when you initially create the publication (I do not believe you can do this after the fact), you have a list of articles you can add to the publication. Go into the details for the article and set up identity ranges. This will probably eliminate the problem entirely. As far as what the range values should be set at, that's up to you. Estimate a day's traffic and start from there, I guess.

Dan Hickman Valued Member Points: 62 More actions · Answer 4

Yes, we have ranges set up already, but thanks for the advice.

Actually, we finally found the answer by calling Microsoft directly. It is a known (but not public) bug that occurs when you drop and recreate the publication. The fix will be in SQL Server SP4, whenever that comes out. Here's the word, straight from the multi-billion-dollar horse's mouth:

In your situation, the primary key violation was caused by a known issue in SQL Server merge replication. When you drop an article and re-add it to the publication, it does not keep accurate track of any higher values that came in from subscribers and when it reassigns the identity ranges there may be conflicts. This won't happen every time.

The workaround is to run a CHECKIDENT reseed on each of the tables dropped and re-added. This will populate to the maximum value in the table and when the article is added back to the publication it will pick up from there.

This will be fixed in SP4 by automating that workaround process. There is still no ETA on when SP4 will be released, however.

robertk SSCommitted Points: 1608 More actions · Answer 5

wow, thanks a bunch. I guess that also covers the current merge issue I'm having, but I'll probably give them a call anyway. Maybe if enough of us have the same kind of error, Microsoft will realize that it's an important issue that should be fixed soon.

robertk SSCommitted Points: 1608 More actions · Answer 6

I was just told Q1 of 2005. It will be fixed in Yukon as well, which is slated to be released the end of 2005.