Function in Change Data Capture returns error message ...

_{Monday, August 6, 2007 9:01 AM}

I have tried code for CDC in Katmai CTP 4 :

Code Snippet

declare @from_lsn binary(10)

set @from_lsn = sys.fn_cdc_get_min_lsn('Person.Contact')

declare @to_lsn binary(10)

set @to_lsn = sys.fn_cdc_get_max_lsn()

declare @row_filter_option nvarchar(10)

set @row_filter_option = 'all'

-- then all corresponding changes ...

select * from cdc.fn_cdc_get_all_changes_Person_Contact( @from_lsn, @to_lsn, 'all')

Select returns :

Msg 313, Level 16, State 3, Line 8

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

Cheers,

Jiri Lichtenberg

_{Thursday, August 16, 2007 5:21 PM ✅Answered | 1 vote}

Couple issues. 

1.  For the repro you cut/pasted above, you're passing in invalid LSN values, the reason you have invlaid LSN values is because you're querying LSNs for the wrong object.  You enabled CDC for object 'Contacttype', but you're querying LSNs for object 'Contact'.  So naturally, the @from_lsn and @to_lsn will be either 0x000 or NULL, which are invalid.

2.  The actual error message is by design, but it can easily be misleading if you're not familiar with the error handling (I should doublecheck that we are documenting this).  Unfortunately CDC has functions, and we have no way of doing error checking for invalid functions, we cannot have "raiserrors".  So we created dummy functions, one of them is named the one you see in your error msg: "cdc.fn_get_all_changes_ ...".    This is the only way we can indicate that invalid LSN values were passed in.  Otherwise the customer may never know they have wrong LSN values.  We're still working on trying to improve this type of error. 

In short, it's up to the user to ensure they're not passing in NULL value for any LSN.  We need to return this confusing error for now to let customer know they need to double-check their values, otherwise if we returned zero rows (empty rowset) customer may think they actually have no rows to query for, which could be incorrect.

_{Monday, August 6, 2007 3:13 PM}

Change Data Capture questions should be posted to the Replication forum.

I do not see see anything wrong with the syntax you are using. What happens if you pass ing @row_filter_option instead of the string?

_{Monday, August 6, 2007 5:45 PM}

I only try sample from webcast Change Data Capture ( Live Meeting Series ). I also do not see mistake - cdc.fn_cdc_get_all_changes_Person_Contact has 3 parameters in definition :

Code Snippet

ALTER function [cdc].[fn_cdc_get_all_changes_Person_Contact]

( @from_lsn binary(10),

@to_lsn binary(10),

@row_filter_option nvarchar(30)

)

returns table

return

select t.__$start_lsn as __$start_lsn, ....

This is all code :

Code Snippet

-- Enable CDC on database

if exists (

select is_cdc_enabled from sys.databases

where name = 'AdventureWorks'

and is_cdc_enabled = 1

)

exec sp_cdc_disable_db_change_data_capture

go

if exists (

select is_cdc_enabled from sys.databases

where name = 'AdventureWorks'

and is_cdc_enabled = 0

)

exec sp_cdc_enable_db_change_data_capture

go

-- Enable a table for CDC

sys.sp_cdc_enable_table_change_data_capture

@source_schema = 'Person',

@source_name = 'Contacttype',

--,@capture_instance = 'Person_contact'

@supports_net_changes = 1,

@role_name = null

--@index_name = 'PK_contact_contactID'

--@capture_column_list = null

--@filegroup_name = null

go

select name, is_tracked_by_cdc,* from sys.tables

where schema_id = schema_id ('Person') and name = 'Contact'

insert person.contact

(NameStyle, Title,firstname, Lastname,emailpromotion, passwordHash,PasswordSalt)

select 0,'Mr.','Stevens','Banners',1,'TVGHbhY=','TVGHbhY='

declare @from_lsn binary(10)

set @from_lsn = sys.fn_cdc_get_min_lsn('Person.Contact')

declare @to_lsn binary(10)

set @to_lsn = sys.fn_cdc_get_max_lsn()

declare @row_filter_option nvarchar(10)

set @row_filter_option = 'all'

-- then all corresponding changes ...

select * from cdc.fn_cdc_get_all_changes_Person_Contact( @from_lsn, @to_lsn, 'all')