Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_dropsubscriber

  No additional text.


Syntax
create procedure sys.sp_MSrepl_dropsubscriber
(
	@subscriber			sysname,
	@reserved			nvarchar(50),
	@ignore_distributor	bit,
	@publisher			sysname,
	@noraise			int = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @db_name			sysname
    DECLARE @foundSubscription	int
    DECLARE @ClearSubStatus		int
    DECLARE @command			nvarchar(255)
    DECLARE @transpublishdb_bit	int
    DECLARE @mergepublishdb_bit	int
    DECLARE @distdb_bit	        int
    DECLARE @distributor		sysname
    DECLARE @distribdb			sysname
    DECLARE @distproc			nvarchar (300)
    DECLARE @retcode			int
    DECLARE @type				nvarchar(10)
    DECLARE @publisher_local	sysname

    SELECT @transpublishdb_bit	= 1
    SELECT @mergepublishdb_bit	= 4
    SELECT @distdb_bit			= 16
    SELECT @ClearSubStatus		= 1

	-- 'sysadmin' Security check was moved to sp_dropsubscriber.
	-- NOTE:  sp_MSrepl_dropsubscriber is also called directly by sp_dropsubscription.
	--        'db_owner' is sufficient authorization when called in this manner.
    /*
    ** Security Check: require sysadmin or DBO of publishing database
    */
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0) AND
        (ISNULL(IS_MEMBER('db_owner'),0) = 0)
    BEGIN
         RAISERROR(21050,16,-1)
         RETURN (1)
    END

    /*
    ** If @publisher is NULL, set @publisher_local to @@SERVERNAME
    ** otherwise, set @publisher_local to @publisher
    */
    IF @publisher IS NULL
    BEGIN
        select @publisher_local = publishingservername()
    END
    ELSE
    BEGIN
		/* For heterogeneous publisher, the current database must be the */
		/* distribution db for the publisher */
        select @publisher_local = @publisher

		IF NOT EXISTS
        (
        	SELECT	*
        	FROM	master.dbo.sysservers s,
        			msdb..MSdistpublishers m
			WHERE	UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)
			  AND	UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
			  AND	UPPER(m.distribution_db collate database_default) = UPPER(DB_NAME()) collate database_default
        )
        BEGIN
        	RETURN (1)
        END
    END

    /*
    ** Parameter Check:  @subscriber.
    ** Check to make sure that the subscriber exists.
    */

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

    EXECUTE @retcode = sys.sp_validname @subscriber

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

    IF LOWER(@subscriber) = 'all'
    BEGIN
        DECLARE #hCdropsubscriber_all CURSOR LOCAL FAST_FORWARD FOR
		SELECT	srvname
		FROM	master.dbo.sysservers
		WHERE	srvstatus & 4 <> 0
        FOR READ ONLY

        OPEN #hCdropsubscriber_all
        FETCH #hCdropsubscriber_all INTO @subscriber

        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE @retcode  = sys.sp_dropsubscriber	@subscriber         = @subscriber,
							@publisher          = @publisher,
							@ignore_distributor = @ignore_distributor,
							@reserved           = @reserved
            IF @@ERROR <> 0 OR @retcode <> 0
                RETURN (1)

            FETCH #hCdropsubscriber_all INTO @subscriber
        end

        return(0)
    end


    /*
    ** There should be no subscription by the subscriber
    ** Open a cursor the published databases.
    **
    */
    IF @publisher IS NULL
    BEGIN
	DECLARE #hCdropsubscriber CURSOR LOCAL FAST_FORWARD FOR
	SELECT	name,
			N'tran'
	FROM	master.dbo.sysdatabases
	WHERE	(category & @transpublishdb_bit) <> 0
			and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
	UNION
	SELECT	name, N'merge' from master.dbo.sysdatabases
	WHERE	(category & @mergepublishdb_bit) <> 0
			and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
	       and has_dbaccess(name) = 1
	FOR READ ONLY

	OPEN #hCdropsubscriber
	FETCH #hCdropsubscriber INTO @db_name, @type

	WHILE (@@fetch_status <> -1)
	BEGIN
            IF LOWER(@reserved) = N'drop_subscriptions'
            BEGIN
                if @type = N'tran'
                begin
                    SELECT @command = quotename(@db_name) + N'.sys.sp_dropsubscription'
                    EXECUTE @retcode  = @command
			@publication        = N'all',
			@article            = N'all',
			@subscriber         = @subscriber,
			@publisher          = @publisher,
			@ignore_distributor = @ignore_distributor
                end
                if @type = 'merge'
                begin
                    SELECT @command = quotename(@db_name) + N'.sys.sp_dropmergesubscription'
                    EXECUTE @retcode  = @command
                    	@publication = N'all',
                    	@subscriber = @subscriber,
                    	@subscription_type = N'both',
                    	@ignore_distributor = @ignore_distributor
                end
                IF @@ERROR <> 0 OR @retcode <> 0
                    RETURN (1)
            END

            -- Set @foundSubscription to not null so that sp_helpsubscription will not display
            -- result set
            select @foundSubscription = 0

            if @type = 'tran'
            begin
                SELECT @command = quotename(@db_name) + N'.sys.sp_helpsubscription'
                EXECUTE @retcode  = @command
						@publisher   = @publisher,
						@publication = N'%',
						@article     = N'%',
						@subscriber  = @subscriber,
						@found       = @foundSubscription  OUTPUT

                IF @@ERROR <> 0 OR @retcode <> 0
                    RETURN (1)
            end
            else
            begin
                SELECT @command = quotename(@db_name) + '.sys.sp_helpmergesubscription'
                EXECUTE @retcode  =	@command	@publication 		= N'%',
						@subscriber  		= @subscriber,
						@subscription_type	= N'both',
						@found	     		= @foundSubscription  OUTPUT

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


            IF @foundSubscription <> 0
            BEGIN

                IF ISNULL(@noraise, 0) = 0
                BEGIN
                    RAISERROR ( 14144,  16, -1, @subscriber, @db_name)
                    RETURN (1)
                END
			
                -- Return without error if @noraise = 1
                -- Used when called from sp_dropsubscription to drop
                -- subscriber when all subscriptions have been dropped.
                RETURN (0)
            END		

            FETCH #hCdropsubscriber INTO @db_name, @type

        END
    END
    ELSE
    BEGIN
        IF LOWER(@reserved) = N'drop_subscriptions'
        BEGIN
            -- For heterogeneous publishers execute sp_dropsubscription for the publisher and
            -- subscriber in the current database.
            -- @ignore_distributor should always be set to 0 since this will always execute
            -- on the Oracle distributor.
            SELECT @command = N'sys.sp_dropsubscription'
            EXECUTE @retcode  = @command
		@publication        = N'all',
		@article            = N'all',
		@subscriber         = @subscriber,
		@publisher          = @publisher,
		@ignore_distributor = 0

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

	SELECT @foundSubscription = 0
	SELECT @db_name = DB_NAME()

	SELECT @command = N'sys.sp_helpsubscription'
	EXECUTE @retcode  = @command
		@publisher   = @publisher,
		@publication = N'%',
		@article     = N'%',
		@subscriber  = @subscriber,
		@found       = @foundSubscription  OUTPUT

	IF @foundSubscription <> 0
	BEGIN

                IF ISNULL(@noraise, 0) = 0
                BEGIN
                    RAISERROR ( 14144,  16, -1, @subscriber, @db_name)
                    RETURN (1)
                END
			
                -- Return without error if @noraise = 1
                -- Used when called from sp_dropsubscription to drop
                -- subscriber when all subscriptions have been dropped.
                RETURN (0)
	END		

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

    END

    /*
    ** Drop the subsubscriber_info in the distribution database
    */
    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin


        /*
        ** Get distribution server information for remote RPC
        ** agent verification.
        */
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher  = @publisher,
															@rpcsrvname = @distributor OUTPUT,
															@distribdb  = @distribdb   OUTPUT
        IF @@error <> 0 OR @retcode <> 0
            BEGIN
            RAISERROR (14071, 16, -1)
               RETURN (1)
        END

        /*
        ** Delete information from MSsubscriber_info in the distribution db
        */
        if @distribdb is not null
        begin
            SELECT @distproc =	QUOTENAME(RTRIM(@distributor)) + '.' +
            					QUOTENAME(RTRIM(@distribdb)) +
            					'.dbo.sp_MSdrop_subscriber_info'
            					
            EXEC @retcode = @distproc
							@publisher  = @publisher_local,
							@subscriber = @subscriber

            IF @@error <> 0 OR @retcode <> 0
                BEGIN
                RAISERROR (14042, 16, -1)
                RETURN (1)
            END
        end
    end

    -- We have already validated the existence of the server at this point
    SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default

	IF @distribdb IS NOT NULL AND UPPER(@@SERVERNAME) = UPPER(@distributor)
	BEGIN
	
		-- Determine whether SUBSCRIBER bit in SRVSTATUS can be cleared
		EXECUTE @ClearSubStatus = sys.sp_MSDropCanClearSubscriberStatus @publisher_local,
											@distributor,
											@subscriber,
											@distribdb
	END

	IF @ClearSubStatus = 1
	BEGIN
		-- Turn off the subscriber server option(s)
		SELECT @subscriber = sys.fn_getpersistedservernamecasevariation(@subscriber) collate database_default
		if (@subscriber is not null)
			EXEC @retcode = sys.sp_MSdrop_subserver @subscriber = @subscriber
		IF @@error <> 0 OR @retcode <> 0
		BEGIN
			RETURN (1)
		END
	END

    RAISERROR (14062, 10, -1)
END

 
Last revision 2008RTM
See also

  sp_dropsubscriber (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSrepl_dropsubscription (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