PK error with Merge Replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

PK error with Merge Replication

I have these two servers, Server A and Server B. Their respective sql servers have a copy of the same database.These two servers work in a "round robin" manner. To synchronize the databases we use merge replication with the merge agent "working" every ten minutes. Lately, I’ve been getting these error messages in the application that
I’m running; "VB Description: Violation of PRIMARY KEY constraint ‘PK_ShippedSpecs’.Cannot insert duplicate key in object’ShippedSpecs’." Should the merge agent run "continuously" in order to avoid this problem or should I start looking in the application to fix this problem?
What is the level of service pack on SQL? 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. 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. I believe this may be fixed in SQL SP4 soon. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>