Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Drop table from a published SQL Server database


By:   |   Last Updated: 2016-11-01   |   Comments (1)   |   Related Tips: More > 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

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

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
   @[email protected],
   @[email protected],
   @force_invalidate_snapshot=1;
GO

sp_droparticle for SQL Server Transactional Replication

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

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
   @[email protected],
   @[email protected],
   @force_invalidate_snapshot=1;
GO

sp_droparticle stored procedure command with the @force_invalidate_snapshot option

If we don't use the @force_invalidate_snapshot=1. we get this error:

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 101 [Batch Start Line 11] Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

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

Drop article from database

Now we can DROP the article from the published database using the DROP command.

Successful Drop Table command

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

Now we can drop the article using the DROP command.

Successful drop table in SQL Server Replication

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


Last Updated: 2016-11-01


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 01, 2016 - 11:47:34 AM - Sreekanth Back To Top

@property = 'allow_drop' - Wow...Good to know! Thanks for the tip!


Learn more about SQL Server tools