Wanted to blog about this issue for long time but somehow it did not materialize. Recently one of my colleague faced this issue and I decided that this is the time to put it in the blog.
I was working on a data warehouse. The source had enabled CDC for a table to identify delta records. Our code was using “cdc.fn_cdc_get_net_changes” to query the _CT tables for a range of LSN and put into a staging table and then merge into main table.
One fine day, our merge statement failed with an error like “table does not allow null values”. When I looked into the _Delta table, I could see only one record with all the columns value as NULL. I checked the _CT table and I could find many records with valid values.
But whenever I tried to query the _CT with the openquery (above statement), what I got was a null row. Lot of trial and error went but nothing solved the issue. Neither Google nor forums (internal & external) could help me 😦
Few days later I got a mail from an internal forum that this issue might arise if the service account does not have access to CDC gating role. I went back to my source and found a role with name CDC_Admin. The service account with wich we connect was not part of that role. I asked the admin to add the account to that role and that solved the issue 🙂 Very simple solution but took days to find it.
If the Service Account does not have access to the CT table, SQL should have failed it but instead it added a null row. This is very misleading.
Few days back, another colleague pinged me saying that she also faced the same issue and tried my fix but it did not help her. I looked into it and everything was fine. The service account was part of CDC_Admin role but still the openquery was fetching a NULL row. But she was smart enough to find the reason by herself 🙂 There was one more account with different name. She added her service account to that and it resolved it.
The essence is that whenever you enable CDC (using sp_cdc_enable_table), you can mention a database role. You can create a new role for each table (however this will cause lot of confusion as in the above case). Users should be part of this role otherwise they will not be able to access the _CT.