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?

Answer 1

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 |
+------------------------+------------------------+--------------+----------------+----+----------+------------+-------------------------+