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