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.
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.