none
Duplicate key inserting row to subscriber RRS feed

  • Question

  • Greetings, have transactional replication set up and has been working for over a year now. Publisher and distributor on same SQL 2008 R2 with the publication database still 2000 (all in the process of upgrading). Multiple subscribers, they are 2008 R2 servers and databases. A publication was added 4 months ago, has 3 tables in the publication, 2 type tables and one detail table, the details table has been adding rows from the publisher to subscribe since it was set up, no problem. Yesterday we inserted a row into one of the type tables on the publisher and started getting an error "Error executing a batch of commands. Retrying individual commands. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object" and the undistributed commands started piling up. Eventually I initialized this subscription to "fix" it and get the row over there. I have a test environment set up and I am getting the same error there when I go through the same steps.  Its a simple insert statement, table has a primary key of a char(1) and another column char(60). if I insert the row to the publisher it works fine on the publisher, but it gets a dup pk on the subscriber and the row is not there on the subscriber when I run a select. Ultimately the distribution agent gets hung up with the "Error executing a batch of commands" and "Violation of PRIMARY KEY constraint Cannot insert duplicate key in object" and continues retrying. I can run a trace and see all this on the subscriber, I am at a loss where else to look to resolve. It seems a straight forward error, any suggestions? Thanks.
    Wednesday, October 16, 2019 7:45 PM

Answers

  • The most frequent cause of this is having the same article published twice in the same publication, or in different publications going to the same subscriber.

    It can also be caused by triggers which are not marked as not for replication on the subscriber. 

    • Marked as answer by Abeljdang Thursday, October 17, 2019 1:11 PM
    Thursday, October 17, 2019 10:58 AM
    Moderator

All replies

  • Hi Abeljdang,

     

    >>Error executing a batch of commands" and "Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

     

    Would you please check if your replicated table has an identity column? Check if you are replicating identity columns which can cause "duplicate key" errors, and primary key collisions.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, October 17, 2019 3:20 AM
  • The most frequent cause of this is having the same article published twice in the same publication, or in different publications going to the same subscriber.

    It can also be caused by triggers which are not marked as not for replication on the subscriber. 

    • Marked as answer by Abeljdang Thursday, October 17, 2019 1:11 PM
    Thursday, October 17, 2019 10:58 AM
    Moderator
  • That is exactly what it is, this table had apparently been around for some time and was in another one of the 40 publications on this server. I was wondering if there would have been any other way to figure that out as it just appeared to be a duplicate key error with no other indication? I had looked at syspublications/sysarticles/syssubscriptions but failed to group it by article that would have given me a clue. Thanks for the info will mark that as the answer Hilary.
    Thursday, October 17, 2019 1:11 PM
  • no identity just 2 columns on the table, turned out to be that this table was in another publication causing the duplication.
    Thursday, October 17, 2019 1:12 PM
  • I do this:

    select pubid, name, count(*) From sysarticles 
    group by pubid, name
    having count(*)>1

    Monday, October 21, 2019 3:38 PM
    Moderator