Primary Key Conflict Resolution

Hello,

I'm running into problems with my replication where I get the following error:
Violation of PRIMARY KEY constraint

I know what the error means, and I know what is causing it. In my case, a property is being added to an inventory item independently at the publisher and subscriber end. Each available property has a particular ID, each inventory item has its own unique ID, and of course the properties per inventory item are stored in a linking table. I am using UUIDs for the inventory items, to avoid collisions in that aspect, but the list of properties is fixed (currently only 15 - 20 available properties), so it doesn't make sense to me to have managed ranges, UUIDs, or other such things for the properties. Of course, I could apply a "source ID" to each added property to avoid these collisions, but I'd prefer not having to redesign the database, not to mention deal with the extraneous copies of properties.

My preference would be to simply have the server delete the copy on the server and take the subscriber row. I would have thought that using the "subscriber always wins" conflict resolver would have this effect, but it doesn't work for me. Is there a straightforward way of dealing with this problem? Am I missing something obvious? I've looked into a custom conflict resolver, but that seems like overkill for what must be a fairly common scenario.

For the record, the publisher in my case is SQL Server 2005, and the subscribers are SQL Server Mobile clients.

Any advice would be greatly appreciated!

Thanks,
Adrien.

Adrien,

In your case, "subscriber always wins" conflict resolver won't work for you because although you have inserted at both publisher and subscriber, they are treated as different rows with different rowguid. I think what you can do is just ignore that failure since your subscriber row will be rolled back if you set @.compensate_for_errors='true'. Or you need to make sure only one side insert into property table, and make sure the other side gets it, then both side can insert into the linking table which refers to the property table.

Hope it helps

Wanwen

|||Wanwen,

This "compensate_for_errors" property does exactly what I needed. I've tested causing PK collisions on purpose, and it seems to do more or less what I'd expect, and gets rid of the errors.

Thanks!

Adrien.