Merge Replication - Identity Management (PK Error)

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.

more ▼

asked Dec 15, 2010 at 10:59 PM in Default

Cyborg gravatar image

10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest


the BOL page [Replicating Identity Columns][1] 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.

[1]: http://msdn.microsoft.com/en-us/library/ms152543.aspx
more ▼

answered Dec 15, 2010 at 11:13 PM

WilliamD gravatar image

25.9k 17 19 41

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, 2010 at 11:27 PM Cyborg
No, you need to initially set the ranges, after that the system looks after itself.
Dec 16, 2010 at 12:11 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 15, 2010 at 10:59 PM

Seen: 1745 times

Last Updated: Dec 15, 2010 at 10:59 PM