September 21, 2004 at 3:29 pm
Hello everyone. I have a publication that is set up to automatically manage identity values. When I get the message telling me that I cannot insert a row because it would violate the primary key on either the publisher or subscriber, what is the recommended procedure to fix this?
September 22, 2004 at 6:44 am
Paul,
Have you got the 'not for replication' option set on your identity columns? If not then this would give you this error.
Another option would be if you have inserted data with Identity Insert On eg via a DTS data import. This would also do the same.
Alternatively have you just run out of the assigned identity range on that machine (taking into account the margin?). Try a synchronise and see if that reseeds the identity value.
Regards
David Saville
September 22, 2004 at 9:37 am
I've had merge replication die on me like this before. Your identity ranges are simply set as constraints on the tables. When your range needs to be updated, the constraint gets updated. I've had situations before where the next available identity value was not within the assigned identity range. In my particular case, it was a bug in sql server itself, and after many support calls to microsoft, they said this wouldn't be fixed until at least Yukon.
If it comes down to it, you might have to drop and recreate replication (yes, delete all the subscription info and the publisher info) and recreate your publisher and ranges. I guess, only do that as a last resort, but check to see if your next identity value is outside of the table constraint range.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply