This topic has not yet been rated - Rate this topic

sp_dropdistributor (Transact-SQL)

SQL Server 2008

Uninstalls the Distributor. This stored procedure is executed at the Distributor on any database except the distribution database.

Topic link icon Transact-SQL Syntax Conventions

Syntax

sp_dropdistributor [ [ @no_checks= ] no_checks ] 
    [ , [ @ignore_distributor= ] ignore_distributor ]

Arguments

[ @no_checks=] no_checks

Indicates whether to check for dependent objects before dropping the Distributor. no_checks is bit, with a default of 0.

If 0, sp_dropdistributor checks to make sure that all publishing and distribution objects in addition to the Distributor have been dropped.

If 1, sp_dropdistributor drops all the publishing and distribution objects prior to uninstalling the distributor.

[ @ignore_distributor=] ignore_distributor

Indicates whether this stored procedure is executed without connecting to the Distributor. ignore_distributor is bit, with a default of 0.

If 0, sp_dropdistributor connects to the Distributor and removes all replication objects. If sp_dropdistributor is unable to connect to the Distributor, the stored procedure fails.

If 1, no connection is made to the Distributor and the replication objects are not removed. This is used if the Distributor is being uninstalled or is permanently offline. The objects for this Publisher at the Distributor are not removed until the Distributor is reinstalled at some future time.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_dropdistributor is used in all types of replication.

If other Publisher or distribution objects exist on the server, sp_dropdistributor fails unless @no_checks is set to 1.

This stored procedure must be executed after dropping the distribution database by executing sp_dropdistributiondb.

Example

TSQL

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

Permissions

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

Tasks

How to: Disable Publishing and Distribution (Replication Transact-SQL Programming)

Reference

sp_adddistributor (Transact-SQL)

sp_changedistributor_property (Transact-SQL)

sp_helpdistributor (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