Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_droppublication

  No additional text.


Syntax

create procedure sys.sp_MSrepl_droppublication
(
	@publication		sysname,
	@ignore_distributor	bit,
	@publisher			sysname,
	@publisher_type		sysname,
    @from_backup        bit = 0
)
AS
BEGIN
    /*
    ** Declarations.
    */

    DECLARE @article sysname
                ,@cmd nvarchar(255)
                ,@retcode int
                ,@obj_tmp int
                ,@distributor sysname
                ,@distribdb sysname
                ,@distproc nvarchar (255)
                ,@agentname nvarchar (40)
                ,@dbname sysname
                ,@virtual_id smallint
                ,@ad_guidname sysname
                ,@alt_snapshot_folder nvarchar(255)
                ,@pub_alt_snapshot_folder nvarchar(255)
                ,@qualname nvarchar(517)
                ,@logreaderAgent bit
                ,@publisher_db sysname
                ,@publisher_param sysname -- publisher given via the @publisher parameter
                -- @publisher is null for Sql publications and is replace with publishingservername()
                ,@srvname sysname
                ,@pubid int
	
	set nocount on
    SELECT @virtual_id = -1
                ,@dbname = db_name()
                ,@ad_guidname = NULL
                ,@srvname = publishingservername()
                -- Set publisher param to be case insensitive by putting it to UPPERCASE
                ,@publisher = UPPER(@publisher)

    /*
    ** Security check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)
    /*
    ** Parameter Check: @ignore_distributor
    ** The @publication name must conform to the rules for identifiers.
    */

    IF NOT @publisher_type = N'MSSQLSERVER' and @ignore_distributor <> 0
    BEGIN
        RAISERROR (21608, 16, -1)
        RETURN (1)
    END

	IF (@ignore_distributor = 0) or ([sys].[fn_cdc_is_db_enabled]() = 1)
	BEGIN
		/*
		** Get distribution server information for remote RPC call.
		*/
		EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher,
												@rpcsrvname		= @distributor OUTPUT,
												@distribdb		= @distribdb OUTPUT

		IF (@@error <> 0 OR @retcode <> 0) and (@ignore_distributor = 0)
		BEGIN
			RAISERROR (14071, 16, -1)
			RETURN (1)
		END
	END

    IF (@publisher IS NOT NULL) AND (@publisher_type IS NULL OR @distribdb IS NULL OR @distributor IS NULL)
    BEGIN
        RAISERROR(21600, 16, -1, @publisher)
        RETURN (1)
    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

    -- Set distributor and db to current to check if we are dealing with het. publications
    -- We use the default distributor/distribution database to discover the heterogenous
    --  publisher's distributor/distribution database
    SELECT @publisher_param = @publisher
    if @publisher is NULL
    BEGIN
        SELECT @publisher = publishingservername()
        SELECT @publisher_db = DB_NAME()
		SELECT @publisher_type = N'MSSQLSERVER'
    END
    ELSE
    BEGIN
		SELECT @publisher_db = @distribdb
    END

   /*
    ** Parameter Check:  @publication.
    ** If the @publication is 'all', drop all publications.  Otherwise,
    ** make sure the @publication is a valid non-null identifier.
    ** Delete the logreader agent after all the publications have been
    ** removed.
     */
    IF LOWER(@publication) = 'all'
        BEGIN
            DECLARE hC1  CURSOR LOCAL FAST_FORWARD FOR
                SELECT DISTINCT name FROM syspublications
                    WHERE pubid NOT IN
                        (SELECT pubid FROM sysextendedarticlesview WHERE artid IN
                            (SELECT artid FROM syssubscriptions WHERE srvid <> @virtual_id))
            OPEN hC1
            FETCH hC1 INTO @publication
            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXECUTE sys.sp_droppublication	@publication,
													@ignore_distributor = @ignore_distributor,
													@publisher = @publisher_param,
                                                    @from_backup = @from_backup
                    FETCH hC1 INTO @publication
                END
            CLOSE hC1
            DEALLOCATE hC1
            RETURN (0)
        END

    IF @publication IS NULL
    BEGIN
        RAISERROR (14003, 16, -1)
        RETURN (1)
    END

	-- Get publication id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

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

    /*
    ** Check to make sure that there are no subscriptions on the publication.
    */

	IF EXISTS
	(
		SELECT	*
		FROM	syssubscriptions a,
				sysextendedarticlesview b,
				syspublications c
		WHERE	c.pubid = @pubid
		  AND	c.pubid = b.pubid
		  AND	b.artid = a.artid
		  AND	a.srvid <>@virtual_id
	)
	BEGIN
		RAISERROR (14005, 16, -1)
		RETURN (1)
	END

    /*
    ** Delete all articles from the publication.
    */

    EXEC @retcode = sys.sp_droparticle	@publication				= @publication,
										@publisher					= @publisher_param,
										@article					= N'all',
										@ignore_distributor			= @ignore_distributor,
										@force_invalidate_snapshot	= 1,
										@from_drop_publication		= 1

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

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
		select	@ad_guidname			= ad_guidname,
				@alt_snapshot_folder	= alt_snapshot_folder
		from	 syspublications
		where	name=@publication
    END
    
    -- drop PAL role for the publication
    
    exec @retcode = sys.sp_MSdroptranpalrole @pubid

    if @retcode<>0 or @@error<>0
    begin
        return 1
    end

    BEGIN TRAN tr_drop_publication
    SAVE TRAN tr_drop_publication

    /*
    ** Delete publication from syspublications.
    */

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        DELETE FROM syspublications WHERE name = @publication

        IF @@ERROR <> 0
            GOTO UNDO
    END
    ELSE
    BEGIN
        EXEC @retcode = sys.sp_IHdroppublication
						@publisher		= @publisher,
						@publication	= @publication,
						@publisher_type	= @publisher_type,
						@ad_guidname	= @ad_guidname OUTPUT,
						@logreaderAgent	= @logreaderAgent OUTPUT

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

    /*
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 0
    begin
        /*
        ** Delete sync agent of Publication if it exists.
        */
        SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSdrop_snapshot_agent'
        EXECUTE @retcode = @distproc
            @publisher = @publisher,
            @publisher_db = @dbname,
            @publication = @publication

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

	end

	if ((@ignore_distributor = 0) or ([sys].[fn_cdc_is_db_enabled]() = 1)) and (@distribdb IS not NULL and  @distributor IS not NULL)
	begin
	    IF NOT EXISTS (SELECT * FROM syspublications  where repl_freq = 0)
        BEGIN
            /*
            ** Delete logreader agent, continue if drop is not successful
            */
            SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSdrop_logreader_agent'
            EXECUTE @retcode = @distproc @publisher = @publisher,
							                @publisher_db = @dbname,
							                -- 'ALL' is used in sp_addpublication.
							                @publication = 'ALL'
			--if we can not drop logreader agent when brute-force cleaning up and CDC is enabled, don't error out, assuming CDC will be able to add a job
            IF (@@ERROR <> 0 or @retcode <> 0) and (@ignore_distributor = 0)
                GOTO UNDO

			IF @publisher_type = N'MSSQLSERVER'
			BEGIN
				DECLARE @error_num	int,
						@attempts 	int
						

				SELECT @error_num = 1,
						@attempts = 0
				
				WHILE (@attempts < 5)
				BEGIN
					-- we wait a bit to give the server time to
					-- release the logreader sessions being held
					WAITFOR DELAY '00:00:02'

					--cannot use TSQL try/catch since sp_replcmds uses ex_callprint instead of ex_raise internally
					--TSQL try/catch will just absorb the error msg without going to catch
					EXEC @retcode = sys.sp_replcmds 0

					SELECT @error_num = @@ERROR
					
					IF @error_num = 0 AND @retcode = 0
					BEGIN
						BREAK
					END
					
					SELECT @attempts = @attempts + 1
				END -- end of while

				IF @error_num != 0 OR @retcode != 0
				BEGIN
					-- An error occurred during the execution of 'sp_droppublication'. A call to 'sp_replcmds' failed with error code: '%d', return code: '%d'.
					RAISERROR (20817, 16, 1, N'sp_droppublication', N'sp_replcmds', @error_num, @retcode)
					GOTO UNDO
				END

				set @error_num	= 1
				EXEC @retcode = sys.sp_replflush
				SELECT @error_num = @@ERROR
				IF @error_num != 0 OR @retcode != 0
				BEGIN
					-- An error occurred during the execution of 'sp_droppublication'. A call to 'sp_replflush' failed with error code: '%d', return code: '%d'.
					RAISERROR (20817, 16, 2, N'sp_droppublication', N'sp_replflush', @error_num, @retcode)
					GOTO UNDO
				END

			END	-- end of IF @publisher_type = N'MSSQLSERVER'
        END -- end of IF NOT EXISTS (SELECT * FROM syspublications  where repl_freq = 0)
	end  --end of if (@ignore_distributor = 0) or ([sys].[fn_cdc_is_db_enabled]() = 1)
	if @ignore_distributor = 0
 	begin
	    /*
        ** Delete the publication at the distribution server
        */
        SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
            '.dbo.sp_MSdrop_publication'
        EXECUTE @retcode = @distproc
            @publisher = @publisher,
            @publisher_db = @dbname,
            @publication = @publication,
            @alt_snapshot_folder = @alt_snapshot_folder
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

    end

    -- Since we drop publisher_database_id in sp_MSdrop_publication at the distribution db when
    -- dropping the last tran (snapshot) publication, we should call repldone here to clear
    -- repl counters and lsns. This will ensure the correctness of repl counters and avoid
    -- unnecessary log scan in the logreader if it is created again after this.
    -- Ignore all errors.
    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
		declare @cdc_tracked_tables table (object_id int)
		declare @cdc_table_id int

		if object_id('cdc.change_tables') is not null
		begin
			insert @cdc_tracked_tables select distinct source_object_id from cdc.change_tables
		end

		if not exists (select * from syspublications)
		begin
		    -- Used for attach and restored db.
		    -- sysservers table in master db might be changed so that
		    -- sp_dropsubscription won't work, which left repl bits marked in
		    -- sys.objects.
		    -- We have to unmark them before calling sp_repldone, otherwise
		    -- A new transaction updating those objects will be considered
		    -- as repl tran. It will set the truncation point to not null, which will
		    -- prevent log truncation.
		    declare @repl_bit smallint
		    select @repl_bit = 2
		    DECLARE #cur_unrepl_obj CURSOR LOCAL FAST_FORWARD FOR
		        SELECT t.object_id, quotename(schema_name(t.schema_id))+N'.'+ quotename(t.name), c.object_id FROM sys.tables t
						left outer join @cdc_tracked_tables c on t.object_id = c.object_id
		            WHERE is_replicated = 1
		
		    OPEN #cur_unrepl_obj
		    FETCH #cur_unrepl_obj INTO @obj_tmp, @qualname, @cdc_table_id
		    WHILE (@@fetch_status <> -1)
		    BEGIN
				if @cdc_table_id is null
				begin
					EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @obj_tmp, Exclusive = 1, BindInternal = 0)
					IF @@ERROR <> 0 GOTO BULK_UNMARK_UNDO

					EXEC %%Relation(ID = @obj_tmp).SetReplicated(Value = 0)
					IF @@ERROR <> 0 GOTO BULK_UNMARK_UNDO

				end
				FETCH #cur_unrepl_obj INTO @obj_tmp, @qualname, @cdc_table_id
				CONTINUE

	BULK_UNMARK_UNDO:
				CLOSE #cur_unrepl_obj
				DEALLOCATE #cur_unrepl_obj
				GOTO UNDO
		    END



            CLOSE #cur_unrepl_obj
            DEALLOCATE #cur_unrepl_obj

			if not exists (select * from @cdc_tracked_tables)
			begin
				/* ensure we can get in as logreader */
				exec sys.sp_replflush

				/* clear repl dbtable fields unmark all xacts marked for replication */
				exec sys.sp_repldone NULL, NULL, 0, 0, 1

				/* release our hold on the db as logreader */
				EXEC sys.sp_replflush

				-- Run checkpoint to make sp_repldone result durable (write repl dbtable fields
				-- into the checkpoint record).
				checkpoint
			end
        end

        -- At this point we should have dropped the logreader agent so we check to see
        --  if CDC is enabled, if so we add the CDC job
        IF [sys].[fn_cdc_is_db_enabled]() = 1
        	and not exists (select * from syspublications where status = 1 and repl_freq = 0) -- only add this if there is no active tran pub
		BEGIN
            exec [sys].[sp_cdc_add_job] @job_type = N'capture'
        END
    END

    -- If this is a heterogeneous publisher, signal log reader to update article cache
    IF NOT @publisher_type = N'MSSQLSERVER'
    BEGIN
        SELECT @distproc = QUOTENAME(@distribdb) + '.sys.sp_IHreplflush '
        EXEC @retcode = @distproc
		@publisher
        IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
    END	

	-- Cleanup PeerToPeer Metadata - do not drop table here...
	-- drop table is performed by sp_replicationdboption false.
	EXEC @retcode = sys.sp_MScleanup_peer_metadata @type = 0,
														@publication = @publication,
                                                        @from_backup = @from_backup
	IF @@ERROR <> 0 OR @retcode <> 0
		GOTO UNDO
	
    COMMIT TRAN tr_drop_publication

    return (0)

UNDO:
    ROLLBACK TRAN tr_drop_publication
	COMMIT TRAN
	
    return(1)
END

 
Last revision 2008RTM
See also

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