CDC Function not displaying information from past day

Question

I'm using SQL Server 2014 (SP2).

I have enabled CDC on a table. I then want to view all that has happened in the past 24 hours so I'm using the below:

DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10); 
SELECT @begin_time = GETDATE()-1, @end_time = GETDATE(); 
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 * 
FROM [MyDatabase].cdc.fn_cdc_get_all_changes_dbo_MyTable(@begin_lsn,@end_lsn,'all') 
GO

But I keep receiving the below error:

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

However ,the above function is system generated when I enabled CDC and only contains 3 parameters:

ALTER function [cdc].[fn_cdc_get_all_changes_dbo_MyTable]
(   @from_lsn binary(10),
    @to_lsn binary(10),
    @row_filter_option nvarchar(30)
)

Why is the function not working?

Duplicate: https://stackoverflow.com/questions/16299422/an-insufficient-number-of-arguments-were-supplied-for-the-procedure-or-function — jean, Feb 18 '20 at 10:48
Does this answer your question? [An insufficient number of arguments were supplied for the procedure or function cdc.fn\_cdc\_get\_all\_changes\_](https://stackoverflow.com/questions/16299422/an-insufficient-number-of-arguments-were-supplied-for-the-procedure-or-function) — jean, Feb 18 '20 at 10:48
thanks @jean but I'm afraid not, the values returned are not zero value — Michael, Feb 18 '20 at 10:53
What the answer tells us is the error message is, in fact, not warning about missing parameters bad rather there is one parameter with a bad (too big or to little) value — jean, Feb 18 '20 at 10:56

Esat Erkec · Accepted Answer · 2020-02-18T14:14:42.903

If the specified LSN range does not fall within the change tracking timeline for the capture instance, the function returns error 208 ("An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes.").

According to this explanation, you might need to determine@begin_time and @end_time Such as you can specify the begin time and end time according to __$start_lsn of the CDC table. If you execute the following query you will not experience an error.

DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10); 
--SELECT @begin_time = GETDATE()-1, @end_time = GETDATE(); 
SELECT @begin_time=sys.fn_cdc_map_lsn_to_time(MIN(__$start_lsn))  FROM AdventureWorks2017.cdc.dbo_MyTable_CT
SELECT @end_time = sys.fn_cdc_map_lsn_to_time(MAX(__$start_lsn)) FROM AdventureWorks2017.cdc.dbo_MyTable_CT
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 * 
FROM AdventureWorks2017.cdc.fn_cdc_get_all_changes_dbo_MyTable(@begin_lsn, @end_lsn, N'all') 

+------------------------+------------------------+--------------+----------------+----+----------+------------+-------------------------+
|      __$start_lsn      |       __$seqval        | __$operation | __$update_mask | Id | SomeText | ModifiedBy |      ModifiedDate       |
+------------------------+------------------------+--------------+----------------+----+----------+------------+-------------------------+
| 0x0000002E000070F80004 | 0x0000002E000070F80003 |            2 | 0x0F           |  2 | SomeText | Matt       | 2020-02-18 14:38:00.120 |
+------------------------+------------------------+--------------+----------------+----+----------+------------+-------------------------+

there are no ModifiedDate fields in the CDC table – Michael Feb 18 '20 at 13:06 — Michael, Feb 18 '20 at 13:06

CDC Function not displaying information from past day

1 Answers1