x

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.

Anuj
more ▼

asked Dec 15 '10 at 10:59 PM in Default

Cyborg gravatar image

Cyborg
10.6k 36 39 45

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

1 answer: sort oldest

Anuj,

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 '10 at 11:13 PM

WilliamD gravatar image

WilliamD
25.8k 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 '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)
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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x293
x22
x2

asked: Dec 15 '10 at 10:59 PM

Seen: 1496 times

Last Updated: Dec 15 '10 at 10:59 PM