Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

Wednesday, January 7, 2009(copied from: http://bassplayerdoc.blogspot.gr/2009/01/fixing-primary-key-constraint-issues-in.html) 

Fixing PRIMARY KEY constraint issues in SQL Server 2005 Replication

I must admit that I had to stay away from replication for quite a while since most of the work I've done in my previous job has a lot to do with disaster recovery. Nonetheless, I'm presented with new challenges every single day which I need to face. I got notified for an error regarding a transactional replication issue between two SQL Server 2005 instances. The error went something like this

Replication-Replication Distribution Subsystem: agent INSTANCE-db-Publication-SUBSCRIBER_INSTANCE-21 failed. Violation of PRIMARY KEY constraint 'PK_Whatever'. Cannot insert duplicate key in object 'dbo.table1'

The error message simply says that replication between the publisher and the subscriber could not push thru due to an existing record in the subscription that is causing a violation of the PRIMARY KEY constraint. Either a record is manually inserted in the subscriber or its just an annoying application which is not properly configured. Here's how you can fix this

1. Open Replication Monitor and check for the publication that is causing the error. You should see some error messages in the details that display something about the transaction sequence number, publisher id and command id values
2. Next, you retrieve the command associated with the transaction sequence number using the system stored procedure sp_browsereplcmds. This will return a result set of the replicated commands stored in the Distribution database at the Distributor. The query should look something like this

EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x000B5103000043B7000700000000' ,@xact_seqno_end = '0x000B5103000043B7000700000000',@command_id =2 , @publisher_database_id = 1
This will give you an idea on the possible INSERT/UPDATE/DELETE statement that is inside this transaction. Based on the error message, we are looking at either an INSERT or an UPDATE statement.
3. Next, run a SELECT query on the subscriber instance against the table specified in the subscription passing the values you retrieved from the sp_browsereplcmds system stored procedure.
4. If the record already exists, simply delete the record since it will be written over by the replication (of course, this is not always the case and would depend on your business requirements)

This is just an overview of what you can do when you see such errors in replication. There are a few more articles and tips to help you get by from this website by Paul Ibison