Removing Replication from a SQL Database

Corporate, Electronic Medical Record, Featured, Medical Software, SQL, Tech

When trying to run a database update with a third party tool, I am getting a message stating “Cannot drop <table name> because it is being used for replication.”

To try to resolve this, I deleted the subscriber to the publication, but that didn’t help. I have now gone back and deleted the publication as well. I was using transactional publication.

I want to make sure that I don’t run into this error the next time I run the tool, so I’m trying to verify that the table (or the whole database for that matter) is no longer replicated. If I run the following queries, I get no results (not an error, just no results):

SELECT *
FROM sys.tables
WHERE is_published =1;

SELECT *
FROM sys.tables
WHERE is_replicated =1;

SELECT *
FROM sys.tables
WHERE is_merge_published =1;

However, if I go to the database, and under Views -> System Views -> sys.tables -> Columns -> is_published -> it says (bit, not null).

After seeing that, I right clicked the Replication folder in SQL Server Management Studio and selected Remove Distribution and then ran the following command:

sp_removedbreplication

Once that was done, the tool was able to run and replication was completely removed from the server.

Leave a Reply