Edit

sys.sp_cdc_generate_wrapper_function (Transact-SQL)

  • Article

Applies to: SQL Server

Generates scripts to create wrapper functions for the change data capture query functions that are available in SQL Server. The API supported in the generated wrappers enables specifying the query interval as a datetime interval. This specification makes the function good for use in many warehousing applications, including those applications developed by Integration Services package designers who are using change data capture technology to determine incremental load.

For more information on the functions generated by sys.sp_cdc_generate_wrapper_function, see sys.fn_all_changes_<capture_instance> (Transact-SQL) and sys.fn_net_changes_<capture_instance> (Transact-SQL).

Transact-SQL syntax conventions

Syntax

sys.sp_cdc_generate_wrapper_function
    [ [ @capture_instance sysname = ] 'capture_instance' ]
    [ , [ @closed_high_end_point = ] closed_high_end_pt ]
    [ , [ @column_list = ] N'column_list' ]
    [ , [ @update_flag_list = ] N'update_flag_list' ]
[ ; ]

Arguments

[ @capture_instance = ] 'capture_instance'

The capture instance that scripts are generated for @capture_instance is sysname and has a default value of NULL. If a value is omitted or explicitly set to NULL, wrapper scripts are generated for all capture instances.

[ @closed_high_end_point = ] high_end_pt_flag

The flag bit that indicates whether changes that have a commit time equal to the high endpoint are included within the extraction interval by the generated procedure. @closed_high_end_point is bit and has a default value of 1, which indicates that the endpoint should be included. A value of 0 indicates that all commit times are strictly less than the high endpoint.

[ @column_list = ] N'column_list'

A list of captured columns included in the result set that is returned by the wrapper function. @column_list is nvarchar(max) and has a default value of NULL. When NULL is specified, all captured columns are included.

[ @update_flag_list = ] N'update_flag_list'

A list of included columns for which an update flag is included in the result set returned by the wrapper function. @update_flag_list is nvarchar(max) and has a default value of NULL. When NULL is specified, no update flags are included.

Return code values

0 (success) or 1 (failure).

Result set

Column name Column type Description
function_name nvarchar(145) Name of the generated function.
create_script nvarchar(max) The script that creates the capture-instance wrapper function.

Remarks

The script that creates the function to wrap the all-changes query for a capture instance is always generated. If the capture instance supports net-changes queries, the script to generate a wrapper for this query is also generated.

Examples

The following example show how you can use sys.sp_cdc_generate_wrapper_function to create wrappers for all the change data capture functions.

DECLARE @wrapper_functions TABLE (
    function_name SYSNAME,
    create_script NVARCHAR(MAX)
);

INSERT INTO @wrapper_functions
EXEC sys.sp_cdc_generate_wrapper_function;

DECLARE @create_script NVARCHAR(MAX);

DECLARE #hfunctions CURSOR LOCAL FAST_FORWARD
FOR
SELECT create_script
FROM @wrapper_functions;

OPEN #hfunctions;

FETCH #hfunctions
INTO @create_script;

WHILE (@@fetch_status <> -1)
BEGIN
    EXEC sp_executesql @create_script

    FETCH #hfunctions
    INTO @create_script
END;

CLOSE #hfunctions;

DEALLOCATE #hfunctions;

Related content