Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MStran_altertable

  No additional text.


Syntax
create procedure sys.sp_MStran_altertable
(
	@qual_object_name nvarchar(512) --owner qualified name			
	,@objid	int
	,@pass_through_scripts nvarchar(max)
	,@target_object_name nvarchar(512) = NULL
)
AS
	set nocount on

	declare @retcode int
			,@temp_ddlcmds nvarchar(max)
			,@subtype int
			,@schema_start_lsn binary(10)
			,@schema_end_lsn binary(10)
			,@dropped_column_counts int
			,@dropped_artcolumn_counts int
			,@break_add_into_singles bit
			,@break_drop_into_singles bit
			,@break_artdrop_into_singles bit
			,@debug_print bit
			,@is_biton bit
			,@timestampcol_column_id int
			,@addtimestampcol sysname
			,@addidentitycol sysname
			,@identityNFR bit
			,@operation varchar(16)
			,@mode int
			,@checkpartition_mask int
			,@checkadds_mask int
			,@switch_targettable sysname
			,@switch_targetobjid int
			,@art_objid int
			--enum of constants
			,@subtype_addcolumn int
			,@subtype_dropcolumn int
			,@subtype_altercolumn int
			,@subtype_disabletrigger int
			,@subtype_enabletrigger int
			,@subtype_disabletriggerall int
			,@subtype_enabletriggerall int
			,@subtype_dropconstraint int
			,@subtype_enableconstraint int
			,@subtype_disableconstraint int
			,@subtype_enableconstraintall int
			,@subtype_disableconstraintall int
			,@subtype_addfk int
			,@subtype_addun int
			,@subtype_addchk int
			,@subtype_adddf int
			,@subtype_switch int
			,@include_ddl int
			,@bcp_char int
			,@concurrent_char int
			,@subscription_active int
			,@p2p_option int
			,@p2p_cd_option int = 0x8
			
			--article/subscription property
			,@colname nvarchar(1000)
			,@tran_artid int
			,@artname nvarchar(1000)
			,@qual_dest_object nvarchar(1000)
			,@qual_p2p_conflict_table nvarchar(1000) = NULL
			,@schema_option bigint
			,@art_status int
			,@sub_status int
			,@refresh_synctran_procs int
			,@dest_owner nvarchar(1000)
			,@Servername sysname
			,@DestDB sysname

			--publication property
			,@pubid int
			,@pubname nvarchar(1000)
			,@sync_method int
			,@replicate_ddl int
			,@tran_pub_options int = 0
			,@OPT_ALLOW_PARTITION_SWITCH int = 0x20
			,@OPT_REPLICATE_PARTITION_SWITCH int = 0x40


	--sp_repladd/dropcolumn sets session context info as such so we
	--know if we came from there, nothing need to be done
	--in that case since sp_repladd/dropcolumn already handle all the
	--logic similiar to here.
	exec @retcode = sys.sp_MScheckcontext_bypasswholeddleventbit @is_biton OUTPUT
	if @retcode <>0 or @@ERROR<>0
		return 1
	-- we should by pass
	if @is_biton=1
		return 0

	--set const, @subtype_dropcolumn should have the smallest const, @subtype_addcolumn should be the biggest
	--so when we order by type desc, addcolumn goes first, dropcolumn goes at the end
	select @include_ddl = 0x1
		,@subtype_addcolumn = 0x1 					--N'ADDCOLUMN'
		,@subtype_altercolumn = 0x2					--N'ALTERCOLUMN'
		,@subtype_addfk = 0x4								--N'ADDREFERENCE'
		,@subtype_addun = 0x8							--N'ADDUNIQUE'
		,@subtype_addchk = 0x10							--N'ADDCHECK'
		,@subtype_adddf = 0x20							--N'ADDDEFAULT'
		,@subtype_disabletrigger = 0x40				--N'DISABLETRIGGER'
		,@subtype_enabletrigger = 0x80				--N'ENABLETRIGGER'
		,@subtype_disabletriggerall = 0x100		--N'DISABLETRIGGER'
		,@subtype_enabletriggerall = 0x200		--N'ENABLETRIGGER'
		,@subtype_dropconstraint = 0x400			--N'DROPCONSTRAINT'
		,@subtype_enableconstraint = 0x800		--N'ENABLECONSTRAINT'
		,@subtype_disableconstraint = 0x1000		--N'DISABLECONSTRAINT'
		,@subtype_enableconstraintall = 0x2000	--N'ENABLECONSTRAINT'
		,@subtype_disableconstraintall = 0x4000	--N'DISABLECONSTRAINT'
		,@subtype_dropcolumn = 0x8000					--N'DROPCOLUMN'
		,@subtype_switch = 0x10000				--N'SWITCH'
		,@subscription_active = 2
		,@bcp_char = 1
		,@p2p_option = 0x1
		
	--initialize variables
	select @sync_method = 0
		,@retcode = 0
		,@subtype=0
		,@art_status = 0
		,@sub_status = 0
		,@break_add_into_singles = 0
		,@dropped_column_counts = 0
		,@schema_option = 0
		,@timestampcol_column_id = NULL
		,@addtimestampcol = NULL
		,@addidentitycol = NULL
		,@identityNFR = 0
		,@refresh_synctran_procs = 1
		
	if object_id('MSrepl_debug_DDL') is not null
		set @debug_print = 1
	else
		set @debug_print = 0
		
	create table #tran_altertable(id int identity,
				DDLsubtype sysname collate database_default,
				TableOwner sysname collate database_default,
				TableName sysname collate database_default,
				ColumnName sysname collate database_default NULL,
				ColumnAttr sysname collate database_default NULL,
				EnumType int NULL,
				column_id int NULL,
				check_nocheck sysname collate database_default default N'',
				stmt nvarchar(max) collate database_default NULL,
				reftable nvarchar(max) collate database_default NULL,
				refcollist nvarchar(max) collate database_default NULL,
				refcolcount int NULL,
				stmtend nvarchar(512) collate database_default NULL,
				inpartition bit default 0)
	if @@ERROR <> 0
	begin
		return 1
	end					

	--set up @temp_ddlcmds to feed to sp_replddlparser,
	--note this may not be the command we post at the end
	set @temp_ddlcmds = N'ALTER table '
									+ @qual_object_name + N' '
									+ @pass_through_scripts
	if @debug_print = 1
		select 'stage' = 'sp_replddlparser: feed', '@temp_ddlcmds' = @temp_ddlcmds

	insert #tran_altertable ( DDLsubtype, TableOwner, TableName, ColumnName, ColumnAttr )
		exec sys.sp_replddlparser @ddlCmd = @temp_ddlcmds

	if @@ERROR <> 0
	begin
		return 1
	end	
