如何删除推送订阅(复制 Transact-SQL 编程)

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

删除对快照发布或事务发布的推送订阅

  1. 在发布服务器上,对发布数据库执行sp_dropsubscription (Transact-SQL)。 指定 @publication@subscriber。 将 @article 的值指定为 all。 (可选)如果无法访问分发服务器,将 @ignore_distributor 的值指定为 1,以便在不删除分发服务器上相关对象的情况下删除订阅。

  2. 在订阅服务器上,对订阅数据库执行 sp_subscription_cleanup (Transact-SQL),以便删除订阅数据库中的复制元数据。

删除对合并发布的推送订阅

  1. 在发布服务器上执行 sp_dropmergesubscription (Transact-SQL),同时指定 @publication@subscriber@subscriber_db。 (可选)如果无法访问分发服务器,将 @ignore_distributor 的值指定为 1,以便在不删除分发服务器上相关对象的情况下删除订阅。

  2. 在订阅服务器上,对订阅数据库执行 sp_mergesubscription_cleanup (Transact-SQL)。 指定 @publisher@publisher_db@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 [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 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