Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Bottom line question:

How can I insert data into subscriber tables that are governed by identity range management, where the data has valid identity values that fall outside of the current range?

Details:

On SQL Server 2008 R2, I changed from transactional replication with updateable subscriptions to merge replication. Replication has been down for 3 days, during which time users have continued to change data on subscriber tables.

I now have merge replication set up and running successfully. The aforementioned data was not included in the initialization snapshot because the publisher did not have it. So now I need to insert this data into various tables on each subscriber.

The identity values of some of the data rows fall outside of the new ranges that merge replication publisher set for the the subscriber tables.

Changing the identity values to work within the new ranges is not an option. Is there a way I can disable the constraint that is enforcing identity value range, insert my records that have identity values outside the range, then enable the constraint again? Or perhaps another solution I'm not considering?

I am confident (mostly) that these out-of-range values will not conflict with existing identity values in the snapshot.

Example:

Table tArea has a current identity value of 2221005.

DBCC CHECKIDENT ('tArea')

Current identity value '2221005', current column value '2221005'.

tArea identity range:

sp_helpconstraint 'tArea'

([id]>(2221005) AND [id]<=(2222005) OR [id]>(2222005) AND [id]<=(2223005))

Here's one of the row inserts I need to do:

SET IDENTITY_INSERT [dbo].[tArea] ON
INSERT INTO [dbo].[tArea] ([id], [name], [organizationId], [isActive]) 
  VALUES (603, N'Integration', 100, 1)
SET IDENTITY_INSERT [dbo].[tArea] OFF

Resulting error:

The insert failed. It conflicted with an identity range check constraint in database '', replicated table 'dbo.tArea', column '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.

I would like to insert this row then go back to the current identity range constraints.

I have found much information here and on other sites that detail merge replication and the inner workings of identity range management. However, I can not find any information regarding the insertion of valid records into identity-range-managed tables where the records identities fall outside of the currently active range. Any thoughts or suggestions would be greatly appreciated.

share|improve this question

migrated from stackoverflow.com Oct 23 '13 at 3:15

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.