删除请求订阅

项目
03/06/2017

本主题介绍如何在 SQL Server 2014 中使用 SQL Server Management Studio、Transact-SQL 或复制管理对象 (RMO) 删除请求订阅。

本主题内容

删除请求订阅，使用：

SQL Server Management Studio

Transact-SQL

复制管理对象 (RMO)

使用 SQL Server Management Studio

在发布服务器上删除请求订阅（从的 “本地发布” SQL Server Management Studio文件夹中），或在订阅服务器上删除请求订阅（从 “本地订阅” 文件夹中）。删除订阅不会从订阅中删除对象或数据，必须对其手动删除。

在发布服务器上删除请求订阅

在 SQL Server Management Studio中连接到发布服务器，然后展开服务器节点。
展开 “复制” 文件夹，再展开 “本地发布” 文件夹。
展开与要删除的订阅关联的发布。
右键单击该订阅，再单击 “删除” 。
在确认对话框中，选择是否连接到订阅服务器以删除订阅信息。如果清除 “连接到订阅服务器” 复选框，则应在以后连接到订阅服务器以删除订阅信息。

在订阅服务器上删除请求订阅

在 SQL Server Management Studio中连接到订阅服务器，然后展开服务器节点。
展开 “复制” 文件夹，再展开 “本地订阅” 文件夹。
右键单击要删除的订阅，再单击 “删除” 。
在确认对话框中，选择是否连接到发布服务器以删除订阅信息。如果清除 “连接到发布服务器” 复选框，则应在以后连接到发布服务器以删除订阅信息。

“使用 Transact-SQL”

可以使用复制存储过程以编程方式删除请求订阅。所用的存储过程取决于订阅所属的发布的类型。

删除对快照发布或事务发布的请求订阅

在订阅服务器上，对订阅数据库执行 sp_droppullsubscription (Transact-SQL) 。指定 @publication、 @publisher和 @publisher_db。
在发布服务器上，对发布数据库执行 sp_dropsubscription (Transact-SQL) 。指定 @publication 和 @subscriber。为 @article 指定 all 值。 (可选) 如果无法访问分发服务器，请为@ignore_distributor指定值 1 以删除订阅，而无需删除分发服务器上的相关对象。

删除对合并发布的请求订阅

在订阅服务器上，对订阅数据库执行 sp_dropmergepullsubscription (Transact-SQL) 。指定 @publication、 @publisher和 @publisher_db。
在发布服务器上，对发布数据库执行 sp_dropmergesubscription (Transact-SQL) 。指定 @publication、 @subscriber和 @subscriber_db。为@subscription_type指定拉取值。 (可选) 如果无法访问分发服务器，请为@ignore_distributor指定值 1 以删除订阅，而无需删除分发服务器上的相关对象。

示例 (Transact-SQL)

以下示例删除对事务发布的请求订阅。第一个批处理在订阅服务器上执行，第二个批处理在发布服务器上执行。

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

以下示例删除对合并发布的请求订阅。第一个批处理在订阅服务器上执行，第二个批处理在发布服务器上执行。

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

使用复制管理对象 (RMO)

可通过使用复制管理对象 (RMO) 以编程方式删除请求订阅。用于删除请求订阅的 RMO 类由该请求订阅所订阅的发布类型决定。

删除对快照发布或事务发布的请求订阅

使用 ServerConnection 类创建与订阅服务器和发布服务器的连接。
创建 TransPullSubscription 类的一个实例，并设置 PublicationName、 DatabaseName、 PublisherName以及 PublicationDBName 属性。使用步骤 1 中的订阅服务器连接来设置 ConnectionContext 属性。
检查 IsExistingObject 属性以确保该订阅存在。如果此属性的值为 false，则步骤 2 中所定义的订阅属性不正确或者该订阅不存在。
调用 Remove 方法。
使用步骤 1 中的发布服务器连接，创建 TransPublication 类的实例。指定 Name、 DatabaseName 和 ConnectionContext。
调用 LoadProperties 方法。如果该方法返回 false，则表示步骤 5 中指定的属性不正确，或者服务器中不存在发布。
调用 RemovePullSubscription 方法。将订阅服务器和订阅数据库的名称分别指定给 subscriber 和 subscriberDB 参数。

删除对合并发布的请求订阅

使用 ServerConnection 类创建与订阅服务器和发布服务器的连接。
创建 MergePullSubscription 类的一个实例，并设置 PublicationName、 DatabaseName、 PublisherName以及 PublicationDBName 属性。使用步骤 1 中的连接来设置 ConnectionContext 属性。
检查 IsExistingObject 属性以确保该订阅存在。如果此属性的值为 false，则步骤 2 中所定义的订阅属性不正确或者该订阅不存在。
调用 Remove 方法。
使用步骤 1 中的发布服务器连接，创建 MergePublication 类的实例。指定 Name、 DatabaseName 和 ConnectionContext。
调用 LoadProperties 方法。如果该方法返回 false，则表示步骤 5 中指定的属性不正确，或者服务器中不存在发布。
调用 RemovePullSubscription 方法。将订阅服务器和订阅数据库的名称分别指定给 subscriber 和 subscriberDB 参数。

示例 (RMO)

本示例将删除对事务发布的请求订阅并删除发布服务器上的订阅注册。

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

本示例将删除对合并发布的请求订阅并删除发布服务器上的订阅注册。

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

另请参阅

订阅发布
 复制安全最佳做法

通过

删除请求订阅

使用 SQL Server Management Studio

在发布服务器上删除请求订阅

在订阅服务器上删除请求订阅

“使用 Transact-SQL”

删除对快照发布或事务发布的请求订阅

删除对合并发布的请求订阅

示例 (Transact-SQL)

使用复制管理对象 (RMO)

删除对快照发布或事务发布的请求订阅

删除对合并发布的请求订阅

示例 (RMO)

另请参阅

其他资源