| title | sp_dropsubscription (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/03/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 | 7551f345-5510-4684-ab53-f9057249d13a | ||
| caps.latest.revision | 23 | ||
| author | BYHAM | ||
| ms.author | rickbyh | ||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]
Drops subscriptions to a particular article, publication, or set of subscriptions on the Publisher. This stored procedure is executed at the Publisher on the publication database.
Transact-SQL Syntax Conventions
sp_dropsubscription [ [ @publication= ] 'publication' ]
[ , [ @article= ] 'article' ]
, [ @subscriber= ] 'subscriber'
[ , [ @destination_db= ] 'destination_db' ]
[ , [ @ignore_distributor = ] ignore_distributor ]
[ , [ @reserved= ] 'reserved' ]
[ @publication= ] 'publication'
Is the name of the associated publication. publication is sysname, with a default of NULL. If all, all subscriptions for all publications for the specified Subscriber are canceled. publication is a required parameter.
[ @article= ] 'article'
Is the name of the article. article is sysname, with a default value of NULL. If all, subscriptions to all articles for each specified publication and Subscriber are dropped. Use all for publications that allow immediate updating.
[ @subscriber= ] 'subscriber'
Is the name of the Subscriber that will have its subscriptions dropped. subscriber is sysname, with no default. If all, all subscriptions for all Subscribers are dropped.
[ @destination_db= ] 'destination_db'
Is the name of the destination database. destination_db is sysname, with a default of NULL. If NULL, all the subscriptions from that Subscriber are dropped.
[ @ignore_distributor = ] ignore_distributor
[!INCLUDEssInternalOnly]
[ @reserved= ] 'reserved'
[!INCLUDEssInternalOnly]
0 (success) or 1 (failure)
sp_dropsubscription is used in snapshot and transactional replication.
If you drop the subscription on an article in an immediate-sync publication, you cannot add it back unless you drop the subscriptions on all the articles in the publication and add them all back at once.
[!code-sqlHowTo#sp_droptransubscription]
Only members of the sysadmin fixed server role, the db_owner fixed database role, or the user that created the subscription can execute sp_dropsubscription.
Delete a Push Subscription
sp_addsubscription (Transact-SQL)
sp_changesubstatus (Transact-SQL)
sp_helpsubscription (Transact-SQL)