Edit

Share via

sp_dropmergepublication (Transact-SQL)

  • Article
  • 11/24/2023

Applies to: SQL Server

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

Transact-SQL syntax conventions

Syntax

sp_dropmergepublication
    [ @publication = ] N'publication'
    [ , [ @ignore_distributor = ] ignore_distributor ]
    [ , [ @reserved = ] reserved ]
    [ , [ @ignore_merge_metadata = ] ignore_merge_metadata ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication to drop. @publication is sysname, with no default. If all, all existing merge publications are removed as well as the Snapshot Agent job associated with them. If you specify a particular value for @publication, only that publication and its associated Snapshot Agent job are dropped.

[ @ignore_distributor = ] ignore_distributor

Used to drop a publication without doing cleanup tasks at the Distributor. @ignore_distributor is bit, with a default of 0. This parameter is also used when reinstalling the Distributor.

[ @reserved = ] reserved

Reserved for future use. @reserved is bit, with a default of 0.

[ @ignore_merge_metadata = ] ignore_merge_metadata

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

Return code values

0 (success) or 1 (failure).

Remarks

sp_dropmergepublication is used in merge replication.

sp_dropmergepublication recursively drops all articles that are associated with a publication and then drops the publication itself. A publication can't be removed if it's one or more subscriptions to it. For information about how to remove subscriptions, see Delete a Push Subscription and Delete a Pull Subscription.

Executing sp_dropmergepublication to drop a publication doesn't 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

DECLARE @publication AS sysname
DECLARE @publicationDB    AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 
SET @publicationDB = N'AdventureWorks'

-- Remove the merge publication.
USE [AdventureWorks]
EXEC sp_dropmergepublication @publication = @publication;

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

Permissions

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

Related content