• 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