| title | sp_droparticle (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql-server-2016 | ||
| ms.reviewer | |||
| ms.suite | |||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| applies_to |
|
||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 09fec594-53f4-48a5-8edb-c50731c7adb2 | ||
| caps.latest.revision | 25 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Drops an article from a snapshot or transactional publication. An article cannot be removed if one or more subscriptions to it exist. This stored procedure is executed at the Publisher on the publication database.
Transact-SQL Syntax Conventions
sp_droparticle [ @publication= ] 'publication'
, [ @article= ] 'article'
[ , [ @ignore_distributor = ] ignore_distributor ]
[ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @publisher = ] 'publisher' ]
[ , [ @from_drop_publication = ] from_drop_publication ]
[ @publication=] 'publication'
Is the name of the publication that contains the article to be dropped. publication is sysname, with no default.
[ @article=] 'article'
Is the name of the article to be dropped. article is sysname, with no default.
[ @ignore_distributor =] ignore_distributor
[!INCLUDEssInternalOnly]
[ @force_invalidate_snapshot = ] force_invalidate_snapshot
Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0.
0 specifies that changes to the article do not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.
1 specifies that changes to the article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.
[ @publisher= ] 'publisher'
Specifies a non-[!INCLUDEmsCoName] [!INCLUDEssNoVersion] Publisher. publisher is sysname, with a default of NULL.
Note
publisher should not be used when changing article properties on a [!INCLUDEssNoVersion] Publisher.
[ @from_drop_publication= ] from_drop_publication
[!INCLUDEssInternalOnly]
0 (success) or 1 (failure)
sp_droparticle is used in snapshot and transactional replication.
For horizontally filtered articles, sp_droparticle checks the type column of the article in the sysarticles (Transact-SQL) table to determine whether a view or filter should also be dropped. If a view or filter was autogenerated, it is dropped with the article. If it was manually created, it is not dropped.
Executing sp_droparticle to drop an article from a publication does not remove the object from the publication database or the corresponding object from the subscription database. Use DROP <object> to manually remove these objects if necessary.
[!code-sqlHowTo#sp_droparticle]
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_droparticle.
Delete an Article
Add Articles to and Drop Articles from Existing Publications
sp_addarticle (Transact-SQL)
sp_changearticle (Transact-SQL)
sp_helparticle (Transact-SQL)
sp_helparticlecolumns (Transact-SQL)
Replication Stored Procedures (Transact-SQL)