Interesting SQL 2008 CDC Discovery

I have an SSIS package that sets up CDC in a SQL 2008 by looping through a series of tables and activating each using the following basic command:

EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’
,@source_name = <tablename>
,@role_name = N’dbo’
,@Filegroup_Name = N’Primary’
,@capture_instance = <tablename>
,@supports_net_changes =1;

Obviously the code replaces <tablename> with the actual tablename.

Later when I was working on some merge logic I tried to reference use the cdc_net_changes function for one of the tables; I kept getting an error that the function didn’t exist.

I checked and sure enough, it didn’t.

Digging in it some more I found that if a table does not have a primary key defined, and no alternate unique index, the enable_table procedure above will not create the net_change function and while at it, not tell you that it did not create that function.

In my case this is ok for now, but I’ll be talking with the data architect as to why there is no key on this reference table and with users to ensure that not catching the net changes will be ok.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.