This topic has not yet been rated - Rate this topic

sp_droppublication (Transact-SQL)

SQL Server 2008 R2

Drops a publication and its associated Snapshot Agent. All subscriptions must be dropped before dropping a publication. The articles in the publication are dropped automatically. This stored procedure is executed at the Publisher on the publication database.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_droppublication [ @publication= ] 'publication' 
    [ , [ @ignore_distributor = ] ignore_distributor ]

Arguments

[ @publication= ] 'publication': Is the name of the publication to be dropped. publication is sysname, with no default. If all is specified, all publications are dropped from the publication database, except for those with subscriptions.
[ @ignore_distributor = ] ignore_distributor: Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_droppublication is used in snapshot replication and transactional replication.

sp_droppublication recursively drops all articles associated with a publication and then drops the publication itself. A publication cannot be removed if it has one or more subscriptions to it. For information about how to remove subscriptions, see How to: Delete a Push Subscription (Replication Transact-SQL Programming) and How to: Delete a Pull Subscription (Replication Transact-SQL Programming).

Executing sp_droppublication to drop a publication does not remove published objects from the publication database or the corresponding objects from the subscription database. Use DROP <object> to remove these objects manually if necessary.

Examples

TSQL

DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
SET @publicationDB = N'AdventureWorks2008R2'; 
SET @publication = N'AdvWorksProductTran'; 

-- Remove a transactional publication.
USE [AdventureWorks2008R2]
EXEC sp_droppublication @publication = @publication;

-- Remove replication objects from the database.
USE [master]
EXEC sp_replicationdboption 
  @dbname = @publicationDB, 
  @optname = N'publish', 
  @value = N'false';
GO

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_droppublication.

Tasks

How to: Delete a Publication (Replication Transact-SQL Programming)

Reference

sp_addpublication (Transact-SQL)

sp_changepublication (Transact-SQL)

sp_helppublication (Transact-SQL)

Replication Stored Procedures (Transact-SQL)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD