SQL Server Change Data Capture Functions Returning Nulls

So we’re trying to set up Change Data Capture on our SQL Server database for one of our projects. The MSDN articles made it seem super simple to set up: just run a couple of stored procedures on your database and they do the work of creating schemas, system tables, and other functions for CDC use. Simple!

We made a few changes to data, noted the entries made in the capture instance tables, and naively thought everything was going fine. Called cdc.fn_cdc_get_all_changes_<capture_instance> and got back… nothing. Just a bunch of null values. Wha…?

We narrowed it down to the more core function, sys.fn_cdc_get_column_ordinal, that was failing. But not throwing an error, just returning null, which in a cascade caused all other functions that relied on it (most of the CDC stuff) to also return nothing but nulls. Unfortunately since it is a system function we couldn’t glean too much about what it was doing that was failing.

So we’re tearing our hair out and trying it on other databases and computers to see what could be wrong. Is it just one machine’s DB hosed up? Is it because we forgot to start up the SQL Agent service before running all the setup scripts?

It worked beautifully fine on other databases but not some. It seemed at first like it was just hit or miss until I noticed one commonality to the databases that CDC didn’t work in:

They all had a period in the database name.

Turns out, that’s a no-no for any database identifiers. In my limited defense I did not name the databases nor come up with the naming scheme, but I also didn’t realize it was a no-no. According to the verbiage, if you break the rules on identifiers you can still get away with it if you escape the name in all of your scripts (which, looking back, we were doing everywhere; should have been a red flag, I suppose).

Of course, Microsoft makes no guarantee that all of the internal system functions properly escape everything to protect against naughty-naming. After all, they told you not to do it in the documentation, right?

Anyway, changing the periods to an underscore, while not as nice looking, seems to be perfectly legal and works fine. But we have half a dozen or so databases what will require renaming at some point, which means a lot of refactoring of scripts and entity framework models and all that jazz. Should be so much fun.

But, anyway, let this be a warning to ye!