Set Identity_insert on - Merge Replication

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?

Geneticist

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

Executory

Thanks Mitch, but in the situation I am dealing with, customers are actively entering records in both the pub and sub which are getting assigned Ids. There is a specific ID I want to insert into the pub (lower than current Range). but I want custs who are adding to the pub to add within the range. – Geneticist

Answer 2

I have resolved by increasing Automatic insert by increasing 20000 row . Helpful blog added here

Washedout