Scalar function fn_cdc_get_min_lsn() constantly returns '0x00000000000000000000' for valid table names?
My mystake was to assume that sys.fn_cdc_get_min_lsn() accepts the table name. I was mostly misguided by the examples in MSDN documentation, probably and didn't check the exact meaning of the parameters.
It turns out that the sys.fn_cdc_get_min_lsn() accepts the capture instance name, not table name!
A cursory glance at my current capture instances:
SELECT capture_instance FROM cdc.change_tables
returns the correct parameter name:
ordering_Fields
So, one should use underscore as schema separator, and not the dot notation as it is common in SQL Server.
The above answer is correct. Alternatively you can add an additional parameter capture_instance to the cdc enable
EXEC sys.sp_cdc_enable_table
@source_schema ='ordering',
@source_name ='Fields',
@capture_instance = 'dbo_Fields'
@role_name = NULL,
@supports_net_changes = 0;
then use the capture_instance string in the min_lsn function
SET @Begin_LSN = sys.fn_cdc_get_min_lsn('dbo_Fields')
will return the first LSN, and not 0x00000000000000000000.
This is partiularly useful when trying to solve the error "An insufficient number of arguments were supplied for the procedure or function cdc..." from SQL when calling
cdc_get_net_changes_Fields(@Begin_LSN, sys.fn_cdc_get_max_lsn(), 'all')
Which simply means "LSN out of expected range"
I know this is mostly already explained in this post but I thought I would put together my evenings journey through CDC
- This error:
"An insufficient number of arguments were supplied for the procedure or function cdc..."
Is probably caused by your low LSN being 0x00
This in turn might be because you put the wrong instance name in with fn_cdc_get_min_lsn.
Use SELECT * FROM cdc.change_tables to find it
Lastly make sure you use binary(10) to store your LSN. If you use just varbinary or binary, you will again get 0x00. This is clearly payback for me scoffing at all those noobs using varchar and wondering why their strings are truncated to one character.
Sample script:
declare @S binary(10)
declare @E binary(10)
SET @S = sys.fn_cdc_get_min_lsn('dbo_YourTable')
SET @E = sys.fn_cdc_get_max_lsn()
SELECT @S, @E
SELECT *
FROM [cdc].[fn_cdc_get_net_changes_dbo_issuedToken2]
(
@S,@E,'all'
)