Error message when you try to batch insert data on a publisher of a merge replication in SQL Server 2008 or SQL Server 2005: "Msg 548, Level 16, State 2, Line 1. The insert failed"

Source: Microsoft Support

RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Consider the following scenario. In SQL Server 2008, you configure a merge publication. You add a table that contains an identity column to the merge publication. Then, you try to perform a batch insert into the table on the publisher. In this scenario, you may receive the following error message on the publisher.

Msg 548, Level 16, State 2, Line 1
The insert failed. It conflicted with an identity range check constraint in database ' DatabaseName ', replicated table ' Schema . 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.

This problem occurs because the current identity value of the table on the publisher is approaching the maximum allowable value of the identity range check constraint and the batch insert is attempting to insert enough rows to exceed the maximum allowable value of the identity range check constraint. The behavior occurs because the trigger logic for insert operations, that automatically adjusts the identity ranges on the publisher table, does not account for batched operations.

For example, the current identity value of the table is 9,995 and the identity range check constraint is configured to allow identity values between 1 and 10,000. You attempt to perform an INSERT SELECT statement that will batch insert 10 rows into the table. This will fail with the error described above.

When you insert a row in the publisher table that increments the seed beyond the configured range for the article, the table's insert trigger will automatically allocate a new range of identity values for the article's identity column and adjust the constraint defined on the column to reflect the new range. However the same trigger logic does not get fired when you perform a batch insert that exceeds the maximum seed of the current identity range resulting in the problem discussed in this article.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

As mentioned in the error message, you can use one of the following options to adjust the identity range of the publisher table. Either of these options will analyze the currently configured threshold for the identity column and adjust the identity range check constraint with a new range.

• Start the merge agent. This will automatically adjust the identity range to new values.
• Run sp_adjustpublisheridentityrange at the publisher. This will check the identity range threshold at the publisher and adjust the article's identity range if the current seed is beyond the configured threshold. For more informaion, see the sp_adjustpublisheridentityrange (Transact-SQL) topic on the following Microsoft TechNet Web site:
  
  http://technet.microsoft.com/en-us/library/ms181527.aspx (http://technet.microsoft.com/en-us/library/ms181527.aspx)

MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.

TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.