5

I need to rename some tables in my DB - SQL Server 2005, with this:

EXEC sp_rename 'dbo.TableName', 'Tmp_TableName', 'OBJECT'

but I can't do this - I get this exception -

Cannot rename the table because it is published for replication

This DB doesn't have replication - it was removed 5 years ago - we've just tried it and removed it after a week. So I suppose that it should be a flag somewhere.

Please help me remove this flag somehow.

8

sp_removedbreplication 'DB_NAME' that will remove replication from the database.

From How to cleanup Replication Bits by Chris Skorlinski of Microsoft SQL Server Escalation Services:

sp_removedbreplication – Removes all replication objects from a database without updating data at the Distributor. This stored procedure is executed at the Publisher on the publication database or at the Subscriber, on the subscription database.

sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.

1

You do not need to remove the whole replication. You can just remove the single article (aka your table) from the replication. In SSMS connect to the publishing server, expand Local Publications -> Properties -> Articles. Uncheck the table you want to rename, then rename the table, then re-add it to the article list (make sure to uncheck "Show only checked articles in the list" option to see all addable objects). Then start a new snapshot in the snapshot agent: Local Publications -> Properties -> View Snapshot Agent Status -> Start.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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