Change data collection errors

I am trying to get clean changes in a table with CDC support bypassing the Min and Max dates. But rushes below the error.

Msg 313, Level 16, State 3, Line 24
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ... .

My code is as follows:

DECLARE @CDate DATE = '2013-03-18' --This is the date after the CDC was enabled on the table
DECLARE @count INT;
DECLARE @lsnStartDatetime DATETIME;
DECLARE @lsnEndDateTime DATETIME;

DECLARE @begin_time DATETIME ,
@end_time DATETIME ,
@from_lsn BINARY(10) ,
@to_lsn BINARY(10);


SELECT  @lsnStartDatetime = CAST(CAST(@CDate AS NVARCHAR(10)) + ' 00:00:00' AS DATETIME)
SELECT  @lsnEndDateTime = CAST(CAST(@CDate AS NVARCHAR(10)) + ' 23:59:59' AS DATETIME)


SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal',
                                       @lsnStartDatetime);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',
                                     @lsnEndDateTime);

if exists (select * from sys.objects where name = 'EmployeeCDCbyDate' and type = 'u')
drop table etl.EmployeeCDCbyDate
SELECT  *
FROM    cdc.fn_cdc_get_net_changes_employee(@from_lsn, @to_lsn, N'all')

Whether it is from_lsn and to_lsn derived from sys.fn_cdc_map_time_to_lsn does not match the mapped to cdc table 'employee'

Below code is working fine; but he gets all the clean changes from min max lsn.

DECLARE @min_lsn BINARY(10) = sys.fn_cdc_get_min_lsn ('employee')
DECLARE @max_lsn BINARY(10) = sys.fn_cdc_get_max_lsn ()
SELECT * FROM cdc.fn_cdc_get_net_changes_employee(@min_lsn, @max_lsn, 'all') ORDER BY 1 desc

I need to get the min and max lsn of the cdc instance for the specified date and get the net changes for that date. Any clues?

Edit:

This works great with the first table when I include a bunch of tables.

Example:

USE ERP

EXEC sys.sp_cdc_disable_db
EXEC sys.sp_cdc_enable_db

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', 
@source_name = N'Employee', 
@capture_instance = 'Employee', 
@supports_net_changes =1, 
@role_name = NULL  

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', 
@source_name = N'StoreListing', 
@capture_instance = 'StoreListing', 
@supports_net_changes =1, 
@role_name = NULL

Go

This works great with the Employee table. If I change the order in which they are included in the CDC (if I first put storelist first and employee next), then it works great with listing employees.

+5
2

MSDN Social: cdc.fn_cdc_get_net_changes _.... SQL Server 2012.

. :

. script SQL Server 2008 R2, .

script SQL Server 2012, , . , SQL Server 2012, , .

Microsoft. .

+1
0

All Articles