Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_reinitsubscription

  No additional text.


Syntax
create procedure sys.sp_MSrepl_reinitsubscription
(
	@publication				sysname,
	@article					sysname,
	@subscriber					sysname,
	@destination_db				sysname,
	@for_schema_change			bit,
	@distributor				sysname,
	@distribdb					sysname,
	@publisher					sysname,
	@publisher_type				sysname,
	@ignore_distributor_failure bit = 0,
	@invalidate_snapshot		bit
)
AS
BEGIN
    DECLARE @retcode			    int
    declare @active				    tinyint
    declare @subscribed			    tinyint
    declare @automatic			    tinyint
    DECLARE @artid				    int
    DECLARE @distproc			    nvarchar (255)
    DECLARE @dbname				    sysname
    DECLARE @sync_type			    tinyint
    DECLARE @immediate_sync		    bit
    DECLARE @immediate_sync_ready   bit
    DECLARE @subscription_type	    int
    DECLARE @push				    int
    DECLARE @pub				    sysname
    DECLARE @dest_db			    sysname
    DECLARE @sub_name			    sysname
    DECLARE @art_name			    sysname
    DECLARE @none				    tinyint
    DECLARE @login_name			    sysname
    DECLARE @loc_publisher		    sysname
    DECLARE @publisher_db		    sysname
    DECLARE @options			    int
    DECLARE @opt_enable_p2p		    int
	DECLARE @current_publication	sysname
	
	DECLARE @publication_ids TABLE
	(
		pubid	int
	)
	
    -- Initialization
    select @active = 2
    select @subscribed = 1
    select @publisher_db = DB_NAME()
    select @dbname = DB_NAME()
    SELECT @none = 2            /* Const: synchronization type 'none' */
    SELECT @automatic = 1       /* Const: synchronization type 'automatic' */
    select @push = 0
	select @opt_enable_p2p = 0x1
	
    /*
    ** Security Check.
    ** We use login_name stored in syssubscriptions to manage security
    */

    -- Ensure that this is being run on a published tran database
    if sys.fn_MSrepl_istranpublished (@dbname,1) = 0
    begin
        -- This database is not enabled for publication.
        raiserror (14013, 16, -1)
        return 1
    end

    /* Validate names */
    EXECUTE @retcode = sys.sp_validname @publication
    IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)

    /* article name can be a quoted name
    EXECUTE @retcode = sys.sp_validname @article
    IF @@ERROR <> 0 OR @retcode <> 0
        RETURN (1)
    */

    -- Subscriber can be NULL
    IF @subscriber IS NOT NULL
    BEGIN
        EXECUTE @retcode = sys.sp_validname @subscriber
        IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)

        EXECUTE @retcode = sys.sp_validname @destination_db
        IF @@ERROR <> 0 OR @retcode <> 0
            RETURN (1)
    END

    -- Get a real server name if needed
    IF @publisher IS NOT NULL
		SET @loc_publisher = @publisher
	ELSE
		SET @loc_publisher = publishingservername()

    -- Replace 'all' with '%'
    if LOWER(@publication) = 'all'
        SELECT @publication = '%'

    if LOWER(@article) = 'all'
        SELECT @article = '%'

    if LOWER(@subscriber) = 'all'
        SELECT @subscriber = '%'

    if LOWER(@destination_db) = 'all'
        SELECT @destination_db = '%'

	-- 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)
		
    /*
    ** 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 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 NOT EXISTS
    (
    	SELECT	*
		FROM	sysextendedarticlesview a,
				syspublications b,
				@publication_ids pi
		WHERE	((@article = N'%') or (a.name = @article))
		  AND	a.pubid = b.pubid
		  AND	((b.name = @publication) or  (@publication = N'%'))
		  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

    -- Wrong dest_db will be caught by the following query

    -- Check to make sure the specific subscription exists
    IF (@publication <> '%' AND @subscriber <> '%' AND
        NOT EXISTS
        (
        	SELECT	*
			FROM	syssubscriptions sub,
					sysextendedarticlesview art,
					syspublications pub,
					@publication_ids pi
			WHERE	pub.name LIKE @publication collate database_default
			  AND	((@article = N'%') or (art.name = @article collate database_default))
			  AND	(sub.srvname = UPPER(@subscriber)
				and (sub.srvname is not null and len(sub.srvname)> 0)
			   OR	(@subscriber is NULL
			  AND	pub.allow_anonymous = 1))
			  AND	sub.artid = art.artid
			  AND	art.pubid = pub.pubid
			  AND	((@destination_db = N'%') OR (sub.dest_db = @destination_db collate database_default))
			  AND	pub.pubid = pi.pubid
		))
    BEGIN
        RAISERROR (14055, 16, -1)
        RETURN (1)
    END
    -- Check for any subscriptions
    ELSE IF NOT EXISTS
    (
    	SELECT	art.artid
		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 collate database_default))
		  AND	pub.pubid = pi.pubid
		UNION
		SELECT	art.artid
		FROM	syspublications pub,
				sysarticles art,
				@publication_ids pi
		WHERE	((@publication = N'%') or (pub.name = @publication collate database_default))
		  AND	((@article = N'%') or (art.name = @article collate database_default))
		  AND	art.pubid = pub.pubid
		  AND	(@subscriber = '%' OR @subscriber IS NULL)
		  AND	pub.pubid = pi.pubid
	)
	BEGIN
		-- No subscription for specified parameters
		RAISERROR (14135, 16, -1, @loc_publisher, @publisher_db, @publication)
		RETURN (1)
	END

    IF @invalidate_snapshot = 1
    BEGIN
	    IF @distribdb IS NULL OR @distributor IS NULL
		BEGIN
			RAISERROR (14071, 16, -1)
			RETURN 1
		END
		
    	BEGIN TRAN tr_reinit_invalidate_snap
    	SAVE TRAN tr_reinit_invalidate_snap
    	
		UPDATE syspublications
			SET	immediate_sync_ready = 0
			WHERE pubid IN (SELECT pubid
								FROM @publication_ids)
			  AND immediate_sync_ready <> 0

		IF @@ERROR <> 0
		BEGIN
			GOTO UNDO_INVALIDATE_SNAP
		END
		
		DECLARE #hCpublications CURSOR LOCAL FAST_FORWARD FOR
			SELECT sysp.name
				FROM syspublications sysp
					JOIN @publication_ids pids
						ON sysp.pubid = pids.pubid

		OPEN #hCpublications

		FETCH #hCpublications INTO @current_publication

		WHILE @@FETCH_STATUS != -1
		BEGIN
			-- invalidate snapshots at the distributor
			SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSinvalidate_snapshot'
			EXEC @retcode = @distproc
							@publisher		= @loc_publisher,
							@publisher_db	= @publisher_db,
							@publication	= @current_publication

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

			FETCH #hCpublications INTO @current_publication
		END

		CLOSE #hCpublications
		DEALLOCATE #hCpublications
		
		-- Raise a warning. Snapshot is invalidated.
		-- Need to run snapshot agent again...
		RAISERROR(20605, 10, -1)

		COMMIT TRAN tr_reinit_invalidate_snap
    END

	-- Get list of subscriptions to reinit
    DECLARE #hCresyncsub CURSOR LOCAL FAST_FORWARD FOR
	-- non immediate_sync pubs
	SELECT	pub.name,
			pub.immediate_sync,
            pub.immediate_sync_ready,
			art.name,
			sub.srvname,
			sub.dest_db,
			sub.sync_type,
			sub.subscription_type,
			sub.login_name,
			pub.options
	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 collate database_default))
	  AND	sub.status = @active
	  AND	pub.immediate_sync = 0
	  AND	pub.pubid = pi.pubid
	UNION
	-- Immediate_sync pubs
	SELECT DISTINCT
			pub.name,
			pub.immediate_sync,
            pub.immediate_sync_ready,
			-- If @article is '%', do publication level operation.
			-- otherwise, do article level
			case @article
				when '%' then '%'
				else art.name
				end,
			sub.srvname,
			sub.dest_db,
			sub.sync_type,
			sub.subscription_type,
			sub.login_name,
			pub.options
	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 collate database_default))
	  AND	sub.status = @active
	  AND	pub.immediate_sync = 1
	  AND	pub.pubid = pi.pubid
	UNION
	-- For anonymous subscribers or attached subscriptions.
	SELECT DISTINCT
			pub.name,
			pub.immediate_sync,
            pub.immediate_sync_ready,
			-- If @article is '%', do publication level operation.
			-- otherwise, do article level
			case @article
				when '%' then '%'
				else art.name
				end,					-- art.name is '%' from immediate_sync pub
			CONVERT(sysname, NULL),		-- subscriber name (null represent virtual)
			'virtual',					-- destination_db for virtual subscription is hardcoded in
										-- sp_MSadd_subscription.
			@automatic,					-- sub.sync_type is auto tor anonymous subscriber
			@push,						-- virtual subscription is push type,
			SUSER_SNAME(0x01), -- sa account
			pub.options
	FROM	syspublications pub,
			sysarticles art,
			@publication_ids pi
	WHERE	((@publication = N'%') or (pub.name = @publication collate database_default))
	  AND	((@article = N'%') or (art.name = @article collate database_default))
	  AND	art.pubid = pub.pubid
	  AND	pub.immediate_sync = 1
	  AND	(@subscriber = '%' OR @subscriber IS NULL)
	  AND	pub.pubid = pi.pubid
    FOR READ ONLY

    OPEN #hCresyncsub

    -- Note: Don't overwrite the variables used in the cursor.
    FETCH	#hCresyncsub
    INTO	@pub,
    		@immediate_sync,
            @immediate_sync_ready,
    		@art_name,
    		@sub_name,
			@dest_db,
			@sync_type,
			@subscription_type,
			@login_name,
			@options

	-- if non found then return an error saying that no subscriptions to reinit
	if @@fetch_status = -1
	begin
		-- NO ACTIVE SUSBSCRIPTIONS - will skip the next section
		select @publisher_db = db_name()
	end
	
    WHILE (@@fetch_status <> -1)
    BEGIN
        -- Security Check
        IF suser_sname(suser_sid()) <> @login_name
        		AND is_srvrolemember('sysadmin') <> 1
            	AND is_member ('db_owner') <> 1
        BEGIN
			-- if they do not pass security check then we will skip over the reinit for this case
            goto FETCH_NEXT
        END

		-- We cannot continue if this is for Peer-To-Peer
		IF (@options & @opt_enable_p2p) = @opt_enable_p2p
		BEGIN
            IF UPPER(@art_name) = N'%'
            BEGIN
				SELECT @art_name = N'all'
            END

			IF @sub_name IS NULL
				AND @dest_db = 'virtual'
			BEGIN
				SELECT @sub_name = 'anonymous_server',
						@dest_db = 'anonymous_db'
			END

			-- Cannot reinitialize article '%s' in subscription '%s:%s' to publication '%s'. The publication is enabled for 'Peer-To-Peer'.
			raiserror(20800, 16, -1, @art_name, @sub_name, @dest_db, @pub)
            GOTO UNDO
		END

		-- Replication-Support-Only Check
        if @sync_type = @none
        begin
	        raiserror(21071, 10, -1, @art_name, @sub_name, @dest_db, @pub)
	        goto FETCH_NEXT
        end

        begin tran
        save TRAN sp_reinitsubscription

        -- Reset subscription status to subscribed.
        -- It will be reactivated later as following:
        -- 1. Well known on non immediate_sync: it need to be reactivated by snapshot agent
        -- 2. Well known on immediate_sync: it will be reactivated laster in
        -- this stored procedure to the state of virtual subscription. The status will be
        -- active if the virtual subscription is active.
        -- 3. Anonymous (on immediate_sync by design): Only reset the status to subscribed
        -- if a single article is reinited or there's a schema change on the article.
        -- (refer to sp_MSreinit_article.) In this case, the status will be reactivated by
        -- snapshot agent. If the whole publication is reinited and it is not for a schema
        -- change, we don't need to do this
        -- since the anonymous agent will automatically pick up latest snapshots after
        -- we reset the subscription guid later.

        -- If @sub_name is null, we are resetting anonymous subscriptions.
        -- Don't do this when reiniting anonymous subscription on whole publication.
        IF not (@sub_name IS NULL and @article = '%') or @for_schema_change = 1
        BEGIN
            EXEC @retcode = sys.sp_changesubstatus
							@publication	= @pub,
							@article		= @art_name,
							@subscriber		= @sub_name,
							@destination_db	= @dest_db,
							@status			= 'subscribed',
							@publisher		= @publisher
							,@ignore_distributor_failure = @ignore_distributor_failure
            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14070, 16, -1)
                GOTO UNDO
            END
        END

        -- Don't do this when reiniting a single article.
        -- Reset the subscription guid at the distributor for immediate_sync publication.
        -- Reset subscription creation datetime for all types of publication
        -- used by retention cleanup.
        if (@article = '%')
        begin
			if (@distributor is not NULL and @distribdb is not null)
			begin
				SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSreset_subscription'
				EXEC @retcode = @distproc
							@publisher			= @loc_publisher,
							@publisher_db		= @dbname,
							@publication		= @pub,
							@subscriber			= @sub_name,
							@subscriber_db		= @dest_db,
							@subscription_type	= @subscription_type

				IF (@@ERROR <> 0 OR @retcode <> 0 ) and @ignore_distributor_failure = 0
				BEGIN
					GOTO UNDO
				END
			end
        end

        -- Activate the subscription again if the publication is immediate_sync and
        -- the whole publication is reinitted.
        -- Otherwise, the snapshot agent will activate the subscription

        -- If this is for schema change, commands generated by the LR will be invalid
        -- until the new snapshot is generated and applied so DON'T reactivate.
        -- Let the snapshot agent do it.

        --IF (@for_schema_change = 0 AND @immediate_sync = 1 AND @subscriber IS NOT NULL)
        IF (@for_schema_change = 0 and
                @immediate_sync = 1 AND
                @immediate_sync_ready = 1 and
                @subscriber IS NOT NULL and
                @article = '%')
        BEGIN
            -- Set subscription status back to active again.
            EXEC @retcode = sys.sp_changesubstatus
							@publication	= @pub,
							@article		= @art_name,
							@subscriber		= @sub_name,
							@destination_db	= @dest_db,
							@status			= 'active',
							@publisher		= @publisher
							,@ignore_distributor_failure = @ignore_distributor_failure

            IF @@ERROR <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14070, 16, -1)
                GOTO UNDO
            END
        END

        -- If article level reinit, reinit dependent articles in the publication as well
        if @article <> '%'
        begin
            -- Reinit articles on which the current article depends on.
            declare	@objid int,
            		@pubid int,
					@pre_creation_cmd tinyint
					
            select	@pubid = sp.pubid
            from	syspublications sp,
            		@publication_ids pi
            where	sp.name = @pub
              AND	sp.pubid = pi.pubid

            select	@objid = objid,
					@pre_creation_cmd = pre_creation_cmd
			from	sysarticles
			where	pubid = @pubid
			  and	name = @art_name
			
            -- Have to use temp cursor name otherwise we will get a 'cursor already exists' error
            -- in recursive calls.
            DECLARE #hCdep CURSOR LOCAL FAST_FORWARD FOR
                SELECT distinct art.name from sysextendedarticlesview art, syssubscriptions s where
                    art.pubid = @pubid and
                    s.artid = art.artid and
                    (s.srvid = -1 or s.srvname = upper(@sub_name)   ) and
                    s.dest_db = @dest_db and
                    s.status = @active and
                    -- Has dri on referencing table or not
                    (convert(int, substring(art.schema_option, len(art.schema_option) - 2 + 1, 2)) & 0x00000200 <> 0 and
                      -- If the article schema option includes DRI, reinit articles that have
                      -- forein key relationship on this table, have to do this
                      -- otherwise dist will fail because we cannot drop or delete base table.
                      exists ( select * from  sysreferences r where
                            r.rkeyid = @objid and
                            art.objid = r.fkeyid) or
                      -- If there's a schema bound view on this table, reinit that view etc.
                      -- We have to do this for schema bound view other wise, we cannot drop the table
                     -- Only do it if precreation command is 'drop table'
                     (@pre_creation_cmd = 1 and
                      exists ( select * from sysdepends d where
                            d.depid = @objid and
                            art.objid = d.id and
                            objectproperty(art.objid, 'IsSchemaBound') = 1)))
            FOR READ ONLY

            OPEN #hCdep

            -- Note: @art_name is changed
            FETCH #hCdep INTO @art_name

            WHILE (@@fetch_status <> -1)
            BEGIN
            	-- on this call we do not need to re-invalidate_snapshot since it's already been done.
                EXEC @retcode = sys.sp_reinitsubscription
								@publication		= @pub,
								@article			= @art_name,
								@subscriber			= @sub_name,
								@destination_db		= @dest_db,
								@for_schema_change	= @for_schema_change,
								@publisher			= @publisher,
								@ignore_distributor_failure = @ignore_distributor_failure,
								@invalidate_snapshot = 0

                IF @@ERROR <> 0 OR @retcode <> 0
                    GOTO UNDO
                FETCH #hCdep INTO @art_name
            END

            CLOSE #hCdep
            DEALLOCATE #hCdep
        end

	--if after this reinit, there is no more active article, update dbtable so future scan can skip previous records
	    if (@publisher_type = N'MSSQLSERVER')
	    	and not exists(SELECT * FROM sys.tables WHERE is_replicated = 1)
	    	and not exists(SELECT * FROM sys.views WHERE is_replicated = 1)
	    	and not exists (SELECT 1 FROM dbo.syssubscriptions where status = @active)
			--if cdc is enabled, don't call sp_repldone
			and not exists(select * from sys.databases where db_id() = database_id and is_cdc_enabled = 1)
    begin
				--don't fail on error, we don't want to change any behavior here,
				--this is just best effort attempt to do thing the optimal way, if this fails, doesn't mean you can't reinit

				--mark local transactions to have been replicated, in case there were large amount of pending transactions prior to reinit
				--this will allow truncation of log,
				exec sys.sp_replflush
				exec sys.sp_repldone NULL, NULL, 0, 0, 1
				exec sys.sp_replflush
SKIPOP:	
	    end
        COMMIT TRAN

FETCH_NEXT:
        FETCH #hCresyncsub INTO @pub, @immediate_sync, @immediate_sync_ready,
            @art_name, @sub_name, @dest_db, @sync_type, @subscription_type,
            @login_name, @options
    END

    CLOSE #hCresyncsub
    DEALLOCATE #hCresyncsub

    RETURN(0)

UNDO:
    IF @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN sp_reinitsubscription
        COMMIT TRAN
    end
    return 1

UNDO_INVALIDATE_SNAP:
	ROLLBACK TRAN tr_reinit_invalidate_snap
	COMMIT TRAN

	RETURN 1
END

 
Last revision 2008RTM
See also

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