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
asked Dec 15, 2010 at 10:59 PM in Default
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.: http://msdn.microsoft.com/en-us/library/ms152543.aspx
answered Dec 15, 2010 at 11:13 PM