Set Identity_insert on - Merge Replication - SQL Server 2005

Question

I have merge replication set up between two databases and am using identity ranges on both. I want to add a specific row to a merged table (setting the identity value to something outside of the identity range) on the publisher. When I try this, I get the following error.

The insert failed. It conflicted with an identity range check constraint in database 'xxx', replicated table 'dbo.yyy', column 'yyy_id'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Is there a way to force specific identity value into a merge replicated table that is using identity range management?

Answer 1

One way is to ensure that each node in the replication topology is using a different range of identity values, so that duplicates do not occur.

For example, the Publisher could be assigned the range 1-100, Subscriber A the range 101-200, and Subscriber B the range 201-300. If a row is inserted at the Publisher and the identity value is, for example, 65, that value is replicated to each Subscriber. When replication inserts data at each Subscriber, it does not increment the identity column value in the Subscriber table; instead, the literal value 65 is inserted. Only user inserts, but not replication agent inserts cause the identity column value to be incremented.

Ref. Replicating Identity Columns