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'
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.