CDC: dbo.fn_all_changes_... frustrating

I've come to find that the generated dbo.fn_all_changes_... function for Change Data Capture (CDC) returns some errors and if you skip to the end of this blog entry, I have a quick fix to fn_all_changes_dbo_... that solves this problem.

They are created for you with the sproc sys.sp_cdc_generate_wrapper_function with the idea of giving you a way to easily select changed data rows between two dates.  The first two parameters of the fn_all_changes_... (and its sister, the fn_net_changes...) are dates, start and end date.

This works for my testing table called dbo.cdctest:

select * from dbo.fn_all_changes_dbo_cdctest (null,null, 'all')

This doesn't:

select * from dbo.fn_all_changes_dbo_cdctest ('2010-01-01',null, 'all')

It returns:

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 statement has been terminated.

Why?  Because when looking for LSN's at or after 2010-01-01, fn_all_changes_.. chokes.

(Yeah, there are other reasons the error can happen.  Its a dummy response to an inherent problem that TVF's can't return errors.  There are other problems that could create the above error message.  This blog entry is specifically dealing with the problem of dates-before-CDC-was-enabled.)

On my server, the date 2010-01-01 occurs before any CDC was activated.  Before any LSN's had been created for this table.  The error occurs if you provide a date before CDC was started. 

Which means you have to give it a date/time that is between the time you set up CDC and the time of the first changed data events.  A very specific date/time, too.

Even though I didn't set up CDC until this afternoon, this still returns a valid LSN:

SELECT @lsn = sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal','01/01/2010')

But if I run this, I can find out exactly when I set up CDC on this test table, dbo.cdctest.  Note the increments of time in the first parameter.

select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.6',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.7',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.8',null, 'all')
select * from dbo.fn_all_changes_dbo_cdctest ('2010-08-17 16:46:24.9',null, 'all')


Here's what I get

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 statement has been terminated.
Msg 313, Level 16, State 3, Line 2
An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ... .
The statement has been terminated.

(69 row(s) affected)

(69 row(s) affected)

Research that "An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_" error and you find that it is a dummy response for when a valid LSN can't be found. 

LSN's are binary expressions that compare with logical operators, right? What is the reason it couldn't have been done with simple > < signs?  (funny, because >.< is exactly how I feel...)

This CDC function works with the date 01/01/2010, even though I started CDC long after 01/01/2010:

SELECT sys.fn_cdc_map_time_to_lsn ('smallest greater than or equal','01/01/2010')

Returns:

0x0000001B000002690034

And this still works too:

select * from cdc.dbo_cdctest_CT where __$Start_lsn > 0x0000001B000002690034 order by __$start_lsn


So why doesn't this work? 


select * from dbo.fn_all_changes_dbo_cdctest ('2010-01-01',null, 'all')


This is because "the LSN range is invalid". This can be fixed it by adding two lines to fn_all_changes_dbo_...:

Before the line:

if @from_lsn is not null and @to_lsn is not null and     (@from_lsn = [sys].[fn_cdc_increment_lsn](@to_lsn)) 
    return 

Add:

    if @from_lsn is null
        select @from_lsn = [sys].[fn_cdc_get_min_lsn]('dbo_cdctest')   

(Obviously, I'm disclaiming myself from any liability if you muck with a MS-provided stored proc on your production box.  I don't recommend making the above fix, I'm only pointing out its maddening simplicity.)

And before you ask about my insistence about using 01/01/2010, its just for an example.  Nothing special.  And sure, this problem is not a big one because CDC is typically used on a rolling or ongoing basis, not from a beginning-to-now basis. 

I welcome any and all responses on this.

UPDATE: small typo fixes.