By: Rajendra Gupta | Comments (1) | Related: > Replication
Problem
Sometimes there is a need to drop a SQL Server table from a database that is published for transactional replication. In this tip we will walk through the steps to do this and an easier way with SQL Server 2014 SP2 and later.
Solution
Suppose we have SQL Server transactional replication (the publication name is TestPublication) configured for the Adventurewors2014 database for two articles (i.e. tables) Company and Employee as shown below.
![SQL Server Replication Articles](/tipimages2/4491_overview.jpg)
Now we have the requirement to drop the Company table from the database. If we run the DROP command on the published database it fails with the below error message.
![Drop table command fails for SQL Server Replicated tables](/tipimages2/4491_drop_command.jpg)
Since the table is being used in SQL Server transactional replication we cannot drop it since there are replication dependencies.
Drop article from published database prior to SQL Server 2014 SP2
To drop the table, first we need to remove it from the publication. We can use the sp_droparticle command. This stored procedure drops an article from a snapshot or transactional publication.
The syntax for sp_droparticle is:
sp_droparticle [ @publication= ] 'publication' , [ @article= ] 'article' [ , [ @ignore_distributor = ] ignore_distributor ] [ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ] [ , [ @publisher = ] 'publisher' ] [ , [ @from_drop_publication = ] from_drop_publication ]
We run the sp_droparticle command on the published database like so:
DECLARE @publication AS sysname; DECLARE @article as sysname; SET @publication=N'TestPublication'; SET @article=N'Company'; --Drop the transactional article. Use [AdventureWorks2014] EXEC sp_droparticle @publication=@publication, @article=@article, @force_invalidate_snapshot=1; GO
![sp_droparticle for SQL Server Transactional Replication](/tipimages2/4491_transactional_article.jpg)
As we can see, we get an error that the article cannot be dropped from transactional replication since the subscriptions exist, so we have to first remove the subscriptions.
Drop subscriptions to a particular article
To drop the subscription for a particular database use the sp_dropsubscription stored procedure. This stored procedure is executed at the Publisher on the published database.
sp_dropsubscription [ [ @publication= ] 'publication' ] [ , [ @article= ] 'article' ] , [ @subscriber= ] 'subscriber' [ , [ @destination_db= ] 'destination_db' ]
So in my example, we need to drop the article from the TestReplication publication, for the Company article, for the subscriber Subscriber_Server for the destination database TestReplication.
EXEC sp_dropsubscription 'TestReplication','Company','Subscriber_Server','TestReplication'
![sp_dropsubscription stored procedure](/tipimages2/4491_TestReplication.jpg)
Drop article from publication
Now we need to drop the article using the sp_droparticle stored procedure command and we need to use the @force_invalidate_snapshot option.
DECLARE @publication AS sysname; DECLARE @article as sysname; SET @publication=N'TestPublication'; SET @article=N'Company'; --Drop the transactional article. Use [AdventureWorks2014] EXEC sp_droparticle @publication=@publication, @article=@article, @force_invalidate_snapshot=1; GO
![sp_droparticle stored procedure command with the @force_invalidate_snapshot option](/tipimages2/4491_force_invalidate.jpg)
If we don't use the @force_invalidate_snapshot=1. we get this error:
We can also do this step using SQL Server Management Studio by right clicking on the publication and selecting Properties. On the Articles page if we uncheck the article, we get the below message:
![SQL Server Management Studio Replication Publication Properties](/tipimages2/4491_click_publication.jpg)
Drop article from database
Now we can DROP the article from the published database using the DROP command.
![Successful Drop Table command](/tipimages2/4491_publisher_database.jpg)
This method needs to be followed for each article that needs to be dropped from the published database.
Drop article from published database using SQL Server 2014 SP2 or later
In SQL Server 2014 SP2 a change was added that allows a table that’s included as an article in a transactional replication publication to be dropped from the database and the publication.
To do so, we need to set the allow_drop property to TRUE on all the publications that have the article. If the property is set to FALSE for any of the publications that contain the article, then the DROP TABLE operation will fail and report that replicated articles can’t be dropped. The default value of the allow_drop property is FALSE.
Syntax for making the change is as follows.
exec sp_changepublication @publication = 'publication name', @property = 'allow_drop', @value = 'true'
So we will run it on the publication 'TestReplication' as follows:
exec sp_changepublication @publication='TestReplication', @property='allow_drop', @value='true'
![Run the sp_changepublication stored procedure](/tipimages2/4491_allow_drop.jpg)
Now we can drop the article using the DROP command.
![Successful drop table in SQL Server Replication](/tipimages2/4491_publisher_database.jpg)
Note: Please be careful while enabling this option as it allows database articles to be dropped directly from the published database, so it should be disabled once the task is completed.
To disable the allow_drop property we need to specify the value as FALSE as follows.
exec sp_changepublication @publication = 'TestReplication', @property = 'allow_drop', @value = 'false'
Next Steps
- Download and explore SQL Server 2014 SP2
- Check out SQL Server 2016 tips
- Read more about SQL Server service pack 2 features
- More Replication tips
About the author
![MSSQLTips author Rajendra Gupta](/images/Rajendra-Gupta-20220116.png)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips