June 21, 2017 at 5:07 pm
I have a requirement to copy some data (including maintaining identity column values) to the publication database in a merge replication setup. However, I am unable to populate the table as the identity range constraint is reporting the error:
The insert failed. It conflicted with an identity range check constraint in database 'XXX', replicated table 'YYY.ZZZ', 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.
I believe I understand the principles behind the constraint and why the data is failing to insert (some of the values are outside the constraints' permitted ranges) but I don't understand why, on a different server which also hosts a similar setup (prod environment rather than dev), there are values in the table which appear to violate the definition of the equivalent constraint.
Can anyone explain what's going on and how I can get this data into the table?
Thanks.
June 21, 2017 at 5:46 pm
Pete Bishop - Wednesday, June 21, 2017 5:07 PMI have a requirement to copy some data (including maintaining identity column values) to the publication database in a merge replication setup. However, I am unable to populate the table as the identity range constraint is reporting the error:The insert failed. It conflicted with an identity range check constraint in database 'XXX', replicated table 'YYY.ZZZ', 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.I believe I understand the principles behind the constraint and why the data is failing to insert (some of the values are outside the constraints' permitted ranges) but I don't understand why, on a different server which also hosts a similar setup (prod environment rather than dev), there are values in the table which appear to violate the definition of the equivalent constraint. Can anyone explain what's going on and how I can get this data into the table?Thanks.
Did you check if the articles are defined the same on both systems using sp_helpmergearticle?
You can also query MSmerge_identity_range_allocations and MSmerge_identity_range to get more information.
Some query examples and further explanations can be found in this article -
All about “Identity Range Managementâ€
Sue
June 22, 2017 at 4:53 am
Thanks for this information, especially the referenced article.
I've been advised that it might be because the quantity of information being written has exceeded the configured ranges (this is a pseudo-refresh exercise). I've tried increasing the pub_identity_range and identity_range values of the relevant articles (those where identity_support=1) and running sp_adjustpublisheridentityrange (against the individual articles or the publication as a whole) but that hasn't solved the problem.
Does this provide any further insight and, if so, is there a step I'm missing to solve the problem?
June 23, 2017 at 10:14 am
<engage embarrassment mode>
This turned out to be (mostly) unrelated to replication and entirely unrelated to identity range inserts.
As I was refreshing the entire table, my first step was to disable the foreign key constraints since the data population wouldn't be in a controlled manner. This was achieved with:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
This command also disabled the "repl_identity_range" constraints so that they didn't prevent insertion of data outside the currently-permitted ranges.
So far, so good.
After the update process was complete, I re-enabled the constraints using:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
This was the sole cause of my troubles. Including the "WITH CHECK" directive meant that the "repl_identity_range" constraints would perform validation against *existing* data, and this failed as there was data in the tables which didn't conform to the permitted range. Once I (eventually) worked this out, I changed my post-population command to include the "WITH CHECK" for all constraints *except* the "repl_identity_range" ones, which were re-enabled using the "WITH NOCHECK" directive. Once I did this, everything was successful.
<disengage embarrassment mode>
June 23, 2017 at 10:26 am
Pete Bishop - Friday, June 23, 2017 10:14 AM<engage embarrassment mode>
This turned out to be (mostly) unrelated to replication and entirely unrelated to identity range inserts.
As I was refreshing the entire table, my first step was to disable the foreign key constraints since the data population wouldn't be in a controlled manner. This was achieved with:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
This command also disabled the "repl_identity_range" constraints so that they didn't prevent insertion of data outside the currently-permitted ranges.
So far, so good.
After the update process was complete, I re-enabled the constraints using:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
This was the sole cause of my troubles. Including the "WITH CHECK" directive meant that the "repl_identity_range" constraints would perform validation against *existing* data, and this failed as there was data in the tables which didn't conform to the permitted range. Once I (eventually) worked this out, I changed my post-population command to include the "WITH CHECK" for all constraints *except* the "repl_identity_range" ones, which were re-enabled using the "WITH NOCHECK" directive. Once I did this, everything was successful.
<disengage embarrassment mode>
Thanks for posting back. People will learn as much from reading this post as they do from others posts and these forums are essentially about all of us learning. We all do things like that and thankfully some such as yourself will be open about it so we all can learn from it. So thank you.
All of those types of things I do just become things I never forget so I figure the more I do things like that, the more I will know. Nothing lost even if my palm print is permanently embedded in my forehead.
Sue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply