Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_changesubstatus

  No additional text.


Syntax
create procedure sys.sp_MSrepl_changesubstatus
(
    @publication			sysname = '%',
    @article				sysname = '%',
    @subscriber				sysname = '%',	
    @status				sysname,
    @previous_status			sysname = NULL,
    @destination_db			sysname = '%',
    @frequency_type			int = NULL,
    @frequency_interval			int = NULL,
    @frequency_relative_interval	int = NULL,
    @frequency_recurrence_factor	int = NULL,
    @frequency_subday			int = NULL,
    @frequency_subday_interval		int = NULL,
    @active_start_time_of_day		int = NULL,
    @active_end_time_of_day		int = NULL,
    @active_start_date			int = NULL,
    @active_end_date			int = NULL,
    @optional_command_line		nvarchar(4000) = NULL,
    @distribution_jobid			binary(16) = NULL OUTPUT,
    @from_auto_sync			bit = 0,
    @ignore_distributor			bit = 0,
    -- Agent offload
    @offloadagent			bit = 0,
    @offloadserver			sysname = NULL,
    @dts_package_name			sysname = NULL,
    @dts_package_password		nvarchar(524) = NULL,
    @dts_package_location		int = 0,
    @skipobjectactivation		int = 0,
    @distribution_job_name		sysname = NULL,
    @publisher				sysname = NULL,
    @publisher_type			sysname
    ,@ignore_distributor_failure 	bit = 0
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @inactive tinyint
                ,@subscribed tinyint
                ,@active tinyint
                ,@initiated tinyint
                ,@public tinyint
                ,@replicate_bit smallint
                ,@msg nvarchar(255)
                ,@prevstatid tinyint
                ,@artid int
                ,@tabid int
                ,@objid int
                ,@qualified_name nvarchar(517)
                ,@srvid smallint
                ,@statusid tinyint
                ,@distributor sysname
                ,@distribdb sysname
                ,@distproc nvarchar (1000)
                ,@pub_db sysname
                ,@dest_db sysname
                ,@sub_name sysname
                ,@sub_status tinyint
                ,@sub_ts varbinary (16)
                ,@non_sql_flag bit
                ,@qcmd nvarchar (4000)
                ,@cmd1 nvarchar (255)
                ,@cmd2 nvarchar (255)
                ,@cmd3 nvarchar (255)
                ,@retcode int
                ,@repl_freq tinyint
                ,@art_type tinyint
                ,@proccmd  nvarchar(255)
                ,@procnum  smallint
                ,@finished_real bit
                ,@finished_virtual bit
                ,@virtual_id smallint
                ,@immediate_sync bit
                ,@enabled_for_internet bit
                ,@allow_anonymous bit
                ,@subscription_type int
                ,@xact_seqno binary(10)
                ,@sync_type tinyint
                ,@nosync_type tinyint
                ,@automatic tinyint
                ,@allow_initialize_from_backup bit
                ,@sync_method int
                ,@art_change bit
                ,@login_name sysname
                ,@pubid int
                ,@syncinit_lsn binary(10)
                ,@f_syncstat_posted bit
                -- synctran
                ,@update_mode tinyint
                ,@art_name sysname
                ,@synctran tinyint
                ,@no_distproc bit
                ,@loopback_detection bit
                ,@bcp_char tinyint,  @concurrent_char tinyint
                -- heterogeneous publishers
                ,@publisher_local sysname
                ,@publisher_id int
                ,@publisher_db sysname
                ,@internal sysname
                ,@publisher_engine_edition int
                ,@current_publication sysname
                ,@OPT_ENABLED_FOR_HET_SUB tinyint

    DECLARE @publication_ids TABLE
        (
            pubid int
        )

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

		SELECT	@publisher_id = s.srvid,
				@publisher_db = m.distribution_db
		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	m.distribution_db = DB_NAME() collate database_default
	
		IF @@ERROR <> 0
			RETURN (1)

		IF @publisher_id is NULL or @publisher_db IS NULL
		BEGIN
			RETURN (1)
		END

		-- Security Check.
		exec @retcode = sys.sp_MSreplcheck_publish
		if @@ERROR <> 0 or @retcode <> 0
		BEGIN
			RETURN (1)
		END
    END


    
    -- Initializations.
    
    select @bcp_char          = 1,
            @concurrent_char   = 4,
            @synctran          = 1,
            @automatic         = 1,
            @inactive          = 0,			-- Const: subscription status 'inactive'
            @subscribed        = 1,			-- Const: subscription status 'subscribed'
            @active            = 2,			-- Const: subscription status 'active'
            @initiated         = 3,			-- Const: subscription status 'initiated'
            @public            = 0,			-- Const: publication status 'public'
            @pub_db            = DB_NAME(),
            @virtual_id        = -1,
            @art_change        = 0,
            @f_syncstat_posted = 0,
            @replicate_bit     = 2,
            @publisher_engine_edition = sys.fn_MSrepl_editionid()
            ,@OPT_ENABLED_FOR_HET_SUB = 0x4

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

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

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

    IF @publication <> '%'
	BEGIN
		EXECUTE @retcode = sys.sp_validname @publication
		IF @@ERROR <> 0 OR @retcode <> 0
		RETURN (1)
	END

	-- Get list of matching publication id's for
	-- this publisher/type combo
	INSERT INTO @publication_ids
	SELECT pubid
	FROM   sys.fn_IHgetpubid(@publication, @publisher, @publisher_type)

	IF NOT EXISTS
	(
		SELECT	*
		FROM	@publication_ids
	)
	BEGIN
		IF @publication = '%'
		BEGIN
			RAISERROR (14008, 11, -1)
		END
		ELSE
		BEGIN
			RAISERROR (20026, 11, -1, @publication)
		END
		
		RETURN (1)
	END

    /*
    ** Parameter Check:  @article
    ** Check to make sure that the article exists, that it's not null,
    ** and that it conforms to the rules for identifiers.
    */

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

    /*
    IF @article <> '%'
        BEGIN
            EXECUTE @retcode = sys.sp_validname @article
            IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
        END
    */

    IF NOT EXISTS
	(
		SELECT	*
		FROM	sysextendedarticlesview a,
				syspublications b,
				@publication_ids pi
		WHERE	a.pubid = b.pubid
		  AND	 ((@article = N'%') or (a.name = @article))
		  AND	((@publication = N'%') or (b.name = @publication))
		  AND	b.pubid = pi.pubid
	)
	BEGIN
		IF @article = '%'
		BEGIN
			RAISERROR (14009, 11, -1, @publication)
		END
		ELSE
		BEGIN
			RAISERROR (20027, 11, -1, @article)
		END

		RETURN (1)
	END

    /*
    ** Parameter Check:  @subscriber
    ** Check to make sure that the subscriber exists, that it is not NULL,
    ** and that it conforms to the rules for identifiers.
    ** Null subscriber represents virtual subscriptions
    */

    IF @subscriber IS NOT NULL AND @subscriber <> '%'
    BEGIN
        EXECUTE @retcode = sys.sp_validname @subscriber
        IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

        IF NOT EXISTS (SELECT *
                         FROM dbo.syssubscriptions
                        WHERE srvname = UPPER(@subscriber)
                        )
            and @ignore_distributor_failure = 0

            BEGIN
                RAISERROR (14063, 11, -1)
                RETURN (1)
            END
    END

    /*
    ** Parameter Check: @status.
    ** Set the @statusid according to the @status value.  Values can be
    ** any of the following:
    **
    **      status      statusid
    **      =========   ========
    **      inactive           0
    **      subscribed         1
    **      active             2
    **      initiated          3
    */

    IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'subscribed', 'inactive', 'initiated')
        BEGIN
            RAISERROR (14065, 16, -1)
        RETURN (1)
        END

    IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
        SELECT @statusid = @initiated
    ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
        SELECT @statusid = @active
    ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
        SELECT @statusid = @subscribed
    ELSE
        SELECT @statusid = @inactive

    /*
    ** Parameter Check: @previous_status.
    ** Set the @prevstatid according to the @previous_status value.
    ** Values can be any of the following:
    **
    **      previous_status      prevstatid
    **      ===============      ==========
    **      inactive                      0
    **      subscribed                    1
    **      active                        2
    **      initiated                     3
    */

    IF @previous_status IS NOT NULL
    BEGIN
        IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('initiated','active', 'subscribed', 'inactive')
        BEGIN
            RAISERROR (14066, 16, -1)
            RETURN (1)
        END

        IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS)
        BEGIN
            RAISERROR (14067, 16, -1)
            RETURN (1)
        END

        IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
            SELECT @prevstatid = @initiated
        ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
            SELECT @prevstatid = @active
        ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
            SELECT @prevstatid = @subscribed
        ELSE
           SELECT @prevstatid = @inactive
    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 <> '%'
    BEGIN
        EXECUTE @retcode = sys.sp_validname @destination_db
        IF @retcode <> 0
        RETURN (1)
    END

	/*
    ** Parameter Check: @offloadagent.
	*/
	IF @offloadagent IS NOT NULL
		AND @offloadagent != 0
	BEGIN
		-- "Parameter '@offloadagent' is no longer supported."
		RAISERROR(21698, 16, -1, '@offloadagent')
		RETURN 1
	END

	IF ISNULL(@offloadserver, N'') != N''
	BEGIN
		-- "Parameter '@offloadserver' is no longer supported."
		RAISERROR(21698, 16, -1, '@offloadserver')
		RETURN 1
	END

    /*
    ** Get distribution server information for remote RPC
    ** subscription calls.
    ** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
    */
    if @ignore_distributor = 1
        select @no_distproc = 1
    else
  select @no_distproc = 0

    IF @no_distproc = 0 --and @from_auto_sync = 0
    BEGIN
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher,
												@rpcsrvname		= @distributor OUTPUT,
												@distribdb		= @distribdb   OUTPUT

		IF (@@error <> 0 OR @retcode <> 0) and @ignore_distributor_failure = 0
		BEGIN
			RAISERROR (14071, 16, -1)
			RETURN (1)
		END
	
		IF (@publisher IS NOT NULL) AND ((@distribdb IS NULL OR @distributor IS NULL) and @ignore_distributor_failure = 0)
		BEGIN
			RAISERROR(21600, 16, -1, @publisher)
			RETURN (1)
		END
    END


    create table #sysextendedarticlesview
    (
		artid               		int                 NULL,
		creation_script     		nvarchar(255)       collate database_default null,
		del_cmd             		nvarchar(255)       collate database_default null,
		description         		nvarchar(255)       collate database_default null,
		dest_table          		sysname             collate database_default null,
		filter              		int                 NULL,
		filter_clause       		ntext               NULL,
		ins_cmd             		nvarchar(255)       collate database_default null,
		name                		sysname             collate database_default null,
		objid               		int                 NULL,
		pubid               		int                 NULL,
		pre_creation_cmd    		tinyint             NULL,
		status              		tinyint             NULL,
		sync_objid          		int                 NULL,
		type                		tinyint             NULL,
		upd_cmd             		nvarchar(255)       collate database_default null,
		schema_option       		binary(8)           NULL,
		dest_owner          		sysname             collate database_default null,
        ins_scripting_proc			int					NULL,
        del_scripting_proc			int					NULL,
        upd_scripting_proc			int					NULL,
        custom_script				nvarchar(2048)		NULL,
        fire_triggers_on_snapshot	bit					NOT NULL
    )

	INSERT INTO	#sysextendedarticlesview
	SELECT	sxav.*
	FROM	sysextendedarticlesview sxav,
			syspublications sp,
			@publication_ids pi
	WHERE	sp.pubid = sxav.pubid
	  AND	((@publication = N'%') or (sp.name = @publication collate database_default))
	  AND	 ((@article = N'%') or (sxav.name = @article collate database_default))
	  AND	sp.pubid = pi.pubid

    begin tran
    save TRANSACTION changesubstatus

    SELECT @finished_virtual = 0
    SELECT @finished_real = 0

    /*
    ** If @subscriber is null, don't process real subscriptions
    ** If @subscriber is not null and '%', don't process virtual subscriptions
    */
    IF @subscriber IS NULL
    BEGIN
    	SELECT @finished_real = 1
    END
    ELSE IF @subscriber <> '%'
    BEGIN
    	SELECT @finished_virtual = 1
    END

    WHILE (@finished_real = 0 OR @finished_virtual = 0)
    BEGIN
        /*
        ** Declare cursor containing subscriptions to be updated.
        */
        IF @finished_real = 0
        BEGIN
            IF @previous_status IS NOT NULL
            BEGIN
				DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
				SELECT pub.name,
						sub.artid,
						art.objid,
						sub.srvid,
						sub.srvname,
						sub.dest_db,
						sub.status,
						case
							when ((pub.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
									or pub.allow_dts = 1 then 1
							else 0
						end,
						pub.repl_freq,
						art.type,
						pub.immediate_sync,
						pub.enabled_for_internet,
						pub.allow_anonymous,
						sub.subscription_type,
						sub.sync_type,
						sub.update_mode,
						art.name,
						sub.login_name,
						sub.loopback_detection,
						pub.pubid,
						pub.allow_initialize_from_backup,
                        pub.sync_method,
                        sub.nosync_type
				  FROM	syssubscriptions sub,
						#sysextendedarticlesview art,
						syspublications pub,
						@publication_ids pi
				 WHERE	((@publication = N'%') or (pub.name = @publication collate database_default))
				   AND	((@article = N'%') or (art.name = @article collate database_default))
				   AND	((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
					and (sub.srvname is not null and len(sub.srvname)> 0)
				   AND	sub.artid = art.artid
				   AND	art.pubid = pub.pubid
				   AND	sub.status = @prevstatid
				   AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db collate database_default))
			  	   AND	pub.pubid = pi.pubid
				ORDER BY art.objid
            END
            ELSE
            BEGIN
				DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
				SELECT	pub.name,
						sub.artid,
						art.objid,
						sub.srvid,
						sub.srvname,
						sub.dest_db,
						sub.status,
						case
							when ((pub.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
									or pub.allow_dts = 1 then 1
							else 0
						end,
						pub.repl_freq,
						art.type,
						pub.immediate_sync,
						pub.enabled_for_internet,
						pub.allow_anonymous,
						sub.subscription_type,
						sub.sync_type,
						sub.update_mode,
						art.name,
						sub.login_name,
						sub.loopback_detection,
						pub.pubid,
						pub.allow_initialize_from_backup,
                        pub.sync_method,
                        sub.nosync_type
				FROM	syssubscriptions sub,
						#sysextendedarticlesview art,
						syspublications pub,
						@publication_ids pi
				WHERE	((@publication = N'%') or (pub.name = @publication collate database_default))
				  AND	((@article = N'%') or (art.name = @article collate database_default))
				  AND	((@subscriber = N'%') OR (sub.srvname = UPPER(@subscriber) ))
					and (sub.srvname is not null and len(sub.srvname)> 0)
				  AND	sub.artid = art.artid
				  AND	art.pubid = pub.pubid
				  AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db))
			  	  AND	pub.pubid = pi.pubid
				order by art.objid
            END

            SELECT @finished_real = 1
        END
        ELSE IF @finished_virtual = 0
        BEGIN
            DECLARE @sub_bit smallint
                        ,@null_name sysname

            SELECT @sub_bit = 4
                        ,@null_name = NULL

            /*
            ** Treat anonymous virtual subscription as DSN subscriber.
            ** This will cause sp_MSarticlecol being called in sp_changesubstatus
            */
			DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
			SELECT	pub.name,
					sub.artid,
					art.objid,
					sub.srvid,
					@null_name,              /* subscriber name. NULL for virtual */
					sub.dest_db,
					sub.status,
					case
						when
							(pub.allow_anonymous = 1 and
								(pub.sync_method = @bcp_char or	pub.sync_method = @concurrent_char))
							or pub.allow_dts = 1 then 1
						else 0
					end, /*indicate dsn or not */
					pub.repl_freq,
					art.type,
					pub.immediate_sync,
					pub.enabled_for_internet,
					pub.allow_anonymous,
					sub.subscription_type,
					sub.sync_type,
					sub.update_mode,
					art.name,
					login_name,
					sub.loopback_detection,
					pub.pubid,
					pub.allow_initialize_from_backup,
                    pub.sync_method,
                    sub.nosync_type
			FROM	syssubscriptions sub,
					#sysextendedarticlesview art,
					syspublications pub,
					@publication_ids pi
			WHERE	((@publication = N'%') or (pub.name = @publication))
			  AND	((@article = N'%') or (art.name = @article))
			  AND	sub.srvid = -1
			  AND	sub.artid = art.artid
			  AND	art.pubid = pub.pubid
		  	  AND	pub.pubid = pi.pubid
			
            SELECT @finished_virtual = 1
        END

        OPEN #hCsubstatus
        FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name, @dest_db,
            @sub_status, @non_sql_flag, @repl_freq, @art_type,
            @immediate_sync, @enabled_for_internet,
            @allow_anonymous, @subscription_type, @sync_type, @update_mode,
            @art_name, @login_name, @loopback_detection,@pubid,
            @allow_initialize_from_backup, @sync_method, @nosync_type

        WHILE (@@fetch_status <> -1)
        BEGIN

            IF  suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1
                AND is_member ('db_owner') <> 1
            BEGIN
                if @@trancount > 0
                begin
                    ROLLBACK TRANSACTION changesubstatus
                    commit tran
                end
                RAISERROR (14126, 11, -1)
                RETURN (1)
            END
            /*
            ** condition 1:
            ** If current status is same as new status, and status is not 'initiated' do nothing.
            ** If both old and new status = 'initiated', this indicates that the
            ** snapshot agent previously bombed out between the initiation and activation stages and
            ** is now again trying to sync the publication.
            **
            ** condition 2:
            ** @auto_sync_only is used by snapshot for immediate_sync
            ** publications.
            **
            ** condition 3:
            ** Because sp_MSactivate_auto_sub (and thus the snapshot agent)
            ** calls this procedure for all subscriptions, we need to ignore
            ** the real subscriptions that are already active so that they won't be
            ** transitioned to the initiated state.  If we don't do this, those
            ** subscriptions will be resynced using the new snapshot.
	**
            ** however, we DO want a new snapshot to be generated for virtual
            ** subscriptions to active publications.
            **
            ** condition 4:
            ** Because the heterogeneous log reader (with @from_auto_sync = 1)
            ** calls this procedure for all subscriptions, we need to ignore
            ** the real subscriptions that are already active so that they won't be
            ** transitioned to the initiated state.  If we don't do this, those
            ** subscriptions will be resynced using the new snapshot.
	**
            ** however, we DO want a new snapshot to be generated for virtual
            ** subscriptions to active publications.
            **
            ** condition 5:
            ** Only initiated subscription can be activated by concurrent
            ** snapshot, this is to avoid new subscriptions added during
            ** concurrent snapshot generation from being partially
            ** activated (active at publisher but inactive at distributor)
            **
            */
            IF  (@sub_status = @statusid AND @sub_status <> @initiated ) OR
                (@from_auto_sync = 1 AND @sync_type <> @automatic) OR
                (@sub_status = @active AND @statusid = @initiated AND @srvid <> -1 AND @from_auto_sync = 1) OR
                (@sub_status in (@subscribed, @inactive) AND @statusid = @active AND @from_auto_sync = 1 AND @sync_method in (3,4))
            BEGIN
                FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name,
                   @dest_db, @sub_status, @non_sql_flag, @repl_freq, @art_type,
                   @immediate_sync, @enabled_for_internet,
                   @allow_anonymous, @subscription_type, @sync_type, @update_mode,
                   @art_name, @login_name, @loopback_detection, @pubid,
                   @allow_initialize_from_backup, @sync_method, @nosync_type

                CONTINUE
            END

			if((@publisher IS NULL) or (@publisher_type = N'MSSQLSERVER'))
			begin
				EXEC sys.sp_MSget_qualified_name @tabid, @qualified_name OUTPUT
				IF @qualified_name IS NULL
					goto UNDO
			end

            -- If changing a virtual subscription to 'subscribed' status
            -- change the immediate_sync_ready bit
            if @statusid = @subscribed and @sub_name is NULL
            begin
                UPDATE syspublications SET immediate_sync_ready = 0 WHERE
                    pubid = @pubid and
                    immediate_sync_ready <> 0
                IF @@ERROR <> 0
                BEGIN
                  if @@trancount > 0
                    begin
                        ROLLBACK TRANSACTION changesubstatus
                        commit tran
                    end
                  RETURN (1)
                END
            end

            /*
            ** Update syssubscription status
            */
			UPDATE	syssubscriptions
			SET		status = @statusid
			FROM	syssubscriptions sub,
					sysextendedarticlesview art,
					syspublications pub
			WHERE	pub.name LIKE @current_publication
			  AND	art.artid = @artid
			  AND	sub.srvid = @srvid
			  AND	sub.artid = @artid
			  AND	art.pubid = pub.pubid
			  AND	sub.dest_db = @dest_db
			  AND	pub.pubid = @pubid
			
            IF @@ERROR <> 0
			BEGIN
				IF @@trancount > 0
				BEGIN
					ROLLBACK TRANSACTION changesubstatus
					COMMIT TRAN
				END
				RAISERROR (14053, 16, -1)
				RETURN (1)
			END

            
            -- Subscription reinitialization processing for Immediate
            -- and Queued publications
            
            if (@update_mode in (1,2,3,4,5,6,7))
            begin
                select @retcode = 0
                IF ((@statusid != @active) AND (@sub_status = @active))
                begin
                    
                    -- If we are going from active state to subscribed
                    -- set the reinit column so that no more updates from
                    -- subscriber are applied until (re)activation
                    
                    update dbo.syssubscriptions
                    set queued_reinit = 1
                    where
                        artid = @artid
                        and srvid = @srvid
                        and dest_db = @dest_db
                end
                ELSE IF ((@statusid = @active) AND (@sub_status != @active ))
                begin
                    
                    -- If we are going from subscribed state to active state
                    
                    if (@update_mode = 1)
                    begin
                        
                        -- Sync tran case : reset the reinit column
                        
                        update dbo.syssubscriptions
                        set queued_reinit = 0
                        where
                            artid = @artid
                            and srvid = @srvid
                            and dest_db = @dest_db
                    end
                    
                    -- For queued case : we do not need to send compensating
                    -- command anymore, sp_addqueued_artinfo will do the
                    -- queue reinitialization for all types of queued
                    -- subscriptions
                    
                end
                
                -- Check for error
                
                if (@@error != 0 or @retcode != 0)
                begin
                    if @@trancount > 0
                    begin
                        ROLLBACK TRANSACTION changesubstatus
                        commit tran
                    end
                    RAISERROR (14053, 16, -1)
                    RETURN (1)
                end
            end

            /*
            ** Get timestamp of subscription.
            */
            if @publisher IS NULL
                -- SQL Server publisher
                EXEC @retcode = sys.sp_replincrementlsn_internal @xact_seqno OUTPUT
            else
                -- Heterogeneous publisher	
     EXEC @retcode = dbo.sp_replincrementlsn @xact_seqno OUTPUT, @publisher
            IF @@ERROR <> 0 or @retcode <> 0
            BEGIN
               if @@trancount > 0
                begin
                    ROLLBACK TRANSACTION changesubstatus
                    commit tran
                end
               RETURN (1)
            END

            select @sub_ts = @xact_seqno
            IF @sub_ts IS NULL
                        BEGIN
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RAISERROR (14053, 16, -1)
                            RETURN (1)
                        END

            
            -- If initiating the subscription, toss a SYNCINIT token into the
            -- log for the article and return LSN as a results set
            
            -- Note:  This should come after the subscription LSN is obtained.
            -- in order to assure proper application of SYNSTAT tokens in the
            -- distribution database
            
            -- Make sure @sub_status <> @initiated
            -- Skip HREPL
            IF @statusid = @initiated
				and @publisher_type = N'MSSQLSERVER'
            BEGIN
                -- Exclude schema-only articles here (proc exec article as well?)
                IF @art_type not in (0x20, 0x40, 0x60, 0x80, 0xA0, 24, 8)
                BEGIN
                    -- set filtered status.  Must log old text information during initiated state
                    -- in order to support update splitting

                    exec sys.sp_MSsetfilteredstatus @tabid

                    -- set nonsqlsub status.  must prevent UPDATETEXT operations during
                    -- initiated state

                    exec sys.sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'add'
                END
                exec sp_replpostsyncstatus @pubid, @artid, 1, @syncinit_lsn output
                if @f_syncstat_posted = 0
                begin
                    if @from_auto_sync = 1
                    begin
                        select @pubid, @artid, @syncinit_lsn
                    end
                    select @f_syncstat_posted = 1
                end
            END

            
            -- If changing the state FROM initiated, post a SYNCDONE token to the
            -- log for the article.
            
            -- Exclude schema-only articles here (proc exec article as well?)
            -- Exclude HREPL
            IF @sub_status = @initiated
				and @statusid <> @initiated
				and @publisher_type = N'MSSQLSERVER'
            BEGIN
                IF @art_type not in (0x20, 0x40, 0x60, 0xA0, 0x80, 24, 8)
                BEGIN
                    -- reset filtered status to normal value

                    exec sys.sp_MSsetfilteredstatus @tabid

                    -- clear nonsqlsub status for this article.

                    exec sys.sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'drop'

                END
                --if @f_syncstat_posted = 0
                --begin
                    exec sp_replpostsyncstatus @pubid, @artid, 0, @syncinit_lsn output
                --  select @f_syncstat_posted = 1
                --end
            END

            /*
            ** If activating subscription, update sysextendedarticlesview, sys.objects and
            ** MSrepl_subscriptions.
            */
            IF @statusid in ( @active, @initiated )
            BEGIN
                /*
              ** Update status of article to show it has been activated.
                */
                IF @repl_freq = 0 and EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
                    AND status & 1 <> 1)
                BEGIN
                    -- At most one row will be updated in the following two updates as the artid is unique
                    -- among both sysarticles and sysschemaarticles
                    UPDATE sysarticles SET status = status | 1 WHERE artid = @artid
                    IF @@ERROR <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRANSACTION changesubstatus
                            commit tran
                        end
                        RAISERROR (14069, 16, -1)
                        RETURN (1)
                    END
                    UPDATE sysschemaarticles SET status = status | 1 WHERE artid = @artid
                    IF @@ERROR <> 0
                    BEGIN
                        if @@trancount > 0
                        begin
                            ROLLBACK TRANSACTION changesubstatus
                            commit tran
                        end
                        RAISERROR (14069, 16, -1)
                        RETURN (1)
                    END
                    SELECT @art_change = 1
                END

                    /*
                    ** Turn the replication flag on for this object in the
                    ** sys.objects table (make it logbased).
                    */

                -- For Yukon non-concurrent snapshot processing, the
                -- activation would have been accomplished outside and
                -- before the bcp&subscription activation transaction so
                -- bypass the activation as appropriate
                if @repl_freq = 0 and @skipobjectactivation = 0 and @art_type not in (0x20,0x40,0x60,0xA0,0x80,8,24)
                BEGIN
                    SELECT @objid = objid FROM sysextendedarticlesview WHERE artid = @artid
                    IF @objid IS NOT NULL
                    BEGIN
							EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @objid
							IF @@ERROR <> 0 or @retcode <> 0
								goto UNDO

                        EXEC %%Relation(ID = @objid).SetReplicated(Value = 1)
                        IF @@ERROR <> 0
                        BEGIN
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RAISERROR (14068, 16, -1)
                            RETURN (1)
                        END
                    END
                END
            END

            /*
            ** Update status of all columns if subscriber is non-SQL Server.
            */
            IF @publisher_type = N'MSSQLSERVER'
            BEGIN
				IF @non_sql_flag <> 0 AND ( @art_type & 1 ) = 1
				BEGIN
					IF @statusid = @subscribed OR @statusid = @active
					BEGIN
						EXEC @retcode = sys.sp_MSarticlecol @artid, NULL, 'nonsqlsub', 'add'
						
						IF @@ERROR <> 0 OR @retcode <> 0
						BEGIN
							if @@trancount > 0
							begin
								ROLLBACK TRANSACTION changesubstatus
								commit tran
							end
							RAISERROR (14068, 16, -1)
							RETURN (1)
						END
					END
					ELSE IF @statusid = @inactive
					BEGIN
						EXEC @retcode = sys.sp_MSarticlecol @artid, NULL, 'nonsqlsub', 'drop'

						IF @@ERROR <> 0 OR @retcode <> 0
						BEGIN
							if @@trancount > 0
							begin
								ROLLBACK TRANSACTION changesubstatus
								commit tran
							end
							RAISERROR (14068, 16, -1)
							RETURN (1)
						END
					END
				END
			END

            /*
            ** If deactivating subscription, update sysextendedarticlesview, sys.objects and
            ** MSrepl_subscriptions.
            ** NOTE: Don't deactivate the article if it is in a publication that allows
            ** is enabled for autonosyncs.
            */

            IF @statusid NOT IN( @active, @initiated ) AND @sub_status IN ( @active, @initiated )
            BEGIN
                /*
                ** Set the article status to 'inactive' if there are
                ** no other active subscriptions on it.
                */
                IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
                   artid = @artid AND status = @active)
                BEGIN
                    IF EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
                        AND status & 1 = 1 and @allow_initialize_from_backup = 0)
                    BEGIN
                        -- If the snapshot agent is running, we will abort here
                        -- as we don't want to deactivate articles under the
                        -- snapshot agent's feet so to speak.
                        exec @retcode = sp_MSissnapshotagentrunning @publication = @publication
                        IF @retcode <> 0
                        BEGIN
                            if @@trancount > 0
                            BEGIn
                                ROLLBACK TRANSACTION changesubstatus
                                COMMIT TRAN
                            END
                            RAISERROR(21861, 16, -1)
                            RETURN (1)
                        END


                        -- At most one row will be updated in the following two updates as the artid is unique
                        -- among both sysarticles and sysschemaarticles
                        UPDATE sysarticles SET status = status & ~1 WHERE
                            artid = @artid
                        IF @@ERROR <> 0
                        BEGIN
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RAISERROR (14069, 16, -1)
                            RETURN (1)
                        END
                        UPDATE sysschemaarticles SET status = status & ~1 WHERE
                            artid = @artid
                        IF @@ERROR <> 0
                        BEGIN
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RAISERROR (14069, 16, -1)
                            RETURN (1)
                        END

                        SELECT @art_change = 1
                    END
                END

				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

                /*
                ** Set the object replication bits  to 'inactive' if
                ** there are no other active subscriptions on the
                ** table and the object is not published in a
                ** publication that allows is enabled for autonosyncs.
                */
                IF @publisher_type = N'MSSQLSERVER'
                	AND	NOT EXISTS
					(
						SELECT	*
						FROM	syssubscriptions
						WHERE	artid IN
								(
									SELECT	sa.artid
									FROM	sysextendedarticlesview sa,
											syspublications sp
									WHERE	sa.objid = @tabid
									  AND	sa.pubid = sp.pubid
									  AND	sp.repl_freq = 0
								)
						  AND	status = @active
					)
					AND NOT EXISTS
					(
						SELECT	*
						FROM	sysextendedarticlesview sa INNER JOIN syspublications sp
						  ON	sa.pubid = sp.pubid
						WHERE	sa.objid = @tabid
						  AND	sp.allow_initialize_from_backup = 1
					)
					AND not exists
					(	
						select * from @cdc_tracked_tables where object_id = @tabid
					)
                BEGIN
                    SELECT @objid = objid FROM sysextendedarticlesview WHERE artid = @artid and @art_type not in (0x20,0x40,0x60,0xA0,0x80,8,24)
                    IF @objid IS NOT NULL
                    BEGIN
							EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @objid
							IF @@ERROR <> 0 or @retcode <> 0
								goto UNDO

                        EXEC %%Relation(ID = @objid).SetReplicated(Value = 0)
                        IF @@ERROR <> 0
                        BEGIN
                            RAISERROR (14068, 16, -1)
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RETURN (1)
                        END
                    END
                END
            END

            -- Note:  Not only do we need to have the replupdateschema already executed
            -- so we can handle rollbacks, we also need to
            -- acquire the schema lock before RPC to the distributor to avoid livelock
            -- with snapshot agents. Snapshot agents acquire lock on user table before
            -- updating the distribution db.

            if (@no_distproc = 0) and (@distribdb IS not NULL and @distributor IS not NULL)
            begin
                /*
                ** Add the active subscription to the distributor's
                ** subscriptions table if changing status from @inactive
                */
                IF @sub_status = @inactive
                -- From inactive to subscribed or active
                BEGIN
					DECLARE @null_char sysname
                    DECLARE @zero_bit bit

					SELECT @null_char = NULL
                    SELECT @zero_bit = 0

					-- Check strict security requirements (>= 90 Security)
					IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
					BEGIN
						SELECT @internal = N'YUKON ADD SUB'
					END
					ELSE
					BEGIN
						SELECT @internal = N'PRE-YUKON'
					END

                    SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscription'
                    EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name,
                        @artid, @dest_db, @statusid, @sub_ts,
                        @current_publication,
                        @null_char, -- Pass null to @article, we already gave @artid
                        @subscription_type,
                        --@immediate_sync,
                        @sync_type,
                        @zero_bit,
                        @frequency_type,
                        @frequency_interval,
                        @frequency_relative_interval,
                        @frequency_recurrence_factor,
                        @frequency_subday,
                        @frequency_subday_interval,
                        @active_start_time_of_day,
                        @active_end_time_of_day,
                        @active_start_date,
                        @active_end_date,
                        @optional_command_line = @optional_command_line,
                        -- synctran
                        @update_mode = @update_mode,
                        @loopback_detection = @loopback_detection,
                        @distribution_jobid = @distribution_jobid OUTPUT,
                        @dts_package_name = @dts_package_name,
                        @dts_package_password = @dts_package_password,
                        @dts_package_location = @dts_package_location,
                        @distribution_job_name = @distribution_job_name,
                        @internal = @internal,
                        @publisher_engine_edition = @publisher_engine_edition,
                        @nosync_type = @nosync_type
                    IF @@ERROR <> 0 OR @retcode <> 0
                    BEGIN
                        RAISERROR (14070, 16, -1)
                        if @@trancount > 0
                        begin
                            ROLLBACK TRANSACTION changesubstatus
                            commit tran
                        end
                        RETURN (1)
                    END
                END
                ELSE
                -- From subscribed or active to others
                BEGIN
                    /*
                    ** Drop the deactivated subscription from the distributor's
                    ** subscriptions table.
                    */
                    IF @statusid = @inactive
                    -- From subscribed to inactive or from active to inactive
                    BEGIN
                        SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSdrop_subscription'
                        EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name,  @artid, @dest_db, @current_publication

                        IF @@ERROR <> 0 OR @retcode <> 0
                        BEGIN
                            RAISERROR (14070, 16, -1)
                            if @@trancount > 0
                            begin
                                ROLLBACK TRANSACTION changesubstatus
                                commit tran
                            end
                            RETURN (1)
                        END
                    END
                    ELSE
                    -- From subscribed to initiated to active or from active to subscribed.
                    BEGIN
                        -- Don't do it if activating the subscription for snapshot agent.
                        --IF NOT (@from_auto_sync = 1 AND @statusid in(@active, @initiated) )
                        IF NOT (@from_auto_sync = 1 AND @statusid in(@active) )
                        BEGIN
                            SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSupdate_subscription'
                            EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name, @artid, @statusid, @sub_ts, @dest_db
                            IF (@@ERROR <> 0 OR @retcode <> 0) and @ignore_distributor_failure = 0
                            BEGIN
                                RAISERROR (14070, 16, -1)
                                if @@trancount > 0
                                begin
                                    ROLLBACK TRANSACTION changesubstatus
                                    commit tran
                                end
                                RETURN (1)
                            END
                        END
                    END
                END
            end

            /*
            ** Set internal object replication bit  to 'inactive' if
            ** there are no other active subscriptions on the
            ** procedure, but don't try to deactivate the procedure
            ** if it is published in a publication that allows
            ** is enabled for autonosyncs.
            */

			IF @statusid = @inactive AND @sub_status IN (@active,@initiated) AND
                NOT EXISTS (SELECT * FROM syssubscriptions WHERE
                artid IN (SELECT artid FROM sysextendedarticlesview WHERE
                objid = @tabid) AND status IN (@active,@initiated) ) AND
                NOT EXISTS (SELECT *
                              FROM sysextendedarticlesview sa
                            INNER JOIN syspublications sp
                                ON sa.pubid = sp.pubid
                             WHERE sa.objid = @tabid
                               AND sp.allow_initialize_from_backup = 1)
            BEGIN
                   /*
                   ** If it's a procedure execution article, clear proc status bits
                   */
                IF (@art_type & 8 ) = 8
                BEGIN
						EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @tabid
						IF @@ERROR <> 0 or @retcode <> 0
							goto UNDO

                    EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 0)
                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                    EXEC %%Module(ID = @tabid).SetProcReplSerialOnly(Value = 0)
                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                END
            END


            /* Turn on object replication */

            ELSE IF @statusid = @active
            BEGIN
					IF( @art_type & 8 ) = 8
					begin
						EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @tabid
						IF @@ERROR <> 0 or @retcode <> 0
							goto UNDO
					end
                IF (@art_type & 24 ) = 24
                BEGIN
                    EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 1)
                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                    EXEC %%Module(ID = @tabid).SetProcReplSerialOnly(Value = 1)
                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                END
                ELSE IF( @art_type & 8 ) = 8
                BEGIN
                    EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 1)
                    IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
                END
            END

           /*
           ** Get next row.
           */
           FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name, @dest_db,
           @sub_status, @non_sql_flag, @repl_freq, @art_type ,
           @immediate_sync, @enabled_for_internet,
           @allow_anonymous, @subscription_type, @sync_type, @update_mode,
           @art_name, @login_name, @loopback_detection, @pubid,
           @allow_initialize_from_backup, @sync_method, @nosync_type

       END  -- end while for cursor

       CLOSE #hCsubstatus
       DEALLOCATE #hCsubstatus

    END -- end while for virtual and real

    -- force refresh of article cache
    -- Only do it if necessary
    -- No need on brute force cleanup or for heterogeneous publications
    IF ( @art_change = 1 ) and ( @ignore_distributor = 0 ) and (@publisher IS NULL)
        EXECUTE sys.sp_replflush

    COMMIT TRANSACTION
    drop table #sysextendedarticlesview
    RETURN(0)

UNDO:
    RAISERROR (14068, 16, -1)
    IF @@trancount > 0
    BEGIN
        ROLLBACK TRANSACTION changesubstatus
        COMMIT TRAN
    END
    RETURN (1)
END

 
Last revision 2008RTM
See also

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