Skip to content

Instantly share code, notes, and snippets.

@MasayukiOzawa

MasayukiOzawa/01.sys.sp_MSactivate_auto_sub.sql

Last active August 15, 2024 22:39
Show Gist options
  • Save MasayukiOzawa/12d99ae760997db37fd74dfbd4364301 to your computer and use it in GitHub Desktop.
Save MasayukiOzawa/12d99ae760997db37fd74dfbd4364301 to your computer and use it in GitHub Desktop.
Download ZIP
Replication にアーティクルを追加した際に実行されるストアド
Raw
01.sys.sp_MSactivate_auto_sub.sql
create procedure sys.sp_MSactivate_auto_sub
(
@publication sysname,
@article sysname,
@status sysname = 'active',
@skipobjectactivation int = 0,
@publisher sysname = null
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @retcode int
DECLARE @publisher_type sysname
DECLARE @pubid int
DECLARE @from_auto_sync int
EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@publisher_type = @publisher_type OUTPUT,
@skipSecurityCheck = 1
IF @retcode <> 0
RETURN (@retcode)
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
/*
** Security Check.
*/
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
/*
** Check to see if the database has been activated for publication.
*/
IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
-- parameter check: @status:
IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) not in (N'active', N'initiated')
BEGIN
RAISERROR(21156, 16, -1)
RETURN 1
END
/*
** Parameter Check: @publication.
** Make sure that the publication exists and the publication is not push type
*/
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_MSactivate_auto_sub')
RETURN (1)
END
--
-- 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
BEGIN TRAN
IF @status = N'active'
BEGIN
UPDATE syspublications
SET immediate_sync_ready = 1
WHERE immediate_sync = 1
AND immediate_sync_ready <> 1
AND pubid = @pubid
IF @@ERROR <> 0
BEGIN
GOTO UNDO
RETURN (1)
END
END
SET @from_auto_sync = 1
EXECUTE @retcode = sys.sp_changesubstatus
@publication = @publication,
@article = @article,
@status = @status,
@from_auto_sync = @from_auto_sync,
@skipobjectactivation = @skipobjectactivation,
@publisher = @publisher
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
RETURN (1)
END
COMMIT TRAN
RETURN(0)
UNDO:
IF @@TRANCOUNT = 1
ROLLBACK TRAN
ELSE
COMMIT TRAN
END
Raw
02.sys.sp_changesubstatus
create procedure sys.sp_changesubstatus
(
@publication sysname = '%',
@article sysname = '%',
@subscriber sysname = '%',
@status sysname,
@previous_status sysname = NULL,
@destination_db sysname = '%',
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@optional_command_line nvarchar(4000) = NULL,
@distribution_jobid binary(16) = NULL OUTPUT,
@from_auto_sync bit = 0,
@ignore_distributor bit = 0,
-- Agent offload
@offloadagent bit = 0,
@offloadserver sysname = NULL,
@dts_package_name sysname = NULL,
@dts_package_password nvarchar(524) = NULL,
@dts_package_location int = 0,
@skipobjectactivation int = 0,
@distribution_job_name sysname = NULL,
@publisher sysname = NULL
,@ignore_distributor_failure bit = 0
) AS
BEGIN
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @publisher_type sysname
SET @retcode = 0
EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@rpcheader = @cmd OUTPUT,
@publisher_type = @publisher_type OUTPUT,
@skipSecurityCheck = 1
IF @retcode <> 0
RETURN (@retcode)
-- Add sp
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_changesubstatus'
EXEC @retcode = @cmd
@publication,
@article,
@subscriber,
@status,
@previous_status,
@destination_db,
@frequency_type,
@frequency_interval,
@frequency_relative_interval,
@frequency_recurrence_factor,
@frequency_subday,
@frequency_subday_interval,
@active_start_time_of_day,
@active_end_time_of_day,
@active_start_date,
@active_end_date,
@optional_command_line,
@distribution_jobid OUTPUT,
@from_auto_sync,
@ignore_distributor,
-- Agent offload
@offloadagent,
@offloadserver,
@dts_package_name,
@dts_package_password,
@dts_package_location,
@skipobjectactivation,
@distribution_job_name,
@publisher,
@publisher_type
,@ignore_distributor_failure
RETURN (@retcode)
END
Raw
03.sys.sp_MSrepl_changesubstatus
create procedure sys.sp_MSrepl_changesubstatus
(
@publication sysname = '%',
@article sysname = '%',
@subscriber sysname = '%',
@status sysname,
@previous_status sysname = NULL,
@destination_db sysname = '%',
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@optional_command_line nvarchar(4000) = NULL,
@distribution_jobid binary(16) = NULL OUTPUT,
@from_auto_sync bit = 0,
@ignore_distributor bit = 0,
-- Agent offload
@offloadagent bit = 0,
@offloadserver sysname = NULL,
@dts_package_name sysname = NULL,
@dts_package_password nvarchar(524) = NULL,
@dts_package_location int = 0,
@skipobjectactivation int = 0,
@distribution_job_name sysname = NULL,
@publisher sysname = NULL,
@publisher_type sysname
,@ignore_distributor_failure bit = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @inactive tinyint
,@subscribed tinyint
,@active tinyint
,@initiated tinyint
,@public tinyint
,@replicate_bit smallint
,@msg nvarchar(255)
,@prevstatid tinyint
,@artid int
,@tabid int
,@objid int
,@qualified_name nvarchar(517)
,@srvid smallint
,@statusid tinyint
,@distributor sysname
,@distribdb sysname
,@distproc nvarchar (1000)
,@pub_db sysname
,@dest_db sysname
,@sub_name sysname
,@sub_status tinyint
,@sub_ts varbinary (16)
,@non_sql_flag bit
,@qcmd nvarchar (4000)
,@cmd1 nvarchar (255)
,@cmd2 nvarchar (255)
,@cmd3 nvarchar (255)
,@retcode int
,@repl_freq tinyint
,@art_type tinyint
,@proccmd nvarchar(255)
,@procnum smallint
,@finished_real bit
,@finished_virtual bit
,@virtual_id smallint
,@immediate_sync bit
,@enabled_for_internet bit
,@allow_anonymous bit
,@subscription_type int
,@xact_seqno binary(10)
,@sync_type tinyint
,@nosync_type tinyint
,@automatic tinyint
,@allow_initialize_from_backup bit
,@sync_method int
,@art_change bit
,@login_name sysname
,@pubid int
,@syncinit_lsn binary(10)
,@f_syncstat_posted bit
-- synctran
,@update_mode tinyint
,@art_name sysname
,@synctran tinyint
,@no_distproc bit
,@loopback_detection bit
,@bcp_char tinyint, @concurrent_char tinyint
-- heterogeneous publishers
,@publisher_local sysname
,@publisher_id int
,@publisher_db sysname
,@internal sysname
,@publisher_engine_edition int
,@current_publication sysname
,@OPT_ENABLED_FOR_HET_SUB tinyint
,@OPT_ALLOW_DROP tinyint
,@alreadyactiveforCSS bit
,@allow_drop bit
DECLARE @pubid_toreturn int, @artid_toreturn int, @syncinit_lsn_toreturn binary(10)
DECLARE @publication_ids TABLE
(
pubid int
)
-- Validate @publisher
IF @publisher IS NULL
BEGIN
select @publisher_local = publishingservername()
END
ELSE
BEGIN
/* For heterogeneous publisher, the current database must be the */
/* distribution db for the publisher */
select @publisher_local = @publisher
SELECT @publisher_id = s.srvid,
@publisher_db = m.distribution_db
FROM MSreplservers s, msdb..MSdistpublishers m
WHERE UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)
AND UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
AND m.distribution_db = DB_NAME() collate database_default
IF @@ERROR <> 0
RETURN (1)
IF @publisher_id is NULL or @publisher_db IS NULL
BEGIN
RETURN (1)
END
-- Security Check.
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
BEGIN
RETURN (1)
END
END
--
-- Initializations.
--
select @bcp_char = 1,
@concurrent_char = 4,
@synctran = 1,
@automatic = 1,
@inactive = 0, -- Const: subscription status 'inactive'
@subscribed = 1, -- Const: subscription status 'subscribed'
@active = 2, -- Const: subscription status 'active'
@initiated = 3, -- Const: subscription status 'initiated'
@public = 0, -- Const: publication status 'public'
@pub_db = DB_NAME(),
@virtual_id = -1,
@art_change = 0,
@f_syncstat_posted = 0,
@replicate_bit = 2,
@publisher_engine_edition = sys.fn_MSrepl_editionid()
,@OPT_ENABLED_FOR_HET_SUB = 0x4
,@OPT_ALLOW_DROP = 0x80
,@allow_drop = 0
/*
** Security Check.
** We use login_name stored in syssubscriptions to manage security
*/
/*
** 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 @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_changesubstatus')
RETURN (1)
END
IF @publication <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @publication
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
-- 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)
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 @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_changesubstatus')
RETURN (1)
END
/*
IF @article <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @article
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
*/
IF NOT EXISTS
(
SELECT *
FROM sysextendedarticlesview a,
syspublications b,
@publication_ids pi
WHERE a.pubid = b.pubid
AND ((@article = N'%') or (a.name = @article))
AND ((@publication = N'%') or (b.name = @publication))
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
/*
** Parameter Check: @subscriber
** Check to make sure that the subscriber exists, that it is not NULL,
** and that it conforms to the rules for identifiers.
** Null subscriber represents virtual subscriptions
*/
IF @subscriber IS NOT NULL AND @subscriber <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @subscriber
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
IF NOT EXISTS (SELECT *
FROM dbo.syssubscriptions
WHERE srvname = UPPER(@subscriber)
)
and @ignore_distributor_failure = 0
BEGIN
RAISERROR (14063, 11, -1)
RETURN (1)
END
END
/*
** Parameter Check: @status.
** Set the @statusid according to the @status value. Values can be
** any of the following:
**
** status statusid
** ========= ========
** inactive 0
** subscribed 1
** active 2
** initiated 3
*/
IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'subscribed', 'inactive', 'initiated')
BEGIN
RAISERROR (14065, 16, -1)
RETURN (1)
END
IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
SELECT @statusid = @initiated
ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
SELECT @statusid = @active
ELSE IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
SELECT @statusid = @subscribed
ELSE
SELECT @statusid = @inactive
/*
** Parameter Check: @previous_status.
** Set the @prevstatid according to the @previous_status value.
** Values can be any of the following:
**
** previous_status prevstatid
** =============== ==========
** inactive 0
** subscribed 1
** active 2
** initiated 3
*/
IF @previous_status IS NOT NULL
BEGIN
IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('initiated','active', 'subscribed', 'inactive')
BEGIN
RAISERROR (14066, 16, -1)
RETURN (1)
END
IF LOWER(@status collate SQL_Latin1_General_CP1_CS_AS) = LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS)
BEGIN
RAISERROR (14067, 16, -1)
RETURN (1)
END
IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('initiated')
SELECT @prevstatid = @initiated
ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('active')
SELECT @prevstatid = @active
ELSE IF LOWER(@previous_status collate SQL_Latin1_General_CP1_CS_AS) IN ('subscribed')
SELECT @prevstatid = @subscribed
ELSE
SELECT @prevstatid = @inactive
END
/*
** Parameter Check: @destination_db.
** Set @destination_db to current database if not specified. Make
** sure that the @destination_db conforms to the rules for identifiers.
*/
IF @destination_db <> '%'
BEGIN
EXECUTE @retcode = sys.sp_validname @destination_db
IF @retcode <> 0
RETURN (1)
END
/*
** Parameter Check: @offloadagent.
*/
IF @offloadagent IS NOT NULL
AND @offloadagent != 0
BEGIN
-- "Parameter '@offloadagent' is no longer supported."
RAISERROR(21698, 16, -1, '@offloadagent')
RETURN 1
END
IF ISNULL(@offloadserver, N'') != N''
BEGIN
-- "Parameter '@offloadserver' is no longer supported."
RAISERROR(21698, 16, -1, '@offloadserver')
RETURN 1
END
/*
** Get distribution server information for remote RPC
** subscription calls.
** if @ignore_distributor = 1, we are in bruteforce cleanup mode, don't do RPC.
*/
if @ignore_distributor = 1
select @no_distproc = 1
else
select @no_distproc = 0
IF @no_distproc = 0 --and @from_auto_sync = 0
BEGIN
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF (@@error <> 0 OR @retcode <> 0) and @ignore_distributor_failure = 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
IF (@publisher IS NOT NULL) AND ((@distribdb IS NULL OR @distributor IS NULL) and @ignore_distributor_failure = 0)
BEGIN
RAISERROR(21600, 16, -1, @publisher)
RETURN (1)
END
END
create table #sysextendedarticlesview
(
artid int NULL,
creation_script nvarchar(255) collate database_default null,
del_cmd nvarchar(255) collate database_default null,
description nvarchar(255) collate database_default null,
dest_table sysname collate database_default null,
filter int NULL,
filter_clause nvarchar(max) NULL,
ins_cmd nvarchar(255) collate database_default null,
name sysname collate database_default null,
objid int NULL,
pubid int NULL,
pre_creation_cmd tinyint NULL,
status tinyint NULL,
sync_objid int NULL,
type tinyint NULL,
upd_cmd nvarchar(255) collate database_default null,
schema_option binary(8) NULL,
dest_owner sysname collate database_default null,
ins_scripting_proc int NULL,
del_scripting_proc int NULL,
upd_scripting_proc int NULL,
custom_script nvarchar(2048) NULL,
fire_triggers_on_snapshot bit NOT NULL
)
INSERT INTO #sysextendedarticlesview
SELECT sxav.*
FROM sysextendedarticlesview sxav,
syspublications sp,
@publication_ids pi
WHERE sp.pubid = sxav.pubid
AND ((@publication = N'%') or (sp.name = @publication collate database_default))
AND ((@article = N'%') or (sxav.name = @article collate database_default))
AND sp.pubid = pi.pubid
begin tran
save TRANSACTION changesubstatus
SELECT @finished_virtual = 0
SELECT @finished_real = 0
/*
** If @subscriber is null, don't process real subscriptions
** If @subscriber is not null and '%', don't process virtual subscriptions
*/
IF @subscriber IS NULL
BEGIN
SELECT @finished_real = 1
END
ELSE IF @subscriber <> '%'
BEGIN
SELECT @finished_virtual = 1
END
WHILE (@finished_real = 0 OR @finished_virtual = 0)
BEGIN
/*
** Declare cursor containing subscriptions to be updated.
*/
IF @finished_real = 0
BEGIN
IF @previous_status IS NOT NULL
BEGIN
DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
SELECT pub.name,
sub.artid,
art.objid,
sub.srvid,
sub.srvname,
sub.dest_db,
sub.status,
case
when ((pub.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
or pub.allow_dts = 1 then 1
else 0
end,
pub.repl_freq,
art.type,
pub.immediate_sync,
pub.enabled_for_internet,
pub.allow_anonymous,
sub.subscription_type,
sub.sync_type,
sub.update_mode,
art.name,
sub.login_name,
sub.loopback_detection,
pub.pubid,
pub.allow_initialize_from_backup,
pub.sync_method,
sub.nosync_type,
case when (pub.options & @OPT_ALLOW_DROP) = @OPT_ALLOW_DROP then 1 else 0 end
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 sub.status = @prevstatid
AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db collate database_default))
AND pub.pubid = pi.pubid
ORDER BY art.objid
END
ELSE
BEGIN
DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
SELECT pub.name,
sub.artid,
art.objid,
sub.srvid,
sub.srvname,
sub.dest_db,
sub.status,
case
when ((pub.options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
or pub.allow_dts = 1 then 1
else 0
end,
pub.repl_freq,
art.type,
pub.immediate_sync,
pub.enabled_for_internet,
pub.allow_anonymous,
sub.subscription_type,
sub.sync_type,
sub.update_mode,
art.name,
sub.login_name,
sub.loopback_detection,
pub.pubid,
pub.allow_initialize_from_backup,
pub.sync_method,
sub.nosync_type,
case when (pub.options & @OPT_ALLOW_DROP) = @OPT_ALLOW_DROP then 1 else 0 end
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))
AND pub.pubid = pi.pubid
order by art.objid
END
SELECT @finished_real = 1
END
ELSE IF @finished_virtual = 0
BEGIN
DECLARE @sub_bit smallint
,@null_name sysname
SELECT @sub_bit = 4
,@null_name = NULL
/*
** Treat anonymous virtual subscription as DSN subscriber.
** This will cause sp_MSarticlecol being called in sp_changesubstatus
*/
DECLARE #hCsubstatus CURSOR LOCAL SCROLL_LOCKS FOR
SELECT pub.name,
sub.artid,
art.objid,
sub.srvid,
@null_name, /* subscriber name. NULL for virtual */
sub.dest_db,
sub.status,
case
when
(pub.allow_anonymous = 1 and
(pub.sync_method = @bcp_char or pub.sync_method = @concurrent_char))
or pub.allow_dts = 1 then 1
else 0
end, /*indicate dsn or not */
pub.repl_freq,
art.type,
pub.immediate_sync,
pub.enabled_for_internet,
pub.allow_anonymous,
sub.subscription_type,
sub.sync_type,
sub.update_mode,
art.name,
login_name,
sub.loopback_detection,
pub.pubid,
pub.allow_initialize_from_backup,
pub.sync_method,
sub.nosync_type,
case when (pub.options & @OPT_ALLOW_DROP) = @OPT_ALLOW_DROP then 1 else 0 end
FROM syssubscriptions sub,
#sysextendedarticlesview art,
syspublications pub,
@publication_ids pi
WHERE ((@publication = N'%') or (pub.name = @publication))
AND ((@article = N'%') or (art.name = @article))
AND sub.srvid = -1
AND sub.artid = art.artid
AND art.pubid = pub.pubid
AND pub.pubid = pi.pubid
SELECT @finished_virtual = 1
END
OPEN #hCsubstatus
FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name, @dest_db,
@sub_status, @non_sql_flag, @repl_freq, @art_type,
@immediate_sync, @enabled_for_internet,
@allow_anonymous, @subscription_type, @sync_type, @update_mode,
@art_name, @login_name, @loopback_detection,@pubid,
@allow_initialize_from_backup, @sync_method, @nosync_type, @allow_drop
WHILE (@@fetch_status <> -1)
BEGIN
IF suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1
AND is_member ('db_owner') <> 1
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14126, 11, -1)
RETURN (1)
END
/*
** condition 1:
** If current status is same as new status, and status is not 'initiated' do nothing.
** If both old and new status = 'initiated', this indicates that the
** snapshot agent previously bombed out between the initiation and activation stages and
** is now again trying to sync the publication.
**
** condition 2:
** @auto_sync_only is used by snapshot for immediate_sync
** publications.
**
** condition 3:
** Because sp_MSactivate_auto_sub (and thus the snapshot agent)
** calls this procedure for all subscriptions, we need to ignore
** the real subscriptions that are already active so that they won't be
** transitioned to the initiated state. If we don't do this, those
** subscriptions will be resynced using the new snapshot.
**
** however, we DO want a new snapshot to be generated for virtual
** subscriptions to active publications.
**
** condition 4:
** Because the heterogeneous log reader (with @from_auto_sync = 1)
** calls this procedure for all subscriptions, we need to ignore
** the real subscriptions that are already active so that they won't be
** transitioned to the initiated state. If we don't do this, those
** subscriptions will be resynced using the new snapshot.
**
** however, we DO want a new snapshot to be generated for virtual
** subscriptions to active publications.
**
** condition 5:
** Only initiated subscription can be activated by concurrent
** snapshot, this is to avoid new subscriptions added during
** concurrent snapshot generation from being partially
** activated (active at publisher but inactive at distributor)
**
*/
IF (@sub_status = @statusid AND @sub_status <> @initiated AND
/** An exception is after adding an article into a publication with concurrent-snapshot, where
** the existing subscriptions are already 'active'. Upon finishing another concurrent snapshot, we
** need to ensure the article/table of any existing 'active' subscription is disabled for offrow logging.*/
NOT (@sub_status = @active AND @statusid = @active AND @srvid >= 0 AND @from_auto_sync = 1 AND @sync_method in (3,4))) OR
(@from_auto_sync = 1 AND @sync_type <> @automatic) OR
(@sub_status = @active AND @statusid = @initiated AND @srvid <> -1 AND @from_auto_sync = 1 AND
/** An exception is after adding an article into a publication with concurrent-snapshot, where the
** existing subscriptions are already 'active'. Upon starting another concurrent snapshot, we need to
** ensure the article/table of any existing 'active' subscription is enabled for offrow logging.*/
NOT (@sub_status = @active AND @statusid = @initiated AND @srvid >= 0 AND @from_auto_sync = 1 AND @sync_method in (3,4))) OR
(@sub_status in (@subscribed, @inactive) AND @statusid = @active AND @from_auto_sync = 1 AND @sync_method in (3,4))
BEGIN
FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name,
@dest_db, @sub_status, @non_sql_flag, @repl_freq, @art_type,
@immediate_sync, @enabled_for_internet,
@allow_anonymous, @subscription_type, @sync_type, @update_mode,
@art_name, @login_name, @loopback_detection, @pubid,
@allow_initialize_from_backup, @sync_method, @nosync_type, @allow_drop
CONTINUE
END
if @sub_status = @active AND (@statusid = @active OR @statusid = @initiated) AND @srvid >= 0 AND @from_auto_sync = 1 AND @sync_method in (3,4)
begin
set @alreadyactiveforCSS = 1
end
else
begin
set @alreadyactiveforCSS = 0
end
if((@publisher IS NULL) or (@publisher_type = N'MSSQLSERVER'))
begin
EXEC sys.sp_MSget_qualified_name @tabid, @qualified_name OUTPUT
-- only fail on unresolved @qualified_name if table drop operation is not allowed for this publication
IF @allow_drop = 0 and @qualified_name IS NULL
goto UNDO
end
-- If changing a virtual subscription to 'subscribed' status
-- change the immediate_sync_ready bit
if @statusid = @subscribed and @sub_name is NULL
begin
UPDATE syspublications SET immediate_sync_ready = 0 WHERE
pubid = @pubid and
immediate_sync_ready <> 0
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
end
/*
** Update syssubscription status
*/
if @alreadyactiveforCSS = 0
begin
UPDATE syssubscriptions
SET status = @statusid
FROM syssubscriptions sub,
sysextendedarticlesview art,
syspublications pub
WHERE pub.name LIKE @current_publication
AND art.artid = @artid
AND ((sub.srvname = @sub_name) OR (sub.srvid < 0 and sub.srvid = @srvid))
AND sub.artid = @artid
AND art.pubid = pub.pubid
AND sub.dest_db = @dest_db
AND pub.pubid = @pubid
IF @@ERROR <> 0
BEGIN
IF @@trancount > 0
BEGIN
ROLLBACK TRANSACTION changesubstatus
COMMIT TRAN
END
RAISERROR (14053, 16, -1)
RETURN (1)
END
end
--
-- Subscription reinitialization processing for Immediate
-- and Queued publications
--
if (@update_mode in (1,2,3,4,5,6,7)) and @alreadyactiveforCSS = 0
begin
select @retcode = 0
IF ((@statusid != @active) AND (@sub_status = @active))
begin
--
-- If we are going from active state to subscribed
-- set the reinit column so that no more updates from
-- subscriber are applied until (re)activation
--
update dbo.syssubscriptions
set queued_reinit = 1
where
artid = @artid
and ((srvname = @sub_name) OR (srvid < 0 and srvid = @srvid))
and dest_db = @dest_db
end
ELSE IF ((@statusid = @active) AND (@sub_status != @active ))
begin
--
-- If we are going from subscribed state to active state
--
if (@update_mode = 1)
begin
--
-- Sync tran case : reset the reinit column
--
update dbo.syssubscriptions
set queued_reinit = 0
where
artid = @artid
and ((srvname = @sub_name) OR (srvid < 0 and srvid = @srvid))
and dest_db = @dest_db
end
--
-- For queued case : we do not need to send compensating
-- command anymore, sp_addqueued_artinfo will do the
-- queue reinitialization for all types of queued
-- subscriptions
--
end
--
-- Check for error
--
if (@@error != 0 or @retcode != 0)
begin
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14053, 16, -1)
RETURN (1)
end
end
/*
** Get timestamp of subscription.
*/
if @publisher IS NULL
-- SQL Server publisher
EXEC @retcode = sys.sp_replincrementlsn_internal @xact_seqno OUTPUT
else
-- Heterogeneous publisher
EXEC @retcode = dbo.sp_replincrementlsn @xact_seqno OUTPUT, @publisher
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
select @sub_ts = @xact_seqno
IF @sub_ts IS NULL
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14053, 16, -1)
RETURN (1)
END
-------------------------------------------------------------------
-- If initiating the subscription, toss a SYNCINIT token into the
-- log for the article and return LSN as a results set
--
-- Note: This should come after the subscription LSN is obtained.
-- in order to assure proper application of SYNSTAT tokens in the
-- distribution database
-------------------------------------------------------------------
-- Make sure @sub_status <> @initiated
-- Skip HREPL
IF @statusid = @initiated
and @publisher_type = N'MSSQLSERVER'
BEGIN
-- Exclude schema-only articles here (proc exec article as well?)
IF @art_type not in (0x20, 0x40, 0x60, 0x80, 0xA0, 24, 8)
BEGIN
-- set filtered status. Must log old text information during initiated state
-- in order to support update splitting
exec sys.sp_MSsetfilteredstatus @tabid, @alreadyactiveforCSS
-- set nonsqlsub status. must prevent UPDATETEXT operations during
-- initiated state
exec sys.sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'add'
END
exec sp_replpostsyncstatus @pubid, @artid, 1, @syncinit_lsn output
if @f_syncstat_posted = 0 and @from_auto_sync = 1
BEGIN
-- change made as per 13564167
-- cache the LSN information instead of returning for 1st article
select @pubid_toreturn = @pubid, @artid_toreturn = @artid, @syncinit_lsn_toreturn = @syncinit_lsn
END
END
-------------------------------------------------------------------
-- If changing the state FROM initiated, post a SYNCDONE token to the
-- log for the article.
-------------------------------------------------------------------
-- Exclude schema-only articles here (proc exec article as well?)
-- Exclude HREPL
IF (@sub_status = @initiated or @alreadyactiveforCSS = 1)
and @statusid <> @initiated
and @publisher_type = N'MSSQLSERVER'
BEGIN
IF @art_type not in (0x20, 0x40, 0x60, 0xA0, 0x80, 24, 8)
BEGIN
-- reset filtered status to normal value
exec sys.sp_MSsetfilteredstatus @tabid
-- clear nonsqlsub status for this article.
exec sys.sp_MSarticlecol @artid, NULL,N'nonsqlsub', N'drop'
END
--if @f_syncstat_posted = 0
--begin
exec sp_replpostsyncstatus @pubid, @artid, 0, @syncinit_lsn output
-- select @f_syncstat_posted = 1
--end
END
if @alreadyactiveforCSS = 1
begin
FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name,
@dest_db, @sub_status, @non_sql_flag, @repl_freq, @art_type,
@immediate_sync, @enabled_for_internet,
@allow_anonymous, @subscription_type, @sync_type, @update_mode,
@art_name, @login_name, @loopback_detection, @pubid,
@allow_initialize_from_backup, @sync_method, @nosync_type, @allow_drop
CONTINUE
end
/*
** If activating subscription, update sysextendedarticlesview, sys.objects and
** MSrepl_subscriptions.
*/
IF @statusid in ( @active, @initiated )
BEGIN
/*
** Update status of article to show it has been activated.
*/
IF @repl_freq = 0 and EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
AND status & 1 <> 1)
BEGIN
-- At most one row will be updated in the following two updates as the artid is unique
-- among both sysarticles and sysschemaarticles
UPDATE sysarticles SET status = status | 1 WHERE artid = @artid
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14069, 16, -1)
RETURN (1)
END
UPDATE sysschemaarticles SET status = status | 1 WHERE artid = @artid
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14069, 16, -1)
RETURN (1)
END
SELECT @art_change = 1
END
/*
** Turn the replication flag on for this object in the
** sys.objects table (make it logbased).
*/
-- For Yukon non-concurrent snapshot processing, the
-- activation would have been accomplished outside and
-- before the bcp&subscription activation transaction so
-- bypass the activation as appropriate
if @repl_freq = 0 and @skipobjectactivation = 0 and @art_type not in (0x20,0x40,0x60,0xA0,0x80,8,24)
BEGIN
SELECT @objid = objid FROM sysextendedarticlesview WHERE artid = @artid
IF @objid IS NOT NULL AND @qualified_name IS NOT NULL
BEGIN
EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @objid
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
EXEC %%Relation(ID = @objid).SetReplicated(Value = 1)
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14068, 16, -1)
RETURN (1)
END
END
END
END
/*
** Update status of all columns if subscriber is non-SQL Server.
*/
IF @publisher_type = N'MSSQLSERVER'
BEGIN
IF @non_sql_flag <> 0 AND ( @art_type & 1 ) = 1
BEGIN
IF @statusid = @subscribed OR @statusid = @active
BEGIN
EXEC @retcode = sys.sp_MSarticlecol @artid, NULL, 'nonsqlsub', 'add'
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14068, 16, -1)
RETURN (1)
END
END
ELSE IF @statusid = @inactive
BEGIN
EXEC @retcode = sys.sp_MSarticlecol @artid, NULL, 'nonsqlsub', 'drop'
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14068, 16, -1)
RETURN (1)
END
END
END
END
/*
** If deactivating subscription, update sysextendedarticlesview, sys.objects and
** MSrepl_subscriptions.
** NOTE: Don't deactivate the article if it is in a publication that allows
** is enabled for autonosyncs.
*/
IF @statusid NOT IN( @active, @initiated ) AND @sub_status IN ( @active, @initiated )
BEGIN
/*
** Set the article status to 'inactive' if there are
** no other active subscriptions on it.
*/
IF NOT EXISTS (SELECT * FROM syssubscriptions WHERE
artid = @artid AND status = @active)
BEGIN
IF EXISTS (SELECT * FROM sysextendedarticlesview WHERE artid = @artid
AND status & 1 = 1 and @allow_initialize_from_backup = 0)
BEGIN
-- If the snapshot agent is running, we will abort here
-- as we don't want to deactivate articles under the
-- snapshot agent's feet so to speak.
exec @retcode = sp_MSissnapshotagentrunning @publication = @publication
IF @retcode <> 0
BEGIN
if @@trancount > 0
BEGIn
ROLLBACK TRANSACTION changesubstatus
COMMIT TRAN
END
RAISERROR(21861, 16, -1)
RETURN (1)
END
-- At most one row will be updated in the following two updates as the artid is unique
-- among both sysarticles and sysschemaarticles
UPDATE sysarticles SET status = status & ~1 WHERE
artid = @artid
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14069, 16, -1)
RETURN (1)
END
UPDATE sysschemaarticles SET status = status & ~1 WHERE
artid = @artid
IF @@ERROR <> 0
BEGIN
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RAISERROR (14069, 16, -1)
RETURN (1)
END
SELECT @art_change = 1
END
END
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
/*
** Set the object replication bits to 'inactive' if
** there are no other active subscriptions on the
** table and the object is not published in a
** publication that allows is enabled for autonosyncs.
*/
IF @publisher_type = N'MSSQLSERVER'
AND NOT EXISTS
(
SELECT *
FROM syssubscriptions
WHERE artid IN
(
SELECT sa.artid
FROM sysextendedarticlesview sa,
syspublications sp
WHERE sa.objid = @tabid
AND sa.pubid = sp.pubid
AND sp.repl_freq = 0
)
AND status = @active
)
AND NOT EXISTS
(
SELECT *
FROM sysextendedarticlesview sa INNER JOIN syspublications sp
ON sa.pubid = sp.pubid
WHERE sa.objid = @tabid
AND sp.allow_initialize_from_backup = 1
)
AND not exists
(
select * from @cdc_tracked_tables where object_id = @tabid
)
BEGIN
SELECT @objid = objid FROM sysextendedarticlesview WHERE artid = @artid and @art_type not in (0x20,0x40,0x60,0xA0,0x80,8,24)
IF @objid IS NOT NULL AND @qualified_name IS NOT NULL
BEGIN
EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @objid
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
EXEC %%Relation(ID = @objid).SetReplicated(Value = 0)
IF @@ERROR <> 0
BEGIN
RAISERROR (14068, 16, -1)
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
END
END
END
-- Note: Not only do we need to have the replupdateschema already executed
-- so we can handle rollbacks, we also need to
-- acquire the schema lock before RPC to the distributor to avoid livelock
-- with snapshot agents. Snapshot agents acquire lock on user table before
-- updating the distribution db.
if (@no_distproc = 0) and (@distribdb IS not NULL and @distributor IS not NULL)
begin
/*
** Add the active subscription to the distributor's
** subscriptions table if changing status from @inactive
*/
IF @sub_status = @inactive
-- From inactive to subscribed or active
BEGIN
DECLARE @null_char sysname
DECLARE @zero_bit bit
SELECT @null_char = NULL
SELECT @zero_bit = 0
-- Check strict security requirements (>= 90 Security)
IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
BEGIN
SELECT @internal = N'YUKON ADD SUB'
END
ELSE
BEGIN
SELECT @internal = N'PRE-YUKON'
END
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscription'
EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name,
@artid, @dest_db, @statusid, @sub_ts,
@current_publication,
@null_char, -- Pass null to @article, we already gave @artid
@subscription_type,
--@immediate_sync,
@sync_type,
@zero_bit,
@frequency_type,
@frequency_interval,
@frequency_relative_interval,
@frequency_recurrence_factor,
@frequency_subday,
@frequency_subday_interval,
@active_start_time_of_day,
@active_end_time_of_day,
@active_start_date,
@active_end_date,
@optional_command_line = @optional_command_line,
-- synctran
@update_mode = @update_mode,
@loopback_detection = @loopback_detection,
@distribution_jobid = @distribution_jobid OUTPUT,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@distribution_job_name = @distribution_job_name,
@internal = @internal,
@publisher_engine_edition = @publisher_engine_edition,
@nosync_type = @nosync_type
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14070, 16, -1)
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
END
ELSE
-- From subscribed or active to others
BEGIN
/*
** Drop the deactivated subscription from the distributor's
** subscriptions table.
*/
IF @statusid = @inactive
-- From subscribed to inactive or from active to inactive
BEGIN
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSdrop_subscription'
EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name, @artid, @dest_db, @current_publication
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14070, 16, -1)
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
END
ELSE
-- From subscribed to initiated to active or from active to subscribed.
BEGIN
-- Don't do it if activating the subscription for snapshot agent.
--IF NOT (@from_auto_sync = 1 AND @statusid in(@active, @initiated) )
IF NOT (@from_auto_sync = 1 AND @statusid in(@active) )
BEGIN
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSupdate_subscription'
EXEC @retcode = @distproc @publisher_local, @pub_db, @sub_name, @artid, @statusid, @sub_ts, @dest_db
IF (@@ERROR <> 0 OR @retcode <> 0) and @ignore_distributor_failure = 0
BEGIN
RAISERROR (14070, 16, -1)
if @@trancount > 0
begin
ROLLBACK TRANSACTION changesubstatus
commit tran
end
RETURN (1)
END
END
END
END
end
/*
** Set internal object replication bit to 'inactive' if
** there are no other active subscriptions on the
** procedure, but don't try to deactivate the procedure
** if it is published in a publication that allows
** is enabled for autonosyncs.
*/
IF @statusid = @inactive AND @sub_status IN (@active,@initiated) AND
NOT EXISTS (SELECT * FROM syssubscriptions WHERE
artid IN (SELECT artid FROM sysextendedarticlesview WHERE
objid = @tabid) AND status IN (@active,@initiated) ) AND
NOT EXISTS (SELECT *
FROM sysextendedarticlesview sa
INNER JOIN syspublications sp
ON sa.pubid = sp.pubid
WHERE sa.objid = @tabid
AND sp.allow_initialize_from_backup = 1)
BEGIN
/*
** If it's a procedure execution article, clear proc status bits
*/
IF (@art_type & 8 ) = 8 AND @qualified_name IS NOT NULL
BEGIN
EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @tabid
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 0)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
EXEC %%Module(ID = @tabid).SetProcReplSerialOnly(Value = 0)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
END
END
/* Turn on object replication */
ELSE IF @statusid = @active AND @qualified_name IS NOT NULL
BEGIN
IF( @art_type & 8 ) = 8
begin
EXEC @retcode = sys.sp_MSLockMatchID @qualified_name = @qualified_name, @tabid = @tabid
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
end
IF (@art_type & 24 ) = 24
BEGIN
EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 1)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
EXEC %%Module(ID = @tabid).SetProcReplSerialOnly(Value = 1)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
END
ELSE IF( @art_type & 8 ) = 8
BEGIN
EXEC %%Module(ID = @tabid).SetProcReplicated(Value = 1)
IF @@ERROR <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
END
END
/*
** Get next row.
*/
FETCH #hCsubstatus INTO @current_publication, @artid, @tabid, @srvid, @sub_name, @dest_db,
@sub_status, @non_sql_flag, @repl_freq, @art_type ,
@immediate_sync, @enabled_for_internet,
@allow_anonymous, @subscription_type, @sync_type, @update_mode,
@art_name, @login_name, @loopback_detection, @pubid,
@allow_initialize_from_backup, @sync_method, @nosync_type, @allow_drop
END -- end while for cursor
-- change made as per vsts 13564167
-- instead of returning the LSN for 1st article, now we return lsn for last article
IF @statusid = @initiated and @publisher_type = N'MSSQLSERVER'
begin
-- check @syncinit_lsn_toreturn to make sure it is set else we will pass null to Snapshot agent Distributor connection thread
if @@fetch_status = -1 and @f_syncstat_posted = 0 and @from_auto_sync = 1 and @syncinit_lsn_toreturn is not null
begin
select @pubid_toreturn, @artid_toreturn, @syncinit_lsn_toreturn
select @f_syncstat_posted = 1
end
end
CLOSE #hCsubstatus
DEALLOCATE #hCsubstatus
END -- end while for virtual and real
-- force refresh of article cache
-- Only do it if necessary
-- No need on brute force cleanup or for heterogeneous publications
IF ( @art_change = 1 ) and ( @ignore_distributor = 0 ) and (@publisher IS NULL)
EXECUTE sys.sp_replflush
COMMIT TRANSACTION
drop table #sysextendedarticlesview
RETURN(0)
UNDO:
RAISERROR (14068, 16, -1)
IF @@trancount > 0
BEGIN
ROLLBACK TRANSACTION changesubstatus
COMMIT TRAN
END
RETURN (1)
END
Raw
04.sys.sp_MSsetfilteredstatus
create procedure sys.sp_MSsetfilteredstatus @object_id int, @forceset bit = 0
as
declare @qualified_name nvarchar(517)
,@retcode int
,@setFlag bit
,@pkkey sysname
,@source_table sysname
,@fcdc_tracked bit
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
exec sys.sp_MSget_qualified_name @object_id, @qualified_name output
BEGIN TRANSACTION
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
if (@@rowcount = 0)
set @fcdc_tracked = 0
else
set @fcdc_tracked = 1
if not (@qualified_name is null)
begin
EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)
if @@error <> 0
select @qualified_name = null
end
if @qualified_name is null
begin
raiserror(18750, -1, -1, 'sp_MSsetfilteredstatus', 'object_id');
goto error_abort_exit
end
if exists( select * from sys.objects where type = 'RF' and parent_object_id = @object_id )
or exists( select * from sysarticles A join syspublications P on A.pubid = P.pubid where A.objid = @object_id and (P.allow_queued_tran = 1 or P.allow_sync_tran = 1))
or exists( select * from sysarticles where objid = @object_id and (upper(upd_cmd) like 'CALL%' OR upper(upd_cmd) like 'XCALL%' ) )
or exists( select * from sysarticles where objid = @object_id and (upper(del_cmd) like 'XCALL%' ) )
or exists( select * from sysarticles sa, syssubscriptions ss where sa.objid = @object_id and sa.artid = ss.artid and ss.status = 3)
or @forceset = 1
begin
EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = 1)
set @setFlag = 1
end
else
begin
if (@fcdc_tracked = 0)
EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = 0)
set @setFlag = 0
end
--set/reset IsLoggedForRepl for all replicated column (except legacy blobs) depand on if has_replication_filter is set
--this controls logging behavior of offrow columns in deletes
--don't clear the bits if table is still being tracked by cdc
if(@setFlag = 1 or @fcdc_tracked = 0)
begin
declare #hpk cursor local fast_forward for
select name from sys.columns where object_id = @object_id and is_replicated = 1
and system_type_id not in (34, 35, 99) --image, text. ntext
and user_type_id not in (34, 35, 99) --image, text. ntext
open #hpk
fetch #hpk into @pkkey
while (@@fetch_status = 0)
begin
EXEC %%ColumnEx(ObjectID = @object_id, Name = @pkkey).SetLogForRepl(Value = @setFlag)
IF @@ERROR <> 0
BEGIN
select @source_table = object_name(@object_id)
RAISERROR(15052, -1, -1, @source_table)
return 1
END
fetch #hpk into @pkkey
end
close #hpk
deallocate #hpk
--in case of turning of has_replicate_filter, make sure IsLoggedForRepl is still set for base of pk/computed pk columns
if(@setFlag = 0)
begin
exec @retcode = sp_MSSetLogForRepl @object_id
if @@ERROR <> 0 or @retcode <> 0
return(1)
end
end
COMMIT TRANSACTION
return (0)
error_abort_exit:
IF @@TRANCOUNT = 1
ROLLBACK TRAN
ELSE COMMIT TRAN
return (1)
Raw
gistfile1.txt
exec sp_MSactivate_auto_sub @publication=N'ReplDB-repliation',@article=N'%',@status=N'initiated'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment