pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

8/17/2010

CDC: dbo.fn_all_changes_... frustrating

I've come to find that the generated dbo.fn_all_changes_... function for Change Data Capture (CDC) in SQL 2008 is not my favorite. I think it is poorly implemented.

(In fact, skip to the end of this blog entry for 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 tableThe 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

That's inconvenient.

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


Because "the LSN range is invalid"?

I'm sure there's a perfectly good reason, but even the folks on the product team have to admit, its goofy, frustrating and counter-intuitive.

And the most frustrating part is that they could fix 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')   

And it fixes it.  No joke.  (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.

2 comments:

  1. In SQL SERVER 2008r2, the TVF is defined as
    [DB].[cdc].[fn_cdc_get_net_changes_cdc_cdctest] (
    <@from_lsn, binary(10),>
    ,<@to_lsn, binary(10),>
    ,<@row_filter_option, nvarchar(30),>) but the problem is just the same as reported above.

    The workaround is to relegate the TVFunction to unusuable and use (for example):

    select * from cdc.dbo_cdctest_CT
    where __$Start_lsn > sys.fn_cdc_map_time_to_lsn('smallest greater than', GETDATE()-1)
    order by __$start_lsn

    ReplyDelete
  2. query = "USE CDC_Practice;"+
    "DECLARE @begin_time DATETIME, @end_time DATETIME, @begin_lsn BINARY(10), @end_lsn BINARY(10);" +
    "SELECT @begin_lsn = (select MIN(e1.__$start_lsn) from cdc.dbo_"+table_name+"_CT as e1 INNER JOIN cdc.lsn_time_mapping as e2 ON e1.__$start_lsn=e2.start_lsn where e2.tran_begin_time> GETDATE()); " +
    "SELECT @end_lsn = (select MAX(e1.__$start_lsn) from cdc.dbo_"+table_name+"_CT as e1 INNER JOIN cdc.lsn_time_mapping as e2 ON e1.__$start_lsn=e2.start_lsn where e2.tran_begin_time< GETDATE()+1 ); " +
    "SELECT * " +
    "FROM cdc.fn_cdc_get_all_changes_"+table_schema+"_"+table_name+"(@begin_lsn,@end_lsn,'all') ;



    use your own database and table name

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.