Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_dropdistpublisher

  No additional text.


Syntax


-- Name:
--          sp_dropdistpublisher

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

-- Security:
--          Public
-- Requires Certificate signature for catalog access

-- Returns:
--          Success (0) or failure (1)

-- Owner:
--          

create procedure sys.sp_dropdistpublisher
(
	@publisher			sysname,
	@no_checks			bit = 0,
	@ignore_distributor	bit = 0
)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @distributor sysname
    DECLARE @distaccount nvarchar(127)
    DECLARE @proc nvarchar (255)
    DECLARE @retcode int
    DECLARE @privilege sysname
    DECLARE @return_status int
    DECLARE @found int
    DECLARE @distribdb sysname
    DECLARE @command nvarchar(255)
    DECLARE @active_value int
    DECLARE @publish_bit int
    DECLARE @mergepub_bit int
    DECLARE @vendor sysname
    DECLARE @publisher_type sysname
    DECLARE @tempdistr sysname
    DECLARE @tempdistrdb sysname
    DECLARE @cmd nvarchar(255)
    DECLARE @publisher_db sysname
	DECLARE @hrepl bit
    DECLARE @sa_login sysname

    SELECT @found = 0
	SELECT @hrepl = 1

    SELECT @publish_bit = 1
    SELECT @mergepub_bit = 4

    /*
    ** Security Check: require sysadmin
    */
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure that the publisher exists, that the name isn't
    ** NULL, and that the name conforms to the rules for identifiers.
    */

    IF @publisher IS NULL
	BEGIN
		RAISERROR (14043, 16, -1, '@publisher', 'sp_dropdistpublisher')
		RETURN (1)
	END

    EXECUTE @retcode = sys.sp_validname @publisher

    IF @retcode <> 0
    BEGIN
        RETURN (1)
    END

    -- Make publisher name case insensitive
    SELECT @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT

    /*
    ** Get distribution server information for remote RPC
    ** agent verification.
    */
    EXEC @retcode =	sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher,
														@distributor	= @distributor    OUTPUT,
														@distribdb		= @distribdb      OUTPUT,
														@publisher_type	= @publisher_type OUTPUT

    IF @@error <> 0 OR @retcode <> 0 OR @distributor IS NULL
	BEGIN
		RAISERROR (14071, 16, -1)
		RETURN (1)
    END

    /*
    ** Only a local distributor can be modified.
    */
    IF UPPER(@distributor) <> UPPER(@@SERVERNAME)
	BEGIN
		RAISERROR (14114, 16, -1, @@SERVERNAME)
		RETURN (1)
	END

    -- Verify publisher exists
    IF NOT EXISTS
    (
    	SELECT	*
    	FROM	msdb..MSdistpublishers
		WHERE	UPPER(name  collate database_default) = UPPER(@publisher) collate database_default
	)
    BEGIN
        RAISERROR (14080, 11, -1, @publisher)
        RETURN (1)
    END

    IF @publisher_type = N'MSSQLSERVER' AND UPPER(@publisher) = UPPER(@@SERVERNAME)
    BEGIN
        /*
        ** If @publisher is local,
        ** 1. check to make sure there is no subscriber for publisher in distribution database
        ** 2. check to make sure there no databases enabled for replication
        */
		IF @ignore_distributor = 0
		BEGIN
			-- Check to see if there are subscribers defined.
			EXEC @retcode = sys.sp_MSrepl_helpsubscriberinfo	@publisher  = @publisher,
																@subscriber = N'%',
																@found		= @found OUTPUT

			IF @retcode <> 0 OR @@ERROR <> 0
			BEGIN
				RETURN (1)
			END

			IF @found = 1
			BEGIN
				RAISERROR(21047, 16, -1)
				RETURN(1)
			END
		END

		-- Check to see if any db's enabled for replication
        IF EXISTS
        (
        	SELECT	*
        	FROM	master.dbo.sysdatabases
        	WHERE	(category & @publish_bit) <> 0
        	   OR	(category & @mergepub_bit) <> 0
        )
        BEGIN
            RAISERROR (21033, 16, -1, @@SERVERNAME)
            RETURN (1)
        END
    END
    ELSE
    BEGIN
        IF @no_checks = 0
        BEGIN
            /*
            ** If the publisher is remote, check the status of the distpublisher
            ** The status will be inactive if the remote publisher dropped the
            ** distributor.
            */
            IF @publisher_type = N'MSSQLSERVER'
            BEGIN
                IF EXISTS
                (
                	SELECT	*
                	FROM	msdb.dbo.MSdistpublishers
                	WHERE	UPPER(name  collate database_default) = UPPER(@publisher) collate database_default
                	  AND	active = 1
                )
                BEGIN
                    RAISERROR (14098, 16, -1, @publisher, @@SERVERNAME)
                    RETURN (1)
                END
            END
        END
    END

	IF @ignore_distributor = 0
	BEGIN
		IF NOT @publisher_type = N'MSSQLSERVER'
		BEGIN
			SELECT @command = QUOTENAME(@distribdb) + '.sys.sp_IHdroppublisher'
			exec @retcode = @command @publisher, @publisher_type, @no_checks
			
			IF ((@retcode != 0) OR (@@ERROR != 0)) AND (@no_checks = 0)
			BEGIN
				-- Note failure and return an error at the end
				SET @hrepl = 0
			END
		END

		SELECT @command = QUOTENAME(@distribdb) + '.dbo.sp_MSdistpublisher_cleanup'
		EXEC @retcode = @command @publisher
		IF @retcode <> 0 or @@error <> 0
		BEGIN
			RETURN(1)
		END
	END

    DECLARE @fExists int

	-- get the servername that was persisted in the sysservers table
    SELECT @publisher = sys.fn_getpersistedservernamecasevariation(@publisher) collate database_default
    SELECT @sa_login = SUSER_SNAME(0x01)


    EXEC @fExists = sys.sp_MSIfExistsRemoteLogin @publisher,
    											'distributor_admin',
    											@sa_login

    IF (@fExists = 1)
    BEGIN
		EXEC @retcode = sys.sp_dropremotelogin	@publisher,
												'distributor_admin',
												@sa_login

		IF @@ERROR <> 0 OR @retcode <> 0
		BEGIN
			RETURN (1)
		END
    END

    EXEC @fExists = sys.sp_MSIfExistsRemoteLogin	@publisher,
    												'distributor_admin',
    												'distributor_admin'

    IF (@fExists = 1)
    BEGIN
		EXECUTE @retcode = sys.sp_dropremotelogin	@publisher,
       												'distributor_admin',
       												'distributor_admin'
       												
		IF @@ERROR <> 0 OR @retcode <> 0
		BEGIN
			RETURN (1)
		END
    END

	-- store entry prior to delete so we can manually rollback
	-- in case of a failure durring the remaining steps. we can
	-- not use transactions since sp_dropserver can not be exec'd
	-- within a transaction... and we can not wait on the delete
	-- till after the dropserver since drop server will check for
	-- this entry and fail if there is a distpub for this server
	SELECT *
		INTO #MSdistpublishers
		FROM msdb..MSdistpublishers
		WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default
	
    -- Remove the publisher now that most of cleanup has completed
	-- only thing that remains is the dop of server...
    DELETE msdb..MSdistpublishers
    	WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default
    IF @@ERROR <> 0
    BEGIN
    	RETURN (1)
    END

	IF (@publisher_type = N'MSSQLSERVER')
	BEGIN
		
		-- SQL Server specific
		-- drop RPC server entry for remote publisher
		
		IF (upper(@distributor) != upper(@publisher))
		BEGIN
			IF EXISTS
			(
				SELECT	*
				FROM	master.dbo.sysservers
				WHERE	upper(srvname collate database_default) = upper(@publisher)
			)
			BEGIN
				EXEC @retcode = sys.sp_dropserver @publisher, 'droplogins'

				IF (@@error != 0 or @retcode != 0)
				BEGIN
					GOTO FAILURE
				END
			END
		END
	END
	ELSE
	BEGIN
		-- For heterogeneous publishers drop the remote server and it's logins
		exec @retcode = sys.sp_dropserver @publisher, 'droplogins'

		IF @@ERROR <> 0 OR @retcode <> 0
		BEGIN
			GOTO FAILURE
		END

		-- Drop expired subscription cleanup job and alerts
		EXEC @retcode = sys.sp_MSrepl_drop_expired_sub_cleanup_job @publisher

		IF (@@ERROR != 0 OR @retcode != 0)
		BEGIN
			GOTO FAILURE
		END
	END

	-- Override status and return an error if HREPL failed.
	-- Post-hrepl steps will have completed and left the distributor
	-- in a consistent state.
	IF @hrepl = 0
	BEGIN
		-- Report failure to drop publisher info but don't finish cleanup
		RAISERROR(21749, 16, -1, @publisher)
		GOTO FAILURE
	END

    RETURN (0)
FAILURE:
	INSERT INTO msdb..MSdistpublishers
		SELECT *
			FROM #MSdistpublishers

	RETURN (1)
END

 
Last revision 2008RTM
See also

  sp_dropdistributor (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSrepl_adddistpublisher (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash