Merge Replication Fail with error : The insert failed. It conflicted with an identity range check constraint in database 'DataBase Name', replicated table 'TableName', column 'ColumnName'. 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.

Issue:-

sp_adjustpublisheridentityrange fail to increase the Range identity

---------------------------------------------------------------------------------------------------------


 Issue appears in Application log..

Installation failed: System.Exception: The insert failed. It conflicted with an identity range check constraint in database '<DatabaseName>', replicated table 'dbo.<TableName>', column '<ColumnName>'. 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.

The statement has been terminated. ---> System.Data.DataException: Error executing SQL command: INSERT INTO [PublishQueue] ( [ItemID], [Language], [Version], [Date], [Action] ) VALUES( @itemID, @language, @version, @date, @action ) ---> System.Data.SqlClient.SqlException: The insert failed. It conflicted with an identity range check constraint in database '<DatabaseName>', replicated table 'dbo.<TableName>', column '<Column>'. 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. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at

Problem:-

Bulk Insertion will fail, if you have Merge Replication Configured..

This problem arises when Merge replication is configured using Wizard on a table which has an identity column. In merge Replication by default, it comes as a settings  Automatic

as per BOL

• Automatic. Used for merge replication and transactional replication with updates at the Subscriber. Specify size ranges for the Publisher and Subscribers, and replication automatically manages the assignment of new ranges. Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber, so that only user inserts cause the value to be incremented at the Subscriber.

  Note
  Subscribers must synchronize with the Publisher to receive new ranges. Because Subscribers are assigned identity ranges automatically, it is possible for any Subscriber to exhaust the entire supply of identity ranges if it repeatedly requests new ranges.

• Manual. Used for snapshot and transactional replication without updates at the Subscriber, peer-to-peer transactional replication, or if your application must control identity ranges programmatically. If you specify manual management, you must ensure that ranges are assigned to the Publisher and each Subscriber and that new ranges are assigned if the initial ranges are used. Replication sets the NOT FOR REPLICATION option on the identity column at the Subscriber.
• None. This option is recommended only for backward compatibility with earlier versions of SQL Server and is available only from the stored procedure interface for transactional publications.

Resolution:-

Sometimes running this command issue will get resolve..

 Exec sp_adjustpublisheridentityrange  @@publication = '<PublicationName>'

and 

Exec sp_adjustpublisheridentityrange @table_ name = 'TableName'

Example:-

 Exec sp_adjustpublisheridentityrange @publication=   N'XXXXX'

and 


Exec sp_adjustpublisheridentityrange @table_name = 'YYYYY'

However, this issue will only resolve, when 

after running this command 

sp_helpconstraint 'TableName'

you get The identity range check constraint at right most 

that is  spans two separate ranges. The two sets of ranges do not have to be contiguous. For example, the value of the constraint_keys column can be as follows:

([ColumnName]>(1001) AND [ColumnName]<=(2001) 
OR [ColumnName] > (2001) AND [ColumnName]<=(3000))

This indicates if the bulk insert has inserted more than 3000 records, again you will get error specified as above.

Because the condition is ColumnName >1001 to Column Name <=3000

Hence if issue do not resolve even after executing 

Exec sp_adjustpublisheridentityrange  @publisher '<PublicationName>'

and 

Exec sp_adjustpublisheridentityrange @table_ name = 'TableName'

Actually, when we configure Merge replication on a table which has an Identity column. SQL Server itself create an identity range constraint for that identity column.

è And Lower bound and Upper bound value for identity range constraint increase after executing the query Exec sp_adjustpublisheridentityrange  @publisher '<PublicationName>'

Constraint syntax for that column something will be like this: 

USE [DatabaseName] GO ALTER TABLE [dbo].[<TableName>]  WITH NOCHECK ADD  CONSTRAINT [repl_identity_range_D111A256_E756_434D_BDAA_0C0DAB941E6E] CHECK NOT FOR REPLICATION (([Index]>(26253) AND [Index]<=(27253) OR [Index]>(27253) AND [Index]<=(28253))) GO ALTER TABLE [dbo].[PublishQueue] CHECK CONSTRAINT [repl_identity_range_D111A256_E756_434D_BDAA_0C0DAB941E6E] GO

hence, in this case,  you are inserting more than 2000 records, the transaction will fail because the upper bound value is 28253 and start from 26253.

In order to resolve this issue:-

Go to publisher database and use command:-

Use [<DatabaseName>]

go

EXEC sp_changemergearticle
@publication = N'<PublicationName>',
@article = N'<TableName>',
@property='identityrangemanagementoption',
@value = N'none',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;

GO

After executing this command, you will find the constraint repl_identity_range_D111A256_E756_434D_BDAA_0C0DAB941E6E] will delete automatically.

Then you Need to run

• snapshot agent, and run the
•  Merge agent,

because your existing subscription will expire.

After your bulk inserts will be successful.

Link:-


https://support.microsoft.com/en-us/kb/953481