Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdisableautonosync

  No additional text.


Syntax

-- Name: sp_MSdisableautonosync

-- Description: This is a helper procedure for performing the necessary
--              cleanup when the allow_initialize_from_backup property is changed
--              from 1 to 0 in sp_changepublication.

--              Steps that will be performed by this procedure:
--              1) Deactivate articles in publication ONLY IF there are not
--                 any active subscriptions on them including virtual
--                 subscriptions
--              2) Reset the publication's allow_initialize_from_backup bit and
--                 min. autonosync lsn to 0 and null respectively.
--              2.1) Make the same change as 2, on the distributor
--              3) Reset the replicated bit of article objects in this
--                 publication ONLY IF
--                   i) there are not any active subscriptions on the
--                      article object, and
--                   ii) the article object is not published in another
--                       publication that is enabled for automated nosync
--                       subscription setup
--						iii) object not published in cdc
--              4) Flush the article cache

-- Parameter: @publication sysname (mandatory)

-- Note: @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.

-- Security: This is an internal system procedure.

create procedure sys.sp_MSdisableautonosync (
    @publication sysname
    )
as
begin
    set nocount on
    declare @retcode int,
            @transactionopened bit,
            @cursorallocated bit,
            @cursoropened bit,
            @pubid int,
            @subscribed tinyint,
            @inactive tinyint,
            @articletype tinyint,
            @objid int,
            @distributor sysname,
            @distribdb sysname,
            @dbname sysname,
            @distproc nvarchar(768),
            @publisher sysname,
            @distver int
            ,@qualifiedname nvarchar(517)

    select @retcode = 0,
           @transactionopened = 0,
           @subscribed = 1,
           @inactive = 0

    -- Get publication information
    select @pubid = pubid
      from dbo.syspublications
     where name = @publication
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

    begin transaction
    save transaction sp_MSdisableautonosync
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
    select @transactionopened = 1

    -- 1)
    update dbo.sysarticles
       set status = status & ~1
     where pubid = @pubid
       and artid not in (select artid -- Set of artids with active subscriptions
                           from dbo.syssubscriptions ss
                          where ss.status not in (@subscribed, @inactive)
                         group by artid)
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

    update dbo.sysschemaarticles
       set status = status & ~1
     where pubid = @pubid
       and artid not in (select artid -- Set of artids with active subscriptions
                           from dbo.syssubscriptions ss
                          where ss.status not in (@subscribed, @inactive)
                         group by artid)
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

    -- 2)
    update dbo.syspublications
       set allow_initialize_from_backup = 0,
           min_autonosync_lsn = null
     where pubid = @pubid
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end

    -- 2.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 begin select @retcode = 1 goto Failure end

    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 = '0'

        EXECUTE @retcode = @distproc
            @publisher = @publisher,
            @publisher_db = @dbname,
            @publication = @publication,
            @property = N'min_autonosync_lsn',
            @value = NULL
    END

    -- 3)
	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 hCarticleobject cursor local fast_forward for
        select objid, type
          from dbo.sysarticles sa1
         where sa1.pubid = @pubid
           and sa1.status & 1 = 0 -- Only consider objects of deactivated articles
                                -- 3)i) Set of objids with active subscriptions
           and sa1.objid not in (select sa2.objid
                                   from dbo.sysarticles sa2
                                 inner join dbo.syssubscriptions  ss
                                      on sa2.artid = ss.artid
                                  where ss.status not in (@subscribed, @inactive)
                                   group by objid)
                                -- 3)ii) Set of objids published in
                                -- publications enabled for autonosync
           and sa1.objid not in (select sa3.objid
                                   from dbo.sysarticles sa3
                                 inner join dbo.syspublications sp
                                     on sa3.pubid = sp.pubid
                                  where sp.allow_initialize_from_backup = 1
                                    -- Exclude current publication
                                    and sp.pubid <> @pubid
                                    group by objid)
                                -- 3)iii) Set of objids enabled for cdc
           and sa1.objid not in (select object_id from @cdc_tracked_tables)
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
    select @cursorallocated = 1

    open hCarticleobject
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
    select @cursoropened = 1

    fetch hCarticleobject into @objid, @articletype
    while @@fetch_status <> -1
    begin
		EXEC sys.sp_MSget_qualified_name @objid, @qualifiedname OUTPUT
		IF @qualifiedname IS NULL
			goto Failure

		EXEC %%Object(MultiName = @qualifiedname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
        if @articletype & 0x1 <> 0 -- Logbased article
        begin
            exec %%Relation(ID = @objid).SetReplicated(Value = 0)
            if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
        end
        else if @articletype & 0x8 <> 0 -- Stored procedure execution article
        begin
            exec %%Module(ID = @objid).SetProcReplicated(Value = 0)
            if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
            if @articletype & 0x10 <> 0
            begin
                exec %%Module(ID = @objid).SetProcReplSerialOnly(Value = 0)
                if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
            end
        end

        fetch hCarticleobject into @objid, @articletype
    end

    close hCarticleobject
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
    select @cursoropened = 0

    deallocate hCarticleobject
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure end
    select @cursorallocated = 0

    -- 4)
    exec @retcode = sys.sp_replflush
    if @@error <> 0 or @retcode <> 0 begin select @retcode = 1 goto Failure 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 hCarticleobject
    end

    if @cursorallocated = 1
    begin
        deallocate hCarticleobject
    end

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

    return @retcode
end

 
Last revision 2008RTM
See also

  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