Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_create_change_enumeration_wrapper_function

  No additional text.


Syntax
create procedure sys.sp_cdc_create_change_enumeration_wrapper_function
(
	@capture_instance sysname,
	@function_name nvarchar(145),
	@mapping_option nvarchar(30),
	@column_list nvarchar(max),
	@update_flag_list nvarchar(max),
	@create_script nvarchar(max) OUTPUT
)	
as
begin
    set nocount on

    create table #included_columns
		(name sysname collate database_default,
		 column_id int)

    create table #update_flags
		(name sysname collate database_default)

    declare @update_flag_list_with_type nvarchar(max),
		@column_ordinals nvarchar(max),
		@is_bit_set_list nvarchar(max),
		@column_list_with_type nvarchar(max),
		@select_column_list nvarchar(max),
		@column sysname,
		@quoted_change_table nvarchar(1000),
		@object_id int,
		@column_id int,
		@retcode int,
		@order_by_clause nvarchar(40),
		@seqval_column nvarchar(80),
		@seqval_column_with_type nvarchar(80),
		@function_prefix nvarchar(30),
		@colcnt int
		
	set @quoted_change_table = N'[cdc].' + quotename(@capture_instance + N'_CT')
	set	@object_id = object_id(@quoted_change_table)
	
	if (@function_name like N'fn_all_changes%')
	begin
		set @order_by_clause = N'order by [__$seqval], [__$operation]'
		set @seqval_column = N'[__$start_lsn] as [__CDC_STARTLSN], [__$seqval] as [__CDC_SEQVAL], '
		set @seqval_column_with_type = N'[__CDC_STARTLSN] binary(10), [__CDC_SEQVAL] binary(10), '
		set @function_prefix = 'fn_cdc_get_all_changes_'
	end
	else
	begin
		set @order_by_clause = N' '
		set @seqval_column = N' '
		set @seqval_column_with_type = N' '
		set @function_prefix = 'fn_cdc_get_net_changes_'
	end
		
	-- If non-null, parse column list and verify that identified columns
	-- appear in the change table.
	if (@column_list is not null)
	begin
		exec @retcode = [sys].[sp_cdc_parse_included_column_list] @object_id, @column_list
			
		if (@retcode != 0) or (@@error != 0)
		begin
			return 1
		end		
    end
    else
    -- If @column_list is null, all of the tracked columns will be included
    begin
		insert into #included_columns
		select name, column_id
		from [sys].[columns]
		where object_id = @object_id
		and lower(name) collate database_default not in ('__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask')	
		order by column_id
	end
	
	-- If non-null, parse update flag list and verify that identified columns
	-- appear in the change table.
	if (@update_flag_list is not null)
	begin
		exec @retcode = [sys].[sp_cdc_parse_update_flag_list] @object_id, @update_flag_list
			
		if (@retcode != 0) or (@@error != 0)
		begin
			return 1
		end		
    end						

	set @colcnt = 0
	declare #hflags cursor local fast_forward for
		select name from #update_flags
	
	open #hflags
	fetch #hflags into @column
	
	set @update_flag_list_with_type = N''
	set @column_ordinals = N''
	set @is_bit_set_list = N''

	while (@@fetch_status <> -1)
	begin
		set @colcnt = @colcnt + 1
		set @update_flag_list_with_type = @update_flag_list_with_type + N', ' + quotename(@column + N'_uflag') + N' bit'
			
		set @column_ordinals = @column_ordinals + N'
		declare @ordinal_' + convert(nvarchar(10),@colcnt) + N' int
		select @ordinal_' + convert(nvarchar(10),@colcnt) + N' = [sys].[fn_cdc_get_column_ordinal]
			(' + quotename(@capture_instance, '''') + ', ' + quotename(@column, '''') + N') '	
		
		set @is_bit_set_list = @is_bit_set_list + N',
			case [__$operation]
				when 4 then
					case (isnull(cast([__$update_mask] as varchar),''''))
						when '''' then null
						else [sys].[fn_cdc_is_bit_set](@ordinal_' + convert(nvarchar(10),@colcnt) + N', [__$update_mask])
					end	
				else null
			 end as ' + quotename(@column + N'_uflag') 			
	
		fetch #hflags into @column
	end
	
	close #hflags
	deallocate #hflags
	
    declare #hcolumns cursor local fast_forward for
		select name, column_id from #included_columns
	
	open #hcolumns
	fetch #hcolumns into @column, @column_id
	
	set @column_list_with_type = N''
	set @select_column_list = N''
	
	while (@@fetch_status <> -1)
	begin
		set @column_list_with_type = @column_list_with_type + quotename(@column) + N' ' +
				[sys].[fn_cdc_format_type]( @object_id, @column_id) +  N', '

		set @select_column_list = @select_column_list +  quotename(@column)  + N', '
		
		fetch #hcolumns into @column, @column_id
		
	end
	
	close #hcolumns
	deallocate #hcolumns
	
	set @create_script =
	N'
	create function ' + quotename(@function_name) + N'
	(	@start_time datetime = null,
		@end_time datetime = null,
		@row_filter_option nvarchar(30) = N''all''
	)
	returns @resultset table ( ' + @seqval_column_with_type + @column_list_with_type + N' [__CDC_OPERATION] varchar(2) ' +
	            @update_flag_list_with_type + N'
	) as
	begin
		declare @from_lsn binary(10), @to_lsn binary(10) ' + @column_ordinals + N'
		
		if (@start_time is null)
			select @from_lsn = [sys].[fn_cdc_get_min_lsn](' + quotename(@capture_instance, '''') + N')
		else
		begin
			if ([sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_min_lsn](' + quotename(@capture_instance, '''') + N')) > @start_time) or
			   ([sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_max_lsn]()) < @start_time)
				select @from_lsn = null
			else
				select @from_lsn = [sys].[fn_cdc_increment_lsn]([sys].[fn_cdc_map_time_to_lsn](''' + @mapping_option + N''',@start_time))
		end	
	
		if (@end_time is null)
			select @to_lsn = [sys].[fn_cdc_get_max_lsn]()
		else
		begin
			if [sys].[fn_cdc_map_lsn_to_time]([sys].[fn_cdc_get_max_lsn]()) < @end_time
				select @to_lsn = null
			else
				select @to_lsn = [sys].[fn_cdc_map_time_to_lsn](''' + @mapping_option + N''',@end_time)
		end
		
		if @from_lsn is not null and @to_lsn is not null and
			(@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn))
			return
			
		insert into @resultset
		select ' + @seqval_column + @select_column_list + N'
			case [__$operation]
				when 1 then ''D''
				when 2 then ''I''
				when 3 then ''UO''
				when 4 then ''UN''
				when 5 then ''M''
				else null
			end as [__CDC_OPERATION] ' + @is_bit_set_list + N'		
		from [cdc].' +
			quotename(@function_prefix + @capture_instance) + N'(@from_lsn, @to_lsn, @row_filter_option) ' +
			@order_by_clause + '
		
		return
	end'		
	
		return 0
end

 
Last revision 2008RTM
See also

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