Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_dropsubscription

 

Also see http://support.microsoft.com/kb/924807 for a performance fix of this procedure.




Syntax
create procedure sys.sp_MSrepl_dropsubscription
(
	@publication sysname = NULL,
	@article sysname = NULL,
	@subscriber sysname,
	@destination_db sysname =NULL,	-- If null, all the subscriptions from that subscriber will be dropped
	@ignore_distributor bit = 0,
	@reserved nvarchar(10) = NULL,	-- reserved, used when calling from other system
					-- stored procedures, it will be set to 'internal'.
					-- It should never be used directly
	@publisher sysname = NULL,
	@publisher_type		sysname
)
AS
BEGIN
    /*
    ** Declarations.
    */

    DECLARE @subscriber_bit smallint
    DECLARE @cmd nvarchar(255)
    DECLARE @srvid smallint
	DECLARE @pubid int
    DECLARE @artid int
    DECLARE @obj_tmp int
    DECLARE @retcode int
    DECLARE @internal nvarchar(10)
    DECLARE @expand_article nvarchar(10)
    DECLARE @push tinyint
    DECLARE @virtual_id smallint
    DECLARE @login_name sysname
    DECLARE @immediate_sync bit
    DECLARE @subscription_type int
    DECLARE @qualified_subscription_name nvarchar(512)
    DECLARE @sync_method tinyint
    DECLARE @concurrent tinyint
    DECLARE @concurrent_char tinyint
    DECLARE @publisher_local sysname

    /*
    ** Set local publisher value
    */
    IF @publisher IS NULL
    BEGIN
        set @publisher_local = publishingservername()
    END
    ELSE
    BEGIN
        set @publisher_local = @publisher
    END

    -- Check to see if database is activated for publication
    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    /*
    ** Initializations.
    */
    SET NOCOUNT ON
    SELECT @subscriber_bit = 4  /* Const: subscription server status */
    SELECT @push = 0        /* Const: push publication type */
    SELECT @virtual_id = -1 /* Const: virtual subscriber id */
    SELECT @internal = 'internal' /* Const: Flag of calling internally from system */
                                  /* stored procedures     */
    SELECT @expand_article = 'expand_art'
        /* Const: Flag of calling after expand 'all' for @article  */
    SELECT @concurrent = 3
    SELECT @concurrent_char = 4

    /*
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security
    */

    -- Test distributor RPC connection before open the cursor

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        declare @distributor sysname
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher  = @publisher,
												@rpcsrvname = @distributor OUTPUT
        if @@ERROR <> 0 or @retcode <> 0
            return(1)
    end

    /*
    ** If the @subscriber is 'all', the user wants to cancel all subscriptions
    ** to the specified article(s).
    */

    IF LOWER(@subscriber) = 'all'
    BEGIN
    	BEGIN TRANSACTION
    	SAVE TRANSACTION dropallsubscriptions

        DECLARE hCdrop_subscription1  CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT srvname
                FROM dbo.syssubscriptions
                	where srvid <> -1 and srvname is not null and len(srvname) > 0

		-- With ANSI Defaults ON, the cursor will automatically
		-- be closed on commit.   Since this proc gets called recursively,
		-- this can happen.  So check before opening.
		IF CURSOR_STATUS('local','hCdrop_subscription1') = -1
			OPEN hCdrop_subscription1

        -- must owner qual proc invoke to exec inside server on restore/attach cleanup
        FETCH hCdrop_subscription1 INTO @subscriber
        WHILE (@@fetch_status <> -1)
        BEGIN
            -- We are already executing in the correct database context for both SQL Server
            -- publishers and heterogeneous publishers. sp_MSrepl_dropsubscription can be
            -- called instead of sp_dropsubscription.
            EXECUTE @retcode = sys.sp_MSrepl_dropsubscription
                @publication = @publication,
                @article = @article,
                @subscriber  = @subscriber,
                @destination_db = 'all',
                @ignore_distributor = @ignore_distributor,
                @reserved = @reserved,
                @publisher = @publisher,
                @publisher_type = @publisher_type

			IF @retcode != 0 or @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION dropallsubscriptions
				COMMIT TRANSACTION

				RETURN 1
			END
			
		    IF CURSOR_STATUS('local','hCdrop_subscription1') = -1
	            OPEN hCdrop_subscription1
	            FETCH hCdrop_subscription1 INTO @subscriber
        END

        CLOSE hCdrop_subscription1
        DEALLOCATE hCdrop_subscription1

        COMMIT TRANSACTION dropallsubscriptions
        RETURN (0)
    END

    /*
    ** Parameter Check: @subscriber.
    **
    ** Check if the server exists and that it is a subscription server.
    **
    */
    IF @subscriber IS NULL
        BEGIN
            SELECT @srvid = @virtual_id
        END
    ELSE
        BEGIN
            /* validate name and get subscriber ID  and server status  */
            EXECUTE @retcode = sys.sp_validname @subscriber
            IF @retcode <> 0
            RETURN (1)

        END

    -- Have to check @destination_db before expanding publications and articles
    -- Otherwise, the error will not be caught because the cursor will return zero row.
    if @destination_db is not null and LOWER(@destination_db) <> 'all'
    begin
        if not exists (select * from syssubscriptions where
            ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
            or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ) )
            and dest_db = @destination_db)
        begin
            RAISERROR (14055, 11, -1)
            RETURN (1)
        end
    end

    /*
    ** If the @publication is 'all', the user wants to cancel all subscriptions
    ** for all publications associated with the specified @subscriber.
    */

    IF LOWER(@publication) = 'all'
	BEGIN
		BEGIN TRANSACTION
		SAVE TRANSACTION dropallsubscriptions

		DECLARE hCdrop_subscription2 CURSOR LOCAL FAST_FORWARD FOR
		SELECT	DISTINCT a.name
		FROM	syspublications a,
				sysextendedarticlesview b,
				syssubscriptions c
		WHERE ((c.srvname is not null and len(c.srvname) > 0 and c.srvname = UPPER(@subscriber)  )
					or ((c.srvname is null or len(c.srvname) = 0) and c.srvid = @virtual_id))
				-- @destination_db will not be expanded before @publication is expanded.
		  AND	(
					c.dest_db = @destination_db
					OR @destination_db is null
					OR LOWER(@destination_db) = 'all'
				)
		  AND	a.pubid = b.pubid
		  AND	b.artid = c.artid

		OPEN hCdrop_subscription2
		FETCH hCdrop_subscription2 INTO @publication
		WHILE (@@fetch_status <> -1)
		BEGIN
			EXEC @retcode =  sys.sp_MSrepl_dropsubscription	@publication		= @publication,
														@article			= 'all',
														@subscriber			= @subscriber,
														@destination_db		= @destination_db,
														@ignore_distributor	= @ignore_distributor,
														@reserved			= @reserved,
														@publisher			= @publisher,
														@publisher_type			= @publisher_type

			IF @retcode != 0 or @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION dropallsubscriptions
				COMMIT TRANSACTION

				RETURN 1
			END

			FETCH hCdrop_subscription2 INTO @publication
		END
		
		CLOSE hCdrop_subscription2
		DEALLOCATE hCdrop_subscription2
		
		COMMIT TRANSACTION dropallsubscriptions
		
		-- Call sp_MSrepl_dropsubscriber to drop the subscriber if there are now no subscriptions
		-- associated with the subscriber.  This call is intentionally made outside the
		-- transaction, since a failure here should not prevent dropping the subscription.
		-- We will ignore all errors and not just the error returned when there are existing
		-- subscriptions and only make the call if we are not in a transaction.
		IF NOT @@trancount > 0
		BEGIN
			EXECUTE sys.sp_MSrepl_dropsubscriber	@subscriber = @subscriber,
													@publisher = @publisher,
													@ignore_distributor = @ignore_distributor,
													@noraise = 1,
													@reserved = NULL
		END

		RETURN (0)
	END

    /*
    ** Parameter Check: @publication.
    ** Check to make sure that the publication exists and that it conforms
    ** to the rules for identifiers.
    */

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

    
    -- Verify publication exists
    
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

	IF (@pubid IS NULL)
	BEGIN
		RAISERROR (20026, 11, -1, @publication)
		RETURN (1)
	END

    /* Get subscription type of the publication */
	SELECT	@immediate_sync	= immediate_sync,
			@sync_method	= sync_method
	FROM	syspublications
	WHERE	pubid = @pubid

    /*
    ** Parameter Check:  @article
    */

    /* @article can not be null     */
    IF @article IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_dropsubscription')
            RETURN (1)
        END


    /*
    ** If publication is of concurrent sync, then all articles must
    ** be unsubscribed to
    */
    /*IF @sync_method in (@concurrent, @concurrent_char) AND
       LOWER(@article) != 'all' AND
       @reserved NOT IN( @expand_article, @internal )
    BEGIN
        RAISERROR( 14102, 16, -1 )
        RETURN (1)
    END*/

    /** For immediate_sync publication, @article has to be 'all'     */
    -- Relax this constraint since users will need to do this before dropping
    -- an article
    /*
    IF @reserved <> @internal AND @reserved <> @expand_article
        AND @immediate_sync = 1
        AND NOT LOWER(@article) = 'all'
        BEGIN
            RAISERROR (14122, 16, -1)
            RETURN (1)
        END
    */

    /*
    ** If the @article is 'all', the user wants to cancel all
    ** subscriptions on this publisher associated with the given @subscriber
    ** and @publication.
    */

    IF LOWER(@article) = 'all'
	BEGIN
		/* Make the operation automic for immediate_sync publications */
		BEGIN TRANSACTION
		SAVE TRANSACTION dropallsubscriptions

		IF @reserved IS NULL
			SELECT @reserved = @expand_article

		DECLARE hCdrop_subscription3  CURSOR LOCAL FAST_FORWARD FOR
		SELECT	DISTINCT art.name
		FROM	sysextendedarticlesview art,
				syssubscriptions sub,
				syspublications pub
		WHERE	 ((sub.srvname is not null and len(sub.srvname) > 0 and sub.srvname = UPPER(@subscriber)  )
				or ((sub.srvname is null or len(sub.srvname) = 0 )and sub.srvid = @virtual_id))
				-- @destination_db will not be expanded before @article is expanded.
		  AND	(
					((@destination_db = N'%') OR (sub.dest_db = @destination_db))   OR
					@destination_db IS NULL         OR
					LOWER(@destination_db) = 'all'
				)
		  AND	sub.artid = art.artid
		  AND	art.pubid = pub.pubid
		  AND	pub.pubid = @pubid

		OPEN hCdrop_subscription3
		FETCH hCdrop_subscription3 INTO @article

		WHILE (@@fetch_status <> -1)
		BEGIN
			EXECUTE @retcode =	sys.sp_MSrepl_dropsubscription
								@publication			= @publication,
								@article			= @article,
								@subscriber			= @subscriber,
								@destination_db		= @destination_db,
								@ignore_distributor	= @ignore_distributor,
								@reserved			= @reserved,
								@publisher			= @publisher,
								@publisher_type			= @publisher_type

			IF @retcode != 0 or @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION dropallsubscriptions
				COMMIT TRANSACTION

				RETURN 1
			END
			
			FETCH hCdrop_subscription3 INTO @article
		END

		CLOSE hCdrop_subscription3
		DEALLOCATE hCdrop_subscription3

		COMMIT TRANSACTION dropallsubscriptions
		
		-- Call sp_MSrepl_dropsubscriber to drop the subscriber if there are now no subscriptions
		-- associated with the subscriber.  This call is intentionally made outside the
		-- transaction, since a failure here should not prevent dropping the subscription.
		-- We will ignore all errors and not just the error returned when there are existing
		-- subscriptions and only make the call if we are not in a transaction.
		IF NOT @@trancount > 0
		BEGIN
			EXECUTE sys.sp_MSrepl_dropsubscriber	@subscriber = @subscriber,
													@publisher = @publisher,
													@ignore_distributor = @ignore_distributor,
													@noraise = 1,
													@reserved = NULL
		END

		RETURN (0)
	END

    /*
    ** Parameter Check: @article
    ** Check if the article exists.
    */

	SELECT	@artid = artid
	FROM	sysextendedarticlesview art,
			syspublications pub
	WHERE	art.name  = @article
	  AND	art.pubid = pub.pubid
	  AND	pub.pubid = @pubid

    IF @artid IS NULL
	BEGIN
		RAISERROR (20027, 11, -1, @article)
		RETURN (1)
	END

    /*
    ** Parameter Check: @destination_db.
    ** Set @destination_db to current database if not specified.  Make
    ** sure that the @destination_db conforms to the rules for identifiers.
    */

    IF @destination_db IS NULL
    BEGIN
        /*
        ** Check if the subscription exists.
        */

        IF NOT EXISTS (SELECT *
                         FROM syssubscriptions
                        WHERE ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
                        		or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
                          AND artid = @artid)
        BEGIN
                RAISERROR (14055, 11, -1)
                RETURN (1)
        END
        ELSE

        SELECT @destination_db = 'all'
    END
    ELSE
    BEGIN
        EXECUTE @retcode = sys.sp_validname @destination_db
        IF @retcode <> 0
        RETURN (1)
    END

    IF LOWER(@destination_db) = 'all'
    BEGIN
    	BEGIN TRANSACTION
		SAVE TRANSACTION dropallsubscriptions

        DECLARE hCdropsub4  CURSOR LOCAL FAST_FORWARD FOR
            SELECT DISTINCT dest_db
                FROM syssubscriptions
                WHERE ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
                or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
                AND artid = @artid
        OPEN hCdropsub4
        FETCH hCdropsub4 INTO @destination_db
        WHILE (@@fetch_status <> -1)
        BEGIN
            EXECUTE @retcode = sys.sp_MSrepl_dropsubscription
						                @publication = @publication,
						                @article = @article,
						                @subscriber = @subscriber,
						                @destination_db = @destination_db,
						                @ignore_distributor = @ignore_distributor,
						                @reserved = @reserved,
						                @publisher = @publisher,
						                @publisher_type = @publisher_type

    		IF @retcode != 0 or @@ERROR != 0
			BEGIN
				ROLLBACK TRANSACTION dropallsubscriptions
				COMMIT TRANSACTION

				RETURN 1
			END

            FETCH hCdropsub4 INTO @destination_db
        END
        CLOSE hCdropsub4
        DEALLOCATE hCdropsub4

        COMMIT TRANSACTION dropallsubscriptions
        RETURN (0)
    END

    /*
    ** Dropping virtual subscriptions is not allowed
    ** in following case:
    ** 1. non sa or dbo user
    ** 2. the stored procedure is not in internal usage mode
    **        (called by system stored procedures)
    **
    ** Note: Only immediate_sync publications have virtual subscriptions
    **
    */

    IF  @srvid = @virtual_id  AND  (
        @reserved <> @internal)
        BEGIN
            RAISERROR (14056, 16, -1)
            RETURN (1)
        END

    /*
    ** Check if the subscription exists.
    */

    IF NOT EXISTS (SELECT *
                     FROM syssubscriptions
                    WHERE ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
	                    or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
                      AND artid = @artid
                      AND dest_db = @destination_db)
    BEGIN
            RAISERROR (14055, 11, -1)
            RETURN (1)
    END


    /* Check the current login id. It is valid only when
    ** 1. sa or dbo
    ** 2. same as the one who added the subscription.
    */
    SELECT @login_name = login_name
         FROM syssubscriptions
        WHERE ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
	                    or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
          AND artid = @artid
          AND dest_db = @destination_db

    IF  suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1
        AND is_member ('db_owner') <> 1
    BEGIN
            SELECT @qualified_subscription_name = @subscriber + N':' + @destination_db
            RAISERROR(21120, 11, -1, @qualified_subscription_name, @publication)
            RETURN (1)
    END

	begin tran
    save TRANSACTION dropsubscription
    		
	/* If dropping virtual subscriptions, reset immediate_sync_ready bit */
	IF @srvid = @virtual_id
	BEGIN
		UPDATE	syspublications
		SET		immediate_sync_ready = 0
		WHERE	immediate_sync = 1
		  AND	immediate_sync_ready = 1
		  AND	pubid = @pubid

	    IF @@ERROR <> 0
	        goto UNDO
	END

	/*
	** Change the status of the subscription to 'inactive'.
	*/

	EXECUTE @retcode =	sys.sp_MSrepl_changesubstatus
						@publication		= @publication,
						@article			= @article,
						@subscriber			= @subscriber,
						@status				= 'inactive',
						@destination_db		= @destination_db,
						@ignore_distributor	= @ignore_distributor,
						@publisher			= @publisher,
						@publisher_type			= @publisher_type

						
	IF @@ERROR <> 0 OR @retcode <> 0
	BEGIN
	    if @@trancount > 0
	    begin
	        ROLLBACK TRANSACTION dropsubscription
	        commit tran
	    end
	    RETURN (1)
	END

    /* Read the subscription_type befor removing the syssubscriptions row */
    select @subscription_type = subscription_type from syssubscriptions
        WHERE artid = @artid
        AND ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
	                    or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
        AND dest_db = @destination_db
    /*
    ** Remove subscription from syssubscriptions.
    */
    DELETE syssubscriptions
     WHERE artid = @artid
       AND ((@subscriber is not null) and (srvname is not null and len(srvname) > 0 and srvname = UPPER(@subscriber)  )
	                    or ((@subscriber is null) and (srvname is null or len(srvname) = 0) and srvid = @virtual_id and @srvid = @virtual_id ))
       AND dest_db = @destination_db

    IF @@ERROR <> 0
    BEGIN
        if @@trancount > 0
        begin
            ROLLBACK TRANSACTION dropsubscription
            commit tran
        end
        RETURN (1)
    END

    /* Call sp_MSunregistersubscription so that the reg entries get deleted (for push subscriptions) */
    if @subscription_type = @push
        begin
            declare @publisher_db sysname
            set @publisher_db = DB_NAME()
            exec @retcode = sys.sp_MSunregistersubscription @publisher = @publisher_local,
                                @publisher_db = @publisher_db,
                                @publication = @publication,
                                @subscriber = @subscriber,
                                @subscriber_db = @destination_db

            IF @retcode<>0 or @@ERROR<>0
                GOTO UNDO
        end

    COMMIT TRANSACTION

    -- Call sp_MSrepl_dropsubscriber to drop the subscriber if there are now no subscriptions
    -- associated with the subscriber.  This call is intentionally made outside the
    -- transaction, since a failure here should not prevent dropping the subscription.
    -- We will ignore all errors and not just the error returned when there are existing
    -- subscriptions and only make the call if we are not in a transaction.
    IF NOT @@trancount > 0
	BEGIN
		EXECUTE sys.sp_MSrepl_dropsubscriber	@subscriber = @subscriber,
												@publisher = @publisher,
												@ignore_distributor = @ignore_distributor,
												@noraise = 1,
												@reserved = NULL
	END
	RETURN (0)		

UNDO:
    ROLLBACK TRAN dropsubscription
    COMMIT TRAN
    RETURN(1)
END

 
Last revision 2008RTM
See also

  sp_dropsubscription (Procedure)
sp_MSdrop_repltran (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