OK so something I’ve been fighting with for the past week or two (off and on) was enabling Change Data Capture for a database and table. Sounds easy enough and I’ve read numerous articles on how to do it, etc.
I have a SQL 2008 instance up and running from I think CTP5 or CTP6 and have been upgrading it during the CTP cycle and into RTM when it was released. I had never played with CDC much during that time but now have a need to get more “into it”. So I go to enable CDC using the following command:
EXEC sys.sp_cdc_enable_db
the CDC system tables are created BUT the schema of the table does not seem to be valid as in the cdc.change_tables system table the has_drop_pending field being create with NOT NULL specified.
This becomes a problem when I then try and run:
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', @source_name = 'tblCustomers', @role_name = NULL, @supports_net_changes = 1
I get the following error:
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607
Could not update the metadata that indicates table [dbo].[tblCustomers] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[change_tables]‘. The error returned was 515: ‘Cannot insert the value NULL into column ‘has_drop_pending’, table ‘CDC.cdc.change_tables’; column does not allow nulls. INSERT fails.’. Use the action and error to determine the cause of the failure and resubmit the request.
So I thought OK something was wrong with my setup/config. I had recently applied CU2 to my instance and thought maybe there was a bug with that so I uninstalled CU2 with no difference in my results. I got a fresh VM up and running and reinstalled SQL 2008 from scratch and STILL had the same problem so at that point I went on a few different message boards and even posted a bug on connect.microsoft.com and with all the searching I had done (and other people as well trying to help solve this problem)
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=387050
“If the following option is set when you run sp_cdc_enable_db for a database
SET ANSI_NULL_DFLT_OFF ON
columns that do not have the NULL/NOT NULL option explicitly set will be assigned the NOT NULL attribute. The column has_drop_pending does not have this option explicitly set when the table cdc.change_tables is created so it takes on the NOT NULL attribute. (This can be checked by examining the column information for the table cdc.change_tables after it is created by sp_cdc_enable_db.) When sp_cdc_enable_table is called to enable a source table, the value assigned to the column has_drop_pending is always NULL, so that the insert into the table will fail.
We acknowledge that not explicitly setting the NULL attribute for the has_drop_pending column of cdc.change_tables is a defect. The problem can be worked around by explicitly setting the option ANSI_NULL_DFLT_ON to ON prior to enabling Change Data Capture for the database so that the column attributes are correct and then resetting it back to the (presumed) desired setting for your environment.
SET ANSI_NULL_DFLT_ON OFF
exec sys.sp_cdc_enable_db
go
SET ANSI_NULL_DFLT_OFF ON”
Looks like I found a defect and something that (hopefully) will be patched in the future… I’m just amazed that I was unable to find anything else on the net about this problem.
Anyways I hope this helps out anyone else who is running into the same problem that I did when trying to enable Change Data Capture.
