Replicating identity column

Question

Replicating identity column

Sanjay Pathy

Newbie

Points: 8
More actions
July 7, 2005 at 10:25 am

#167446

Hi folks,
       I have an issue which im not able to figure out why is this happening. the problem i'm facing is.
       I have transactional replication setup on 2 servers say sql1 and sql2. the production server is sql1 where all the transaction happens and meanwhile it gets replicated on sql2 which im using as a standby database server. sometimes when replication happens i see an error "Violation of PRIMARY KEY constraint 'PK_USER_CITY'. Cannot insert duplicate key in object 'USER_CITY'." I have the pk setup as yes(not for replication) on identity column. when i check the id on the subscriber it generates as some "8769854" code and tries to put the same id for the next record too, but the actual code on the publisher table is "289".
      To get rid of this what i do is i reinitialize the subcription and then it works fine. but this happens sometimes. Is there a way that i can sort this out? any help would be appreciated.
thanks!
sanjay

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

does this happen after a certain event? perhaps after a failed write to the table?

i'm guessing you're using identity columns? - if i knew 2 years ago what i know now about replication i'd have used GUIDS all the way through.

MVDBA

Sanjay Pathy Newbie Points: 8 More actions · Answer 2

Micheal,

Yea im using identity columns. i dont know at what event this happens but one thing for sure i know is that its generating some strange number which doesnt make sense either. but i wanna know the reason why its happening and is there a way that i can resolve this.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

i'd try running a profiler trace and look at the sql either side of the error.

unless this only ever occurs once in a blue moon?

have you tried using merge replication instead?

MVDBA

Ray M SSC-Insane Points: 21093 More actions · Answer 4

I have used identity columns sucessfully in transactional replication with no issues of this happening, except when some joker is goofing around in the subscriber destination database.

Is there any activity in the subscription database other than reads?

Triggers etc?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

good point - have you missed disabling a trigger?

MVDBA

Sanjay Pathy Newbie Points: 8 More actions · Answer 6

there is no activity happening on subcription im just using that as a standby unless sql1 goes down. i wouldnt be using for anything. and i dont have an triggers on that table either.

should i customize the replication procedure with

set identity_insert "tablename" on.. will this work?

Sanjay Pathy Newbie Points: 8 More actions · Answer 7

This is the actual record on publisher

115,285,16,7/6/2005 1:21:20 PM

and when this is getting replicated on the subcriber it goes as

872516, 285, 16, 7/6/2005 1:21:20 PM and again the id in the identity column would be the same for the next record

872516, 286, 17, 7/6/2005 1:21:20 PM this is when i get that error.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 8

What does

DBCC CHECKIDENT (<tablename>, NORESEED)

return?

Sanjay Pathy Newbie Points: 8 More actions · Answer 9

This is what it returned.

Checking identity information: current identity value '839674091', current column value '839674091'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

what does this mean?

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 10

it means the next identity value that will be assigned on the column will be

"839674091" this value is higher than the value you checked "8769854", so it should be ok.

In your original post you error is violation of "PK_USER_CITY", this city is a number?

Sanjay Pathy Newbie Points: 8 More actions · Answer 11

PK_USER_CITY in this city is the city table pk, and this "839674091" is the USER_CITY pk value

Sanjay Pathy Newbie Points: 8 More actions · Answer 12

even today the same error occurred "Violation of primary key"

when i ran DBCC CHECKIDENT (<tablename>, NORESEED) this is the result it through out..

Checking identity information: current identity value '116', current column value '935674381'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

i dont understand how is the column value changing..

Paul Williams SSCommitted Points: 1930 More actions · Answer 13

I may have missed this in your post, but are you actually replicating the identity col. as an identity col.

If so, question is why? Default is not to do this, and as a standby server I don't believe you would need too. You can still replicate as a PK, but by not selecting to replicate as an identity, then the value on the subscriber would just be an int, that is still unique, and is identical to that on the Publisher.

Paul R Williams.