This topic has not yet been rated - Rate this topic

sp_dropdistpublisher (Transact-SQL)

SQL Server 2005

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

Topic link icon Transact-SQL Syntax Conventions


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

Arguments

[ @publisher = ] 'publisher': Is the Publisher to drop. publisher is sysname, with no default.

[ @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 cannot 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.

Permissions

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

Example

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

-- Disable the publication database.
USE [AdventureWorks]
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

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD

sp_dropdistpublisher (Transact-SQL)

Reference

Other Resources

Help and Information

Community Additions