Edit

sp_dropdistpublisher (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Drops a distribution Publisher. This stored procedure is executed at the Distributor on any database.

Transact-SQL syntax conventions

Syntax

sp_dropdistpublisher
    [ @publisher = ] N'publisher'
    [ , [ @no_checks = ] no_checks ]
    [ , [ @ignore_distributor = ] ignore_distributor ]
[ ; ]

Arguments

[ @publisher = ] N'publisher'

The Publisher to drop. @publisher is sysname, with no default.

Note

Using a custom port for the SQL Server publisher was introduced in SQL Server 2019 (15.x). If the SQL Server publisher is configured with a custom port, then when dropping such a publisher on the distributor, supply the publisher server name instead of <Hostname>,<PortNumber>.

[ @no_checks = ] no_checks

Specifies whether sp_dropdistpublisher checks that the Publisher has uninstalled the server as the Distributor. @no_checks is bit, with a default of 0.

  • If 0, replication verifies that the remote Publisher has uninstalled the local server as the Distributor. If the Publisher is local, replication verifies that there are no publication or distribution objects remaining on the local server.

  • If 1, all the replication objects associated with the distribution Publisher are dropped even if a remote Publisher can't be reached. After doing this, the remote Publisher must uninstall replication using sp_dropdistributor with @ignore_distributor = 1.

[ @ignore_distributor = ] ignore_distributor

Specifies whether distribution objects are left at the Distributor when the Publisher is removed. @ignore_distributor is bit, and can be one of these values:

  • 1 = distribution objects belonging to the @publisher remain at the Distributor.
  • 0 = distribution objects for the @publisher are cleaned-up at the Distributor.

Return code values

0 (success) or 1 (failure).

Remarks

sp_dropdistpublisher is used in all types of replication.

When dropping an Oracle Publisher, if unable to drop the Publisher, sp_dropdistpublisher returns an error and the Distributor objects for the Publisher are removed.

Examples

-- 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".

-- Disable publishing and distribution.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N'distribution';
SET @publisher = $(DistPubServer);
SET @publicationDB = N'AdventureWorks2022';

-- Disable the publication database.
USE [AdventureWorks2022]
EXEC sp_removedbreplication @publicationDB;

-- Remove the registration of the local Publisher at the Distributor.
USE master
EXEC sp_dropdistpublisher @publisher;

-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;

-- Remove the local server as a Distributor.
EXEC sp_dropdistributor;
GO

Permissions

Only members of the sysadmin fixed server role can execute sp_dropdistpublisher.

Related content