How to: Delete a Pull Subscription (Replication Transact-SQL Programming)
Pull subscriptions can be deleted programmatically using replication stored procedures. The stored procedures used will depend on the type of publication to which the subscription belongs.
To delete a pull subscription to a snapshot or transactional publication
At the Subscriber on the subscription database, execute sp_droppullsubscription (Transact-SQL). Specify @publication, @publisher, and @publisher_db.
At the Publisher on the publication database, execute sp_dropsubscription (Transact-SQL). Specify @publication and @subscriber. Specify a value of all for @article. (Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.
To delete a pull subscription to a merge publication
At the Subscriber on the subscription database, execute sp_dropmergepullsubscription (Transact-SQL). Specify @publication, @publisher, and @publisher_db.
At the Publisher on the publication database, execute sp_dropmergesubscription (Transact-SQL). Specify @publication, @subscriber, and @subscriber_db. Specify a value of pull for @subscription_type. (Optional) If the Distributor cannot be accessed, specify a value of 1 for @ignore_distributor to delete the subscription without removing related objects at the Distributor.
Example
The following example deletes a pull subscription to a transactional publication. The first batch is executed at the Subscriber and the second is executed at the Publisher.
-- 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 is the batch executed at the Subscriber to drop
-- a pull subscription to a transactional publication.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks';
USE [AdventureWorksReplica]
EXEC sp_droppullsubscription
@publisher = @publisher,
@publisher_db = @publicationDB,
@publication = @publication;
GO
-- 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 [AdventureWorks]
EXEC sp_dropsubscription
@publication = @publication,
@article = N'all',
@subscriber = @subscriber;
GO
The following example deletes a pull subscription to a merge publication. The first batch is executed at the Subscriber and the second is executed at the Publisher.
-- 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 Subscriber to remove
-- a merge pull subscription.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publication_db AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @publisher = $(PubServer);
SET @publication_db = N'AdventureWorks';
USE [AdventureWorksReplica]
EXEC sp_dropmergepullsubscription
@publisher = @publisher,
@publisher_db = @publication_db,
@publication = @publication;
GO
-- 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'AdventureWorksReplica';
USE [AdventureWorks]
EXEC sp_dropmergesubscription
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriptionDB;
GO
See Also
Tasks
How to: Delete a Push Subscription (Replication Transact-SQL Programming)
How to: Delete a Pull Subscription (RMO Programming)
How to: Create a Pull Subscription (Replication Transact-SQL Programming)
How to: Create a Push Subscription (Replication Transact-SQL Programming)