如何删除请求订阅(复制 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'AdventureWorks';
USE [AdventureWorksReplica]
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 [AdventureWorks]
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'AdventureWorks';
USE [AdventureWorksReplica]
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'AdventureWorksReplica';
USE [AdventureWorks]
EXEC sp_dropmergesubscription
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriptionDB;
GO