Microsoft KB Archive/939318

= Error message when you insert data in an article that has automatic identity range handling enabled in a merge replication in SQL Server 2000: &quot;The identity range managed by replication is full and must be updated by a replication agent&quot; =

Article ID: 939318

Article Last Modified on 11/12/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Developer Edition

-





SYMPTOMS
In a Microsoft SQL Server 2000 merge replication, when you insert data in an article that has automatic identity range handling enabled, you receive the following error message:

Server: Msg 548, Level 16, State 2, Line 1

The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'p', table 't2', column 'id'. Sp_adjustpublisheridentityrange can be called to get a new identity range.

The statement has been terminated.

After you receive the error message, you synchronize the data for the publication. Then, when you insert some records from the publication and from one of the subscriptions into the article, you receive the following error message:

Server: Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PrimaryKeyName'. Cannot insert duplicate key in object 'TableName'.

The statement has been terminated.

This problem occurs if the following conditions are true:
 * A merge replication has two articles.
 * Each article has an identity column.
 * Automatic identity range handling is enabled for both articles.
 * The publication has two or more subscriptions.
 * You insert the data in the second article.



CAUSE
This issue occurs because the same identity values are incorrectly inserted at the publisher and at the subscriber. This causes a conflict.



RESOLUTION
To resolve this issue, upgrade to SQL Server 2005.



WORKAROUND
To work around this issue, reinitialize the subscriptions for the publication after you add the new article.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section. 

Steps to reproduce the behavior
 In SQL Server 2000, configure a merge replication. Add an article that has an identity column. Enable automatic identity range handling for the article. Add two or more subscriptions to the publication. Add another article that has an identity column.</li> Enable automatic identity range handling for the second article.</li> Generate the snapshot, and then synchronize the data for the publication.</li>  Run the following statement on these subscriptions: select * from <SubscriptionDatabase>..MSrepl_identity_range Notice that the next_seed values of the identity column of the second article on all the subscriptions are the same. However, these values should be different. </li> Insert some records in the second article.</li></ol>

Keywords: kberrmsg kbtshoot kbprb kbexpertiseadvanced KB939318

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.