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

    Wednesday, October 05, 2011 9:17 PM

All replies

  • Is this an identity key? If so you may need to change the current value on one side so you don't get collisions, ie run dbcc checkident(tablename,reseed, 1000000)

    Also make sure you have created your identity columns as not for replication.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, October 05, 2011 9:49 PM
  • Is this an identity key? If so you may need to change the current value on one side so you don't get collisions, ie run dbcc checkident(tablename,reseed, 1000000)

    Also make sure you have created your identity columns as not for replication.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941


    Thank you for your answer.

    No, I am not using identity key :-)

     

    Best Regards
    Bjerner

    Thursday, October 06, 2011 6:00 AM
  • Hi,

    I also have the same problem. Using transactional replication with updateable subscription.
    1.The update was made from the subscriber and 2. The job executing from the publisher. We are using IDENTITY columns and they are set not for replication.

    I checked the max(ID) from the publisher and I have = 333262
    I checked the max(ID) from the subscriber and I have = 333262

    I checked the current ident from the publisher and I have  = 22202
    I checked the current ident from the subscriber and I have = 358001

    Here is the Identity Range Management for this specific table.

    -Automatic management of identity
    - publisher range size: 10000 (that is the default)
    - subscriber range size: 1000 (that is the default)
    - range threshold: 80
    - next range starting value: 359000

    Thanks,
    Lpang


    Luanne


    • Edited by Saburo Thursday, February 16, 2012 11:45 AM
    Thursday, February 16, 2012 11:30 AM
  • What is the error message you are getting?

    Also - updates should not cause identity range problems - unless  a trigger is involved.

     

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, February 16, 2012 1:57 PM
  • I was getting the error below same with bjerner above.

     "Violation of PRIMARY KEY constraint 'PK_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'. (Source: MSSQLServer, Error number: 2627)".

    But this is fixed now. I reseed from the publication.

    Thanks,

    Luanne

    • Proposed as answer by Saburo Friday, February 17, 2012 2:59 AM
    Friday, February 17, 2012 2:59 AM
  • Hi,

    Could you please provide the remedy for above error,because I am getting same error in my SSIS job.

    Thanks in Advance,

    Santosh Y


    Santosh Y

    Monday, March 05, 2012 1:52 AM
  • Guys,

    can you please check and confirm if there is really an offending record ? you can do this via

    1) SSMS -> SQL Server -> Replication -> Publication -> View Conflicts (Conflict Viewer )

    Alternatively, you can also do

    2) Open Replicatoin Monitor -> Subscription Watch list -> Distributor to Subscriber History -> Synchronization with erros

    and post the error message/ details here

    hth


    Hemantgiri S. Goswami | SQL Server Citation| Surat SQL Server User Group

    Monday, March 05, 2012 4:52 AM