Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Drops a distribution Publisher. This stored procedure is executed at the Distributor on any database.
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.
-- 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'AdventureWorks2012';
-- Disable the publication database.
EXEC sp_removedbreplication @publicationDB;
-- Remove the registration of the local Publisher at the Distributor.
EXEC sp_dropdistpublisher @publisher;
-- Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
-- Remove the local server as a Distributor.