Redigera

Dela via

Delete a Push Subscription

  • Artikel

Applies to: SQL Server Azure SQL Database

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

In This Topic

Using SQL Server Management Studio

Delete a push 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 push subscription at the Publisher

  1. Connect to the Publisher in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Publications folder.

  3. Expand the publication associated with the subscription you want to delete.

  4. Right-click the subscription, and then click Delete.

  5. In the confirmation dialog box, select whether to connect to the Subscriber to delete subscription information. If you clear the Connect to Subscriber checkbox, you should connect to the Subscriber later to delete the information.

To delete a push subscription at the Subscriber

  1. Connect to the Subscriber in SQL Server Management Studio, and then expand the server node.

  2. Expand the Replication folder, and then expand the Local Subscriptions folder.

  3. Right-click the subscription you want to delete, and then click Delete.

  4. 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

Push subscriptions can be deleted programmatically using replication stored procedures. The stored procedures used depend on the type of publication to which the subscription belongs.

To delete a push subscription to a snapshot or transactional publication

  1. 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.

  2. At the Subscriber on the subscription database, execute sp_subscription_cleanup (Transact-SQL) to remove replication metadata in the subscription database.

To delete a push subscription to a merge publication

  1. At the Publisher, execute sp_dropmergesubscription (Transact-SQL), specifying @publication, @subscriber and @subscriber_db. (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.

  2. At the Subscriber on the subscription database, execute sp_mergesubscription_cleanup (Transact-SQL). Specify @publisher, @publisher_db, and @publication. This removes merge metadata from the subscription database.

Examples (Transact-SQL)

This example deletes a push subscription to a transactional publication.

-- 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

This example deletes a push subscription to a merge publication.

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

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

Using Replication Management Objects (RMO)

The RMO classes that you use to delete a push subscription depend on the type of publication to which the push subscription is subscribed.

To delete a push subscription to a snapshot or transactional publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the TransSubscription class.

  3. Set the PublicationName, SubscriptionDBName, SubscriberName, and DatabaseName properties.

  4. Set the ServerConnection from step 1 for the ConnectionContext property.

  5. 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.

  6. Call the Remove method.

To delete a push subscription to a merge publication

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. Create an instance of the MergeSubscription class.

  3. Set the PublicationName, SubscriptionDBName, SubscriberName, and DatabaseName properties.

  4. Set the ServerConnection from step 1 for the ConnectionContext property.

  5. 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.

  6. Call the Remove method.

Examples (RMO)

You can delete push subscriptions programmatically by using Replication Management Objects (RMO).

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

//Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);

// Create the objects that we need.
TransSubscription subscription;

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

    // Define the pull subscription.
    subscription = new TransSubscription();
    subscription.ConnectionContext = conn;
    subscription.SubscriberName = subscriberName;
    subscription.PublicationName = publicationName;
    subscription.SubscriptionDBName = subscriptionDbName;
    subscription.DatabaseName = publicationDbName;

    // Delete the pull subscription, if it exists.
    if (subscription.IsExistingObject)
    {
        // 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
{
    conn.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 = "AdventureWorks2022Replica"
Dim publicationDbName As String = "AdventureWorks2022"

'Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)

' Create the objects that we need.
Dim subscription As TransSubscription

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

    ' Define the pull subscription.
    subscription = New TransSubscription()
    subscription.ConnectionContext = conn
    subscription.SubscriberName = subscriberName
    subscription.PublicationName = publicationName
    subscription.SubscriptionDBName = subscriptionDbName
    subscription.DatabaseName = publicationDbName

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

        ' 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
    conn.Disconnect()
End Try

Related content