none
Error Inserting Rows into Merge Replicated Table

    Frage

  • I have a database with about 20 tables and I am using merge replication to 2 files databases. When I run a stored procedure on the publisher to insert about 4 thousand rows into at particular table, I get the error shown below.

    The ID column is an automatic ID. It is "Not For Replication" but is "Merge Published", similar to all other tables in the publication. The Publisher Range Size for the ID column was 10,000 (by default) and the Subscriber Range Size was 1,000. I added a zero onto the end of each but I am not sure when I need to drop and recreate the subscriptions for that to take effect. Also, the error occurs during the insert, so I assume that it comes from the Publisher. If I insert a single row in SSMS, there is no error. I have run sp_adjustpublisheridentityrange @table_name = 'TableName', but it made no difference.

    I am at my wits end to figure out what to do about this so any help would be appreciated.

    Msg 548, Level 16, State 2, Procedure ImportSpinParamDBF, Line 27 [Batch Start Line 11]
    The insert failed. It conflicted with an identity range check constraint in database 'CBL_Meridian', replicated table 'dbo.SpinParameters', 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.
    


    R Campbell

    Mittwoch, 11. Juli 2018 10:32

Antworten

  • Basically merge replication handles identity ranges and parcels them out in an efficient manner. Part of this management involves prevent subscriber side inserts which would cause identity range pool exhaustion on the subscriber. In other words each subscriber is assigned a range of identity values it can use and if it exhausts them new inserts will be prevented until you sync and a new range or pool is assigned to this subscriber.

    You should have allocated a range for your subscriber which corresponds with the maximum inserts you would expect to insert on a subscriber between syncs. A sync by default could happen every 3 days - which is your retention period and you should size your subscriber for that range.

    It sounds like you did go down that path, but did not reinitialize your subscription.

    A simple single sync should fix this problem, and this problem is a subscriber side only problem, very rarely publisher side.

    • Als Antwort markiert Dick Campbell Mittwoch, 11. Juli 2018 11:35
    Mittwoch, 11. Juli 2018 11:07
    Moderator

Alle Antworten

  • Basically merge replication handles identity ranges and parcels them out in an efficient manner. Part of this management involves prevent subscriber side inserts which would cause identity range pool exhaustion on the subscriber. In other words each subscriber is assigned a range of identity values it can use and if it exhausts them new inserts will be prevented until you sync and a new range or pool is assigned to this subscriber.

    You should have allocated a range for your subscriber which corresponds with the maximum inserts you would expect to insert on a subscriber between syncs. A sync by default could happen every 3 days - which is your retention period and you should size your subscriber for that range.

    It sounds like you did go down that path, but did not reinitialize your subscription.

    A simple single sync should fix this problem, and this problem is a subscriber side only problem, very rarely publisher side.

    • Als Antwort markiert Dick Campbell Mittwoch, 11. Juli 2018 11:35
    Mittwoch, 11. Juli 2018 11:07
    Moderator
  • Thanks Hilary. Sure enough when I tried it again just now, there was no error. I had updated the Publisher and Subscriber Range Sizes but hadn't waited long enough for the to propagate to the subscribers, I guess.

    I am still a little puzzled by the fact that the error occurred immediately, as the publisher which has the default Range Size of 10,000.

    As happens with a lot of things with Replication, you need to wait a few minutes for everything to run its course. Being impatient can have looking for problems that are just a matter of timing.

    Thanks for your help.


    R Campbell

    Mittwoch, 11. Juli 2018 11:35
  • The identity range is updated with every change on the publisher, and on the subscriber when the threshold is threshed and the merge agent runs.

    You could be between identity range adjustments.

    Montag, 16. Juli 2018 17:25
    Moderator