Tag Archives: cdc sql 2008

SQL ERROR Fix: sp_MS_marksystemobject: Invalid object name ‘[cdc].[cdc_TABLE1]’

Hi, one of the new features in Microsoft SQL Server 2008 is the ability to track changes on a table. You can enable change tracking on a table using the Change Data Capture feature. It is extremely useful for BI (Business Intelligence) processes because it allows to track only the changes done on a table instead of importing the whole table each day for processing. Today me and a DEV guy faced a curious problem trying to enable an additional table for cdc on a Database already enabled for change data Capture feature. Just FYI, you need to be sysadmin or have db_owner role to manage the cdc feature.

Normally when you run:

EXEC sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'dbo',
@source_name = 'TABLE1',
@role_name = 'cdc_TABLE1'


Job 'cdc.Test_capture' started successfully.
Job 'cdc.Test_cleanup' started successfully.

The SQL creates 2 new SQL Agent jobs, and a new table named as you specified it at the @role_name parameter, which will reflect the changes on the tracked table.
The table will be market as system table and found under “System Tables” folder from Database/Tables tree in SQL Management Studio.

In my case the cdc table was created inside master database with few warnings and NOT as system table. It was inserted under User tables leaf. At first look it was working as cdc table, but I didn’t want to take the risk.

These where the warnings:

sp_MS_marksystemobject: Invalid object name '[cdc].[cdc_TABLE1]'
sp_MS_marksystemobject: Invalid object name '[cdc].[sp_insdel_605961235]'
sp_MS_marksystemobject: Invalid object name '[cdc].[sp_upd_605961235]'
sp_MS_marksystemobject: Invalid object name '[cdc].[sp_batchinsert_605961235]'

After digging a little on the Internet I found out that, when the stored procedure sys.sp_cdc_enable_table_change_data_capture is executed, the undocumented procedure sp_MS_marksystemobject tries to mark the table as system table. In our case, the task failed and the table was marked as user table which was not what we wanted.
So first thing was to try to run manually the command which I thought could have done the trick and mark the table as system:

sp_MS_marksystemobject '[cdc].[cdc_TABLE1]'

Same warning. Not what I expected…

After another couple of hours of research, I found the issue. The resolution came from a totally unsuspected area. It happened that cdc schema under which all cdc tables are created had a different schema owner than the cdc SQL user. Somehow, in past it was modified to another SQL user. The reason it failed with “Invalid object name” warning became clear for me. The user which owned the schema didn’t have rights to see/change cdc objects. I had to change the cdc schema owner to cdc as it is supposed to be.

This command solved the issue and let me enable the cdc feature on the table without warnings: