Merge Replication -Identity Values

• rangark

  Right there with Babe

  Points: 738

  More actions

  April 2, 2007 at 10:20 pm

  #93075

  We are facing the following problem during merge replication. We have about 50+ databases which are merge replicated.

  In one of our tables (P_MLC) insert happens at both the subscriber and publisher end. This table (P_MLC) has a sl_no (int,Identity) has the primary key  .We had setup the identity to generate odd/even at subcriber/publisher end respectively.

   Part of the functionality, user could also delete the records from this table (P_MLC).

  Once deleted the records from (P_MLC) are stored in backup table(P_MLCARC). We noticed that in the back table  (P_MLCARC) we are getting primary key violation. This backtable has no identity column. looks like since the main table (P_MLC)-insert/delete happens very frequently, system could not maintain the identity values ?. Is there a workaround where we can ensure that (P_MLC) table retain proper identity values.  This backup table (P_MLCARC) is also part of the articles published for Merge replication.

  Incidentally we have never come across primary key violation in the main table (P_MLC)

  Looking foward to forum members views on how to handle this situation

  thanks

  rangark

• Graeme100

  SSCrazy Eights

  Points: 8054

  More actions

  April 17, 2007 at 5:04 am

  #700882

  Hi,

  So it sounds like your archive table has a primary key on it and I assume this is the same as the primary key in the production table.

  Are you having any identity inserts that could be occuring ?

  Once a record is deleted, it's Identity is not reused unless the table is truncated or there is an identity insert ??.

  Do you need the pk on the archive table if it is just used as a record of deletes?

  Graeme