Drops a subscription to a merge publication and its associated Merge Agent. This stored procedure is executed at the Publisher on the publication database.
sp_dropmergesubscription [ [ @publication= ] 'publication' ] [ , [ @subscriber= ] 'subscriber' [ , [ @subscriber_db= ] 'subscriber_db' ] [ , [ @subscription_type= ] 'subscription_type' ] [ , [ @ignore_distributor = ] ignore_distributor ] [ , [ @reserved = ] reserved ]
[ @publication = ] 'publication'
Is the publication name. publication is sysname, with a default of NULL. The publication must already exist and conform to the rules for identifiers.
[ @subscriber = ] 'subscriber'
Is the name of the Subscriber. subscriber is sysname, with a default of NULL.
[ @subscriber_db = ] 'subscriber_db'
Is the name of the subscription database. subscription_databaseis sysname, with a default of NULL.
[ @subscription_type = ] 'subscription_type'
Is the type of subscription. subscription_typeis nvarchar(15), and can be one of these values.
|all||Push, pull, and anonymous subscriptions|
|both (default)||Both push and pull subscriptions.|
[ @ignore_distributor = ] ignore_distributor
Indicates whether this stored procedure is executed without connecting to the Distributor. ignore_distributor is bit, with a default of 0. This parameter can be used to drop a subscription without doing cleanup tasks at the Distributor. It is also useful if you had to reinstall the Distributor.
[ @reserved = ] reserved
Is reserved for future use. reserved is bit, with a default of 0.
Return Code Values
0 (success) or 1 (failure)
sp_dropmergesubscription is used in merge replication.
-- 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 merge publication. DECLARE @publication AS sysname; DECLARE @subscriber AS sysname; DECLARE @subscriptionDB AS sysname; SET @publication = N'AdvWorksSalesOrdersMerge'; SET @subscriber = $(SubServer); SET @subscriptionDB = N'AdventureWorks2012Replica'; USE [AdventureWorks2012] EXEC sp_dropmergesubscription @publication = @publication, @subscriber = @subscriber, @subscriber_db = @subscriptionDB; GO
Only members of the sysadmin fixed server role or the db_owner fixed database role can execute sp_dropmergesubscription.