Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_enable_table_internal

  No additional text.


Syntax
create procedure sys.sp_cdc_enable_table_internal
(
	@source_schema sysname,
	@source_name sysname,
	@capture_instance sysname,
	@supports_net_changes bit,
	@role_name sysname,
	@index_name sysname,
	@captured_column_list nvarchar(max),
	@filegroup_name sysname,
	@allow_partition_switch bit
)
as
begin
	declare @retcode int
            ,@is_cdc_enabled bit
            ,@stmt nvarchar(max)
            ,@role_exists bit
            ,@object_id int
            ,@source_object_id int
            ,@schema_owner sysname
            ,@source_table nvarchar(1000)
            ,@db_name sysname
            ,@use_count int
            ,@existing_tracked tinyint
            ,@first_table tinyint
            ,@instancecnt int
            ,@column_name sysname
            ,@column_type sysname
            ,@is_computed bit
            ,@action nvarchar(1000)
            ,@raised_error int
            ,@raised_message nvarchar(4000)
            ,@trancount int
            ,@resource nvarchar(255)
            ,@table_resource nvarchar(255)
            ,@applock_result int
            ,@count int
            ,@db_id int
            ,@instance_resource nvarchar(255)
            ,@is_partitioned bit
            ,@error_already_set bit
            ,@start_lsn binary(10)
            ,@create_date datetime

    set nocount on

    select @existing_tracked = 0
		,@first_table = 0
		,@raised_error = 0
		,@error_already_set = 0

	create table #index_columns
	(
		column_name sysname,
		index_ordinal int,
		column_id int
	)

    set @db_name = db_name()
    set @db_id = db_id()
    set @resource = N'__$cdc__db_' + convert(nvarchar(10),@db_id)
    set @source_name = rtrim(@source_name)
    set @source_schema = rtrim(@source_schema)
    set @filegroup_name = rtrim(@filegroup_name)
    if (@filegroup_name = N'')
    begin
		set @filegroup_name = null
	end	

    -- Verify source table schema is not null or empty
    if (@source_schema is null) or (@source_schema = N'')
    begin
		raiserror(22963, 16, -1, N'@source_schema')
        return 1
    end

    -- Verify the source table schema is not the CDC schema
    if (lower(@source_schema) = lower('cdc'))
    begin
		raiserror(22974, 16, -1)
        return 1
    end

    -- Verify source table name is not null or empty
    if (@source_name is null) or (@source_name = N'')
    begin
		raiserror(22963, 16, -1, N'@source_name')
        return 1
    end

    -- Verify @source_schema and @source_name together identify a local table
    select @source_table = quotename(@source_schema) + N'.' + quotename(@source_name)
    select @source_object_id = object_id(@source_table)
    select @table_resource = N'__$cdc__table_' + convert(nvarchar(10),@source_object_id)

    if (@source_object_id is null)
	 or not exists (select * from sys.tables where object_id = @source_object_id and is_ms_shipped = 0)							
    begin
		raiserror(22931, 16, -1, @source_schema, @source_name)
        return 1
    end

    -- Verify that two capture instances do not already exist for the table
    select @instancecnt = 0

	select @instancecnt = count(*)
	from [cdc].change_tables with (index = source_object_id_idx)
	where source_object_id = @source_object_id
	
	if (@instancecnt > 1)	
    begin
		raiserror(22962, 16, -1, @source_schema, @source_name)
        return 1
    end

    -- Do a quick check to see if this is the first table added for change tracking,
    -- this is used later to determine whether or not we need to add the cdc jobs.
    if exists (
		select top 1 source_object_id
        from [cdc].change_tables
        )
    begin
		set @first_table = 0
	end		
    else
    begin
		set @first_table = 1
	end
			
    -- Verify @capture_instance is suitable for naming CDC objects
    if (@capture_instance is null)
    begin
		set @capture_instance = @source_schema + N'_' + @source_name
    end
    else
    begin
		set @capture_instance = rtrim(@capture_instance)
    end

    exec @retcode = [sys].[sp_cdc_verify_capture_instance] @capture_instance
    if (@retcode != 0) or (@@error != 0)
    begin
        return 1
    end

    -- Verify @index_name if specified is a valid unique index for the source table
    if (@index_name is not null)
    begin
		exec @retcode = [sys].[sp_cdc_verify_index_name] @source_schema, @source_name, @index_name
		if (@retcode != 0) or (@@error != 0)
		begin
			return 1
		end		
    end

    -- Verify @role_name if specified is a valid name, and determine whether it
    -- already exists.
    if (@role_name is not null)
    begin
		set @role_name = rtrim(@role_name)
		exec @retcode = [sys].[sp_cdc_verify_role_name] @role_name, @role_exists output
		if (@retcode != 0) or (@@error != 0)
		begin
			return 1
		end		
    end

    -- If @index_name is null, we will use the primary key columns
    -- to uniquely identify a row, if a primary key is defined.
    if  (@index_name is null)
    begin
    	select @index_name = i.name
		from [sys].[indexes] i
		where  i.object_id = @source_object_id
		and    i.is_primary_key = 1
		
		-- Verify that a primary key is defined if @supports_net_changes is set
		-- and the index name parameter is null.
		if   (@supports_net_changes = 1) and (@index_name is null)
		begin
			raiserror(22939, 16, -1)
			return 1
		end
    end

    if  (@supports_net_changes is NULL)
    begin
		-- If @index_name is NULL, no @index_name parameter was specified and
		-- no primary key is defined, so @supports_net_changes should default to 0.
		if  (@index_name is null)
		begin
			set @supports_net_changes = 0
		end
		else
		begin
			set @supports_net_changes = 1
		end
	end	

 	-- If @index_name is non-null, populate temporary index column table with index
 	-- columns
	if (@index_name is not null)
	begin
		insert into #index_columns
		select c.name, ic.key_ordinal, c.column_id
		from [sys].[indexes] i inner join [sys].[columns] c
			on i.object_id = c.object_id
		inner join [sys].[index_columns] ic
			on i.object_id = ic.object_id and
			   i.index_id  = ic.index_id and
			   c.column_id = ic.column_id
		where  i.object_id = @source_object_id
		and    i.name = @index_name
	
		if (@@error != 0)
		begin
			raiserror(22941, 16, -1, @index_name, @source_schema, @source_name)
			return 1
		end		
    end

    create table #captured_columns
	(
		column_name sysname,
		column_type sysname,
		is_computed bit
	)

	-- If non-null, parse included column list and verify that identified columns
	-- appear in the source table.
	if (@captured_column_list is not null)
	begin
		insert into #captured_columns
		exec @retcode = [sys].[sp_cdc_parse_captured_column_list] @source_schema
			,@source_name
			,@captured_column_list
			
		if (@retcode != 0) or (@@error != 0)
		begin
			raiserror(22942, 16, -1, @source_schema, @source_name)
			return 1
		end		
    end
    else
    -- If @captured_columns is null, all of the table columns will be tracked
    begin
		-- Insert all of the table columns into #captured_columns
		insert into #captured_columns
		select name, isnull(type_name(system_type_id), type_name(user_type_id)), is_computed
		from [sys].[columns]
		where object_id = @source_object_id
		order by column_id

	end

    -- Verify that none of the reserved change table metadata columns
    -- appear as columns to be tracked.
    if exists
    (
		select top 1 column_name from #captured_columns
		where lower(column_name) collate database_default in ('__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask')	
	)
    begin
		raiserror(22975, 16, -1)
        return 1
    end

    -- Make certain that all of the index columns appear as captured columns
    if exists
    (
		select column_name
		from #index_columns
		where  column_name collate database_default not in
			(	select column_name collate database_default
				from #captured_columns )
    )	
	begin
		raiserror(22943, 16, -1)
		return 1
	end
	
	-- Verify that the filegroup if non-null is a valid filegroup
	if (@filegroup_name is not null)
	begin
		if not exists
			(	select * from sys.filegroups
				where name = @filegroup_name )
		begin
			raiserror(22973, 16, -1, @filegroup_name, @db_name)
			return(1)
		end			
	end	

	if @allow_partition_switch is null
	begin
		raiserror(22844, 16, -1, N'@allow_partition_switch')
		return 1
	end
	
	set @is_partitioned = 0
	
	if exists (
		select partition_number
		from sys.partitions
		where object_id = @source_object_id
		and partition_number <> 1 )
	begin
		set @is_partitioned = 1
	end	

    -- Wrap tranaction logic in a TRY/CATCH block
    BEGIN TRY

		-- Authorization and verification checks have completed.
		-- Open a transaction to begin metadata updates.
		set @trancount = @@trancount
		
		begin tran
		save tran sp_enable_table_change_tracking

		--  Get shared database lock
		exec @applock_result = sys.sp_getapplock @Resource = @resource, @LockMode = N'Shared',
			@LockOwner = 'Transaction', @DbPrincipal = 'db_owner'

		If @applock_result < 0
		begin
			-- Lock request failed.
			set @action = N'sys.sp_getapplock @Resource = ' + @resource + N'@LockMode = N''Shared'', @LockOwner = ''Transaction'', @DbPrincipal = ''db_owner'' '
			raiserror(22840, 16, -1, @action, @applock_result)
		end

		-- The lock request was granted.
		-- Insure database is still enabled before proceeding
		If ([sys].fn_cdc_is_db_enabled() = 0)
		begin
			-- If database is disabled now, another thread disable CDC while
			-- we were waiting for the shared lock. Error with an indication
			-- that CDC is no longer enabled for the database.
			raiserror(22901, 16, -1, @db_name)
		end
	
		--  Get exclusive table lock
		exec @applock_result = sys.sp_getapplock @Resource = @table_resource, @LockMode = N'Exclusive',
			@LockOwner = 'Transaction', @DbPrincipal = 'cdc'
		
		If @applock_result < 0
		begin
			-- Lock request failed.
			set @action = N'sys.sp_getapplock @Resource = ' + @table_resource + N'@LockMode = N''Exclusive'', @LockOwner = ''Transaction'', @DbPrincipal = ''cdc'' '
			raiserror(22840, 16, -1, @action, @applock_result)
		end
	
		-- The lock request was granted.
		-- Insure that there are not already two capture instances for the table
		select @count = count(*)
		from cdc.change_tables with (index = source_object_id_idx)
		where source_object_id = @source_object_id
	
		If (@count > 1)
		begin
			-- If there are already two capture instances for the source table
			-- error here.
			raiserror(22962, 16, -1, @source_schema, @source_name)
		end
		
		--  Get exclusive instance lock
		set @instance_resource = quotename(@capture_instance)
		set @action = N'sys.sp_getapplock @Resource = @instance_resource, @LockMode = N''Exclusive'', @LockOwner = ''Transaction'', @DbPrincipal = ''cdc'' '
		exec @applock_result = sys.sp_getapplock @Resource = @instance_resource, @LockMode = N'Exclusive',
			@LockOwner = 'Transaction', @DbPrincipal = 'cdc'
		
		If @applock_result < 0
		begin
			-- Lock request failed.
			raiserror(22840, 16, -1, @action, @applock_result)
		end
	
		-- Insure that this capture instance name is not already being used
		if exists (
			select top 1 capture_instance
			from [cdc].[change_tables]
			where capture_instance = @capture_instance )
		begin
			raiserror(22926, 16, -1, @capture_instance)
		end

		-- Insure that the change table does not already exist
		if object_id(N'[cdc].' + quotename(@capture_instance + N'_CT')) is not null
		begin
			raiserror(22926, 16, -1, @capture_instance)
		end	
		
		-- Generate the new change table.
		set @action = N'sp_cdc_create_change_table'
		exec [sys].[sp_cdc_create_change_table]
			@source_schema, @source_name, @capture_instance, @filegroup_name
	
		-- Get the object_id of the new change table
		set @object_id = object_id(N'[cdc].' + quotename(@capture_instance + N'_CT'))

		-- Get current LSN and use it as start_lsn for this instance
		set @action = N'sp_replincrementlsn_internal'
		exec @retcode = sys.sp_replincrementlsn_internal @start_lsn OUTPUT
		if (@retcode != 0) or (@@error != 0)
		begin
			-- The actual error raised here is irrelevant. Just to raise an exception so that the "catch" code can run subsequently
			raiserror(22832, 16, -1, @source_table, @action, 0, N'')
		end
		set @create_date = getdate()

		-- post a command to the log so that the log reader can pick it up and insert the mapping of @start_lsn to this current time
		-- into lsn_time_mapping
		set @action = 'sys.sp_replpostcmd'
		--cmd type is CDC_INSTANCE_START_LSN_TIME 214, use parameter 'pubid' to pass the change table's object id
		--the content length of this log record must be greater than 0, so that this log record can be really logged
		--'x' carries no meaning
		exec @retcode = sys.sp_replpostcmd 0, @object_id, 0, 214, N'x'
		if (@retcode != 0) or (@@error != 0)
		begin
			-- The actual error raised here is irrelevant. Just to raise an exception so that the "catch" code can run subsequently
			raiserror(22832, 16, -1, @source_table, @action, 0, N'')
		end

		-- Add entry to [cdc].[change_tables] for the new change table.
		set @action = N'insert into [cdc].[change_tables]'
		insert into [cdc].[change_tables]
		values(@object_id
			,0
			,@source_object_id
			,@capture_instance
			,@start_lsn
			,null
			,@supports_net_changes
			,null
			,@role_name
			,@index_name
			,@filegroup_name
			,@create_date
	   		,case @is_partitioned when 1 then @allow_partition_switch else 1 end	
		)
		
		-- Create the database role if the specified role does not already exist.
		if (@role_name is not null)
		begin
			set @action = N'sp_cdc_verify_role_name '
			exec [sys].[sp_cdc_verify_role_name] @role_name, @role_exists OUTPUT
		end
		
		if ((@role_name is not null) and (@role_exists = 0))
		begin
			set @action = N'create role ' + quotename(@role_name)
			set @stmt = N'create role ' + quotename(@role_name)
			
			begin try
				exec (@stmt)
			end try
			
			begin catch
				if ERROR_NUMBER() <> 15023
				begin
					select @raised_error = ERROR_NUMBER()
					select @raised_message = ERROR_MESSAGE()
					select @error_already_set = 1
					-- We want to ignore the error that the role already exists. Any other
					-- error, we want the outer catch block to catch, so we raise an error here.
					-- Note that the actual error raised here is irrelevant.
					raiserror(22832, 16, -1, @source_table, @action, @raised_error, @raised_message)
				end	
			end catch
		end
	
		-- Populate index_columns table
		set @action = N'insert into [cdc].[index_columns]'
		insert into [cdc].[index_columns]
		select @object_id, column_name, index_ordinal, column_id
		from #index_columns

		-- Create the indexes on the change table.
		set @action = N'sp_cdc_create_change_table_index'
		exec [sys].[sp_cdc_create_change_table_index]
			@capture_instance, @supports_net_changes
		
		-- Populate [cdc].[captured_columns] with the columns of the source table
		-- that are also columns of the change table.  Note that the column_id
		-- is the column id of the source table column. is_computed is also taken
		-- from the source table. The column type and column ordinal are from
		-- the change table. Since we have just created the change table, the
		-- column ids are contiguous starting with 1, and can be used as a surrogate for
		-- column ordinal. Column ordinal excludes the first 5 metadata columns of
		-- the change table and is used solely to interpret the update mask, which
		-- is ordinal based.
		set @action = N'insert into [cdc].[captured_columns]'
		insert into [cdc].[captured_columns]
		select @object_id, t.name, c.column_id, isnull(type_name(t.system_type_id), type_name(t.user_type_id)), t.column_id - 5, c.is_computed
		from [sys].[columns] c inner join [sys].[columns] t
			on c.name = t.name
		where c.object_id = @source_object_id
			and t.object_id = @object_id
			and t.name not in
			('__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask')
		order by t.column_id

		-- Generate the table specific change enumeration functions.
		set @action = N'sp_cdc_create_change_enumeration_functions'
		exec [sys].[sp_cdc_create_change_enumeration_functions]
	 		@source_schema, @source_name, @capture_instance, @supports_net_changes

		-- Grant select permission on the table specific change enumeration functions.
		set @action = N'sp_cdc_grant_select_on_change_enumeration_functions'
		exec [sys].[sp_cdc_grant_select_on_change_enumeration_functions]
			@capture_instance, @supports_net_changes, @role_name

		-- Create the stored procedures to populate the change table
		set @action = N'sp_cdc_create_populate_stored_procs'
		exec [sys].[sp_cdc_create_populate_stored_procs] @capture_instance
		
		-- Enable verbose logging for the table
		set @action = N'sp_MSverboselogging'
		exec [sys].[sp_MSverboselogging]
			@source_object_id, 1
	
		-- Checks are done inside sp_cdc_add_job to see if transactional replication is enabled and contains at least one publication
		-- or logreader agent has been manually added, if so, don't add capture job
		-- note if db is published without any active tran pub but distributor is not accessible, we have to fail next step since we don't know
		--		if there is already logreader agent

		-- if there isn't already logreader agent and if this is the first table added, we add the SQL Agent Capture Job to do change tracking
		if (@first_table = 1)
		begin
			set @action = N'[sys].[sp_cdc_add_job] @job_type = N''capture'''
			exec [sys].[sp_cdc_add_job] @job_type = N'capture'
				,@pollinginterval = 5
				,@continuous = 1
				,@maxtrans = 500
				,@maxscans = 10
				,@check_for_logreader = 1
		end

		-- If this is the first table added, we add the SQL Agent Cleanup Job to do change table cleanup
		-- Default retention is set at 4320 minutes or 3 days.
		if @first_table = 1
		begin
			set @action = N'[sys].[sp_cdc_add_job] @job_type = N''capture'''
			exec [sys].[sp_cdc_add_job] @job_type = N'cleanup'
				,@retention = 4320
				,@threshold = 5000
		end

		-- Call sp_replflush to notify capture process that a new capture instance has been enabled.
		set @action = N'sys.sp_replflush'
		exec sys.sp_replflush

		commit tran

    END TRY

    BEGIN CATCH

		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_enable_table_change_tracking
				commit tran
			end
		end

		-- If we haven't already saved the true error, save the
		-- error number and associated message raised in the TRY block
		if @error_already_set = 0
		begin
			select @raised_error = ERROR_NUMBER()
			select @raised_message = ERROR_MESSAGE()
		end	

    END CATCH

	if @raised_error = 0
	begin
		-- If the source table is a partitioned table, issue an appropriate
		-- warning message before returning
		if exists (
				select partition_number
				from sys.partitions
				where object_id = @source_object_id
				and partition_number <> 1 )
		begin
			if @allow_partition_switch = 1
				raiserror(22855, 10, -1)
			else
				raiserror(22856, 10, -1)
		end
		else
		-- If the source table is not partitioned, and the @allow_partition_switch
		-- parameter was set to 0 (not defaulted or set to 1) issue an appropriate
		-- warning message before returning.
		begin
			if @allow_partition_switch = 0
				raiserror(22857, 10, -1)
		end
			
		return 0
	
	end
		
    raiserror(22832, 16, -1, @source_table, @action, @raised_error, @raised_message)
    return 1
end

 
Last revision 2008RTM
See also

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