Suppose we have the publisher adds a record with a PK = ‘1’ and a subscriber also adds a record with a PK = ‘1’ and then they synchronize.
As far as merge is concerned these are different rows because they have different rowguid values.
The synchronization tells us there is an issue. No error recorded but we can see something is wrong:
So next we check the conflict viewer and see more info there;
You have 3 options in the conflict viewer;
Which one would you use to fix the issue? Each one removes the conflict from the conflict monitor. However – the next synchronization will simply recreate the conflict! A PK-PK conflict like this cannot be solved using these tools. You need to either update the PK value on one node or delete one of the rows, and then sync up. If there are loads of FK records hanging off this record this can become a huge pain as you’ll need to tackle these first. In fact the longer you leave it the bigger the problem becomes. So – keep an eye on the conflicts and resolve them ASAP. Alternatively add another column and use a composite PK to avoid the conflict in the first place :).
2 thoughts on “Merge Primary Key Conflicts – how do we fix them?”
I’ve written a small article some time ago on solving type 5 and 6 conflicts over at sql server central: http://www.sqlservercentral.com/scripts/Merge+Replication/138570/. You may find it helpful. We have these conflicts sometimes (because of some design errors in the software/business processes) and need them resolved a.s.a.p. as you indicate correctly. The published script helps me fixing them fast by deleting the row in the subscriber, after which the copy from the publisher is pushed into the subscriber. A supporting tip is to specify ‘replication=true’ on the additional connection properties tab in the connection dialog in SSMS. This lets you delete rows even though for exmaple a referencing FK (not for replication) exists, exactly like the merge agent can delete it. This can save a lot of hassle resolving these conflicts. And tip 2: if you only have a type 5 conflict, you can’t push the row from the publisher into the subscriber by choosing ‘select winner’ in ConflictViewer. In this case execute sp_mergedummyupdate ”, ” in the publisher database to have the publisher’s copy replicated into the subscriber.
Do you have experience with type 10(download update failed) conflicts too? I’ve had a number of these in the past, and was able to identify the cause for those and fix the conflict. But lately I have one subscriber that for unknown reasons gets this type of conflict without giving any details on what went wrong. All msmerge_conflict_info says is: “‘.’. This failure can be caused by a constraint violation. The merge process was unable to synchronize the row.” And that’s it! I’ve found, using profiler to trace the statements executed, that the merge agent generates an incorrect call to the update procedure, the literal value scripted for some datetime column in the published table has an incorrect format “YYYY-MM-DD HH:mm:ss.nnnnnn”, the problem is in the 5-digit fraction, which is supposed to be 3 digits only. It does this only for some rows, not for all… I have no more clues where to look, so if you’ve got some hints, that would be very welcome…
HI Richard – thanks fo rthe link – I’m including the comment here so others can see it. I haven’t seen the date issues you report – if I come across them I’ll report here.