刪除提取訂閱

發行項
06/18/2013

本主題描述如何使用 SQL Server Management Studio、Transact-SQL 或 Replication Management Objects (RMO) 來刪除 SQL Server 2012 中的提取訂閱。

本主題內容

若要刪除提取訂閱，請使用：

SQL Server Management Studio

Transact-SQL

Replication Management Objects (RMO)

使用 SQL Server Management Studio

在發行者端 (從 SQL Server Management Studio 的 [本機發行集] 資料夾) 或訂閱者端 (從 [本機訂閱] 資料夾) 刪除提取訂閱。刪除訂閱並無法從訂閱中移除物件或資料，這些必須手動移除。

若要在發行者端刪除提取訂閱

連接到 SQL Server Management Studio 中的發行者，然後展開伺服器節點。
展開 [複寫] 資料夾，然後展開 [本機發行集] 資料夾。
展開與您要刪除的訂閱相關聯的發行集。
以滑鼠右鍵按一下訂閱，然後按一下 [刪除]。
在確認對話方塊中，選取是否要連接訂閱者以刪除訂閱資訊。如果清除 [連接到訂閱者] 核取方塊，則應在稍後連接到「訂閱者」以刪除資訊。

若要在訂閱者端刪除提取訂閱

連接到 SQL Server Management Studio 中的訂閱者，然後展開伺服器節點。
展開 [複寫] 資料夾，然後展開 [本機訂閱] 資料夾。
以滑鼠右鍵按一下您要刪除的訂閱，然後按一下 [刪除]。
在確認對話方塊中，選取是否要連接發行者以刪除訂閱資訊。若您清除 [連接到發行者] 核取方塊，應於稍後連接到發行者以便刪除資訊。

搭配回到頁首連結使用的箭頭圖示 [Top]

使用 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 指定 pull 的值。 (選擇性) 如果無法存取散發者，請為 @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

搭配回到頁首連結使用的箭頭圖示 [Top]

使用 Replication Management Objects (RMO)

您可以使用 Replication Management Objects (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

搭配回到頁首連結使用的箭頭圖示 [Top]

請參閱

概念

訂閱發行集

複寫安全性最佳做法

共用方式為

刪除提取訂閱

使用 SQL Server Management Studio

若要在發行者端刪除提取訂閱

若要在訂閱者端刪除提取訂閱

使用 Transact-SQL

刪除快照式或交易式發行集的提取訂閱

刪除合併式發行集的提取訂閱

範例 (Transact-SQL)

使用 Replication Management Objects (RMO)

刪除快照式或交易式發行集的提取訂閱

刪除合併式發行集的提取訂閱

範例 (RMO)

請參閱

概念

其他資源