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

How to determine whether you are experiencing this problem

To determine whether you are experiencing this problem, follow these steps:

1. Verify that the current identity value is smaller than the lower bound of the first identity range of the identity range check constraint.
   
   To obtain the current identity value, run the following statement:

   SELECT IDENT_CURRENT ('<TableName>')

   To obtain the identity ranges of the identity range check constraint, run either of the following statements:
   Statement 1

   sp_helpconstraint '<TableName>'

   Statement 2

   select * from MSmerge_identity_range
   where is_pub_range <>1
   AND artid IN 
    (select artid from sysmergearticles where name='<TableName>')
   AND subid in 
    (select subid from sysmergesubscriptions  MS
   join sysmergepublications MP 
   on MS.subscriber_server=MP.publisher
   AND MS.db_name = MP.publisher_db
   WHERE name='<PublicationName>'
   )
   

2. Use a SQL Server Profiler trace to determine whether interleaved executions of the sp_MSsetup_publisher_idrange stored procedure are started by separate Merge Agent sessions for the same publication.
   
   However, interleaved executions of the sp_MSsetup_publisher_idrange stored procedure do not always indicate that you are experiencing this problem. This is because SQL Server Profiler was not running when the original occurance of the merge synchronization generated the first error message. However, interleaved executions of the sp_MSsetup_publisher_idrange stored procedure do increase the possibility of experiencing this problem.
3. You can find overlapping merge synchronizations that occur when you receive the "548" error message occurs. To do this, you can review the merge history in the distribution database. To do this, run the following statements:

   Use distribution
   GO
   select session_id, agent_id, B.publication, min(time) as StartTime, max(time) as EndTime
   into #sessiontimes
   from dbo.MSmerge_history A
   join dbo.msmerge_agents B
   on A.agent_id = B.id
   group by session_id, agent_id, publication
   order by 3 desc
   GO
   -- The left side result is the original session. The right side result is the overlapping session.
   select A.*, B.* 
   from #sessiontimes A
   Join #sessiontimes B
   On B.StartTime >= A.StartTime
   AND B.StartTime <= A.EndTime
   AND A.session_id <> B.session_id
   And A.publication=B.publication
   Order By A.StartTime asc
   GO
   drop table #sessiontimes
   

How to correct existing damaged identity ranges for a problematic table

After you install the cumulative update or after you use the method that is described in the workaround section, an existing damaged identity range in a table is not corrected. You will still receive the "548" error message if you try to insert data into the table and synchronize data on a subscriber. Therefore, you must manually correct the damaged identity ranges for the table. To do this, follow these steps.

Note These steps involve manually overriding the current identity seed value for the table to correctly reseed the identity value at the publisher. In the damaged state, the current identity value is smaller than the first identity range in the merge replication identity range check constraint. The steps manually raise the identity value to fall inside the identity range that is defined by the merge replication identity range check constraint. These steps assume that identities are configured in an ascending manner and that the identity increment is configured to increase by a value of 1.

1. Confirm that the identity increment value is 1 and that the identity proceeds in ascending manner. You can obtain the identity increment value by running the following statement on the publisher:

   SELECT IDENT_INCR( '<TableName>')

2. Run the following statement on the publisher to find the current identity value on the problematic identity column:

   DBCC CHECKIDENT ('<TableName>')

   After you receive the result, note the current identity value value for comparison in the later steps. Notice that the
   current column value valye may be either larger or smaller than the current identity value value.
   
   
   If the current column value value is larger than the current identity value value, the column value may have originated at other replications in the topology and merged successfully with the publisher replication. If the current column value value is smaller than the current identity value value, the values may have been inserted on the publisher at a earlier time by using the SET IDENTITY_INSERT ON statement before the merge replication configuration.
3. Run the following statements on the publisher to determine the current identity ranges of the identity range check constraint for the problematic table:

   Use <PublishedDatabaseName>
   GO
   sp_helpconstraint '<TableName>'
   GO
   

   After you receive the result, note the value of the
   constraint_keys column of the record where the value of the
   constraint_name column is "repl_identity_range_GUID". The GUID value corresponds to the value of the
   artid column for the article in the sysmergearticles system table. To obtain the GUID, run the following statement:
   

   select artid from sysmergearticles where name = '<TableName>'

   The identity range check constraint 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] > (9001) AND [ColumnName]<=(10001))
   Note This article uses this example to present code in the rest of steps.
   
   In this example, the ranges each span 1,000 values. 1,000 is the default range size. However, you can change the identity range size by using one of the following methods:
   
   • Specify the @pub_identity_range parameter when you run the sp_addmergearticle stored procedure.
     
   • Change the Subscriber range size property for the article in the
     Article Properties dialog box.
4. If you experience the problem that is described in the "Symptoms" section, the current identity value that you noted in step 2 should be smaller than the lower bound of the first identity range of the identity range check constraint that you noted in step 3.
   
   If the current identity value in step 2 is larger than the upper bound of the second identity range of the identity range check constraint, resolve the problem by using the method that is recommended in the error message. Therefore, you should run the sp_adjustpublisheridentityrange stored procedure on the publisher.
   
   For more information about the sp_adjustpublisheridentityrange stored procedure, visit the following Microsoft Developer Network (MSDN) Web site:
   http://msdn.microsoft.com/en-us/library/ms181527.aspx
5. Run the following statement to determine whether any rows are in the current identity ranges of the identity range check constraint:
   

   SELECT COUNT(*) FROM TableName WHERE 
   ([ColumnName]>(1001) AND [ColumnName]<=(2001) 
   OR [ColumnName]>(9001) AND [ColumnName]<=(10001))
   

   Notes
   • If the statement returns 0, no rows are in the current identity ranges. In this case, go to step 6.
   • If the statement returns a value that is larger than 0, run the following statement to obtain the maximum identity value in the current identity ranges:

     SELECT MAX(ColumnName) as MaxValue FROM TableName WHERE 
     ([ColumnName]>(1001) AND [ColumnName]<=(2001) OR 
     [ColumnName]>(9001) AND [ColumnName]<=(10001))
     

     Note the returned value, and then go to step 7.
6. Manually reseed the current identity for the problematic table to fall inside a valid range.
   
   
   Reseed the current identity to the lowest value of the current identity ranges plus 1. For example, if the lowest value of the current identity ranges is 1001, the first possible in-range value is 1002 because the low end of the range of the identity range check constraint uses the greater than sign (>). To do this, run the following statement on the publisher, and then go to step 8:

   DBCC CHECKIDENT ('TableName', RESEED, 1002)

7. Manually reseed the current identity for the problematic table to fall inside a valid range.
   
   Assume that the identity increment is 1. Reseed the current identity to the value that you noted in step 5, and then add 1. For example, if the value that you noted in step 5 is 1507, reseed the current identity to 1508. To do this, run the following statement on the publisher:

   DBCC CHECKIDENT ('TableName', RESEED, 1508)

8. Perform a test to determine whether new rows can be inserted into the table in the publisher database without error 548 occurring.