sp_droppullsubscription (Transact-SQL)
Collapse the table of content
Expand the table of content

sp_droppullsubscription (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Drops a subscription at the current database of the Subscriber. This stored procedure is executed at the Subscriber on the pull subscription database.

Topic link icon Transact-SQL Syntax Conventions

sp_droppullsubscription [ @publisher= ] 'publisher' 
        , [ @publisher_db= ] 'publisher_db' 
        , [ @publication= ] 'publication'
    [ , [ @reserved= ] reserved ]

[ @publisher= ] 'publisher'

Is the remote server name. publisher is sysname, with no default. If all, the subscription is dropped at all the Publishers.

[ @publisher_db= ] 'publisher_db'

Is the name of the Publisher database. publisher_db is sysname, with no default. all means all the Publisher databases.

[ @publication= ] 'publication'

Is the publication name. publication is sysname, with no default. If all, the subscription is dropped to all the publications.

[ @reserved= ] reserved

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

0 (success) or 1 (failure)

sp_droppullsubscription is used in snapshot replication and transactional replication.

sp_droppullsubscription deletes the corresponding row in the MSreplication_subscriptions (Transact-SQL) table and the corresponding Distributor Agent at the Subscriber. If no rows are left in MSreplication_subscriptions (Transact-SQL), it drops the table.

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

Only members of the sysadmin fixed server role or the user who created the pull subscription can execute sp_droppullsubscription. The db_owner fixed database role is only able to execute sp_droppullsubscription if the user who created the pull subscription belongs to this role.

Community Additions

© 2016 Microsoft