發行項
04/02/2012

如何：刪除提取訂閱 (複寫 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 的值來刪除此訂閱，而不需要移除散發者端上的相關物件。

範例

下列範例會刪除交易式發行集的提取訂閱。第一批次是在「訂閱者」上執行，而第二批次是在「發行者」上執行。

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

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

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

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