|
Hi pundits, I recently created a new table and added as an article to an existing merge replication(Running on SQL Server 8, Have 6 subscribers). The table article is having an identity column(Primary Key) and is set to auto-identity management. Last day we stumble into an error "Primary Key Violation" but It is weird to see this error when the tables identity specification was set to “Yes (Not for Replication)”. The surprise is that All subscribers had the same identity range. Usually Publisher would have taken care of the check constraint and the ranges. I restarted the merge agent and executed sp_adjustidentityrange at the publisher to check whether the identity range was updating, but it was of no use, Identity range remains the same for all subscriber. So i assigned new unique range of values for Identity check constraints for each subscribers manually & i updated current identity value for that table at each subscriber(I know its a temp fix only, coz i left with no options!.) But that sounds the same as that of managing identity column manually!. I am surprised to see the same identity range for all subscribers, Why it happens? Thanks in advance. Anuj
(comments are locked)
|
|
Anuj, the BOL page Replicating Identity Columns states that you have to setup the ranges for each subscriber. If identity range management is set to automatic the publisher will then manage these ranges, so that if a subscriber uses up its range, the publisher will assign it a new one. The subscribers should have "Yes (not for replication)" set for the identity ranges according to the book. Thanks for your fast response william! But I had set Auto-Identity management for my identity column and left the publisher and subscriber & left the ranges as default, that doesn't updated my 6 subscribers with unique Identity ranges. All the subscribers had the same Identity Range.
Dec 15 '10 at 11:27 PM
Cyborg
No, you need to initially set the ranges, after that the system looks after itself.
Dec 16 '10 at 12:11 AM
WilliamD
(comments are locked)
|
