none
SQL Server 2005 bidirectional replication fails on primary key constraint

    Question

  • I have set up a replication as described at http://msdn.microsoft.com/en-us/library/ms147929.aspx

    But sometimes the replication fails and says "Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)".

    I did just set up a new replication yesterday. After setting the replication up, I inserted some old data on server 1, and I checked that all data was replicated to server 2 without any problems. But later, when some data was inserted in some tables on server 2, the replication stopped because of the primary key constraint error message. And the error came from a table that wasn't updated after the initial insert of old data, so I don't understand why this error did occur. It sounds like some loopback issue, but I did set loopback_detection to true, so I don't understand if this could be the case.

    Any suggestions on what could be wrong?

    Are there any way to set up a general ignore or overwrite property if a primary key constraint should occur again?

     

    Further it seems that I can't get it correct running again without setting up all the replication settings from scratch again.

     

    Best Regards
    Bjerner

    mercredi 5 octobre 2011 21:17

Toutes les réponses