sp_dropsubscription (Transact-SQL)
Applies to:
SQL Server
Azure SQL Managed Instance
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
Syntax
sp_dropsubscription
[ [ @publication = ] N'publication' ]
[ , [ @article = ] N'article' ]
, [ @subscriber = ] N'subscriber'
[ , [ @destination_db = ] N'destination_db' ]
[ , [ @ignore_distributor = ] ignore_distributor ]
[ , [ @reserved = ] N'reserved' ]
[ , [ @publisher = ] N'publisher' ]
[ ; ]
Arguments
[ @publication = ] N'publication'
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 = ] N'article'
The name of the article. @article is sysname, with a default 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 = ] N'subscriber'
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 = ] N'destination_db'
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
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @reserved = ] N'reserved'
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
[ @publisher = ] N'publisher'
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
Return code values
0
(success) or 1
(failure).
Remarks
sp_dropsubscription
is used in snapshot and transactional replication.
If you drop the subscription on an article in an immediate-sync publication, you can't add it back unless you drop the subscriptions on all the articles in the publication and add them all back at once.
Examples
-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables
-- on the command line and in SQL Server Management Studio, see the
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
-- This batch is executed at the Publisher to remove
-- a pull or push subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);
USE [AdventureWorks2022]
EXEC sp_dropsubscription
@publication = @publication,
@article = N'all',
@subscriber = @subscriber;
GO
Permissions
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
.
Related content
Feedback
https://aka.ms/ContentUserFeedback.
În curând: Pe parcursul anului 2024, vom elimina treptat Probleme legate de GitHub ca mecanism de feedback pentru conținut și îl vom înlocui cu un nou sistem de feedback. Pentru mai multe informații, consultați:Trimiteți și vizualizați feedback pentru