We have a Transactional Replication environment that was initialised from a backup that replicates the data for several hundred tables.
When a table is altered, say a new column added, I have created a script that does the following:
- Apply table ALTER at the subscriber
- Apply table ALTER at the publisher
- Drops the subscription
- Drops the article
- Adds the article again (so it will now include the new column – sp_refreshsubscriptions is called automatically by the sp_addarticle to add it back to the subscription)
In testing I thought this was working but it appears in our production environment that this causes the following replication error:
Cannot update identity column 'XXX'
for the identity column of the table being altered. Investigation has shown that this is not what it seems; the command that is failing is not an update of data on the row but the addition of the article to the existing subscription.
If I turn Is Identity off for the identity column on the subscriber then the above script works as expected and data replication continues. I can even then turn 'Is Identity' back on after the article is added to the subscription and replication continues. The solution is to turn 'Is Identity' off for all the tables (or before we try to alter them) in our subscriber database but these were all on after replication was set up and are working fine with the 'Not For Replication' setting turned on.
Why would 'Is Identity' being on for an table in the subscription database prevent me from adding the table again after I have dropped it? Can I circumvent my having to turn it off before altering the table using my script?
Thanks
Best Answer
I have found a solution though adding:
@identityrangemanagementoption = N'manual'
when I re-add the article using sp_addarticle, means that the automatically created sps used to replicate the data don't try and update the identity column and thus are created succesfully on the subscriber rather than raising the error.