none
Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object...

    Question

  • Hi all I set up transactional replication between a sql 2008 and sql 2005 server.  I have a table with an identiy column and a varchar field. I accidentally SET IDENTITY INSERT ON at the subscriber instead of the subscriber server and inserted a row. After that every row I inserted in the publisher has not be replicated to subscriber? I see this error message in the replication monitor?

    Violation of PRIMARY KEY constraint 'PK__IdentTes__32149A135070F446'. Cannot insert duplicate key in object 'dbo.IdentTest'

     

    I tried deleting the row at subscriber but still the same message?

    Thanks

    Wednesday, May 18, 2011 3:15 PM

Answers

All replies

  • You will likely need to modify the identity column on the subscriber to have the NOT FOR REPLICATION Attribute.

    Then you will need to issue a call to

    dbcc checkident(TableName, reseed, Value)

    and make value something less than the current value that the replication process is trying to insert. Use profiler to figure out what this value is.

    I would also remove the row that you inserted

    Setting set identity_insert tablename on is only valid for the particular query session you are running. Other processes will not be affected by it.


    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
    • Marked as answer by SQL_Jay Wednesday, May 18, 2011 6:02 PM
    Wednesday, May 18, 2011 3:35 PM
    Moderator
  • Thanks Hilary, I created the column as NOT FOR REPLICATION  I also ran the dbcc checkident Let me see if it works.

    Thanks

    Wednesday, May 18, 2011 3:58 PM