--first lets handle all the 'do nothing cases':

	--check on 'alter table enable trigger' only if we have active articles with @schema_option & 0x100 = 0x100
	--note: we can safely do this with trigger, but not so with constraints, as you can add constraints while adding a column
	--note: don't let disble trigger go freely, we need to make sure it does not disable sync_upd trg for updateable publication
	if not exists (SELECT A.artid FROM dbo.sysarticles A
						join dbo.syssubscriptions S on A.artid = S.artid
						WHERE A.objid = @objid	
						and S.status = @subscription_active  -- active
						and cast(A.schema_option as int) & 256 = 256
					)
		and not exists (select * from #tran_altertable where DDLsubtype <> N'ENABLETRIGGER')
	begin
		return 0
	end

	--if table has no active subscription and DDL does not add/drop column or switch partition,  don't do anything
	--note 'alter column' does not change sysarticlecolumns
	if not exists (SELECT A.artid FROM dbo.sysarticles A
						join dbo.syssubscriptions S on A.artid = S.artid
						WHERE A.objid = @objid	
						and S.status = @subscription_active  -- active
					)
		and not exists (select * from #tran_altertable where DDLsubtype in( N'ADDCOLUMN', N'DROPCOLUMN', N'ADDCHECK', N'ADDDEFAULT', N'SWITCH'))
	begin
		return 0
	end

	--if adding msrepl_tran_version column to updatable table,  don't do anything
	if 	exists( select * from #tran_altertable ta
						where ta.ColumnName = 'msrepl_tran_version'
								and ta.DDLsubtype = N'ADDCOLUMN' collate database_default)
		and exists( select * from syspublications sp join sysarticles sar on sp.pubid = sar.pubid
						where (sp.allow_sync_tran = 1 or sp.allow_queued_tran = 1)
								and sar.objid = @objid)
	begin
		return 0
	end

    -- special case for change tracking ddl
    --   We're only capturing the ALTER TABLE for change tracking, and if the subscriber has not manually
    --   enabled it via alter database, the application will fail here.  Since this is likely the normal case
    --   we'll skip the replication of this particular DDL statement.
    if exists (select * from #tran_altertable
        where DDLsubtype in (N'ENABLECHANGETRACK', N'DISABLECHANGETRACK'))
    begin
        return 0
    end

 -- NO NEED TO HANDLE ALTER COLUM ADD/DROP SPARSE UNLESS SPARSENESS IS BEING REPLICATED
	if not exists (SELECT A.artid FROM dbo.sysarticles A
						join dbo.syssubscriptions S on A.artid = S.artid
						WHERE A.objid = @objid	
						and cast(A.schema_option as bigint) & 0x0000020000000000 = 0x0000020000000000
					)
		and not exists (select * from #tran_altertable where DDLsubtype not in (N'ENABLESPARSE', N'DISABLESPARSE'))
	begin
		return 0
	end


    -- Don't want to replicate alter table rebuild statements
    -- so skip if the DDLsubtype is REBUILDTABLE
    if exists (select * from #tran_altertable
            where DDLsubtype = N'REBUILDTABLE')
    begin
        return 0
    end

	--If switch partition
	if exists( select * from #tran_altertable ta
						where ta.DDLsubtype = N'SWITCH')
	begin
		select @switch_targettable = ColumnName from #tran_altertable
		select @switch_targetobjid = object_id(@switch_targettable)
		-- If neither table is published, don't do anything
		if not exists (SELECT * FROM dbo.sysarticles
						WHERE objid = @objid OR objid = @switch_targetobjid)
		begin
			return 0
		end

		-- If any of the publications for the objects in question disallow partition switch, error out
		if exists (select * from sysarticles a join syspublications p on a.pubid = p.pubid
					where (a.objid = @objid or a.objid = @switch_targetobjid) and p.options & @OPT_ALLOW_PARTITION_SWITCH != @OPT_ALLOW_PARTITION_SWITCH)
		begin
			RAISERROR (21867, 16, -1, @qual_object_name)
			return 1
		end

		-- Here we know all publications are set to allow partition switching, we'll do a quick check to see if none of them
		--  are set to replicate the switch, if this is the case then we can just exit.
		else if not exists (select * from sysarticles a join syspublications p on a.pubid = p.pubid
					where (a.objid = @objid or a.objid = @switch_targetobjid) and p.options & @OPT_REPLICATE_PARTITION_SWITCH = @OPT_REPLICATE_PARTITION_SWITCH)
		begin
			-- allow the switch, but no-op from our side
			return 0
		end

		-- Determine whether or not the target table is in article which has renamed the destination, if so
		--  block the DDL, as we cannot support this with replicate_partition_switch at this time
		if exists (select * from sysarticles sa
			where (dest_table != object_name(objid)
				or dest_owner != object_schema_name(objid))
				and (objid = @objid or objid = @switch_targetobjid))
		begin
			RAISERROR (21868, 16, -1)
			return 1
		end
	end

--second all the rejected cases:
	begin transaction replddl_altertable
	save transaction replddl_altertable
	
	--set the numeric field to represent subtype, hopefully this makes future comparasion faster
	update #tran_altertable set EnumType = case DDLsubtype when N'ADDCOLUMN' then @subtype_addcolumn
																				when N'DROPCOLUMN' then @subtype_dropcolumn
																				when N'ALTERCOLUMN' then @subtype_altercolumn
																				when N'ADDREFERENCE' then @subtype_addfk
																				when N'ADDUNIQUE' then @subtype_addun
																				when N'ADDCHECK' then @subtype_addchk
																				when N'ADDDEFAULT' then @subtype_adddf
																				when N'SWITCH' then @subtype_switch
																				when N'DISABLETRIGGER' then
																					case len(isnull(ColumnName, '') )
																						when 0 then @subtype_disabletriggerall
																						else @subtype_disabletrigger
																					end
																				when N'ENABLETRIGGER' then
																					case len(isnull(ColumnName, '') )
																						when 0 then @subtype_enabletriggerall
																						else @subtype_enabletrigger
																					end
																				when N'DROPCONSTRAINT' then @subtype_dropconstraint
																				when N'ENABLECONSTRAINT' then
																					case len(isnull(ColumnName, '') )
																						when 0 then @subtype_enableconstraintall
																						else @subtype_enableconstraint
																					end
																				when N'DISABLECONSTRAINT' then
																					case len(isnull(ColumnName, '') )
																						when 0 then @subtype_disableconstraintall
																						else @subtype_disableconstraint
																					end
																		end
																		
	if (@@ERROR <> 0)
	begin
		return 1
	end	

	--create a bitmask to represent all operations in this DDL
	select @subtype = sum(distinct EnumType) 	from #tran_altertable

	--find out if we are adding timestamp or if our table has one already
	select @timestampcol_column_id = sc.column_id
				,@addtimestampcol = ta.ColumnName
						from
						sys.columns sc left outer join #tran_altertable ta on sc.name = ta.ColumnName
						where object_id = @objid
									and lower(type_name(system_type_id)) = N'timestamp'

	--find out if we are adding identity column
	select @addidentitycol = ta.ColumnName
				,@identityNFR = ColumnProperty(@objid, ta.ColumnName, 'IsIdNotForRepl')
						from
						sys.columns sc join #tran_altertable ta on sc.name = ta.ColumnName
						where object_id = @objid
									and ColumnProperty(@objid, ta.ColumnName, 'IsIdentity') = 1


	--can't drop msrepl_tran_version from updatable table,
	--disallow 'alter table disable trigger all' if table belongs to updateable publications
	--disallow 'alter table disable trigger ' on MS trigger added by updateable publication
	if  exists( select * from syspublications sp join sysarticles sar on sp.pubid = sar.pubid
						where (sp.allow_sync_tran = 1 or sp.allow_queued_tran = 1)
								and sar.objid = @objid)
	begin
		if 	exists( select * from #tran_altertable ta
						where ta.ColumnName = 'msrepl_tran_version'
								and ta.EnumType = @subtype_dropcolumn)
		begin
	        RAISERROR (21080, 16, -1)
			goto FAILURE
		end

		if 	(@subtype & @subtype_disabletriggerall) > 0
		begin
      		RAISERROR (21812, 16, -1, @qual_object_name)
			goto FAILURE
		end
		else 	if 	(@subtype & @subtype_disabletrigger) > 0
		begin
			select @colname = object_name(sau.sync_upd_trig) from #tran_altertable ta
						join sysarticleupdates sau on object_id(ta.ColumnName) = sau.sync_upd_trig
						join syspublications sp on sau.pubid = sp.pubid
						join sysarticles sar on sp.pubid = sar.pubid and sau.artid = sar.artid
						where ta.EnumType  = @subtype_disabletrigger
							 and (sp.allow_sync_tran = 1 or sp.allow_queued_tran = 1)
							and sar.objid = @objid
			if @colname is not null
			begin
        		RAISERROR (21813, 16, -1, @colname, @qual_object_name)
				goto FAILURE
			end
			
		end
	end

	-- do not allow add/alter of a timestamp col in P2P
	-- mode when article will replicate it as binary(8)
	if (@addtimestampcol is not null)
		and exists(select * from syspublications sp join sysarticles sar on sp.pubid = sar.pubid
						where sp.options & @p2p_option = 1
								and sar.objid = @objid
								and sys.fn_replgetbinary8lodword(schema_option) & 0x00000008 = 0)
	begin
		-- Peer-To-Peer publications do not support replicating timestamp columns as varbinary(8). Adding an article with this option or adding/altering a table to include a timestamp column as varbinary(8) is not allowed.
        RAISERROR (21734, 16, -1)
		goto FAILURE
	end
		
	--per logic in sp_addarticle, NFR identity column can not live in immediate_sync article
	--while identitymanagementoption is 'none'
	--note *auto* articles will force NFR even if column was added without, hence the or logic
	if (@addidentitycol is not NULL)
		and
		exists (select * from syspublications sp join sysarticles sar on sp.pubid = sar.pubid
						where sp.allow_sync_tran = 1 and sar.objid = @objid
								and sys.fn_replgetbinary8lodword(schema_option) & 0x4 = 0)
		and
		(@identityNFR = 1
		or --note *auto* articles will force NFR even if column was added without, hence this logic
		exists (SELECT * FROM dbo.sysarticles
						WHERE objid = @objid	
						and sys.fn_replgetbinary8lodword(schema_option) & 0x4 =0x4)
		 )
	begin
           raiserror (21766, 16, 1, @qual_object_name)
			goto FAILURE
	end

--third all the cases requiring single cmd logic:

	--adding constraints without name, switch to single cmd mode
	--now that we can query catelog view for constraints name, update the parser resultset so later join on name will work
	if exists( select * from #tran_altertable ta
			where (ta.EnumType & (@subtype_addun | @subtype_addfk |@subtype_addchk) > 0)
					and len(isnull(ta.ColumnName, '') )= 0)
	begin
		set @break_add_into_singles = 1
		exec @retcode = sys.sp_MSfixup_constraints_name @subtype_addun = @subtype_addun
																									,@subtype_addfk = @subtype_addfk
																									,@subtype_addchk = @subtype_addchk
																									,@objid = @objid
																									,@debug_print = @debug_print
		if @retcode <>0 or @@ERROR<>0
				goto FAILURE
		--don't goto SETSINGLE yet, may need to fill blanks into default + check on identity stuff
	end

	--adding default without name, switch to single cmd mode
	--now that we can query catelog view for constraints name, update the parser resultset so later join on name will work
	if exists( select * from #tran_altertable ta
			where ta.EnumType  = @subtype_adddf
					and (len(isnull(ta.ColumnName, '') )= 0 or
							ColumnName collate database_default in
							(select name from sys.columns where object_id = @objid)))
	begin
		set @break_add_into_singles = 1
		exec @retcode = sys.sp_MSfixup_defaults_name @subtype_adddf = @subtype_adddf
																									,@subtype_addcolumn = @subtype_addcolumn
																									,@objid = @objid
																									,@debug_print = @debug_print
		if @retcode <>0 or @@ERROR<>0
				goto FAILURE
		--don't goto SETSINGLE yet, still need to check on identity stuff
	end

	--build a temp table with all base columns for constraints/computed columns added in this DDL
	create table #basecol_cnst (indexcol int identity
												,type int
												,constraint_name		sysname collate database_default
												,constraint_column_name sysname collate database_default
												,constraint_column_id int)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end		
	exec @retcode = sys.sp_MSfixup_base_columns @subtype_addcolumn = @subtype_addcolumn
																						,@subtype_adddf = @subtype_adddf
																					    ,@objid = @objid
																					    ,@break_add_into_singles = @break_add_into_singles output
																						,@subtype = @subtype
																						,@subscription_active = @subscription_active
																					    ,@debug_print = @debug_print
	if @retcode <>0 or @@ERROR<>0
			goto FAILURE

	--if the identity column was not added as NFR, set it now
	--only do this if at least one article is marked to use 'manual' or 'auto'
	if (@addidentitycol is not NULL	and @identityNFR = 0)
			and
			exists (SELECT * FROM dbo.sysarticles
							WHERE objid = @objid	
							and sys.fn_replgetbinary8lodword(schema_option) & 0x4 =0x4)
	begin
		EXEC %%ColumnEx(ObjectID = @objid, Name =@addidentitycol ).SetIdentityNotForRepl(Value = 1)
		IF @@ERROR <> 0
			goto FAILURE
		set @break_add_into_singles = 1
	end

	-- when p2p conflict detection is on, break into single cmd mode
	-- this way logic to alter conflict table is simpler
	if exists(select * from syspublications sp join sysarticles sar on sp.pubid = sar.pubid
						where sp.options & @p2p_cd_option = @p2p_cd_option
								and sar.objid = @objid)
	begin
		-- generate the conflict table name
		exec @retcode = sp_MSgetpeerconflictname @prefix = N'conflict', @tabid = @objid, @peerconflictname = @qual_p2p_conflict_table output
		IF @@ERROR <> 0 or @retcode <> 0
			goto FAILURE

		select @qual_p2p_conflict_table = N'[dbo].' + QUOTENAME(@qual_p2p_conflict_table)
		
		set @break_add_into_singles = 1
	end

	if (@break_add_into_singles = 1)
		goto SETSINGLE
	
	-- adding fk, switch to single cmd mode in case of three part referenced table name
	-- otherwise we'd minimumly need to check for cascade del/upd without NFR clause
	
	-- adding unique constraint with  'index option', need to switch to single command mode and strip off 'index option'
	
	-- check theseafter sys.sp_MSfixup_*_name though otherwise empty names aren't get udpated!
	
	if ((@subtype & @subtype_addfk) > 0
		or exists(select * from #tran_altertable where ColumnAttr = N'INDEX OPTIONS'))
	or
	--max, xml or timestamp column
	--katmai date\time types
	exists (select * from sys.columns col join sys.types typ on typ.user_type_id = col.system_type_id
			join #tran_altertable ta on col.name = ta.ColumnName
			where col.object_id = @objid and (ta.EnumType & (@subtype_addcolumn | @subtype_altercolumn) > 0) and
					(typ.name in (N'varchar', N'nvarchar', N'varbinary') and col.max_length = -1
					or typ.name in (N'xml', N'bigint', N'sql_variant')
					or typ.name in (N'date', N'time', N'datetime2', N'datetimeoffset')))
	--udt
	or
	exists (select * from sys.columns col join sys.types typ on typ.user_type_id = col.user_type_id
			join #tran_altertable ta on col.name = ta.ColumnName
			where col.object_id = @objid
					and (ta.EnumType & (@subtype_addcolumn | @subtype_altercolumn) > 0)
					and (typ.system_type_id = 240 or (col.user_type_id != col.system_type_id)))	

	--sparse columns
	or
	exists (select * from sys.columns col
			join #tran_altertable ta on col.name = ta.ColumnName
			where col.object_id = @objid
					and col.is_sparse = 1
					and ta.DDLsubtype <> N'ENABLESPARSE')
	begin
		set @break_add_into_singles = 1
		goto SETSINGLE
	end

SETSINGLE:

	if(@break_add_into_singles = 1)
	begin
		exec @retcode = sys.sp_MSfixup_single_ddls @objid = @objid
																						,@subtype_addcolumn = @subtype_addcolumn
																						,@subtype_addun = @subtype_addun
																						,@subtype_addchk = @subtype_addchk
																						,@subtype_adddf = @subtype_adddf
																						,@subtype_addfk = @subtype_addfk
																						,@debug_print = @debug_print
		if @retcode <>0 or @@ERROR<>0
				goto FAILURE
	end

	--if DDL contains the following actions, break into single command so we can add 'if exists' check
	--reason being we don't know for sure if these constrains/triggers live on subscriber or not
	--note enable/disabletriggerall and enable/disableconstraintall should go to the default path, not here

	--do this for drop/alter column as well since replicate_ddl does not apply to these DDL and user may have customerized their subscriber to not have these columns
	if 	(@subtype & (@subtype_dropconstraint | @subtype_enabletrigger |
								@subtype_dropcolumn | @subtype_altercolumn |
										@subtype_disabletrigger | @subtype_enableconstraint | @subtype_disableconstraint) > 0)
			set @break_drop_into_singles = 1				
	else
			set @break_drop_into_singles = 0

	select @dropped_column_counts = count(*) from #tran_altertable where EnumType = @subtype_dropcolumn
	
--now let's start the alter logic

	create table #tran_columnstable (colname sysname collate database_default, colid int)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end			

	--create a copy of sysarticlecolumns to hold colid for a given article id, so we can fake
	--a row for timestamp column if necessary
	--this will make it easier and more accurate to join and find out if all bases are in partition
	create table #artcols (artid int, colid int)
	if @@ERROR <> 0
	begin
		goto FAILURE
	end			
	insert #artcols (artid, colid) select distinct artid, colid from sysarticlecolumns

	--get exclusive app lock on publications so to avoid concurrency issue with ccsnapshot
    exec @retcode = sys.sp_MSprep_exclusive @qual_object_name, @objid
    if @@ERROR<>0 or @retcode <> 0
        goto FAILURE

	--loop through relavent publications/articles
	DECLARE #trancolumn CURSOR LOCAL FAST_FORWARD FOR
		select distinct 	a.artid 																				-- article id
								,a.name 																			-- article name
								,p.name 																			-- pulication name
								,p.pubid 																			-- publication id
								,p.replicate_ddl 																-- replicate DDL or not
								,p.sync_method 																-- native or char BCP
								,a.status																			-- article status
								,convert(bigint, schema_option) 	-- article schema option
								,case len(isnull(a.dest_owner, '')) when 0 then N''
																						else quotename(a.dest_owner) + N'.'
																						end
								+ case len(isnull(a.dest_table, '')) when 0 then @qual_object_name
																						else quotename(a.dest_table)
																						end
								,a.dest_owner
								,p.options
								,a.objid
			from sysarticles a
			join syspublications p on a.pubid = p.pubid
			where a.objid=@objid
			or ((@subtype & @subtype_switch) > 0 and @switch_targetobjid is not null and a.objid=@switch_targetobjid)

	OPEN #trancolumn
	FETCH #trancolumn INTO @tran_artid
										,@artname
										,@pubname
										,@pubid
										,@replicate_ddl
										,@sync_method
										,@art_status
										,@schema_option
										,@qual_dest_object
										,@dest_owner
										,@tran_pub_options
										,@art_objid
	WHILE (@@fetch_status <> -1)
	BEGIN
		-- if publication does not replicate DDL, and it's not a drop/alter column action, goto next publication
		-- always replicate drop/alter column as long as partition has it
		if(	(@replicate_ddl & @include_ddl <> @include_ddl)  and
			(@subtype & (@subtype_dropcolumn | @subtype_altercolumn) = 0))

		--alter table enable/disable trigger, don't post anything unless the trigger was replicated by snapshot
		--should be safe to do it here, it's not possible to add/drop/alter column and enable/disable trigger
		--in the same DDL anyway
			or (@subtype & (@subtype_enabletrigger | @subtype_enabletriggerall |										@subtype_disabletrigger | @subtype_disabletriggerall) > 0
						and @schema_option & 0x0000000000000100 = 0)
		-- If this is a partition switch, we know by this point that allow_partition_switch = 1, so we check
		--  whether or not we're going to replicate it, if not we just continue to the next publication
			or ((@subtype & @subtype_switch) > 0 and @tran_pub_options & @OPT_REPLICATE_PARTITION_SWITCH != @OPT_REPLICATE_PARTITION_SWITCH)
		begin
			goto FETCHMORE
		end 		

		if exists(select * from syssubscriptions where artid = @tran_artid and status =  @subscription_active)
			select @sub_status = @subscription_active
		else
			select @sub_status = @sub_status & ~@subscription_active
			
		-- #tran_columnstable to hold affected colid for add/drop, this table is accessed by
		-- sp_articlecolumn to update sysarticlecolumns
		-- sp_addsynctriggers to script calls to update sysarticlecolumns on subscriber (for queued)
		truncate table #tran_columnstable

		--reset inpartition bit for current article
		update #tran_altertable set inpartition = 0		
		
		--reset local variable to indicate whether DDL dropped any column from current partition
		select @dropped_artcolumn_counts  = 0
				,@break_artdrop_into_singles = 0
		
		--add column, populate #tran_columnstable with columns in #tran_altertable
		if (@subtype & @subtype_addcolumn > 0)
		begin
			insert into #tran_columnstable select t.ColumnName, sc.column_id
						from #tran_altertable t
						join sys.columns sc on t.ColumnName = sc.name
						where t.EnumType = @subtype_addcolumn
						and sc.object_id = @objid
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN

			--if article is supposed to replicate timestamp as timestamp,
			--but it didn't have a timestamp column before current DDL, must check and see if we are adding one
			if (@schema_option & 0x0000000000000008 = 0x0000000000000008 and @art_status & 32 <> 32)
				and @timestampcol_column_id is not null
			begin
				update sysarticles set status = status | 32	where artid = @tran_artid and objid = @objid
			end		
			-- Special check for identity based article with SQL call format
			-- column specification bit 8 in status should be set - this is needed
			-- Shiloh server for NFR processing during insert
			
			if (@addidentitycol is not NULL)
				update sysarticles set status = status | 8 where artid = @tran_artid and objid = @objid
									and upper(ins_cmd) = N'SQL'  and (status & 8 != 8)

		end
		--drop column, populate #tran_columnstable with diffs between sysarticlecolumns and sys.columns
		else if (@subtype & @subtype_dropcolumn > 0)
		begin
			insert into #tran_columnstable select N'', sac.colid
								from sysarticlecolumns sac
								where sac.artid = @tran_artid
								and sac.colid not in (select column_id from sys.columns where object_id = @objid)
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN

			select @dropped_artcolumn_counts = count(*) from #tran_columnstable
	
			--articles who replicate timestamp as timestamp does do by some special logic where
			--timestamp column is not added in article partition according to sysarticlecolumn
			--when drop happens we need to figure out whether timestamp column was being dropped
			if (@schema_option & 0x0000000000000008 = 0x0000000000000008 and @art_status & 32 = 32)
				and @timestampcol_column_id is null
			begin
				update sysarticles set status = status & ~32 where artid = @tran_artid and objid = @objid
				select @dropped_artcolumn_counts = @dropped_artcolumn_counts + 1
				--just in case system  is
				set @break_artdrop_into_singles = 1
				set @refresh_synctran_procs = 2
			end
			--if our partition does not contain the columns being dropped here
			--and if the same DDL cmd does not drop any constraint, we are done for this article

			if (@break_drop_into_singles = 0) -- meaning DDL does not contain dropconstraints
			begin
				if (@dropped_artcolumn_counts = 0)
				begin
					--if we didn't drop any column for this article, and there is no dropconstriants here, go to next article
					goto FETCHMORE
				end
				-- dropped some column from this article, now find out if it dropped anything not in this article
				-- so we know whether the original DDL can be sent as such or broken down to singles,
				-- note this only matters if there is no dropconstraint, otherwise we have to break into singles
				else if @dropped_column_counts > @dropped_artcolumn_counts
				begin
					set @break_artdrop_into_singles = 1
				end
			end
		end
		--alter column, no need to change sysarticlecolumns on pub/sub side
		--but we do need to refresh proc/trigger/conf table on publisher for updateable pubs
		else if @subtype & @subtype_altercolumn > 0
		begin
			--can't alter timestamp column anyway, no need to worry about it here
			insert into #tran_columnstable select t.ColumnName, sc.column_id
						from #tran_altertable t
						join sys.columns sc on t.ColumnName = sc.name
						join sysarticlecolumns sac on sac.colid = sc.column_id
						where t.EnumType = @subtype_altercolumn
						and sc.object_id = @objid and sac.artid = @tran_artid
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN

			--if alter column does not concern this article, go to next one
			--assuming alter column can not live in the same DDL as others
			if not exists (select * from #tran_columnstable)
			begin		
				goto FETCHMORE
			end

			if exists (select * from sysarticlecolumns sac
										join #tran_columnstable tc on sac.colid = tc.colid
							where sac.artid = @tran_artid and
										(sac.is_xml = 1 or sac.is_max = 1 or sac.is_udt = 1))
			begin
				--can not alter udt/xml/max if mapped to base
				if(	@sub_status = @subscription_active
					and exists (select * from sysarticlecolumns sac
											join #tran_columnstable tc on sac.colid = tc.colid
								where sac.artid = @tran_artid and
											((sac.is_xml = 1 and @schema_option & 0x0000000010000000 > 0) --map xml to ntext
											or (sac.is_udt = 1 and @schema_option & 0x0000003000000020 > 0) --map udt to image, ludt to vbmax, hierarchyid to vbmax
											or (sac.is_max = 1 and @schema_option & 0x0000000020000000 > 0)) ))--map max to blob
					begin
		        		RAISERROR (21844, 16, -1, @artname)
						goto DROPTRAN
					end
			end
			--otherwise be sure to update sysarticlecolumns to these bits flag are kept to date.
			update sysarticlecolumns set is_udt = 	case typ.system_type_id when 240 then 1 else 0 end
															,is_xml =	case typ.name when N'xml' then 1 else 0 end
															,is_max = case when typ.name in (N'varchar', N'nvarchar', N'varbinary') and  col.max_length = -1 then 1 else 0 end																
					from sysarticlecolumns sac
							join sys.columns col on col.column_id = sac.colid
							join sys.types typ on typ.user_type_id = col.system_type_id or (typ.system_type_id = 240 and typ.user_type_id = col.user_type_id)
							where col.object_id = @objid and sac.artid = @tran_artid
		end
		else if (@subtype & @subtype_switch > 0)
		begin
			-- We have three cases to deal with here:
			--  Source and Destination Table are both published - we use the source article id, and supress for the destination article
			--  Source table only is published - we use the source article id
			--  Destination table only is published - we use the destination article id
			
			-- Check to see if both the source and destination tables are in the same publication and if we're viewing the target tables
			--  row in sysarticles, if so, we know that we'll post the command for the source table so we ignore it
			if @art_objid != @objid
				and exists (select * from dbo.sysarticles a
						inner join dbo.syspublications p on a.pubid = p.pubid
						where p.pubid=@pubid and a.objid=@objid and p.options & @OPT_REPLICATE_PARTITION_SWITCH = @OPT_REPLICATE_PARTITION_SWITCH)
			begin
				goto FETCHMORE
			end

			--Check to see if there are any active subscribers, if so, then post DDL to log, if not, return 0
			if exists (SELECT * FROM dbo.sysarticles A join dbo.syssubscriptions S on A.artid = S.artid
							WHERE A.artid = @tran_artid and S.status = @subscription_active and S.srvid<>-1)
				begin
					if @art_objid != @objid
					begin
						-- We don't support renamed tables right now, so usage of @art_objid is fine here
						select @qual_dest_object = quotename(object_schema_name(@objid)) + N'.' + quotename(object_name(@objid))
					end

					select @temp_ddlcmds = N'ALTER TABLE '
									+ @qual_dest_object + N' '
									+ @pass_through_scripts
					exec @retcode = sys.sp_MStran_autoproc  @artid = @tran_artid
														,@mode = 0
				  										,@ddlcmd = @temp_ddlcmds
					goto FETCHMORE
				end
			goto FETCHMORE
		end
		-- add/drop/alter column update article metadata
		if (@dropped_artcolumn_counts  > 0
			or
			@subtype & (@subtype_addcolumn |@subtype_altercolumn) > 0)
		begin
			-- add/drop column,
			-- overload is_biton to keep track of whether schema_start_lsn has been set
			-- do it here instead of outside of this if block, because it needs to happen before
			-- sp_MSrepl_articlecolumn, and it only needs to happen if sp_MSrepl_articlecolumn
			-- is going to be called for active articles.
			if (@is_biton = 0)
			and exists (select * from #tran_columnstable)
			and (@sub_status = @subscription_active)
			begin
					exec @retcode = sys.sp_replincrementlsn_internal @schema_start_lsn OUTPUT
					if @@ERROR<>0 or @retcode <> 0
					begin
						goto DROPTRAN
					end
					select @is_biton = 1
			end			

			-- sp_articlecolumn updates sysarticlecolumns base on #tran_columnstable,
			-- it also calls sp_articlesyncprocs with @alter = 1 to refresh the following
			-- objects on publisher for updatable subscriptions:
			--		sync_procs (ins, del, upd)
			--		sync_trigger
			--		conflict table
			--		conflict proc
			select @operation = case @dropped_artcolumn_counts
	 														when 0 then case @subtype & (@subtype_addcolumn |@subtype_altercolumn)
				 																		when @subtype_addcolumn then 'add'
									 													when @subtype_altercolumn then 'alter'
	 																			end
	 														else 'drop'
	 												end
			exec @retcode = sys.sp_MSrepl_articlecolumn 	@publication = @pubname
							,@article = @artname
							,@column = NULL
	 						,@operation = @operation
	 						,@refresh_synctran_procs = @refresh_synctran_procs
							,@change_active = 2
							,@force_invalidate_snapshot = 1
							,@force_reinit_subscription = 1
							,@internal = 1
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN	
		end

		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, @tran_artid)  = 1)
			begin
	           raiserror (21840, 16, 1, @qual_object_name, @artname)
				goto DROPTRAN
			end
		end
		-- no active subscription, just update articlecolumns for add/drop column, no need to reinit or post anything
		if @sub_status <> @subscription_active
		begin		
			goto FETCHMORE
		end

		--reinit if publication may have non-sql subscription, or adding timestamp column
		if @sync_method = @bcp_char
			or
			-- in case of add timestamp column, only reinit if user want it to be replicated as binary(8)
			( (@addtimestampcol is not NULL)
			and (@schema_option & 0x0000000000000008 = 0))
			or
			-- same idea, if identity is not replicated as identity, force reinit for the lack of column
			-- level snapshot
			( (@addidentitycol is not NULL)
			and (@schema_option & 0x0000000000000004 = 0))
		begin
           raiserror (21841, 10, 1, @pubname, @artname)
			exec @retcode = sys.sp_MSreinit_article
					@publication = @pubname
					,@article = @artname
					,@need_new_snapshot = 1
					,@need_reinit_subscription = 1
					,@force_invalidate_snapshot = 1
					,@force_reinit_subscription = 1

			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN

			--once we reinit the article, no need to post anything, let's go to next article
			goto FETCHMORE
		end

		--if replicating timestamp as timestamp, insert the column in #artcols if applicable
		--bugbug this query may bring back multiple rows of timestamp if computed column is used
		--I suspect it will break other things as well but come back here and handle this at least
		if(@schema_option & 0x0000000000000008 = 0x0000000000000008 and	@timestampcol_column_id is not null)
		begin
			insert #artcols (artid, colid) values ( @tran_artid, @timestampcol_column_id )
		end

		if(@debug_print = 1)
			select 'stage' = 'before set inpartition:', * from #tran_altertable
			
		update #tran_altertable set inpartition = 1 where ColumnName not in
			(select distinct constraint_name from #basecol_cnst
					where constraint_column_id not in (select colid from #artcols where artid = @tran_artid))
					
		if(@debug_print = 1)
			select 'stage' = 'after set inpartition:', * from #tran_altertable

	
		select @temp_ddlcmds = N''

		if (@break_add_into_singles = 1
			and @subtype & @subtype_addcolumn > 0)
		--always call fixup for altercolumn now that we always break alter into single cmd for existance check
		or (@subtype & @subtype_altercolumn > 0)
		begin
			exec @retcode = sys.sp_MSfixup_single_artddls @objid = @objid
																						,@schema_option = @schema_option
																						,@subtype_altercolumn = @subtype_altercolumn
																						,@subtype_addcolumn = @subtype_addcolumn
																						,@debug_print = @debug_print
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN
		end

		if ((@subtype & @subtype_dropcolumn ) > 0)
			and (@break_drop_into_singles = 0 )
			and (@break_artdrop_into_singles = 0)
		begin
			--if DDL is about dropping columns, with nothing fancy which reauires breaking DDL into individual cmds
			--the only pre-step is to drop defaults on these columns first (re. @drop_defaults_only = 1)
			exec @retcode = sys.sp_MSbuild_single_post @objid = @objid
													,@tran_artid = @tran_artid
													,@tran_pub_options = @tran_pub_options
													,@qual_dest_object = @qual_dest_object
													,@qual_p2p_conflict_table = @qual_p2p_conflict_table
													,@subtype_dropcolumn = @subtype_dropcolumn
													,@subtype_dropconstraint = @subtype_dropconstraint
													,@drop_defaults_only = 1
													,@debug_print = @debug_print
		end
		
		if (@break_drop_into_singles = 1)				-- containts drop/enable/disable constraints, enable/disable triggers
			or (@break_artdrop_into_singles = 1)	-- drops some column in our partition, some out, or timestamp column
			or (@break_add_into_singles = 1)
		begin
			select @checkpartition_mask = @subtype_enableconstraint |
																@subtype_disableconstraint |
																@subtype_addun |
																@subtype_addchk | @subtype_adddf
						,@checkadds_mask = @subtype_addcolumn | @subtype_altercolumn |
																@subtype_addun | @subtype_addchk | @subtype_adddf
			exec @retcode = sys.sp_MSbuild_single_post @objid = @objid
													,@tran_artid = @tran_artid
													,@tran_pub_options = @tran_pub_options
													,@qual_dest_object = @qual_dest_object
													,@qual_p2p_conflict_table = @qual_p2p_conflict_table
													,@checkpartition_mask = @checkpartition_mask
													,@checkadds_mask = @checkadds_mask
													,@subtype_dropcolumn = @subtype_dropcolumn
													,@subtype_dropconstraint = @subtype_dropconstraint
													,@subtype_enabletrigger = @subtype_enabletrigger
													,@subtype_disabletrigger = @subtype_disabletrigger
													,@subtype_enableconstraint = @subtype_enableconstraint
													,@subtype_disableconstraint = @subtype_disableconstraint
													,@subtype_altercolumn = @subtype_altercolumn
													,@dest_owner = @dest_owner
													,@debug_print = @debug_print
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN
			--add fk after adding all other constraints (such as un) so the order is not random,
			--otherwise if un which fk is depending on is created after fk, the fk will not get created
			exec @retcode = sys.sp_MSbuild_single_post @objid = @objid
													,@tran_artid = @tran_artid
													,@qual_dest_object = @qual_dest_object
													,@checkpartition_mask = @subtype_addfk
													,@subtype_addfk = @subtype_addfk
													,@debug_print = @debug_print
			if @retcode <>0 or @@ERROR<>0
				goto DROPTRAN
		end
		else
		begin
			-- set up the ddl command to be posted to the log
			select @temp_ddlcmds = N'ALTER TABLE '
									+ @qual_dest_object + N' '
									+ @pass_through_scripts
		end
		--question: how do we know if dropcolumn/altercolumn did not touch any column in our partition hence skip the post?
		--if altercolumn did not touch anything in current article partition, we'd long jumped to FETCHMORE without getting here
		--@dropped_artcolumn_counts tells us if any column was dropped from our partition
		select @mode = case @dropped_artcolumn_counts
										when 0 then
													case @subtype & (@subtype_addcolumn |@subtype_altercolumn)
																when 0 then 0
																else 1
														end
											else 1
									end

		exec @retcode = sys.sp_MStran_autoproc @artid = @tran_artid
												,@mode = @mode
				  								,@ddlcmd = @temp_ddlcmds

		if @retcode <>0 or @@ERROR<>0
			goto DROPTRAN

FETCHMORE:
	FETCH #trancolumn INTO @tran_artid
										,@artname
										,@pubname
										,@pubid
										,@replicate_ddl
										,@sync_method
										,@art_status
										,@schema_option
										,@qual_dest_object
										,@dest_owner
										,@tran_pub_options
										,@art_objid
	END
	CLOSE #trancolumn
	DEALLOCATE #trancolumn

	--@is_biton should have been set for the first active article affected by add/alter column
	if (@is_biton = 1)
	begin
		
		--log a REPL_NOOP row to get back current LSN
		
		exec @retcode = sys.sp_replincrementlsn_internal @schema_end_lsn OUTPUT
		if @@ERROR<>0 or @retcode <> 0
			goto FAILURE

		
		--sp_replcmds will scan between these two LSNs to pick up the previous image
		--of article cache for this particular DDL, either one being null will cause it
		--to fail with 9003/9004, we should check here and avoid writing nulls
		
		if (@schema_start_lsn is not null) and (@schema_end_lsn is not null)
		begin
			
			--sp_replcmds scans systranschemas for these pairs of LSNs in the scan pass of logscan
			--to build schema version list
			
			insert systranschemas (tabid, startlsn, endlsn, typeid) values (@objid, @schema_start_lsn, @schema_end_lsn, 51)
			if @@ERROR<>0
				goto FAILURE
			
			--calling sp_replflush so that it has a chance to inform in-memory article cache
			--if sp_replcmds is already running and is after the article cache building phase
			
			exec @retcode = sys.sp_replflush @objid, @schema_start_lsn, @schema_end_lsn
			if @@ERROR<>0 or @retcode <> 0
				goto FAILURE
		end
		else
			goto FAILURE
	end
	commit tran replddl_altertable
	return 0

DROPTRAN:
	CLOSE #trancolumn
	DEALLOCATE #trancolumn

FAILURE:
	rollback tran replddl_altertable
	commit tran
	return 1

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSreseed (Procedure)
sp_MStran_ddlrepl (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash