Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
It feels great to find the answer to your own question. Sometimes, you just have to walk away, get that next cup of coffee, stare at the morning sky, and then the light bulb bursts forth. +1 !!! –  RolandoMySQLDBA Dec 6 '11 at 18:57
    
Nobody was happier then me when I got it to work! And then the next error arrived... coming from a DEV I don't know how you DBAs do it. –  David A Gibson Dec 7 '11 at 10:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.