SQL Server Performance Forum – Threads Archive

Cannot modify a column if it’s replicated

I have replication setup between two SQL 2000 servers, I want to change the
size of a column on the publisher and I get the following error. How can I
modify a column if it’s replicated?
Cannot drop the table ‘dbo.test’ because it is being used for replication. Thanks in advance..

You will have to drop the subscription. Execute sp_droparticle with appropriate parameters to drop the table from publication and then drop the table. You can then recreate subscription. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’m a newbie with replication .Sorry for my lack of knowledge. Do you have any instructions on how to do this? Can I do this from Enterprise managager using the replication folder? Thank you

Review information listed in books online for SP_DROPARTICLE topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’d suggest more reading on replication in general too.
After you drop the article and make the change, you’ll have to start the replication again. This will trigger a snapshot for that table. If the table is huge, there will be a load on the publisher for a short bit (and you need enough drive space for the table snapshot), but the real fun can be at the subscriber. A snapshot will drop the subscriber table, recreated, bring in the data and then apply all the indexes. If this is a small table, no big deal, but if its a large table your subscriber database applications may have to wait until this is finished.
Its not complicated, but worth reading about in BOL before you start changing things.

Here is what I did; Right -click and go to properties on the publication Choose filter columns tab I unchecked the column I wanted to exlude from replication Then I went to subscriptions tab and then hit reinitialize Then I went back to the snapshot agent and start the agent. As soon as I did that it start recreating and replicating the whole database. I just wanted one column to be dropped but instead it replicate the whole database, all the tables and columns which took forever. What do you think I can do? they told me that modifying is easier to do in SQL2000 <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> Thank you for your help.

When any schema changes occurs SQL itself will take care and replication process reinitialises. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Do you know how to remove just one table…im in the filter columns.
i see all the checks on the columns……but i can’t see how to remove it on the key.
Doing this does this release the table
as i cannot drop the table…

Check your other post and do not duplicate the problem. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

]]>