April 16, 2012

sp_cdc_generate_wrapper_function (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_cdc_generate_wrapper_function(nvarchar @capture_instance
, bit @closed_high_end_point
, nvarchar @column_list
, nvarchar @update_flag_list)

MetaData:

 create procedure [sys].[sp_cdc_generate_wrapper_function]                    
(
@capture_instance sysname = null,
@closed_high_end_point bit = 1,
@column_list nvarchar(max) = null,
@update_flag_list nvarchar(max) = null
)
as
begin
set nocount on

declare @retcode int,
@capture_cnt int,
@object_id int,
@quoted_change_table nvarchar(260),
@db_name sysname

set @quoted_change_table = N'[cdc].' + quotename(@capture_instance + N'_CT')
set @db_name = db_name()

-- Verify database is currently enabled for change data capture
if ([sys].[fn_cdc_is_db_enabled]() != 1)
begin
raiserror(22901, 16, -1, @db_name)
return 1
end

create table #capture_instances
(
capture_instance sysname collate database_default null,
object_id int null
)

create table #create_scripts
(
function_name nvarchar(145) collate database_default null,
create_script nvarchar(max) collate database_default null
)

if (@capture_instance is null) or (rtrim(@capture_instance) = N'')
begin
-- If the capture instance is not specified, the @column_list and
-- @update_flag_list parameters must both be null
if (@column_list is not null) or (@update_flag_list is not null)
begin
raiserror(22921, 16, -1)
return 1
end

-- Get a list of potential capture instances.
exec @retcode = sys.sp_cdc_get_capture_instances null, null

if @retcode <> 0
begin
return 1
end
end
else
begin
-- Get a list of potential capture instances.
exec @retcode = sys.sp_cdc_get_capture_instances null, null

if @retcode <> 0
begin
return 1
end

-- If the capture instance is specified, it must exist
if not exists (
select capture_instance
from #capture_instances
where capture_instance = @capture_instance
)
begin
raiserror(22920, 16, -1, @capture_instance, @db_name)
return 1
end

-- Remove all entries except the one for the named instance
delete from #capture_instances
where capture_instance <> @capture_instance
end

if (@closed_high_end_point is null)
begin
set @closed_high_end_point = 1
end

-- Eliminate from the list any entries that the caller is not
-- authorized to access
delete from #capture_instances
where sys.fn_cdc_has_select_access(capture_instance) = 0

-- If there are no elements in #capture_instances and
-- an explicit capture instance was entered, return error
select @capture_cnt = count(*) from #capture_instances

if (@capture_cnt = 0) and (@capture_instance is not null)
begin
raiserror(22981, 16, -1)
return 1
end

-- Generate wrapper for the capture instances
exec sys.sp_cdc_generate_wrapper_function_internal
@closed_high_end_point
,@column_list
,@update_flag_list

select function_name, create_script from #create_scripts

return 0
end

No comments:

Post a Comment

Total Pageviews