Applies To: SQL Server 2014, SQL Server 2016 Preview
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
Generates scripts to create wrapper functions for the change data capture query functions that are available in SQL Server. The API that is supported in the generated wrappers enables the query interval to be specified as a datetime interval. This makes the function good for use in many warehousing applications, including those that are developed by Integration Services package designers who are using change data capture technology to determine incremental load.
Is the capture instance that scripts are to be 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
Is the flag bit that indicates whether changes that have a commit time equal to the high endpoint are to be included within the extraction interval by the generated procedure. high_end_pt_flag 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 will be strictly less than the high endpoint.
[ @column_list= ] 'column_list'
Is a list of captured columns to be 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= ] 'update_flag_list'
Is a list of included columns for which an update flag is included in the result set that is 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.
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 generatedl.
DECLARE @wrapper_functions TABLE (
INSERT INTO @wrapper_functions
DECLARE @create_script nvarchar(max);
DECLARE #hfunctions CURSOR LOCAL fast_forward
SELECT create_script FROM @wrapper_functions;
FETCH #hfunctions INTO @create_script;
WHILE (@@fetch_status <> -1)
EXEC sp_executesql @create_script
FETCH #hfunctions INTO @create_script