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:

USE [DB1]
GO
EXEC sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'dbo',
@source_name = 'TABLE1',
@role_name = 'cdc_TABLE1'
GO

Results:

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:

USE [DATABASE]
GO
ALTER AUTHORIZATION ON SCHEMA::[cdc] TO [cdc]
GO
  1. haleh salari sharif

    repaire cdc when cdc tables are droped manually:
    1. assume your database name is : DB1.
    2. assume Your last backup of DB1 which cdc tables are exists is : DBbk.
    3. restore DBbk.You can script those objects from this database in which you have successfully enabled CDC.
    4. authorize cdc user in DB1.
    5. mark created cdc tables as system tables in DB1.
    6. fill DB1 cdc tables from DBbk.
    7. do this proccess for all cdc tables which are exists in DBbk and are not exists in DB1.
    8. create a needed index on cdc.change_table.
    9. in security section , change cdc user , db role to ‘db_owner’
    10. in security section , correct schema owner to its owner not to cdc
    11. now you can disable cdc and enable it again.
    ———————-scripts step 1..6 ———————–
    use DB1
    GO

    /****** Object: Table [cdc].[captured_columns] Script Date: 02/11/2013 18:12:47 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [cdc].[captured_columns](
    [object_id] [int] NOT NULL,
    [column_name] [sysname] NOT NULL,
    [column_id] [int] NULL,
    [column_type] [sysname] NOT NULL,
    [column_ordinal] [int] NOT NULL,
    [is_computed] [bit] NULL,
    CONSTRAINT [captured_columns_clustered_idx] PRIMARY KEY CLUSTERED
    (
    [object_id] ASC,
    [column_ordinal] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    —————————-
    alter authorization on schema::[cdc] to [cdc]

    —————————-
    exec sp_MS_marksystemobject ‘[cdc].[captured_columns]’;

    —————————
    insert into [cdc].[captured_columns]
    SELECT * FROM [DBbk].[cdc].[captured_columns];

    ———————–scripts step 8 ———————-
    /****** Object: Index [source_object_id_idx] Script Date: 02/11/2013 18:44:00 ******/
    CREATE NONCLUSTERED INDEX [source_object_id_idx] ON [cdc].[change_tables]
    (
    [source_object_id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

  2. Hi, this just came in handy. Was stuck with exact same issue.

    Thanks

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>