Delete a Pull Subscription

Article
03/06/2017

This topic describes how to delete a pull subscription in SQL Server 2014 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

In This Topic

To delete a pull subscription, using:

SQL Server Management Studio

Transact-SQL

Replication Management Objects (RMO)

Using SQL Server Management Studio

Delete a pull subscription at the Publisher (from the Local Publications folder in SQL Server Management Studio) or the Subscriber (from the Local Subscriptions folder). Deleting a subscription does not remove objects or data from the subscription; they must be removed manually.

To delete a pull subscription at the Publisher

Connect to the Publisher in SQL Server Management Studio, and then expand the server node.
Expand the Replication folder, and then expand the Local Publications folder.
Expand the publication associated with the subscription you want to delete.
Right-click the subscription, and then click Delete.
In the confirmation dialog box, select whether to connect to the Subscriber to delete subscription information. If you clear the Connect to Subscriber check box, you should connect to the Subscriber later to delete the information.

To delete a pull subscription at the Subscriber

Connect to the Subscriber in SQL Server Management Studio, and then expand the server node.
Expand the Replication folder, and then expand the Local Subscriptions folder.
Right-click the subscription you want to delete, and then click Delete.
In the confirmation dialog box, select whether to connect to the Publisher to delete subscription information. If you clear the Connect to Publisher check box, you should connect to the Publisher later to delete the information.

Using Transact-SQL

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.

Examples (Transact-SQL)

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'AdventureWorks2012';

USE [AdventureWorks2012Replica]
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 [AdventureWorks2012]
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'AdventureWorks2012';

USE [AdventureWorks2012Replica]
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'AdventureWorks2012Replica';

USE [AdventureWorks2012]
EXEC sp_dropmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB;
GO

Using Replication Management Objects (RMO)

You can delete pull subscriptions programmatically by using Replication Management Objects (RMO). The RMO classes that you use to delete a pull subscription depend on the type of publication to which the pull subscription is subscribed.

To delete a pull subscription to a snapshot or transactional publication

Create connections to both the Subscriber and Publisher by using the ServerConnection Class.
Create an instance of the TransPullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. Use the Subscriber connection from step 1 to set the ConnectionContext property.
Check the IsExistingObject property to verify that the subscription exists. If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.
Call the Remove method.
Create an instance of the TransPublication class by using the Publisher connection from step 1. Specify Name, DatabaseName and ConnectionContext.
Call the LoadProperties method. If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.
Call the RemovePullSubscription method. Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

To delete a pull subscription to a merge publication

Create connections to both the Subscriber and Publisher by using the ServerConnection Class.
Create an instance of the MergePullSubscription class, and set the PublicationName, DatabaseName, PublisherName, and PublicationDBName properties. Use the connection from step 1 to set the ConnectionContext property.
Check the IsExistingObject property to verify that the subscription exists. If the value of this property is false, either the subscription properties in step 2 were defined incorrectly or the subscription does not exist.
Call the Remove method.
Create an instance of the MergePublication class by using the Publisher connection from step 1. Specify Name, DatabaseName and ConnectionContext.
Call the LoadProperties method. If this method returns false, either the properties specified in step 5 are incorrect or the publication does not exist on the server.
Call the RemovePullSubscription method. Specify the name of the Subscriber and the subscription database for the subscriber and subscriberDB parameters.

Examples (RMO)

This example deletes a pull subscription to a transactional publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksProductTran";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
TransPublication publication;
TransPullSubscription subscription;

