Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


transactional replication error in sql 2000 server: violation of primary key


transactional replication error in sql 2000 server: violation of primary key

Author
Message
bhavin123
bhavin123
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
Hello,
We have sql 2000 replication in our environment. recently, we have been getting error saying 'Violation of PRIMARY KEY constraint *sayabc_PK*. Cannot insert duplicate key in object *sayabc*. I am not quite good with replication technique. from error, I know its duplicate record have been inserted or found and that is why the distribution agent has stopped working. I know the temporary solution is I can configure dist. agent to be keep working on consistency errors found. Is there any other solution? also How I can find the duplicate record in replication database (more specific, where to look for duplicate record? publisher or subscriber?)

all the help/suggestions will be appreciated greatly.
thanks in advance
WolfgangE
WolfgangE
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 777
let's do it step by step.

Replication tries to copy a record from publisher to subscriber. If it tells you a PK violation one of the records to be transferred already exists on the subscriber. So you have too find the value on the subscriber!

I'm not sure of the error messages in SQL 2000 but I think it gives you at least the name of the stored procedure that failed. This way you know on which table the error occured (you know this too by the name of the primary key named in the error message).

How to find the primary key value?
Have a look again at your error message. PK violations usually contain the value that could not be inserted.

Another possibility: the error message should contain the xact_seq_nr or something similar. With this you can use the browsereplcmds-command on the distribution database to get the exact command that raises the error. This command should contain the primary key value.
Example:
exec sp_browsereplcmds @xact_seqno_start = '0x0003BB0E000001DF000600000000', @xact_seqno_end = '0x0003BB0E000001DF000600000000'



Next possibility: Use the profiler. As you already know the procedure that the replication executes create a trace that shows you the executions of this procedure. You should get the exact input parameters here.


If you finally found the PK value, what you do now?
As always, it depends.
If the table on the subscriber is only for read operations and is never changed just delete the record from the subscriber. Replication will insert it with all data of the publisher.
If you change the records on the subscriber you cannot just delete it as the changes done on the subscriber get lost. You could store the record on the subscriber, then delete it. After insert of the replication you could manually update it with the saved values.


Hope that helps.
corin.grieves
corin.grieves
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 59
We were plagued by this issue several years ago (SQL2K5 publisher, remote distributor, 25 transactional subscribers in a 24/7 OLTP setup), and it was a real pain as the data set size meant reinitializing all the subscribers took well over 24 hours, along with all the associated customer service issues with out-of-date data.

I also was suspicious about using the "Continue on data consistency errors" agent profile. However we tried one subscriber with the profile, and on the next few PK violation incidents I made sure to follow the procedure mentioned by Wolfgang to check the replication transaction that was at fault. In all cases the the data had been successfully delivered by a previous replication transaction, so there was no data inconsistency. I also used Data Compare to check the table contents and it was absolutely in sync.

The issue appears to be a bug in replication.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search