Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSreinit_article

  No additional text.


Syntax
create procedure sys.sp_MSreinit_article
(
	@publication				sysname,
	@article					sysname = N'%',
	@need_new_snapshot			bit = 0,
	@need_reinit_subscription	bit = 0,
	@force_invalidate_snapshot	bit = 0,
	@force_reinit_subscription	bit = 0,
	@check_only					bit = 0,
	@from_drop_publication		bit = 0,
	@publisher					sysname = NULL,
	@publisher_type				sysname = N'MSSQLSERVER'
	,@ignore_distributor_failure bit = 0
)
AS
BEGIN
    DECLARE @retcode				int,
			@active					tinyint,
			@subscribed				tinyint,
			@artid					int,
			@pubid					int,
			@none					tinyint,
			@immediate_sync_ready	bit,
			@allow_anonymous		bit,
			@loc_publisher			sysname

	-- Initialize constants
    SELECT	@active		= 2,
    		@subscribed	= 1,
    		@none		= 2,
    		@active		= 2

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

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

	-- Get pub info
	SELECT	@immediate_sync_ready	= immediate_sync_ready,
			@allow_anonymous		= allow_anonymous
	FROM	syspublications
	WHERE	pubid = @pubid
		
	IF @publisher_type = N'MSSQLSERVER' AND @publisher IS NULL
	BEGIN
		-- Set publisher name
		SELECT	@loc_publisher = publishingservername()
	END
	ELSE
	BEGIN
		SELECT @loc_publisher = @publisher
	END

    IF @article = N'%'
    BEGIN
        SELECT	@artid = 0
	END
    ELSE
    BEGIN
    	SELECT	@artid = artid
    	FROM	sysarticles with (READPAST)
    	WHERE	name = @article
    	  AND	pubid = @pubid
	END

    begin tran
    save tran sp_MSreinit_article

	-- If at publication level, we know that we should do it since
	-- @immediate_sync_ready = 1
	-- If at article level, we only do it for the articles that have been
	-- processed by the snapshot agent, but not new articles.
	-- sp_addarticle calls this proc at publication level.
	-- It also make calls to sp_articlecolumn and sp_articleview which in turn
	-- call this sp. We don't want to do anything here with those calls.
    IF @need_new_snapshot = 1 AND @immediate_sync_ready = 1 AND
    (@artid = 0 OR EXISTS
		(
			SELECT	*
			FROM	syssubscriptions s,
					sysarticles a
			WHERE	s.artid	 = a.artid
			  AND	a.pubid	 = @pubid
			  AND	s.srvid	 < 0
			  AND	s.status = @active
			  AND	s.artid	 = @artid
		)
	)
	BEGIN
		-- Fail and raiserror error
		if @force_invalidate_snapshot = 0
		begin
			raiserror(20607, 16, -1)
			goto UNDO
		end

		IF @check_only = 0
		BEGIN
			UPDATE	syspublications
			SET		immediate_sync_ready = 0
			WHERE	pubid = @pubid
			  AND	immediate_sync_ready <> 0

			IF @@ERROR <> 0
			BEGIN
				GOTO UNDO
			END

			DECLARE	@distributor	sysname,
					@distribdb		sysname,
					@distproc		nvarchar (255),
					@dbname			sysname

			select @dbname = db_name()

			EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher	= @publisher,
																@rpcsrvname	= @distributor OUTPUT,
																@distribdb	= @distribdb OUTPUT
			IF (@retcode <> 0 OR @@ERROR <> 0) and @ignore_distributor_failure = 0
				goto  UNDO

			IF (@distribdb IS NULL OR @distributor IS NULL) and @ignore_distributor_failure = 0
			BEGIN
				RAISERROR (14071, 16, -1)
				goto  UNDO
			END
			
			
			IF (@distribdb IS not NULL and @distributor IS not NULL)
			BEGIN
				-- Deactivate virtual (but not virtual anonymous) subscriptions at the distributor
				SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSinvalidate_snapshot'
				EXEC @retcode = @distproc
								@publisher		= @loc_publisher,
								@publisher_db	= @dbname,
								@publication	= @publication

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


			-- Raise a warning. Snapshot is invalidated. Need to run
			-- snapshot agent again... Only do so if the publication
			-- is not being dropped.
			IF @from_drop_publication = 0
				RAISERROR(20605, 10, -1)
		END
	END

    IF @need_reinit_subscription = 1
    BEGIN
        -- Reinitialize the subscriptions if there are any.
        -- No need to reinit no_sync subscriptions.
        -- The query below works for an article or whole publication (@artid == 0)
        -- Including virtual subscriptions to take care anonymous.
        IF EXISTS
        (
        	SELECT	*
        	FROM	syssubscriptions s
        	WHERE	s.status = @active
			-- Only include virtual subscription if allow anonymous
			  AND	(s.srvid >= 0 OR (@allow_anonymous = 1 AND @immediate_sync_ready = 1))
			  AND	s.sync_type <> @none
			  AND	(s.artid = @artid OR
            			(@artid = 0 AND EXISTS
							(
								SELECT	*
								FROM	syspublications p,
										sysarticles a
								WHERE	a.artid = s.artid
								  AND	a.pubid = p.pubid
								  AND	p.pubid = @pubid
							)
						)
					)
		)
        BEGIN
            -- Fail and raiserror error
            IF @force_reinit_subscription = 0
            BEGIN
                RAISERROR(20608, 16, -1)
                GOTO  UNDO
            END

            IF @check_only = 0
            BEGIN
                EXEC @retcode = sys.sp_reinitsubscription	@publication		= @publication,
															@article			= @article,
															@subscriber			= 'all',
															@for_schema_change	= 1,
															@publisher			= @publisher
															,@ignore_distributor_failure = @ignore_distributor_failure

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

                -- Raise a warning. Subscriptions is reintialized.
                RAISERROR(20606, 10, -1)
            END
        END
    END

    COMMIT TRAN sp_MSreinit_article

    RETURN 0
UNDO:
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRAN sp_MSreinit_article
        COMMIT TRAN
    END
    RETURN 1
END

 
Last revision 2008RTM
See also

  sp_checkinvalidivarticle (Procedure)
sp_IHarticlecolumn (Procedure)
sp_IHarticleview (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_addarticle (Procedure)
sp_MSrepl_articlecolumn (Procedure)
sp_MSrepl_articlefilter (Procedure)
sp_MSrepl_articleview (Procedure)
sp_MSrepl_changearticle (Procedure)
sp_MSrepl_changearticlecolumndatatype (Procedure)
sp_MSrepl_changepublication (Procedure)
sp_MSrepl_droparticle (Procedure)
sp_MSrepl_reinitsubscription (Procedure)
sp_MStran_altertable (Procedure)
sp_repladdcolumn (Procedure)
sp_repldropcolumn (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