none
Not retaining Identity in SQL Server Merge Replication

    Question

  • Hi,

    I have implemented Merge Replication in SQL Server, but here I am getting one problem-

    After implementing replication it is not retaining next identity for identity column.

    Example-

    Publisher Table- (tab_Emp)

    Id (Identity field) 

    1

    2

    3

    After Replication Subscriber Table - (tab_Emp)

    Id (Identity field) 

    1

    2

    3

    Now, when I am checking for next Identity value in subscriber table then it is -1,  it should be 4.

    Please suggest me possible solution to replicate identity with next value.

    Thanks in advance.

    Monday, June 10, 2013 7:10 AM

All replies

  • What is the identity management for the Publisher? Try to run DBCC CHECKIDENT ('<tablename>') on the subscriber and check.

    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Monday, June 10, 2013 7:53 AM
  • Hi, Thanks for your replay.

    Actually I didn't implemented any identity management because my database is already created and it contains more than 150 tables. Previously I don't have any plan to replicate the database, but now I want to replicate it to subscriber.

    So, I have replicate it and I got same data like publisher but in subscriber identity value is not retaining like publisher. for Identity column next identity is coming as 1.

    So, when I am trying to insert new record in subscriber then I am getting Primary_Key constraint error because primary key 1 is    already present is table.

    Please suggest possible solution.

    Thanks

    Monday, June 10, 2013 10:17 AM
  • Hi,

    Check whether 'NOT FOR REPLICATION' is enabled for the table in the publisher


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.

    Monday, June 10, 2013 12:38 PM