Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

This article describes an improvement that allows a table that's included as an article in transactional replication publication to be dropped from the database and the publication(s). A table can be dropped only if the allow_drop property is set to TRUE on all the publications that have the table(s) as an 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.

If a DROP TABLE DDL is supported by the publication(s), then the DROP TABLE operation will drop the table from the publication and the database. The log reader agent will post a cleanup command for the distribution database of the dropped table and do the cleanup of the publisher metadata. If the log reader hasn't processed all the log records that refer to the dropped table, then it will ignore new commands that are associated with the dropped table. Already processed records will be delivered to distribution database. They may be applied on Subscriber database if the Distribution Agent processes them before Log Reader cleans up the obsolete (dropped) article(s).

Command to enable drop table support for a publication:exec sp_changepublication @publication = '<Publication Name>', @property = 'allow_drop', @value = 'true' Command to enable drop table support while you're creating a publication:

exec sp_addpublication @publication = '<Publication Name>', ..., @allow_drop = N'true' The default value of the allow_drop property is set to False.

Resolution

This problem was fixed in the following cumulative updates for SQL Server:

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Latest cumulative update for SQL Server 2016

Service pack informationThis functionality was first introduced in the following service packs for SQL Server:

About Service packs for SQL Server

Service packs are cumulative. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes. Our recommendation is to apply the latest service pack and the latest cumulative update for that service pack. You don't have to install a previous service pack before you install the latest service pack. Use Table 1 in the following article for finding more information about the latest service pack and latest cumulative update.

How to determine the version, edition and update level of SQL Server and its components

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×