Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_ddl_event_internal

  No additional text.


Syntax
create procedure sys.sp_cdc_ddl_event_internal
(
	@EventData xml,
	@ddl_authorized bit
)
with execute as 'dbo'
as
begin
	set nocount on

	declare @qual_object_name nvarchar(1000) --owner qualified name			
			,@objid	int
			,@pass_through_scripts nvarchar(max)
			,@retcode int
    		,@object_name sysname
			,@object_owner sysname
			,@cmd nvarchar(max)
			,@ddl_time nvarchar(1000)
			,@change_table_id int
			,@cmd_length int
			,@capture_instance sysname
			,@source_column_id int
			,@fis_alter_column bit
			,@DDLsubtype sysname
			,@ColumnName nvarchar(1000)
			,@event_type nvarchar(1000)
			,@target_object_name sysname
			,@index_name sysname
			,@ddl_datetime datetime
			,@target_objid int
			,@switch_cmd nvarchar(max)
			
	--parse xml doc to read various nodes		
    select @object_name  = event_instance.value('ObjectName[1]', 'sysname')
        ,@object_owner = event_instance.value('SchemaName[1]', 'sysname')
        ,@ddl_time = event_instance.value('PostTime[1]', 'nvarchar(1000)')
        ,@pass_through_scripts = event_instance.value('(TSQLCommand/CommandText)[1]', 'nvarchar(max)')
        ,@event_type = event_instance.value('EventType[1]', 'nvarchar(100)')
        ,@target_object_name = event_instance.value('TargetObjectName[1]', 'sysname')
    FROM       @EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

    -- If the object owner is 'cdc' simply return. No objects owned by the 'cdc' schema
    -- can be tracked objects.
    if (@object_owner = N'cdc')
    begin
		return 0
	end	

    select @qual_object_name = quotename(@object_owner) + N'.' + quotename(@object_name)
    select @objid = object_id(@qual_object_name)

	select @ddl_datetime = convert(datetime, @ddl_time)
	if @@error <> 0
	begin
		goto FAILURE
	end

    -- If a table has been dropped, determine whether there are orphaned entries in
    -- cdc.change_tables.  If the table wasn't enabled for CDC, don't propagate the drop.
    if (@event_type = N'DROP_TABLE') and (sys.fn_cdc_dropped_tables() = 0)
    begin
		return 0
	end	

	-- If an altered or dropped index is not being used by a CDC capture instance don't
	-- do anything. If the index is required by a capture instance, rollback the transaction.
	if ((@event_type = N'ALTER_INDEX') or (@event_type = N'DROP_INDEX'))
	begin
	    select @qual_object_name = quotename(@object_owner) + N'.' + quotename(@target_object_name)
		select @objid = object_id(@qual_object_name)
	
		if (sys.fn_cdc_index_required(@objid, @object_name) = 0)
		begin
			return 0
		end
		
		raiserror(22983, 16, -1, @object_name, @qual_object_name)
		goto FAILURE
	end	
	
	--initialize variables
	select @retcode = 0
		
	create table #tran_altertable(id int identity,
				DDLsubtype sysname collate database_default,
				TableOwner sysname collate database_default,
				TableName sysname collate database_default,
				ColumnName nvarchar(1000) collate database_default NULL,
				ColumnAttr sysname collate database_default NULL,
	)
	if @@ERROR <> 0
	begin
		goto	FAILURE
	end					

	if (@event_type = N'ALTER_TABLE')
	begin
		--parse DDL to find out if it has anything to do with add/drop/alter column
		insert #tran_altertable ( DDLsubtype, TableOwner, TableName, ColumnName, ColumnAttr )
			exec sys.sp_replddlparser @ddlCmd = @pass_through_scripts
		if @@ERROR <> 0
		begin
			goto	FAILURE
		end	

		-- If an altered table is not tracked for CDC don't do anything	
		--  we have to wait until this point as we need to do further processing for SWITCH PARTITION
		if not exists (select * from #tran_altertable where DDLsubtype = N'SWITCH') and (sys.fn_cdc_is_table_enabled(@objid) = 0)
		begin
			return 0
		end

		-- If DDL does not add/drop/alter column, drop/disable constraint or switch partition don't do anything
		if not exists (select * from #tran_altertable where DDLsubtype in
			( N'ADDCOLUMN', N'DROPCOLUMN', N'ALTERCOLUMN', N'DROPCONSTRAINT', N'DISABLECONSTRAINT', N'SWITCH'))
		begin
			return 0
		end

		-- If DDL includes drop or disable constraint		
		if exists (select * from #tran_altertable where DDLsubtype in
			( N'DROPCONSTRAINT', N'DISABLECONSTRAINT'))
		begin	
			-- Determine whether a constraint is to be dropped or disabled for the table
			-- that is used by CDC.
			select @index_name = sys.fn_cdc_constraint_required(@objid)
			
			if (@index_name is not null)
			begin
				raiserror(22979, 16, -1, @index_name, @qual_object_name)
				goto FAILURE
			end
		end
		
		-- At this point, we only want to log the DDL event if it includes
		-- at least one of these commands.  This covers the case when a
		-- constraint and column are dropped in the same command.
		if not exists (select * from #tran_altertable where DDLsubtype in
			( N'ADDCOLUMN', N'DROPCOLUMN', N'ALTERCOLUMN', N'SWITCH'))
		begin
			return 0
		end
		
		set @fis_alter_column = 0
		
		select top 1 @DDLsubtype = DDLsubtype, @ColumnName = ColumnName
		from #tran_altertable
		
		-- If ALTERCOLUMN, format the type string for the column to
		-- pass to the capture process.
		if @DDLsubtype = N'ALTERCOLUMN'
		begin
		
			select @source_column_id = column_id
			from sys.columns
			where name = @ColumnName
			and object_id = @objid
		
			set @fis_alter_column = 1


		end
		else if @DDLsubtype = N'ADDCOLUMN'
		begin
			if exists(select * from sys.columns where object_id = @objid and is_column_set = 1)
			begin
				raiserror(22854, 16, -1, @object_name)
				goto FAILURE
			end
		end

		if @DDLsubtype = N'SWITCH'
		begin
			-- The ColumnName column is overloaded to return the target table name for partition switches
			select @target_objid = object_id(@ColumnName)

			if @objid = @target_objid
			begin
				-- Second trigger fired for this switch.  Handling will be done in the first one.
				return 0
			end

			-- If neither table is enabled for CDC there is nothing to do
			if sys.fn_cdc_is_table_enabled(@objid) = 0 and
				sys.fn_cdc_is_table_enabled(@target_objid) = 0
				return 0
	
			-- Error out if the source table is enabled for CDC and disallows partition switching
			-- (This can only currently be true for partitioned tables.)
			if exists (select top 1 partition_switch from [cdc].[change_tables] where source_object_id=@objid and partition_switch = 0 order by create_date DESC)
			begin
				raiserror(22843, 16, -1)
				goto FAILURE
			end

			-- Error out if the target table is enabled for CDC and disallows partition switching
			-- (This can only currently be true for partitioned tables.)
			if exists (select top 1 partition_switch from [cdc].[change_tables] where source_object_id=@target_objid and partition_switch = 0 order by create_date DESC)
			begin
				raiserror(22842, 16, -1)
				goto FAILURE
			end
		
			-- If the target is enabled for CDC, post a command for it
			if sys.fn_cdc_is_table_enabled(@target_objid) = 1
			begin
				select @switch_cmd = N'exec sp_MScdc_logddl '
					+ N'  @source_object_id = ' + cast(@target_objid as nvarchar)
					+ N', @ddl_command = N''' + replace(@pass_through_scripts, N'''', N'''''') + N''''
					+ N', @ddl_lsn = @current_lsn'
					+ N', @ddl_time = N''' + replace(convert(nvarchar(1000), @ddl_datetime), N'''', N'''''') + N''''
					+ N', @commit_lsn = @commit_lsn'
					+ N', @source_column_id = ' + isnull(cast(@source_column_id as nvarchar), N'''''')
					+ N', @fis_alter_column = ' + cast(@fis_alter_column as nvarchar)
			end
		end		

		-- If the (source) object is enabled for CDC, post a command for it
		if sys.fn_cdc_is_table_enabled(@objid) = 1
		begin
			select @cmd = N'exec sp_MScdc_logddl '
				+ N'  @source_object_id = ' + cast(@objid as nvarchar)
				+ N', @ddl_command = N''' + replace(@pass_through_scripts, N'''', N'''''') + N''''
				+ N', @ddl_lsn = @current_lsn'
				+ N', @ddl_time = N''' + replace(convert(nvarchar(1000), @ddl_datetime), N'''', N'''''') + N''''
				+ N', @commit_lsn = @commit_lsn'
				+ N', @source_column_id = ' + isnull(cast(@source_column_id as nvarchar), N'''''')
				+ N', @fis_alter_column = ' + cast(@fis_alter_column as nvarchar)
		end
	end
	else if (@event_type = N'DROP_TABLE')
	begin					
		select @cmd = N'exec sp_MScdc_logddl '
						+ N'  @source_object_id = null'
						+ N', @ddl_command = N''' + replace(@pass_through_scripts, N'''', N'''''') + N''''
						+ N', @ddl_lsn = @current_lsn'
						+ N', @ddl_time = N''' + replace(convert(nvarchar(1000), @ddl_datetime), N'''', N'''''') + N''''
						+ N', @commit_lsn = @commit_lsn'
						+ N', @fis_drop_table = 1'
	end
						
	--post the command to log as artid 0 so logscan can pick it up and add to cdc.ddl_history table
	--sys.sp_replpostcmd @partial, @pubid, @artid, @cmd_type, @command
	--cmd_type 11 means DDL, logreader will look for LOP_REPL_COMMAND with artid 0,
	--pubid 1 and cmd_type 11 to populate cdc.ddl_history
	set @cmd_length = datalength(@cmd)
	
	if (@cmd_length > 0)
	begin
		-- We are ready to act on a DDL command of interest.  We now need to make
		-- sure that the caller was either sysadmin, db_owner, or db_ddladmin.
		if (@ddl_authorized = 0)
		begin
			raiserror(22914, 16, -1)
			goto	FAILURE
		end

		-- Post command to act on the DDL event	
		exec @retcode = sys.sp_replpostcmd 0, @cmd_length, 0, 11, @cmd
		if @retcode <>0 or @@ERROR<>0
			goto	FAILURE
	end		

	if @switch_cmd is not null
	begin
		set @cmd_length = datalength(@switch_cmd)
	
		if (@cmd_length > 0)
		begin	

			-- This should already have been checked earlier, but check again just to be sure
			if (@ddl_authorized = 0)
			begin
				raiserror(22914, 16, -1)
				goto	FAILURE
			end

			-- Post the statement to the log for the target object id
			exec @retcode = sys.sp_replpostcmd 0, @cmd_length, 0, 11, @switch_cmd
			if @retcode <>0 or @@ERROR<>0
				goto	FAILURE
		end
	end

	return 0
FAILURE:
    rollback tran
	
    return 1
end

 
Last revision SQL2008SP2
See also

  sp_MScdc_ddl_event (Procedure)
sp_MSdrop_cdc (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