Scalar function fn_cdc_get_min_lsn() constantly returns '0x00000000000000000000' for valid table names?

Alexander Galkin picture Alexander Galkin · Apr 30, 2013 · Viewed 7.9k times · Source

I have Change Data Capture (CDC) activated on my MS SQL 2008 database and use the following code to add a new tabel to the data capture:

EXEC sys.sp_cdc_enable_table
@source_schema ='ordering',
@source_name ='Fields',
@role_name = NULL,
@supports_net_changes = 0;

However, whenever I try to select the changes from the tracking tables using the sys.fn_cdc_get_min_lsn(@TableName) function

 SET @Begin_LSN = sys.fn_cdc_get_min_lsn('Fields')

I always get the zero value.

I tried adding the schema name using the following spelling:

 SET @Begin_LSN = sys.fn_cdc_get_min_lsn('ordering.Fields')

but this didn't help.

Answer

Alexander Galkin picture Alexander Galkin · Apr 30, 2013

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.