Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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)