create procedure sys.sp_MSrepl_changearticle
(
@publication sysname,
@article sysname,
@property nvarchar(100),
@value nvarchar(386),
@force_invalidate_snapshot bit,
@force_reinit_subscription bit,
@publisher sysname,
@publisher_type sysname
)
AS
BEGIN
SET NOCOUNT ON
/*
** Declarations.
*/
DECLARE @artid int
,@cmd1 nvarchar(512)
,@cmd2 nvarchar(512)
,@db sysname
,@filter int
,@object sysname
,@owner sysname
,@pubid int
,@retcode int
,@site sysname
,@sync_objid int
,@typeid tinyint
,@old_typeid tinyint
,@precmdid tinyint
,@active tinyint
,@virtual_id smallint
,@article_type tinyint
,@repl_freq int
,@colname sysname
,@objid int
,@objtype nchar(2)
,@old_filter_name sysname
,@distributor sysname
,@distribdb sysname
,@dbname sysname
,@distproc nvarchar (255)
,@dts_part nvarchar(50)
,@no_dts_part nvarchar(50)
,@backward_comp_level int
,@allow_dts bit
,@allow_queued_tran bit
,@allow_sync_tran bit
,@allow_initialize_from_backup bit
,@increment_min_autonosync_lsn bit
,@srvname sysname
,@pubstatus tinyint
,@options int
,@for_p2p_ddl int
,@auto_gen int
,@fire_triggers_on_snapshot bit
,@customprocmaxlen_minus_ccs_appendix smallint
,@timestampcolumn sysname
DECLARE @OPT_ENABLED_FOR_P2P int
SELECT @OPT_ENABLED_FOR_P2P = 0x1
select @backward_comp_level = 10 -- default to sphinx
,@dts_part = N'dts horizontal partitions'
,@no_dts_part = N'no dts horizontal partitions'
,@active = 2
,@virtual_id = -1
,@increment_min_autonosync_lsn = 0
,@srvname = publishingservername()
,@customprocmaxlen_minus_ccs_appendix = 117 -- (128-11)
IF (@publisher_type != N'MSSQLSERVER')
BEGIN
-- Set servername to publisher when HREPL
SET @srvname = @publisher
END
/*
** 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: @publication.
** Make sure that the publication exists.
*/
IF (@publication IS NULL)
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_changearticle')
RETURN (1)
END
-- Get publication id
SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
IF @pubid IS NULL
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END
SELECT @pubstatus = status,
@repl_freq = repl_freq,
@allow_dts = allow_dts,
@allow_sync_tran = allow_sync_tran,
@allow_queued_tran = allow_queued_tran,
@allow_initialize_from_backup = allow_initialize_from_backup,
@options = options
FROM syspublications
WHERE pubid = @pubid
/*
** Check to see that the article exists in sysextendedarticlesview.
** Fetch the article identification number.
*/
IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_changearticle')
RETURN (1)
END
-- NOTE: HREPL doesn't support autogen/schema replication
-- @auto_gen should *always* be 0 for HREPL
SELECT @artid = artid,
@article_type = type,
@objid = objid,
@auto_gen = CASE
WHEN @publisher_type = N'MSSQLSERVER' THEN (convert(int, schema_option) & 0x2)
ELSE 0
END
FROM sysextendedarticlesview
WHERE name = @article
AND pubid = @pubid
IF @artid IS NULL
BEGIN
RAISERROR (20027, 11, -1, @article)
RETURN (1)
END
IF (@publisher_type = N'MSSQLSERVER')
BEGIN
-- Get the object type
SELECT @objtype = type
FROM sys.objects
WHERE object_id = @objid
IF @objtype IS NULL
BEGIN
RAISERROR( 20027, 11, -1, @article )
RETURN( 1 )
END
END
ELSE
BEGIN
-- All HREPL objects are treated as user tables
SET @objtype = 'U'
END
/*
** Parameter Check: @property.
** If the @property parameter is NULL, print the options.
*/
IF @property IS NULL
BEGIN
CREATE TABLE #tab1 (properties sysname collate database_default not null)
INSERT INTO #tab1 VALUES ('description')
INSERT INTO #tab1 VALUES ('sync_object (log based article only)')
INSERT INTO #tab1 VALUES ('type')
INSERT INTO #tab1 VALUES ('ins_cmd (log based article only)')
INSERT INTO #tab1 VALUES ('del_cmd (log based article only)')
INSERT INTO #tab1 VALUES ('upd_cmd (log based article only)')
INSERT INTO #tab1 VALUES ('filter (log based article only)')
INSERT INTO #tab1 VALUES ('dest_table (log based article only)')
INSERT INTO #tab1 VALUES ('dest_object')
INSERT INTO #tab1 VALUES ('creation_script')
INSERT INTO #tab1 VALUES ('pre_creation_cmd')
INSERT INTO #tab1 VALUES ('status')
INSERT INTO #tab1 VALUES ('schema_option')
INSERT INTO #tab1 VALUES ('destination_owner')
INSERT INTO #tab1 VALUES ('pub_identity_range (log based article only)')
INSERT INTO #tab1 VALUES ('identity_range (log based article only)')
INSERT INTO #tab1 VALUES ('threshold (log based article only)')
INSERT INTO #tab1 VALUES ('tablespace')
INSERT INTO #tab1 VALUES ('fire_triggers_on_snapshot (log based article only)')
PRINT ''
SELECT * FROM #tab1
RETURN (0)
END
-- Pre-lower @property so we don't have to lower it dynamically below
SELECT @property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)
/*
** (!!All of the following restrictions are temporarily lifted)
** Enforce automated nosync subscription restrictions
** i) No horizontal partitioning
** ii) Dest. object name cannot be different from source
** iv) Dest. owner name cannot be different from source
*/
-- IF @allow_initialize_from_backup = 1
-- BEGIN
-- -- i)
-- IF @property = N'type' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN
-- ('logbased manualfilter',
-- 'logbased manualview',
-- 'logbased manualboth') OR
-- @property = N'filter' AND @value IS NOT NULL AND RTRIM(@value) <> N'' OR
-- @property = N'sync_object' AND OBJECT_ID(@value) <> @objid
-- BEGIN
-- RAISERROR (18792, 16, -1)
-- RETURN (1)
-- END
-- -- ii)
-- IF (@property = N'dest_table' OR @property = N'dest_object') AND @value <> OBJECT_NAME(@objid)
-- BEGIN
-- RAISERROR (18791, 16, -1)
-- RETURN (1)
-- END
-- -- iii)
-- IF @property = N'destination_owner' AND @value <> schema_name(OBJECTPROPERTY(@objid, 'SchemaId'))
-- BEGIN
-- RAISERROR (18791, 16, -1)
-- RETURN (1)
-- END
-- END
/*
Only allow the call format to be changed as it does not require a reinit.
*/
SELECT @for_p2p_ddl = @options & @OPT_ENABLED_FOR_P2P
IF @for_p2p_ddl = @OPT_ENABLED_FOR_P2P
BEGIN
IF @property NOT IN ('description',
'ins_cmd',
'del_cmd',
'upd_cmd')
BEGIN
-- The property '@property' cannot be modified for Peer-To-Peer publications.
RAISERROR(20647, 16, -1, @property)
RETURN (1)
END
IF @property IN ('ins_cmd',
'del_cmd',
'upd_cmd')
AND LEFT(UPPER(LTRIM(@value)), 3) IN (N'SQL')
BEGIN
-- Peer-To-Peer publications do not support 'SQL' command type. Please change the '@value' parameter value.
RAISERROR(20646, 16, -1, '''SQL'' Command Type', '@value')
RETURN (1)
END
END
/*
** Increment the publication's min. autonosync lsn if filtering properties
** are changed or the destination object/owner name is changed
*/
IF @allow_initialize_from_backup = 1
BEGIN
IF @property IN ('ins_cmd',
'del_cmd',
'upd_cmd',
'filter',
'type',
'sync_object',
'dest_table',
'dest_object',
'destination_owner'
)
BEGIN
SELECT @increment_min_autonosync_lsn = 1
END
END
/*
** At this point, we have completed all the validations and
** preprocessings common to both regular and schema only articles
** so we call a different proceudre here to handle the schema only
** articles differently.
*/
IF @article_type in (0x20, 0x40, 0x60, 0xA0, 0x80)
BEGIN
EXEC @retcode = sys.sp_MSchangeschemaarticle
@pubid = @pubid,
@artid = @artid,
@property = @property,
@value = @value
RETURN @retcode
END
IF @objtype = 'U' AND @property NOT IN
('description',
'sync_object',
'type',
'ins_cmd',
'del_cmd',
'upd_cmd',
'filter',
'dest_table',
'dest_object',
'creation_script',
'pre_creation_cmd',
'status',
'schema_option',
'destination_owner',
'pub_identity_range',
'identity_range',
'threshold',
'tablespace',
'fire_triggers_on_snapshot')
BEGIN
RAISERROR (21183, 16, -1, @property)
RETURN (1)
END
IF @objtype = 'P' AND @property NOT IN
('description',
'dest_object',
'dest_table',
'creation_script',
'pre_creation_cmd',
'schema_option',
'destination_owner')
BEGIN
RAISERROR (14110, 16, -1)
RETURN (1)
END
/* dest_object and 'dest_table' are same */
IF @property = 'dest_object'
SELECT @property = 'dest_table'
-- Validate HREPL-only properties
IF @publisher_type NOT LIKE N'ORACLE%'
BEGIN
IF @property = 'tablespace'
BEGIN
RAISERROR(21737, 16, -1, @property, @publisher_type)
RETURN (1)
END
END
-- Validate property combinations for HREPL
IF @publisher_type != N'MSSQLSERVER'
BEGIN
IF (@property IN ('dest_table', 'schema_option', 'destination_owner', 'status', 'pre_creation_cmd')
OR (@property IN ('ins_cmd', 'del_cmd', 'upd_cmd', 'filter') AND (@pubstatus = 1)))
AND (@force_invalidate_snapshot = 0 OR @force_reinit_subscription = 0)
BEGIN
RAISERROR(21772, 16, -1, @property)
RETURN (1)
END
IF (@property IN ('tablespace') AND @repl_freq = 1)
BEGIN
RAISERROR(21738, 16, -1, @property, 'snapshot')
RETURN (1)
END
-- Verify that sp_articleview has been called to insure that
-- the log table has been created and exists to be moved
IF (@property IN ('tablespace') AND
NOT EXISTS (SELECT * FROM dbo.IHarticles WHERE article_id = @artid AND publisher_status = 1)
)
BEGIN
RAISERROR(21739, 16, -1, @property)
RETURN (1)
END
END
-- Processing for custom stored procedures
IF @property IN ('ins_cmd', 'del_cmd', 'upd_cmd' )
begin
if (@allow_dts = 1)
begin
-- For DTS enabled publications the custom procedure are autogenerated
-- and cannot be changed
raiserror(21175, 16, -1)
return (1)
end
else if (@allow_queued_tran = 1 or @allow_sync_tran = 1)
begin
-- Updating subscribers
-- INS/UPD/DEL format is CALL/VCALL/VCALL
-- we will allow XCALL for UPD/DEL for backwards compatibility
declare @newcmd nvarchar(255)
-- validate custom command
select @newcmd = case
when (@value is not null and @property = 'ins_cmd' and (upper(left(@value, 5)) = N'CALL') ) then @value
when (@value is not null and @property = 'upd_cmd' and (upper(left(@value, 5)) in (N'XCALL', N'VCALL')) ) then @value
when (@value is not null and @property = 'del_cmd' and (upper(left(@value, 5)) in (N'XCALL', N'VCALL')) ) then @value
else NULL end
if (@newcmd is null)
begin
-- specified invalid value for the property for updating subscribers
raiserror(21344, 16, 1, @property)
return (1)
end
end -- if (@allow_queued_tran = 1 or @allow_sync_tran = 1)
end -- if @property IN ('ins_cmd', 'del_cmd', 'upd_cmd' )
/*
** Check to make sure that we have a valid type for status
*/
IF @property = 'status'
BEGIN
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IN ('not owner qualified', 'owner qualified')
BEGIN
RAISERROR (21023, 16, -1,@value)
RETURN (1)
END
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'no column names', 'include column names', 'string literals', 'parameters',
@dts_part, @no_dts_part )
BEGIN
RAISERROR (14097, 16, -1)
RETURN (1)
END
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,@no_dts_part)
begin
if @allow_dts = 0
begin
-- Invalid status for non dts pub
raiserror(20592, 16, -1)
RETURN (1)
end
end
else
begin
if @allow_dts = 1
begin
-- Invalid status for dts pub
raiserror(20591, 16, -1)
RETURN (1)
end
end
END
IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
AND @publisher_type != N'MSSQLSERVER'
BEGIN
/*
** Parameter Check: @ins_cmd, @del_cmd, @upd_cmd
** Check to see that the cmd is valid
*/
select @value = rtrim(ltrim(@value))
if @property = 'ins_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ')
begin
-- Invalid '@ins_cmd' specified. It must be NONE, SQL, CALL sp_MSins_article or CALL custom_stored_procedure_name.
raiserror (15251, 16, 1, '@ins_cmd', 'NONE, SQL, CALL sp_MSins_article or CALL custom_stored_procedure_name')
return 1
end
else if @property = 'del_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL')
begin
-- Invalid '@del_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name.
raiserror (15251, 16, 1, '@del_cmd', 'NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name')
return 1
end
else if @property = 'upd_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'MCALL', N'SCALL')
begin
-- Invalid '@upd_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or MCALL sp_MSdel_article.
raiserror (15251, 16, 1, '@upd_cmd', 'NONE, SQL, CALL sp_MSupd_article, XCALL sp_MSupd_article, MCALL sp_MSupd_article or SCALL sp_MSupd_article')
return 1
end
END
IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
AND @publisher_type = N'MSSQLSERVER'
BEGIN
/*
** Parameter Check: @ins_cmd, @del_cmd, @upd_cmd
** Check to see that the cmd is valid
*/
select @value = rtrim(ltrim(@value))
if @property = 'ins_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'SCALL')
begin
-- Invalid '@ins_cmd' specified. It must be NONE, SQL, CALL sp_MSins_article, SCALL sp_MSins_article or CALL custom_stored_procedure_name.
raiserror (15251, 16, 1, '@ins_cmd', 'NONE, SQL, CALL sp_MSins_article, SCALL sp_MSins_article or CALL custom_stored_procedure_name')
return 1
end
else if @property = 'del_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'VCALL')
begin
-- Invalid '@del_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or CALL custom_stored_procedure_name.
raiserror (15251, 16, 1, '@del_cmd', 'NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article, VCALL sp_MSdel_article or CALL custom_stored_procedure_name')
return 1
end
else if @property = 'upd_cmd' and @value is not null
and sys.fn_MSrepltranparsecmdtype(@value) not in (N'', N'NONE', N'SQL', N'CALL ', N'XCALL', N'MCALL', N'VCALL', N'SCALL')
begin
-- Invalid '@upd_cmd' specified. It must be NONE, SQL, CALL sp_MSdel_article, XCALL sp_MSdel_article or MCALL sp_MSdel_article.
raiserror (15251, 16, 1, '@upd_cmd', 'NONE, SQL, CALL sp_MSupd_article, XCALL sp_MSupd_article, MCALL sp_MSupd_article, VCALL sp_MSupd_article or SCALL sp_MSupd_article')
return 1
end
-- concurrent snapshot articles should use procs cuz we need to customize the compensating del/ins
-- it should be OK to allow SQL/None for upd though
if exists (select * from syspublications
where pubid = @pubid and sync_method in (3, 4))
BEGIN
if len(@value) > @customprocmaxlen_minus_ccs_appendix
begin
RAISERROR( 21155, 16, -1, @article, @customprocmaxlen_minus_ccs_appendix)
return 1
end
END
-- for VCALL - msrepl_tran_version column should exist and be of type uniqueidentifier
if @property in ( 'del_cmd', 'upd_cmd' ) and (upper(left(@value,5)) = N'VCALL')
begin
declare @msrepl_tran_version_datatype sysname
select @msrepl_tran_version_datatype = type_name(system_type_id)
FROM sys.columns
WHERE object_id = @objid AND name = 'msrepl_tran_version'
if (@msrepl_tran_version_datatype != 'uniqueidentifier')
begin
raiserror(21567, 16, -1)
return 1
end
end
-- allow custom procedure to have spaces and special characters
-- we will quote the custom procedure as follows:
-- XXXX sp_MSxxx_custom_proc_name or XXXX x_GUID will become
-- XXXX [dbo].[sp_MSxxx_custom_proc_name] or XXXX [dbo].[x_GUID]
-- if the custom procedure is already quoted in some form - user
-- specified custom procedure name - then we will not change it
if (@property = 'ins_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'SCALL'))) or
(@property = 'del_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'XCALL', N'VCALL'))) or
(@property = 'upd_cmd' and (upper(left(@value, 5)) in (N'CALL ', N'XCALL', N'MCALL', N'VCALL', N'SCALL')))
begin
declare @tmp_value nvarchar(386) = @value
select @value = sys.fn_repltranquotecustomproc(@value)
if @value = N''
begin
raiserror (22829, 16, -1, @tmp_value, N'dbo', 255)
return 1
end
end
END -- IF @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
declare @need_new_snapshot bit
,@need_reinit_subscription bit
select @need_new_snapshot = 0
,@need_reinit_subscription = 0
if @property in ( N'dest_table', N'destination_owner' ,N'type',N'filter',
N'pre_creation_cmd', N'schema_option') or
(LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) in (@dts_part,
@no_dts_part))
begin
select @need_new_snapshot = 1
,@need_reinit_subscription = 1
end
else if @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' ) and
(@auto_gen = 0 )
begin
-- If autogeneration is not supported
select @need_new_snapshot = 1
,@need_reinit_subscription = 1
end
else if @property in ('sync_object', 'creation_script')
begin
select @need_new_snapshot = 1
end
-- Have to call this stored procedure to invalidate existing snapshot or reint
-- subscriptions if needed
EXECUTE @retcode = sys.sp_MSreinit_article
@publication = @publication,
@article = @article,
@need_new_snapshot = @need_new_snapshot,
@need_reinit_subscription = @need_reinit_subscription,
@force_invalidate_snapshot = @force_invalidate_snapshot,
@force_reinit_subscription = @force_reinit_subscription,
@check_only = 1,
@publisher = @publisher,
@publisher_type = @publisher_type
IF @@ERROR <> 0 OR @retcode <> 0
return(1)
/*
** Change the property.
*/
-- HREPL properties (can't use the transaction)
IF @property IN ('tablespace')
BEGIN
EXEC @retcode = sys.sp_ORAChangeTableSpace @publisher = @publisher,
@publication_id = @pubid,
@article_id = @artid,
@tablespace = @value
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END -- tablespace PROPERTY
begin tran
save TRAN sp_changearticle
IF @property IN ( 'description', 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'creation_script', 'dest_object')
BEGIN
if @publisher_type = N'MSSQLSERVER' AND @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' )
begin
declare @schema_start_lsn binary(10)
,@schema_end_lsn binary(10)
if (@property = 'ins_cmd' and upper(@value) = 'SQL' and OBJECTPROPERTY(@objid, 'tablehasidentity') = 1)
begin
-- special processing for ins_cmd when value = 'sql'
-- set status bit 0x8 for colname specification in commands
-- this enables proper identity NFR processing in Shiloh server
update sysarticles set status = status | 8
where artid = @artid and objid = @objid and (status & 8 != 8)
end
exec @retcode = sys.sp_replincrementlsn_internal @schema_start_lsn OUTPUT
if @@ERROR<>0 or @retcode <> 0
GOTO UNDO
exec @retcode = sys.sp_replpostcmd 0, @objid, @artid, 51, @objid
if(@retcode <> 0) or (@@error <> 0)
GOTO UNDO
exec @retcode = sys.sp_replincrementlsn_internal @schema_end_lsn OUTPUT
if @@ERROR<>0 or @retcode <> 0
GOTO UNDO
if (@schema_start_lsn is not null) and (@schema_end_lsn is not null)
begin
insert systranschemas (tabid, startlsn, endlsn, typeid) values (@objid, @schema_start_lsn, @schema_end_lsn, 51)
if @@ERROR<>0
GOTO UNDO
end
end
/*
** Check the validity of the destination object. NULL should
** get converted to the source object name. Destination object
** names can be owner qualified, but not database qualified.
*/
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_table' OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'dest_object'
BEGIN
IF @value IS NULL
SELECT @value = object_name(objid)
FROM sysarticles
WHERE artid = @artid
AND pubid = @pubid
END
SELECT @cmd1 = 'UPDATE sysarticles '
IF @value IS NULL
BEGIN
SELECT @cmd1 = @cmd1 + ' SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = NULL'
SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
SELECT @cmd2 = @cmd2 + ' AND pubid = ' + STR(@pubid)
EXECUTE (@cmd1 + @cmd2)
END
ELSE
BEGIN
SELECT @cmd1 = @cmd1 + ' SET ' + LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) + ' = ' + sys.fn_replquotename(@value, N'''') collate SQL_Latin1_General_CP1_CS_AS
SELECT @cmd2 = ' WHERE artid = ' + STR(@artid)
SELECT @cmd2 = @cmd2 + ' AND pubid = ' + STR(@pubid)
EXECUTE (@cmd1 + @cmd2)
END
IF @publisher_type = N'MSSQLSERVER'
AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'upd_cmd'
BEGIN
IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )
BEGIN
exec sys.sp_MSsetfilteredstatus @objid
END
END
IF @@ERROR <> 0
GOTO UNDO
-- For command changes - post regenerated custom procedures
-- if autogeneration is enabled and it is not SQL format
if @property in ( 'ins_cmd', 'del_cmd', 'upd_cmd' ) and (@auto_gen > 0) and
(upper(left(ltrim(@value), 3)) != N'SQL')
begin
declare @temp_id int
select @temp_id = case @property when 'ins_cmd' then 1
when 'del_cmd' then 2
when 'upd_cmd' then 3
end
exec @retcode = sys.sp_MSpost_auto_proc @pubid = @pubid, @artid = @artid, @procmapid = @temp_id, @for_p2p_ddl = @for_p2p_ddl
if (@retcode <> 0) or (@@error <> 0)
GOTO UNDO
-- For XCALL - set proper status for logreader
exec @retcode = sys.sp_MSsetfilteredstatus @objid
if(@retcode <> 0) or (@@error <> 0)
GOTO UNDO
-- Flush the article cache for logreader to pick the change
exec @retcode = sys.sp_replflush
if(@retcode <> 0) or (@@error <> 0)
GOTO UNDO
end
END -- if property in ('description',....)
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'sync_object'
BEGIN
/*
** Check for a valid synchronization object.
*/
IF (@publisher_type != N'MSSQLSERVER')
BEGIN
RAISERROR (21632, 16, -1, '@sync_object', 'NULL')
GOTO UNDO
END
IF @value IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@value', 'sp_MSrepl_changearticle')
GOTO UNDO
END
IF @value LIKE '%.%.%' OR @value LIKE '%.%'
BEGIN
select @object = PARSENAME( @value, 1 )
select @owner = PARSENAME( @value, 2 )
select @db = PARSENAME( @value, 3 )
select @site = PARSENAME( @value, 4 )
if @object IS NULL
GOTO UNDO
END
SELECT @sync_objid = OBJECT_ID(@value)
IF @sync_objid IS NULL
BEGIN
RAISERROR (15001, 11, -1, @value)
GOTO UNDO
END
IF NOT EXISTS
(
SELECT *
FROM sys.objects
WHERE type IN ('U', 'V')
AND object_id = @sync_objid
)
BEGIN
RAISERROR (14031, 16, -1)
GOTO UNDO
END
/*
** Update the article with the new synchronization object.
*/
UPDATE sysarticles
SET sync_objid = @sync_objid
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
END -- sync object property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'type'
BEGIN
/*
** Check to make sure that we have a valid type.
*/
SELECT @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
IF (@publisher_type = N'MSSQLSERVER')
BEGIN
IF @value NOT IN (
'logbased',
'logbased manualfilter',
'logbased manualview',
'logbased manualboth',
'indexed view logbased',
'indexed view logbased manualfilter',
'indexed view logbased manualview',
'indexed view logbased manualboth')
BEGIN
RAISERROR (14023, 16, -1)
GOTO UNDO
END
END
ELSE
BEGIN
IF @value != 'logbased'
BEGIN
RAISERROR(21601, 16, 1)
RETURN (1)
END
END
IF @objtype = 'V' AND @value NOT IN (
'indexed view logbased',
'indexed view logbased manualfilter',
'indexed view logbased manualview',
'indexed view logbased manualboth')
BEGIN
RAISERROR (18795, 16, -1)
GOTO UNDO
END
ELSE IF @value NOT IN (
'logbased',
'logbased manualfilter',
'logbased manualview',
'logbased manualboth')
BEGIN
RAISERROR (18796, 16, -1)
GOTO UNDO
END
/*
** Determine the integer value for the type.
*/
IF @value IN ('logbased', 'indexed view logbased')
SELECT @typeid = 1
ELSE IF @value IN ('logbased manualfilter', 'indexed view logbased manualfilter')
SELECT @typeid = 3
ELSE IF @value IN ('logbased manualview', 'indexed view logbased manualview')
SELECT @typeid = 5
ELSE IF @value IN ('logbased manualboth', 'indexed view logbased manualboth')
SELECT @typeid = 7
/*
** Update the article with the new type.
*/
UPDATE sysarticles
SET type = @typeid
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
END -- type property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'filter'
BEGIN
/*
** Check for a valid filter value.
*/
IF NOT @publisher_type = N'MSSQLSERVER'
BEGIN
RAISERROR(21632, 16, -1, '@filter', 'NULL')
GOTO UNDO
END
IF @value IS NOT NULL
BEGIN
IF @value LIKE '%.%.%' OR @value LIKE '%.%'
BEGIN
select @object = PARSENAME( @value, 1 )
select @owner = PARSENAME( @value, 2 )
select @db = PARSENAME( @value, 3 )
select @site = PARSENAME( @value, 4 )
if @object IS NULL
begin
raiserror (21344, 16, -1, '@value')
GOTO UNDO
end
END
END
SELECT @filter = OBJECT_ID(@value)
IF @value IS NOT NULL
BEGIN
IF @filter IS NULL
BEGIN
RAISERROR (15001, 11, -1, @value)
GOTO UNDO
END
IF NOT EXISTS
(
SELECT *
FROM sys.objects
WHERE type = 'RF'
AND object_id = @filter
)
BEGIN
RAISERROR (14049, 16, -1)
GOTO UNDO
END
END
IF @value IS NULL SELECT @filter = 0
-- save off the old filter
SELECT @old_filter_name = object_name( filter )
FROM sysarticles WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
-- Update the article with the new filter.
UPDATE sysarticles
SET filter = @filter
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
-- SQL SERVER > 7.x ONLY disassociate old filter with table
-- and associate new one
IF ( 0 <> ( SELECT PATINDEX( '%[789].[0-9]%', @@version ) ) ) OR
( 0 <> ( SELECT PATINDEX( '%[1-9][0-9].[0-9]%', @@version ) ) )
BEGIN
-- disassociate table from old filter proc
EXEC sys.sp_MSsetfilterparent @old_filter_name, 0
IF @@ERROR <> 0
GOTO UNDO
-- set the parent of the filter proc to this object_id
EXEC sys.sp_MSsetfilterparent @value, @objid
IF @@ERROR <> 0
GOTO UNDO
END
END -- filter property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_creation_cmd'
BEGIN
/*
** Check to make sure that we have a valid pre_creation_cmd.
*/
IF @objtype = 'P' and LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop')
BEGIN
RAISERROR ( 14111, 16, -1 )
GOTO UNDO
END
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('none', 'drop', 'delete', 'truncate')
BEGIN
RAISERROR (14061, 16, -1)
GOTO UNDO
END
/*
** Determine the integer value for the type.
*/
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'none'
SELECT @precmdid = 0
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'drop'
SELECT @precmdid = 1
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'delete'
SELECT @precmdid = 2
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'truncate'
SELECT @precmdid = 3
/*
** Update the article with the new pre_creation_cmd.
*/
UPDATE sysarticles
SET pre_creation_cmd = @precmdid
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
END -- pre_creation_cmd property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status'
BEGIN
/*
** Determine the integer value for the type.
*/
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'none'
BEGIN
UPDATE sysarticles
SET status = 0
WHERE artid = @artid
AND pubid = @pubid
END
ELSE
BEGIN
IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'not owner qualified'
UPDATE sysarticles
SET status = status & ~4
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'owner qualified'
UPDATE sysarticles
SET status = status | 4
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'no column names'
UPDATE sysarticles
SET status = status & ~8
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'include column names'
UPDATE sysarticles
SET status = status | 8
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'string literals'
UPDATE sysarticles
SET status = status & ~16
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'parameters'
UPDATE sysarticles
SET status = status | 16
WHERE artid = @artid
AND pubid = @pubid
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @dts_part
begin
IF EXISTS
(
SELECT *
FROM sysarticles
WHERE artid = @artid
AND status & 64 = 0
)
BEGIN
UPDATE sysarticles
SET status = status | 64,
upd_cmd = N'XCALL sp_MSXpd_' + @article
WHERE artid = @artid
END
END
ELSE IF LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = @no_dts_part
BEGIN
IF EXISTS
(
SELECT *
FROM sysarticles
WHERE artid = @artid
AND status & 64 <> 0
)
BEGIN
UPDATE sysarticles
SET status = status & ~64,
upd_cmd = N'CALL sp_MSupd_' + @article
WHERE artid = @artid
END
END
IF @@ERROR <> 0
GOTO UNDO
END
END -- status property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'schema_option'
BEGIN
IF @value IS NULL
BEGIN
RAISERROR(14146, 16,1)
GOTO UNDO
END
IF sys.fn_isvalidhexstr(@value) = 0
AND ISNUMERIC(@value) = 0
BEGIN
-- '@value' is not a valid value for the '@value' parameter. The value must be an integer.
RAISERROR(21805, 16, -1, @value, '@value')
GOTO UNDO
END
CREATE TABLE #tab_changearticle (value varbinary(8) NULL)
IF @@ERROR <> 0
GOTO UNDO
EXEC ('insert #tab_changearticle values (' + @value +')' )
IF @@ERROR <> 0
GOTO UNDO
-- Cache the schema option in int form for further checking
declare @schema_option_hidword int
declare @schema_option_lodword int
select @schema_option_lodword = sys.fn_replgetbinary8lodword(fn_replprepadbinary8(value)),
@schema_option_hidword = sys.fn_replgetbinary8hidword(fn_replprepadbinary8(value))
from #tab_changearticle
DROP TABLE #tab_changearticle
DECLARE @valid_schema_options int
IF @publisher_type != N'MSSQLSERVER'
BEGIN
-- Restrict HREPL schema options
SELECT @valid_schema_options = 0xD0D3
IF (@schema_option_lodword & ~@valid_schema_options) <> 0 OR
@schema_option_hidword <> 0
BEGIN
RAISERROR (21635, 16, -1)
GOTO UNDO
END
END
IF @objtype in ('P', 'PC')
BEGIN
SELECT @valid_schema_options = 0xFAC03021
IF (@schema_option_lodword & ~@valid_schema_options) <> 0
BEGIN
RAISERROR ( 20014, 16, -1 )
GOTO UNDO
END
END
-- For updating subscriber publications
-- DRI option has to be included
if ((@allow_queued_tran = 1 or @allow_sync_tran = 1) and ((@schema_option_lodword & 0x80) = 0))
BEGIN
RAISERROR (21394, 16, 2)
GOTO UNDO
END
if @publisher_type = N'MSSQLSERVER'
begin
-- Check schema option for timestamp
if (@schema_option_lodword & 0x8 = 0)
begin
-- Timestamp scripting disabled
-- For Queued this cannot be allowed
if (@allow_queued_tran = 1)
begin
raiserror (29003, 16, 1)
goto UNDO
end
-- Try to find out if there is a timestamp
-- column that needs to be included in sysarticlecolumns
-- now that it is replicated as binary(16)
-- Note: There can only be one timestamp column per table.
set @timestampcolumn = null
select @timestampcolumn = columns.name
from sys.columns columns
inner join dbo.sysarticles arts
on columns.object_id = arts.objid
where arts.artid = @artid
and (arts.status & 32 != 0)
and columns.system_type_id = 189
-- status bit 32 should be reset as well
update sysarticles set status = (status & ~32)
where artid = @artid and (status & 32 != 0)
if @@error != 0
goto UNDO
if @timestampcolumn is not null
begin
exec @retcode = sys.sp_articlecolumn
@publication = @publication ,
@article = @article,
@column = @timestampcolumn,
@operation = N'add',
@force_invalidate_snapshot = @force_invalidate_snapshot,
@force_reinit_subscription = @force_reinit_subscription
if @@error != 0 or @retcode != 0
goto UNDO
end
end
else
begin
-- If the timestamp column is part of the primary key, we
-- can't really replicate the timestamp column as a timestamp
-- column as that will violate the equivalency of primary key
-- values that transactional replication is based on. As such,
-- we will implicitly turn off the KeepTimestamp(0x08) schema
-- option if the primary key contains a timestamp column.
if @repl_freq = 0 and
exists (select c.name
from sys.columns c
inner join sys.index_columns ic
on c.column_id = ic.column_id
and c.object_id = ic.object_id
and ic.is_included_column = 0
inner join sys.indexes i
on i.index_id = ic.index_id
and i.object_id = ic.object_id
and i.is_primary_key = 1
where c.object_id = @objid
and c.system_type_id = 189)
begin
set @schema_option_lodword = @schema_option_lodword & ~8
end
else
begin
-- Try to find out if there is a timestamp
-- column that is currently replicated as a binary column.
-- If so, it should be removed from sysarticlecolumns
-- once the 0x8 schema option is enabled
select @timestampcolumn = columns.name
from sys.columns columns
inner join dbo.sysarticlecolumns artcols
on columns.column_id = artcols.colid
inner join dbo.sysarticles arts
on columns.object_id = arts.objid
and artcols.artid = arts.artid
where arts.artid = @artid
and (arts.status & 32 = 0)
and columns.system_type_id = 189
-- Timestamp scripting enabled
-- status bit 32 should be set as well
update sysarticles set status = (status |32)
where artid = @artid and (status & 32 = 0)
if @@error != 0
goto UNDO
if @timestampcolumn is not null
begin
exec @retcode = sys.sp_articlecolumn
@publication = @publication ,
@article = @article,
@column = @timestampcolumn,
@operation = N'add',
@force_invalidate_snapshot = @force_invalidate_snapshot,
@force_reinit_subscription = @force_reinit_subscription
if @@error != 0 or @retcode != 0
goto UNDO
end
end
end
-- Check schema option for identity scripting
if (@schema_option_lodword & 0x4 = 0)
begin
-- identity scripting disabled
-- If the identity range management is AUTO
-- then this cannot be allowed
if exists (select * from sysarticleupdates where artid = @artid and identity_support = 1)
begin
raiserror (29003, 16, 2)
goto UNDO
end
-- If the identity range management is MANUAL
-- then this cannot be allowed for Queued and P2P
if exists (select name from sys.columns where object_id = @objid and is_identity = 1
and ColumnProperty(object_id, name, 'IsIdNotForRepl') = 1 )
begin
if (@for_p2p_ddl = @OPT_ENABLED_FOR_P2P) or (@allow_queued_tran = 1)
begin
raiserror (29003, 16, 3)
goto UNDO
end
end
end
else
begin
-- identity scripting enabled
-- Need to mark the identity column as NFR
select @colname = name
from sys.columns
where object_id = @objid and
is_identity = 1 and -- is identity
ColumnProperty(object_id, name, 'IsIdNotForRepl') = 0
if @colname is not null
begin
-- Mark 'not for repl'
EXEC %%ColumnEx(ObjectID = @objid, Name = @colname).SetIdentityNotForRepl(Value = 1)
IF @@ERROR <> 0
GOTO UNDO
end
end
end
-- Seems to be a good place to check and see if using
-- collation 0x00001000 or extended property 0x00002000
if
(
(@schema_option_lodword & 0x000001000 <> 0) or
(@schema_option_lodword & 0x000002000 <> 0)
)
BEGIN
select @backward_comp_level = 40
END
declare @schema_option bigint
set @schema_option = convert(bigint,sys.fn_replcombinehilodwordintobinary8(@schema_option_hidword, @schema_option_lodword))
--once we are here, article partition has been updated up to current point, now check on UDT dependency
if ((@schema_option & 0x000000B000000020) <> 0) and
exists (select * from sys.columns where object_id = @objid and system_type_id = 240)
begin
if (sys.fn_MSrepl_dependUDT(@objid, @schema_option, @artid) = 1 )
begin
raiserror (21839, 16, 1, @article)
GOTO UNDO
end
end
UPDATE sysarticles
SET schema_option = convert(binary(8),@schema_option)
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
END -- schema option property
IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'destination_owner'
BEGIN
IF LEN(@value) = 0
BEGIN
SET @value = NULL
END
IF @value IS NOT NULL
BEGIN
EXECUTE @retcode = sys.sp_validname @value
IF @retcode <> 0
GOTO UNDO
END
UPDATE sysarticles
SET dest_owner = @value
FROM sysarticles
WHERE artid = @artid
AND pubid = @pubid
IF @@ERROR <> 0
GOTO UNDO
END
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pub_identity_range'
begin
if not exists (select * from sysarticleupdates where artid = @artid and
identity_support = 1)
begin
raiserror(21235, 16, -1, @property)
goto UNDO
end
declare @pub_range bigint
select @pub_range = convert(bigint, @value)
if @pub_range < 0
begin
raiserror(21232, 16, -1)
goto UNDO
end
if exists (select * from MSpub_identity_range where objid = @objid and
pub_range < 0)
select @pub_range = @pub_range * -1
update MSpub_identity_range set
pub_range = @pub_range
where objid=@objid
if @@error < 0
goto UNDO
end
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'identity_range'
begin
if not exists (select * from sysarticleupdates where artid = @artid and
identity_support = 1)
begin
raiserror(21235, 16, -1, @property)
goto UNDO
end
declare @range bigint
select @range = convert(bigint, @value)
if @range < 0
begin
raiserror(21232, 16, -1)
goto UNDO
end
if exists (select * from MSpub_identity_range where objid = @objid and
range < 0)
select @range = @range * -1
update MSpub_identity_range set
range = @range
where objid=@objid
if @@error < 0
goto UNDO
-- Distributor side data will be changed later by sp_MSchange_article.
end
-- Check to see if the range is too big.
-- Must be down after the change. If the check fails, the transaction
-- will be rolled back.
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('pub_identity_range', 'identity_range')
begin
declare @pub_identity_range bigint, @identity_range int
select @pub_identity_range = pub_range,
@identity_range = range from MSpub_identity_range where
objid = @objid
declare @xtype int, @xprec int, @max_range bigint
select @xtype=system_type_id, @xprec=precision from sys.columns where object_id=@objid and
is_identity=1
select @max_range =
case @xtype when 52 then power((convert(bigint,2)), 8*2-1) - 1 --smallint
when 48 then power((convert(bigint,2)), 8-1) - 1 --tinyint
when 56 then power((convert(bigint,2)), 8*4-1) - 1 --int
when 127 then power((convert(bigint,2)), 62) - 1 + power((convert(bigint,2)), 62) --bigint
when 108 then power((convert(bigint,10)), @xprec) --numeric
when 106 then power((convert(bigint,10)), @xprec) --decimal
else
power((convert(bigint,2)), 62) + power((convert(bigint,2)), 62) - 1 -- defaulted to bigint
end
declare @source_table nvarchar (517)
exec @retcode = sys.sp_MSget_qualified_name @objid, @source_table output
if @retcode <> 0 or @@error <> 0
goto UNDO
if @pub_identity_range * 2 + @identity_range > (@max_range - ISNULL(IDENT_CURRENT(@source_table), IDENT_SEED(@source_table)))
begin
raiserror(21290, 16, -1)
goto UNDO
end
end
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'threshold'
begin
if not exists (select * from sysarticleupdates where artid = @artid and
identity_support = 1)
begin
raiserror(21235, 16, -1, @property)
goto UNDO
end
declare @threshold bigint
select @threshold = convert(int, @value)
if @threshold < 1 or @threshold > 100
begin
raiserror(21233, 16, -1)
goto UNDO
end
update MSpub_identity_range set
threshold = @threshold
where objid=@objid
if @@error < 0
goto UNDO
-- Distributor side data will be changed later by sp_MSchange_article.
end
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'fire_triggers_on_snapshot'
begin
select @value = LOWER(@value collate SQL_Latin1_General_CP1_CS_AS)
if @value not in ('true', 'false')
begin
raiserror(14137, 16, -1)
goto UNDO
end
if @value = 'true'
select @fire_triggers_on_snapshot = 1
else
select @fire_triggers_on_snapshot = 0
update dbo.sysarticles
set fire_triggers_on_snapshot = @fire_triggers_on_snapshot
where artid = @artid
and pubid = @pubid
if @@error <> 0
goto UNDO
end
-- some info on articles is also stored at the distributor.
-- update info at distributor if these properties change
if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ( N'description', N'dest_table', N'dest_object',
'identity_range', 'threshold', 'destination_owner', 'dest_owner' )
BEGIN
/*
** Get distribution server information for remote RPC call.
*/
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
GOTO UNDO
END
SELECT @dbname = DB_NAME()
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' +
QUOTENAME(@distribdb) + '.dbo.sp_MSchange_article'
EXECUTE @retcode = @distproc @publisher = @srvname,
@publisher_db = @dbname,
@publication = @publication,
@article = @article,
@article_id = @artid,
@property = @property,
@value = @value
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
END
-- Have to call this stored procedure to invalidate existing snapshot or reint
-- subscriptions if needed
EXECUTE @retcode = sys.sp_MSreinit_article
@publication = @publication,
@article = @article,
@need_new_snapshot = @need_new_snapshot,
@need_reinit_subscription = @need_reinit_subscription,
@force_invalidate_snapshot = @force_invalidate_snapshot,
@force_reinit_subscription = @force_reinit_subscription,
@publisher = @publisher,
@publisher_type = @publisher_type
IF @@ERROR <> 0 OR @retcode <> 0
GOTO UNDO
if @backward_comp_level > 10
update syspublications set backward_comp_level = @backward_comp_level where pubid = @pubid
IF @increment_min_autonosync_lsn = 1
BEGIN
EXEC @retcode = sys.sp_MSincrementpublicationminautonosynclsn
@publication = @publication
IF @@ERROR <> 0 OR @retcode <> 0 BEGIN SELECT @retcode = 1 GOTO UNDO END
END
-- If the change impacts how the heterogeneous log reader processes change commands
-- call sp_IHreplflush to force a reload of the log reader's article cache.
-- This update can be performed within the transaction.
IF @publisher_type != N'MSSQLSERVER'
AND @property IN ( 'ins_cmd', 'del_cmd', 'upd_cmd', 'dest_table', 'destination_owner', 'status', 'dest_object')
BEGIN
EXEC @retcode = sys.sp_IHreplflush @publisher
IF @@ERROR <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END
END
COMMIT TRAN
/*
** Force the article cache to be refreshed with the new definition.
*/
EXECUTE sys.sp_replflush
/*
** Return succeed.
*/
RAISERROR (14025, 10, -1)
RETURN (0)
UNDO:
if @@TRANCOUNT > 0
begin
ROLLBACK TRAN sp_changearticle
COMMIT TRAN
end
return(1)
END
