Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenableautonosync

  No additional text.


Syntax

-- Name: sp_MSenableautonosync

-- Description: This is a helper procedure for activating all articles in a
--              publication when the allow_initialize_from_backup property is
--              changed from 0 to 1 in sp_changepublication. This procedure
--              can also be used to perform "just-in-time" article activation
--              when setting up a 'replication support only' subscription.

--              Steps that will be performed by this procedure:
--              1) Update all null or empty destination object/owner name
--                 to be their source object's equivalent
--              2) Update all article status to active
--              3) Set the replicated bits of all article objects except those
--                 for schema-only articles
--              3.1) Set NFR identity columns
--              4) Flush the article cache
--              5) Set the allow_initialize_from_backup bit of the publication --                 to 1
--              6) Increment the publication's min autonosync lsn

-- Parameters: @publication sysname (mandatory)
--             @activate_articles_only (optional, default 0)

-- Notes: @publication is assumed to be verified as a valid publication name
--        in the current database by the caller. No attempt will be made in
--        this procedure to check for the validity of the @publication
--        parameter.

--        The @activate_articles_only parameter was subsequently added to
--        bypass all but steps 2) & 3) when it is set to 1. This new parameter
--        is used when setting up a  'replication support only' subscription
--        to perform "just-in-time" article object activation.

-- Security: This is an internal system procedure.

create procedure sys.sp_MSenableautonosync
(
	@publication            sysname,
	@activate_articles_only bit = 0,
	@publisher				sysname = NULL,
	@publisher_type			sysname = N'MSSQLSERVER'
)
WITH EXECUTE AS 'dbo'
as
begin
    set nocount on
    declare @retcode int,
            @transactionopened bit,
            @pubid int,
            @cursorallocated bit,
            @cursoropened bit,
            @objid int,
            @articletype tinyint,
            @active tinyint,
            @qualifiedname nvarchar(520),
            @identitycolname sysname,
            @distributor sysname,
            @distribdb   sysname,
            @distproc    nvarchar(768),
            @dbname      sysname,
            @distver     int

    select @transactionopened = 0,
           @retcode = 0,
           @cursorallocated = 0,
           @cursoropened = 0,
           @active = 1

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

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

    begin transaction
    save transaction sp_MSenableautonosync

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

    select @transactionopened = 1
    -- Set unspecified destination object\owner names to their source
    -- equivalent

    --1) & 2)
    -- Destination object/owner name, status
    update dbo.sysarticles
       set dest_table = case when (dest_table is null or rtrim(dest_table) = N'') and @activate_articles_only <> 1 then object_name(objid)
                             else dest_table
                        end,
           dest_owner = case when (dest_owner is null or rtrim(dest_owner) = N'') and @activate_articles_only <> 1 then schema_name(ObjectProperty(objid, 'SchemaId'))
                             else dest_owner
                        end,
           status = status | @active
     where pubid = @pubid

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

    update dbo.sysschemaarticles
       set dest_object = case when (dest_object is null or rtrim(dest_object) = N'') and @activate_articles_only <> 1 then object_name(objid)
                              else dest_object
                         end,
           dest_owner = case when (dest_owner is null or rtrim(dest_owner) = N'') and @activate_articles_only <> 1 then schema_name(ObjectProperty(objid, 'SchemaId'))
                        else dest_owner
                        end,
           status = status | @active
     where pubid = @pubid

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
		-- 3)
		-- Set replicated bits for article objects
		declare hCarticle cursor local fast_forward for
		  select objid,
				 type
			from dbo.sysarticles -- No need to worry about schema-only articles
		   where pubid = @pubid

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END

		select @cursorallocated = 1

		open hCarticle

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END

		select @cursoropened = 1

		fetch hCarticle into @objid, @articletype
		while @@fetch_status <> -1
		BEGIN
			select @qualifiedname = N'[' + replace(schema_name(ObjectProperty(@objid, 'SchemaId')), N']', N']]') + N'].' + N'[' + replace(object_name(@objid), N']', N']]') + N']'
			
			EXEC %%Object(MultiName = @qualifiedname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
			IF (@@ERROR != 0) OR (@retcode != 0)
			BEGIN
				SELECT	@retcode = 1
				GOTO Failure
			END
			
			if @articletype & 0x1 <> 0 -- Logbased article
			BEGIN
				exec %%Relation(ID = @objid).SetReplicated(Value = 1)

				IF (@@ERROR != 0) OR (@retcode != 0)
				BEGIN
					SELECT	@retcode = 1
					GOTO Failure
				END

				if @activate_articles_only <> 1
				BEGIN
					-- 3.1) Set NFR for identity column
					select @identitycolname = NULL
					select @identitycolname = name
					  from sys.columns
					 where object_id = @objid
					   and columnproperty(object_id, name, 'IsIdentity') = 1
					   and isnull(columnproperty(object_id, name, 'IsIdNotForRepl'),0) = 0

					if @identitycolname is not null
					BEGIN
						-- The following locking code is silently failing, since it is                 -- not absolutely needed, we may as well forego it for now.
						-- (Lock is probably implicitly acquired by the SetReplicated
						-- call above)
--                      select @qualifiedname = N'[' + replace(schema_name(ObjectProperty(@objid, 'SchemaId')), N']', N']]') + N'].' + N'[' + replace(object_name(@objid), N']', N']]') + N']'
--                      if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
--                      exec %%Object(MultiName = @qualifiedname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
--                      if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

						exec %%ColumnEx(ObjectID = @objid, Name = @identitycolname).SetIdentityNotForRepl(Value = 1)

						IF (@@ERROR != 0) OR (@retcode != 0)
						BEGIN
							SELECT	@retcode = 1
							GOTO Failure
						END
					END
				END
			END
			else if @articletype & 0x8 <> 0 -- Stored procedure execution article
			BEGIN
				exec %%Module(ID = @objid).SetProcReplicated(Value = 1)

				IF (@@ERROR != 0) OR (@retcode != 0)
				BEGIN
					SELECT	@retcode = 1
					GOTO Failure
				END

				if @articletype & 0x10 <> 0
				BEGIN
					exec %%Module(ID = @objid).SetProcReplSerialOnly(Value = 1)

					IF (@@ERROR != 0) OR (@retcode != 0)
					BEGIN
						SELECT	@retcode = 1
						GOTO Failure
					END
				END
			END

			fetch hCarticle into @objid, @articletype
		END

		close hCarticle

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END

		select @cursoropened = 0

		deallocate hCarticle

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END

		select @cursorallocated = 0
	END

    -- 4)
    -- Flush the article cache
    exec @retcode = sys.sp_replflush

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

    if @activate_articles_only <> 1
    begin
        -- 5)
        -- Set the allow_initialize_from_backup bit of the publication to 1
        update dbo.syspublications
           set allow_initialize_from_backup = 1
         where pubid = @pubid

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END


        -- 5.1)
        -- Set the allow_initialize_from_backup bit of the publication on the distributor to 1
        SELECT @publisher = publishingservername()
        EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher  = @publisher,
															@rpcsrvname = @distributor OUTPUT,
                                                            @distribdb = @distribdb OUTPUT,
                                                            @version = @distver OUTPUT

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

        if (@distver > 0x090007FF)
        BEGIN
            SELECT @dbname = DB_NAME()
            SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.'
                        + QUOTENAME(@distribdb) + '.dbo.sp_MSchange_publication'

            EXECUTE @retcode = @distproc
                @publisher = @publisher,
                @publisher_db = @dbname,
                @publication = @publication,
                @property = N'allow_initialize_from_backup',
                @value = '1'
        END

        -- Don't fail on exit as it's possible that this table doesn't exist if we have
        --  a downlevel distributor

        -- 6)
        -- Increment the publication's min autonosync lsn
        exec @retcode = sys.sp_MSincrementpublicationminautonosynclsn
            @publication = @publication

		IF (@@ERROR != 0) OR (@retcode != 0)
		BEGIN
			SELECT	@retcode = 1
			GOTO Failure
		END
    END

    COMMIT TRANSACTION

    IF (@@ERROR != 0) OR (@retcode != 0)
    BEGIN
    	SELECT	@retcode = 1
    	GOTO Failure
    END

    select @transactionopened = 0

Failure:
    if @cursoropened = 1
    begin
        close hCarticle
    end

    if @cursorallocated = 1
    begin
        deallocate hCarticle
    end

    if @transactionopened = 1
    begin
        rollback transaction sp_MSenableautonosync
        commit transaction
    end

    return @retcode
END

 
Last revision 2008RTM
See also

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