Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_droparticle

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_droparticle

-- Description:
--          Internal proc for executing the logic of drop article.  Executed in
--			the context of the publisher even if it is the distributor for HREPL.

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

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

-- Owner:
--          

create procedure sys.sp_MSrepl_droparticle
(
    @publication				sysname,
    @article					sysname,
    @ignore_distributor			bit,
    @force_invalidate_snapshot	bit,
    @publisher					sysname,
    @from_drop_publication		bit,
    @publisher_type				sysname,
    @internal					bit = 0
)
AS
BEGIN
    /*
    ** Declarations.
    */

    DECLARE @cmd nvarchar(4000)
    DECLARE @objid int
    DECLARE @qualname nvarchar(517)
    DECLARE @pubid int
    DECLARE @publish_bit smallint
    DECLARE @retcode int
    DECLARE @filter_name nvarchar(517)
    DECLARE @view_name nvarchar(517)
    DECLARE @type tinyint
    DECLARE @procnum smallint
    DECLARE @virtual_id smallint
    DECLARE @push tinyint
    DECLARE @distributor sysname
    DECLARE @distribdb sysname
    DECLARE @distproc nvarchar (255)

    -- SyncTran
    DECLARE @allow_sync_tran_id bit
    DECLARE @allow_queued_tran_id bit
    declare @artid int, @insproc_id int, @updproc_id int, @delproc_id int, @updtrig_id int
    declare @filter_id int
    declare @view_id int
    declare @tran_conflict_tabid int
    declare @tran_conflict_procid int

    -- these are not use but required by sys.sp_IHgetPublisherInfo
    declare @autogen_sync_procs_id bit
    declare @sync_method tinyint
    declare @allow_sync_tran bit
    declare @allow_queued_tran bit
    declare @allow_dts bit
    declare @repl_freq tinyint
    DECLARE @publisher_db sysname
    DECLARE @tablename sysname
    DECLARE @owner sysname                                       -- for recursive call to sp_droparticle we need to original @publisher
    DECLARE @publisher_id int
    DECLARE @table_id int
    DECLARE @hrepl bit
    DECLARE @options int
    				,@publishingservername sysname

    DECLARE @distributortimestamp datetime

	DECLARE @OPT_ENABLED_FOR_P2P int	
			,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION int
	SELECT @OPT_ENABLED_FOR_P2P = 0x1
			,@OPT_ENABLED_FOR_P2P_CONFLICTDETECTION = 0x8
	
    SET NOCOUNT ON
    /*
    ** Initializations.
    */

    SELECT @virtual_id = -1     /* Const: virtual subscriber id */
    SELECT @publish_bit = 1
	SELECT @hrepl = 1

    /*
    ** Security Check.
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

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

    /*
    ** Parameter Check: @publication.
    ** The @publication name must conform to the rules for identifiers.
    */

    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_droparticle')
        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

    /*
    ** Parameter Check: @ignore_distributor
    ** HREPL: Not supported
    */

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

    /*
    ** Get the @pubid.
    */

    -- SyncTran
    --SELECT @pubid = pubid FROM syspublications WHERE name = @publication
    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        SELECT	@pubid					= pubid,
        		@allow_sync_tran_id		= allow_sync_tran,
				@allow_queued_tran_id	= allow_queued_tran,
				@options				= options
        FROM	syspublications
        WHERE	name = @publication
    END
    ELSE
    BEGIN
        EXEC @retcode =	sys.sp_IHgetPublicationInfo
						@publisher				= @publisher,
						@publication			= @publication,
						@publication_id			= @pubid                    OUTPUT,
						@autogen_sync_procs_id	= @autogen_sync_procs_id    OUTPUT,
						@sync_method			= @sync_method              OUTPUT,
						@allow_sync_tran		= @allow_sync_tran          OUTPUT,
						@allow_queued_tran		= @allow_queued_tran        OUTPUT,
						@allow_dts				= @allow_dts                OUTPUT,
						@repl_freq				= @repl_freq                OUTPUT

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

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

    /*
    ** Parameter Check:  @article.
    ** If the @article is 'all', drop all articles for the specified
    ** publication (@publication).
    */

    IF LOWER(@article) = 'all'
       BEGIN
            IF @publisher_type = N'MSSQLSERVER'
            BEGIN

				DECLARE hCart  CURSOR LOCAL FAST_FORWARD FOR
					SELECT DISTINCT  name, artid
					FROM sysextendedarticlesview
					WHERE pubid = @pubid
                    ORDER BY artid DESC
			END
			ELSE
			BEGIN

				DECLARE hCart  CURSOR LOCAL FAST_FORWARD FOR
					SELECT DISTINCT  article, 0
					FROM IHextendedArticleView
					WHERE publication_id = @pubid
			END	

            -- If drop all articles, set force flag to true
            select @force_invalidate_snapshot = 1

            OPEN hCart

            FETCH hCart INTO @article, @artid

            WHILE (@@fetch_status <> -1)
                BEGIN
                    EXEC @retcode = sys.sp_MSrepl_droparticle	@publication				= @publication,
																@article					= @article,
																@ignore_distributor			= @ignore_distributor,
																@force_invalidate_snapshot	= @force_invalidate_snapshot,
																@publisher					= @publisher,
																@from_drop_publication		= @from_drop_publication,
																@publisher_type				= @publisher_type,
																@internal					= 1

					IF (@retcode > 1)
					BEGIN
						-- HREPL failure - ignore until the end
						SET @hrepl = 0
					END
					ELSE IF @@ERROR <> 0 or @retcode <> 0
                    BEGIN
                   		RETURN (1)
                    END

                    FETCH hCart INTO @article, @artid
                END

            CLOSE hCart
            DEALLOCATE hCart

            GOTO PROCEXIT
        END

    /*
    ** Parameter Check: @article.
    ** The @article name must conform to the rules for identifiers.
    */

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

    /*
    EXECUTE @retcode = sys.sp_validname @article

    IF @retcode <> 0
    RETURN (1)
    */

    /*
    ** Ascertain the existence of the article.
    */

	IF NOT EXISTS (SELECT *
				 FROM sysextendedarticlesview
				WHERE name = @article
				  AND pubid = @pubid)
	BEGIN
		RAISERROR (20027, 11, -1, @article)
		RETURN (1)
    END

    /*
    ** Check to make sure that there are no 'real' subscriptions on the article.
    */

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        IF EXISTS (SELECT *
                 FROM syssubscriptions, sysextendedarticlesview
                WHERE sysextendedarticlesview.name = @article
                  AND sysextendedarticlesview.pubid = @pubid
                  AND sysextendedarticlesview.artid = syssubscriptions.artid
                  AND syssubscriptions.srvid <> @virtual_id)
        BEGIN
            RAISERROR (14046, 16, -1)
  RETURN (1)
        END
    END
    
    -- If SyncTran/QueuedTran enabled
    -- retrieve info from sysarticle updates
    
    if (@allow_sync_tran_id = 1 or @allow_queued_tran_id = 1)
    begin
        -- HetPubs do not support updatable subscribers, these should always be 0
        IF @publisher_type = N'MSSQLSERVER'
        BEGIN
            select @artid = artid from sysarticles where name = @article and pubid = @pubid
            select @insproc_id = sync_ins_proc, @updproc_id = sync_upd_proc, @delproc_id = sync_del_proc,
                @updtrig_id = sync_upd_trig,
                @tran_conflict_tabid = conflict_tableid,
                @tran_conflict_procid = ins_conflict_proc
            from sysarticleupdates
            where artid = @artid and pubid = @pubid
        END
        ELSE
        BEGIN
            raiserror(21609, 16, -1)
            return (1)
        END
    end
    -- end SyncTran

    /*
    ** Retrieve the object id of the underlying object,
    ** article id, and article type. Note that the
    ** subsequent code relies on the values of the variables in
    ** the select list. Please do not remove any variable from
    ** the seletc list unless you make sure that all the subsequent
    ** references to the variable are accounted for.
    */

    IF @publisher_type = N'MSSQLSERVER'
    BEGIN
        SELECT @artid = artid, @objid = objid, @type = type
          FROM sysextendedarticlesview
         WHERE name = @article
           AND pubid = @pubid
    END
    ELSE
    BEGIN
        SELECT @artid = a.article_id,
			   @table_id = a.table_id,
			   @type = a.type,
			   @objid = a.objid,
			   @publisher_id = a.publisher_id,
			   @tablename = m.source_object,
			   @owner = m.source_owner
		FROM   MSarticles m
        JOIN   IHarticles a
        ON     m.article_id = a.article_id
          AND  a.publication_id = m.publication_id
        WHERE  a.name = @article
        AND    a.publication_id = @pubid
    END

    begin tran
    save TRAN droparticle

        -- @ignore_distributor is set to 1 when removing replication forcefully. In that
        -- case, no need to check or reinit
        if @ignore_distributor = 0
        begin
            -- Have to call this stored procedure to invalidate existing snapshot
            -- if there are any. immediate_sync_ready bit would be changed or error will be railsed.
            -- for Sql publishers execute on publisher db, for het pubs go to distribution db
            EXECUTE @retcode  = sys.sp_MSreinit_article
								@publication				= @publication,
								@need_new_snapshot			= 1,
								@force_invalidate_snapshot	= @force_invalidate_snapshot,
								@from_drop_publication		= @from_drop_publication,
								@publisher					= @publisher,
								@publisher_type				= @publisher_type

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

        -- Drop virtual subscription first for @immediate_sync publications
		IF EXISTS
			(				
				SELECT *
					FROM syspublications sysp
						JOIN sysextendedarticlesview sysea
							on sysp.pubid = sysea.pubid
						JOIN syssubscriptions syss
							on sysea.artid = syss.artid
					WHERE sysp.name = @publication
						AND sysp.pubid = @pubid
						AND sysp.immediate_sync = 1
						AND syss.srvid = -1
			)
		BEGIN
			EXECUTE @retcode =	sys.sp_dropsubscription
								@publication		= @publication,
								@article			= @article,
								@subscriber			= NULL,
								@ignore_distributor	= @ignore_distributor,
								@reserved			= 'internal',
								@publisher			= @publisher

			IF @@ERROR <> 0 OR @retcode <> 0
			BEGIN
				IF @@trancount > 0
				BEGIN
					ROLLBACK TRAN droparticle
					COMMIT TRAN
				END
				RETURN (1)
			END
		END

        /* Drop article at the distributor side */
        IF NOT @publisher_type = N'MSSQLSERVER'
        BEGIN

          SELECT @publisher_db = publisher_db from dbo.MSpublications
				WHERE @publication = publication
				AND   @publisher_id = publisher_id

            EXEC @retcode = sys.sp_IHdroparticle
							@publisher     = @publisher,
							@publisher_db  = @publisher_db,
							@publication   = @publication,
							@article       = @article

			IF @@ERROR <> 0 OR @retcode <> 0
			BEGIN
				IF @@trancount > 0
				BEGIN
					ROLLBACK TRAN droparticle
					COMMIT TRAN
				END	
				RETURN (1)
			END

        END

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

		if @publisher_type = N'MSSQLSERVER'
		BEGIN
			EXECUTE @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
				@distribdb   = @distribdb OUTPUT
			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				if @@trancount > 0
					ROLLBACK TRAN
				RETURN (1)
			END

			SELECT @publisher_db =  DB_NAME()

			SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
				'.dbo.sp_MSdrop_article'
				,@publishingservername = publishingservername()
			
			EXECUTE @retcode = @distproc
				@publisher = @publishingservername,
				@publisher_db = @publisher_db,
				@publication = @publication,
				@article = @article

			IF @@ERROR <> 0 OR @retcode <> 0
			BEGIN
				if @@trancount > 0
					ROLLBACK TRAN
				RETURN (1)
			END
		END
		ELSE
		BEGIN
			EXECUTE @retcode = dbo.sp_MSdrop_article
				@publisher = @publisher,
				@publisher_db = @publisher_db,
				@publication = @publication,
				@article = @article

			IF @@ERROR <> 0 OR @retcode <> 0
			BEGIN
				if @@trancount > 0
					ROLLBACK TRAN
				RETURN (1)
			END
		END
				
        end

        IF @type IN (0x20, 0x40, 0x60, 0xA0, 0x80)
        BEGIN
            IF @publisher_type = N'MSSQLSERVER'
            BEGIN
                -- Handle the schema only articles a little bit differently from
                -- other articles as they are simpler objects.

                -- Note that we have already obtained the article id earlier
                -- so we can use that to delete the corresponding record
                -- in sysschemaarticles

                DELETE sysschemaarticles WHERE artid = @artid and pubid = @pubid

                -- If the object is no longer published as a schema only
                -- article, unmark its published for schema only bit (512) in
                -- sys.objects/replinfo so that it can be dropped by the user.
                -- Note that we need to check sysmergeschemaarticles too.


                -- Note that we have obtained the object id for the undelying
                -- object of this article already.

                IF NOT EXISTS (SELECT *
                                 FROM sysschemaarticles
                                WHERE objid = @objid)
                BEGIN

                    EXEC sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
                    IF NOT (@qualname IS NULL)
                    BEGIN
                        EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
                        IF @@ERROR = 0
                        BEGIN
                            IF NOT EXISTS (SELECT *
                                             FROM sys.objects
                                            WHERE name = 'sysmergeschemaarticles')
                            BEGIN
                                EXEC %%Object(ID = @objid).SetSchemaPublished(Value = 0)
                            END
                            ELSE IF NOT EXISTS (SELECT *
                                                  FROM sysmergeschemaarticles
                                                 WHERE objid = @objid)
   BEGIN
                                EXEC %%Object(ID = @objid).SetSchemaPublished(Value = 0)
                            END
                        END
                    END

                END
            END
        END
        ELSE
        BEGIN

            /*
            **  Delete article from sysarticles and clear publish bit in
            **  sys.objects.
            */


            /*
            ** If this article is the only one that references this object,
            ** then we can safely turn off the publish bit in sys.objects.
            */

            IF @publisher_type = N'MSSQLSERVER'
            BEGIN
                IF NOT EXISTS (SELECT *
                             FROM sysarticles
                            WHERE objid = @objid
                              AND NOT (name = @article AND pubid = @pubid))
                BEGIN

				declare @cdc_tracked_tables table (object_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
	
                    DECLARE @obj_tmp int
                    SELECT @obj_tmp = objid FROM sysarticles
                        WHERE name = @article AND pubid =  @pubid
                    IF NOT (@obj_tmp IS NULL)
                    BEGIN
                        EXEC sys.sp_MSget_qualified_name @obj_tmp, @qualname OUTPUT
                        IF NOT (@qualname IS NULL)
                        BEGIN
                            EXEC %%Object(MultiName = @qualname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
                            IF @@ERROR = 0
                                EXEC %%Object(ID = @obj_tmp).SetPublished(Value = 0)

                            -- Clear the "Replicated" bits if the published
                            -- object that does not have any active/initiated
                            -- subscriptions and is not published in a
                            -- publication that allows is enabled for autonosyncs
                            IF  -- No active subscriptions
                                NOT EXISTS (SELECT * FROM dbo.syssubscriptions WHERE
                                           artid in (SELECT sa.artid
                                                       FROM dbo.sysextendedarticlesview sa
                                                      INNER JOIN dbo.syspublications sp
                                                         ON sa.pubid = sp.pubid
                                                      WHERE NOT (sa.name = @article AND sa.pubid = @pubid) -- Don't consider the current article
                                                        AND sa.objid = @obj_tmp
                                                        AND sp.repl_freq = 0)
                                               AND status in (2,3)) AND
                                -- Not published in a allow is enabled for autonosyncs publication
                                NOT EXISTS (SELECT *
                                              FROM dbo.sysextendedarticlesview sa
                                            INNER JOIN dbo.syspublications sp
                                                ON sa.pubid = sp.pubid
                                             WHERE NOT (sa.name = @article AND sa.pubid = @pubid) -- Don't consdier the current article
                                               AND sa.objid = @obj_tmp
                                               AND sp.allow_initialize_from_backup = 1)
                                AND NOT EXISTS (SELECT *
                                              FROM @cdc_tracked_tables where object_id = @obj_tmp)
                            BEGIN
                                -- Call different system object methods depending object type
                 IF OBJECTPROPERTY(@obj_tmp, 'IsProcedure') = 1
                                BEGIN
                                    EXEC %%Module(ID = @obj_tmp).SetProcReplicated(Value = 0)
                                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                                    EXEC %%Module(ID = @obj_tmp).SetProcReplSerialOnly(Value = 0)
                                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                                END
                                ELSE
                                BEGIN
                                    EXEC %%Relation(ID = @obj_tmp).SetReplicated(Value = 0)
                                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END

					-- in case the p2p conflict detection is enabled
					if exists(select * from syspublications
							where name = @publication and (options & @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION) = @OPT_ENABLED_FOR_P2P_CONFLICTDETECTION)
					begin				
						--set the table persistent property replpeerid to 0, the table was already x-locked
						--hidden column $p2pversion is removed internally
						EXEC %%Relation(ID = @obj_tmp).SetReplPeerId(Value = 0)
						if @@ERROR <> 0
						begin
							select @retcode = 1
							goto UNDO
						end

						--drop the conflict table
						declare @conflicttablename nvarchar(1000)
              	                  	exec sp_MSgetpeerconflictname @prefix = N'conflict', @tabid = @obj_tmp, @peerconflictname = @conflicttablename output
                     	           	select @conflicttablename = N'[dbo].' + QUOTENAME(@conflicttablename)
						if(object_id(@conflicttablename, N'U') is not null)
						begin
							select @cmd = N'drop table ' + @conflicttablename
							exec (@cmd)
							if @@ERROR <> 0
							begin
								select @retcode = 1
								goto UNDO
							end
						end
					end --in case the p2p conflict detection is enabled

                                END
                            END

                        END
                    END

                /*
                EXEC (@cmd)

                IF @@ERROR <> 0
                    BEGIN
                        if @@trancount > 0
                            ROLLBACK TRAN
                        RAISERROR (14047, 16, -1, @article)
                        RETURN (1)
                    END
                */

                END

                /*
                ** Drop article view if not logbased manualview (type = 5)
                */
                IF (@type & 5) = 1
                BEGIN
                    SELECT @view_id = so.object_id
                      FROM sysarticles as sa join sys.objects as so
                      ON sa.sync_objid = so.object_id
                     WHERE sa.name = @article
                       AND sa.pubid = @pubid
                       AND so.type = 'V'
                    exec sys.sp_MSget_qualified_name @view_id, @view_name OUTPUT

                END

                /*
                ** Drop article filter if not logbased manualfilter (type = 3)
                */
                IF (@type & 3) = 1
                BEGIN
                    SELECT @filter_id = so.object_id
                      FROM sysarticles as sa join sys.objects as so
                      ON sa.filter = so.object_id
                     WHERE sa.name = @article
                       AND pubid = @pubid
                       AND so.type = 'RF'
                    exec sys.sp_MSget_qualified_name @filter_id, @filter_name OUTPUT

                END


                IF( @type & 3 ) = 3
                BEGIN
                    select @filter_id =  filter from sysarticles
                    where name = @article and pubid = @pubid

          exec sys.sp_MSget_qualified_name @filter_id, @filter_name OUTPUT

                    if @filter_name is not null
                        EXEC sys.sp_MSsetfilterparent @filter_name, 0

                    -- Clear base table dependency on the filter
                    EXEC sys.sp_MSsetfilteredstatus @objid

                    -- This is a manual filter, we should not drop it automatically
                    -- since it is not created by us.
                    -- Set @filter_id to null so the object will not be dropped later.
                    select @filter_name = null

                END


                /*
                ** If this is a table based article, Drop all article columns.
                ** This is done to force all Text\Image column status to be updated.
                */

                IF (@type & 8) != 8
                BEGIN

                    -- propagate @ignore_distributor to sp_articlecolumn to allow forced cleanup
                    EXECUTE @retcode = sys.sp_articlecolumn
										@publication,
										@article,
										@operation = 'drop',
										@ignore_distributor = @ignore_distributor,
										-- synctran
										@refresh_synctran_procs = 0,
										@force_invalidate_snapshot = @force_invalidate_snapshot
										, @internal = 1
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRAN droparticle
                            commit tran
                        end
                        RETURN (1)
                    END
                END
            END

            /*
            ** Remove the row from sysarticles.
            */
            IF @publisher_type = N'MSSQLSERVER'
            BEGIN
                DELETE
                  FROM sysarticles
                 WHERE name = @article
                   AND pubid = @pubid

                IF @@ERROR <> 0
                BEGIN
                    if @@trancount > 0
                        ROLLBACK TRAN
                    RAISERROR (14047, 16, -1, @article)
                    RETURN (1)
                END
            END
            ELSE
            BEGIN
				DELETE FROM dbo.IHarticles
				WHERE name = @article
				AND publication_id = @pubid
				
				IF @@ERROR <> 0
				BEGIN
					RAISERROR (14047, 16, -1, @article)
                    RETURN (1)
                END
            END

            -- SyncTran
            /*
            ** Drop associated sync tran procs and entries in sysarticle updates
            ** HetPubs do not support updatable subscribers so these willl alwasy be 0
            */
            if (@allow_sync_tran_id = 1 or @allow_queued_tran_id = 1)
            begin
                exec @retcode = sys.sp_MSdrop_object
                    @object_id = @insproc_id
                if @retcode <> 0 or @@error <> 0
                    goto  UNDO

                exec @retcode = sys.sp_MSdrop_object
                    @object_id = @updproc_id
                if @retcode <> 0 or @@error <> 0
                    goto  UNDO

                exec @retcode = sys.sp_MSdrop_object
                    @object_id = @delproc_id
                if @retcode <> 0 or @@error <> 0
                    goto  UNDO

                if @updtrig_id is not null
                begin
                    exec @retcode = sys.sp_MSdrop_object
                        @object_id = @updtrig_id
                    if @retcode <> 0 or @@error <> 0
                        goto  UNDO
                end

                -- drop conflict tables as necessary
                if @tran_conflict_tabid is not null
                begin
                    exec @retcode = sys.sp_MSdrop_object
                        @object_id = @tran_conflict_tabid
                   if @retcode <> 0 or @@error <> 0
                        goto  UNDO
                end

                if @tran_conflict_procid is not null
                begin
                    exec @retcode = sys.sp_MSdrop_object
                        @object_id = @tran_conflict_procid
                    if @retcode <> 0 or @@error <> 0
                        goto  UNDO
                end

                delete from sysarticleupdates where artid = @artid and pubid = @pubid
                if @@ERROR <> 0
                begin
                    if @@trancount > 0
                        ROLLBACK TRAN
                    RETURN (1)

                end

                -- Cleanup MSpub_identity_range if needed.
                if not exists (select * from sysarticles where objid = @objid)
                begin
                    if exists (select * from MSpub_identity_range where objid = @objid)
                    begin
                        -- Drop the identity range constraits.
                        -- RESEED and change constraint
                        exec @retcode = sys.sp_MSreseed
                            @objid =  @objid,
                            -- next_seed and range can be anything.
                            @next_seed = 10,
                            @range = 10,
                            @is_publisher = -1,
                            @check_only = 1,
                            @drop_only = 1
                        IF @retcode <> 0 or @@ERROR <> 0
                            GOTO UNDO

                        delete MSpub_identity_range where objid = @objid
                        if @@ERROR <> 0
                            GOTO UNDO
                    end
                end
            -- end SyncTran
            end
    end

	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
		-- if this publication is p2p then we will need to attempt to
		-- detect invalid configurations at the peer node to avoid
		-- disjoint article sets. NOTE that the proc will also help
		-- delete MSsubscription_articles meta-data at the subscriber node
		IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
		BEGIN
			DECLARE @min_artid int

			-- since the article is dropped we need the min
			-- article id to post the command... if none left
			-- then we will skip over the post completely...
			SELECT @min_artid = MIN(artid)
				FROM sysextendedarticlesview
				WHERE pubid = @pubid

			IF @min_artid IS NOT NULL
			BEGIN
				SELECT @cmd = N'if (@@microsoftversion >= 0x09000000)' +
						  		N' begin' +
									N' exec sys.sp_MSdetectinvalidpeersubscription @publisher=N' + QUOTENAME(publishingservername(), N'''')  +
																				N',@publisher_db=N' + QUOTENAME(@publisher_db, N'''')  +
																				N',@publication=N' + QUOTENAME(@publication, N'''')  +
																				N',@article=N' + QUOTENAME(@article, N'''')  +
																				N',@dest_table=N' + QUOTENAME(OBJECT_NAME(@objid), N'''')  +
																				N',@dest_owner=N' + QUOTENAME(SCHEMA_NAME(OBJECTPROPERTY(@objid,'SchemaId')), N'''')  +
																				N',@type=''DEL''' +
						  		N' end'

				EXEC @retcode = sys.sp_replpostcmd 0, @pubid, @min_artid, 1, @cmd
				IF @@ERROR <> 0 OR @retcode <> 0
					GOTO UNDO
			END
		END
	END
	
    IF @publisher_type LIKE N'ORACLE%'
    BEGIN
        -- Update distributor timestamp to reflect change in meta data	
        SET @distributortimestamp = GETDATE()
        UPDATE	dbo.IHpublishers
        SET	flush_request_time = @distributortimestamp
        WHERE	publisher_id = @publisher_id

        IF @@error <> 0
        BEGIN
    		GOTO UNDO
        END	
    END

    COMMIT TRAN

    -- Remove the trigger and delete the tracking table at the publisher if the table is not
    -- associated with any published articles.
    -- The SP will check for other publications using the article before deleting it
    IF @publisher_type LIKE N'ORACLE%'
    BEGIN
        DECLARE @article_view nvarchar(256)

        EXEC @article_view = sys.fn_IHview_name @artid,	@table_id

        IF @@error <> 0
        BEGIN
			RAISERROR (21777, 11, -1, @article)
			GOTO UNDO
        END

        EXEC @retcode = sys.sp_ORAdroparticle	@publisher,
												@artid,
												@publisher_id,
												@table_id,
												@owner,
												@tablename,
												@article_view,
												@distributortimestamp

		IF @@ERROR <> 0 OR @retcode <> 0
		BEGIN
			-- Report that the HREPL info at the publisher was not dropped
			-- but do not prevent article drop from finishing.
			SET @hrepl = 0
			SET @retcode = 0
		END
    END

    IF @view_name IS NOT NULL
    BEGIN
        -- @view_name is already quoted.
        SELECT @cmd = 'drop view ' + @view_name
        exec (@cmd)
    END

    IF @filter_name IS NOT NULL
    BEGIN
        -- @filter_name is already quoted.
        SELECT @cmd = 'drop proc ' + @filter_name
        exec (@cmd)
    END
    /*
    ** Force the article cache to be refreshed; only if needed
    */
    if ( @ignore_distributor = 0 )
        EXECUTE sys.sp_replflush

PROCEXIT:
	-- Override status and return an error if HREPL failed.
	-- Post-hrepl steps will have completed and left the distributor
	-- in a consistent state.
	-- SPECIAL CASE: If the internal flag is set, this is part of a drop all
	-- articles.  Hold until the end by passing back @retcode > 1
	IF @hrepl = 0 AND @internal = 0
	BEGIN
		RAISERROR(21748, 16, -1, @publisher)
		SET @retcode = 1
	END
	ELSE IF @hrepl = 0 AND @internal = 1
	BEGIN
		SET @retcode = 2
	END
    RETURN (@retcode)

UNDO:
    if @@trancount > 0
    begin
        ROLLBACK TRANSACTION droparticle
        commit tran
    end
    RETURN (1)
END

 
Last revision SQL2008SP2
See also

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