try
{
    // Connect to the Subscriber.
    subscriberConn.Connect();

    // Define the pull subscription.
    subscription = new TransPullSubscription();
    subscription.ConnectionContext = subscriberConn;
    subscription.PublisherName = publisherName;
    subscription.PublicationName = publicationName;
    subscription.PublicationDBName = publicationDbName;
    subscription.DatabaseName = subscriptionDbName;

    // Define the publication.
    publication = new TransPublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = publisherConn;

    // Delete the pull subscription, if it exists.
    if (subscription.IsExistingObject)
    {
        if (publication.LoadProperties())
        {
            // Remove the pull subscription registration at the Publisher.
            publication.RemovePullSubscription(subscriberName, subscriptionDbName);
        }
        else
        {
            // Do something here if the publication does not exist.
            throw new ApplicationException(String.Format(
                "The publication '{0}' does not exist on {1}.",
                publicationName, publisherName));
        }
        // Delete the pull subscription at the Subscriber.
        subscription.Remove();
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The subscription to {0} does not exist on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here.
    throw new ApplicationException(String.Format(
        "The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
    subscriberConn.Disconnect();
    publisherConn.Disconnect();
}

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksProductTran"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As TransPublication
Dim subscription As TransPullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New TransPullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New TransPublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then
       
        If publication.LoadProperties() Then
            ' Remove the pull subscription registration at the Publisher.
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

This example deletes a pull subscription to a merge publication and removes the subscription registration at the Publisher.

// Define the Publisher, publication, and databases.
string publicationName = "AdvWorksSalesOrdersMerge";
string publisherName = publisherInstance;
string subscriberName = subscriberInstance;
string subscriptionDbName = "AdventureWorks2012Replica";
string publicationDbName = "AdventureWorks2012";

//Create connections to the Publisher and Subscriber.
ServerConnection subscriberConn = new ServerConnection(subscriberName);
ServerConnection publisherConn = new ServerConnection(publisherName);

// Create the objects that we need.
MergePublication publication;
MergePullSubscription subscription;

try
{
    // Connect to the Subscriber.
    subscriberConn.Connect();

    // Define the pull subscription.
    subscription = new MergePullSubscription();
    subscription.ConnectionContext = subscriberConn;
    subscription.PublisherName = publisherName;
    subscription.PublicationName = publicationName;
    subscription.PublicationDBName = publicationDbName;
    subscription.DatabaseName = subscriptionDbName;

    // Define the publication.
    publication = new MergePublication();
    publication.Name = publicationName;
    publication.DatabaseName = publicationDbName;
    publication.ConnectionContext = publisherConn;

    // Delete the pull subscription, if it exists.
    if (subscription.IsExistingObject)
    {
        // Delete the pull subscription at the Subscriber.
        subscription.Remove();

        if (publication.LoadProperties())
        {
            publication.RemovePullSubscription(subscriberName, subscriptionDbName);
        }
        else
        {
            // Do something here if the publication does not exist.
            throw new ApplicationException(String.Format(
                "The publication '{0}' does not exist on {1}.",
                publicationName, publisherName));
        }
    }
    else
    {
        throw new ApplicationException(String.Format(
            "The subscription to {0} does not exist on {1}",
            publicationName, subscriberName));
    }
}
catch (Exception ex)
{
    // Implement the appropriate error handling here.
    throw new ApplicationException(String.Format(
        "The subscription to {0} could not be deleted.", publicationName), ex);
}
finally
{
    subscriberConn.Disconnect();
    publisherConn.Disconnect();
}

' Define the Publisher, publication, and databases.
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publisherName As String = publisherInstance
Dim subscriberName As String = subscriberInstance
Dim subscriptionDbName As String = "AdventureWorks2012Replica"
Dim publicationDbName As String = "AdventureWorks2012"

'Create connections to the Publisher and Subscriber.
Dim subscriberConn As ServerConnection = New ServerConnection(subscriberName)
Dim publisherConn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim publication As MergePublication
Dim subscription As MergePullSubscription

Try
    ' Connect to the Subscriber.
    subscriberConn.Connect()

    ' Define the pull subscription.
    subscription = New MergePullSubscription()
    subscription.ConnectionContext = subscriberConn
    subscription.PublisherName = publisherName
    subscription.PublicationName = publicationName
    subscription.PublicationDBName = publicationDbName
    subscription.DatabaseName = subscriptionDbName

    ' Define the publication.
    publication = New MergePublication()
    publication.Name = publicationName
    publication.DatabaseName = publicationDbName
    publication.ConnectionContext = publisherConn

    ' Delete the pull subscription, if it exists.
    If subscription.IsExistingObject Then

        ' Delete the pull subscription at the Subscriber.
        subscription.Remove()

        If publication.LoadProperties() Then
            publication.RemovePullSubscription(subscriberName, subscriptionDbName)
        Else
            ' Do something here if the publication does not exist.
            Throw New ApplicationException(String.Format( _
             "The publication '{0}' does not exist on {1}.", _
             publicationName, publisherName))
        End If
    Else
        Throw New ApplicationException(String.Format( _
         "The subscription to {0} does not exist on {1}", _
         publicationName, subscriberName))
    End If
Catch ex As Exception
    ' Implement the appropriate error handling here.
    Throw New ApplicationException(String.Format( _
        "The subscription to {0} could not be deleted.", publicationName), ex)
Finally
    subscriberConn.Disconnect()
    publisherConn.Disconnect()
End Try

Share via

Delete a Pull Subscription

Using SQL Server Management Studio

To delete a pull subscription at the Publisher

To delete a pull subscription at the Subscriber

Using Transact-SQL

To delete a pull subscription to a snapshot or transactional publication

To delete a pull subscription to a merge publication

Examples (Transact-SQL)

Using Replication Management Objects (RMO)

To delete a pull subscription to a snapshot or transactional publication

To delete a pull subscription to a merge publication

Examples (RMO)

See Also

Additional resources