Error while executing change data capture sql quer

Question

I am getting this error "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_" while trying to execute the following sql query.

use SpatialDB
go
declare @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);
set @begin_time=GETDATE()-1;
set @end_time=GETDATE();
set @from_lsn=sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);
set @to_lsn=sys.fn_cdc_map_time_to_lsn('largest greater than or eqaul', @end_time);
select * from cdc.fn_cdc_get_net_changes_dbo_Points(@from_lsn, @to_lsn, N'all');
go

Answer 1

More Late, but for future seekers . . .

I received this message and the reason is because I had my Start LSN and Ending LSN numbers switched. So, I switched them back and it worked fine.

I ended up with code like:

SELECT DISTINCT sh.siteid1 AS 'Site Number' ,CASE cdc.__$operation WHEN 1 THEN 'D' WHEN 2 THEN 'I' WHEN 4 THEN 'U' END AS CDC_OPERATION FROM site_header sh(NOLOCK) INNER JOIN cdc.fn_cdc_get_net_changes_dbo_svt_svsiteheader(0x00004B04000019300002, 0x00004B0500003A220001, N'All') cdc ON cdc.site_id = sh.site_id LEFT JOIN site_location sl ON sh.id_site = sl.id_site AND sh.primary_id = sl.id_rec

Answer 2

I had the same issue, and Gerald was spot on - if I use LSNs within the range of CDC captured data, no issue.

I'm now capping my LSN values with sys.fn_cdc_get_min_lsn('my_captureinstance') and sys.fn_cdc_get_max_lsn(), e.g:

DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10), @min_lsn BINARY(10), @max_lsn BINARY(10);

SET @begin_time = '2015-06-23 00:00:00.000';
SET @end_time   = '2015-06-24 00:00:00.000';

SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

SELECT @min_lsn = sys.fn_cdc_get_min_lsn('my_captureinstance')
SELECT @max_lsn = sys.fn_cdc_get_max_lsn()

IF @begin_lsn < @min_lsn BEGIN
  SELECT @begin_lsn = @min_lsn
END

IF @end_lsn > @max_lsn BEGIN
  SELECT @end_lsn = @max_lsn
END

SELECT * FROM cdc.fn_cdc_get_net_changes_my_captureinstance(@begin_lsn, @end_lsn, 'all')

Answer 3

Late, but for future seekers . . .

The error message is somewhat misleading. The parameters that you pass in to the function need to be "valid" or you will get this frustrating error message.

Here is a quote from TechNet for SQL Server 2008 article Using Change Data. I recommend that you read this article.

We recommend validating the LSN boundaries that are to be used in a TVF query before their use. Null endpoints or endpoints that lie outside the validity interval for a capture instance will force an error to be returned by a change data capture TVF.

For example, the following error is returned for a query for all changes when a parameter that is used to define the query interval is not valid, or is out of range, or the row filter option is invalid.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

The corresponding error returned for a net changes query is the following:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

When I first began using these functions in SQL Server 2012, I was passing in wild parameters that were far outside the range of dates that had been captured by CDC. When I cleaned them up--that is, started using date ranges that were valid--good results followed.

Answer 4

That mecdc.fn_cdc_get_net_changes_dbo_Points requires more arguments. Find the definition of this function to see what it's expecting so you can give it the right number of arguments. This is one of your functions, so none of us know what the arguments should be.