Change Data Capture errors

Question

Am trying to get the net changes on CDC enabled table by passing Min and Max dates. But is throwing below 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 below:

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

Is it the from_lsn and to_lsn getting from sys.fn_cdc_map_time_to_lsn doesnt match with is mapped aginst the cdc table 'employee'

Below code works fine; but it gets all net changes from min max lsn's.

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

What i need is to get min and max lsn of cdc instance for given date and get the net changes for that date. Any clues?

Edit:

This works fine with first table when i enable on bunch of tables.

Ex:

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 fine with Employee table. If i change the order in which they are CDC enabled (if i put storelist first and employee next), then it works fine with employee listing.

Answer 1

There is an answer to a similar question on MSDN Social: Is there bug with cdc.fn_cdc_get_net_changes_.... in SQL Server 2012.

It is marked as answered by a moderator. Here is quote:

I tested in my server. When I ran the script in the SQL Server 2008 R2, it could be run successfully without error.

When I ran the script in SQL Server 2012, the error message came out the same as yours. I think you could try to apply the latest SQL Server 2012 Service Pack to see if it would OK.

And here is a bug report with Microsoft. It is under investigation.