locked
SQL server CDC error RRS feed

  • Question

  • when i try to enable cdc on a table i get the below error. i tried making db_owner as sa, granted view server state permission but still i get the below error.

    EXECUTE sys.sp_cdc_enable_table
         @source_schema = N'dbo'
       , @source_name = N'CDC_test1'
       , @role_name = Null
       --@supports_net_changes = 1
     GO


    Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607
    Could not update the metadata that indicates table [dbo].[CDC_test1] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_change_table'. The error returned was 297: 'The user does not have permission to perform this action.'. Use the action and error to determine the cause of the failure and resubmit the request.
    Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table_internal, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_table, Line 61
    The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
    Msg 266, Level 16, State 2, Procedure sp_cdc_enable_table, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    Msg 3998, Level 16, State 1, Line 1
    Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

    Tuesday, May 20, 2014 7:20 AM

Answers

  • Hi,

    Thanks for the reply, after troubleshooting, i found that a DDL trigger was enabled within the database, i disabled it and then tried to enable CDC on table. it worked.  

    Regards,

    Vamsi



    vamsi chaitanya

    • Marked as answer by Elvis Long Tuesday, May 27, 2014 6:04 AM
    Tuesday, May 27, 2014 5:32 AM

All replies

  • In order to successfully run that stored procedure, the user who is running it must be a member of the db_owner role.  To test whether you are a member of that role, you can run

      IF IS_MEMBER ('db_owner') = 1
         PRINT 'Current user is a member of the db_owner role'
      ELSE IF IS_MEMBER ('db_owner') = 0
         PRINT 'Current user is NOT a member of the db_owner role'
      ELSE IF IS_MEMBER ('db_owner') IS NULL
         PRINT 'ERROR: Invalid group / role specified'
      GO
    
    Tom

    Tuesday, May 20, 2014 2:32 PM
  • Hi Tom,

    Thanks for the reply, "Current user is a member of the db_owner role", this is the message which i got when i ran your query using my login and even as SA. 

    I am not able to enable CDC on new tables or disable CDC on CDC enabled tables. Please help.

    Vamsi

    Tuesday, May 20, 2014 4:11 PM
  • Hi,

    Can you check the owner of the database and change it to sa OR can you make sure that owner of the database has sysadmin or db_owner privilges?

    --Prashanth

    Tuesday, May 20, 2014 4:28 PM
  • Hi Prashanth,

    Thanks for your reply. I made owner of the database as sa but still its not working. I made user as owner of the db, but still not working. same error. 

    Tuesday, May 20, 2014 4:38 PM
  • the user is sys admin and has db owner permissions also, but still not working.
    Tuesday, May 20, 2014 4:40 PM
  • Depending on what release you are on and what the value of SET ANSI_NULL_DFLT_OFF ON was when you ran sys.sp_cdc_enable_db , you can get this error.  See Microsoft Connect Issue 387050.

    https://connect.microsoft.com/SQLServer/feedback/details/387050/change-data-capture-sys-sp-cdc-enable-db-not-creating-cdc-system-tables-properly

    Tom

    • Proposed as answer by Naomi N Tuesday, May 20, 2014 5:05 PM
    • Unproposed as answer by vamsichaitanya Tuesday, May 20, 2014 5:27 PM
    Tuesday, May 20, 2014 4:55 PM
  • Hi Tom,

    Sorry, i made it as unproposed as answer, please help me. 

    We are on Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 and when i check select * from cdc.change_tables, the column "has-drop_pending" is set to NULL for all the tables.

    So what do you suggest now ? please help.


    vamsi chaitanya

    Tuesday, May 20, 2014 5:31 PM
  • Sorry, I have no more ideas.  Hopefully someone else can help you.

    Tom

    Tuesday, May 20, 2014 6:52 PM
  • Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 607
    Could not update the metadata that indicates table [dbo].[CDC_test1] is enabled for Change Data Capture. The failure occurred when executing the command 'sp_cdc_create_change_table'. The error returned was 297: 'The user does not have permission to perform this action.'. Use the action and error to determine the cause of the failure and resubmit the request.

    Hi vamsichaitanya,

    Please try to run the following command to check current users, sessions, and processes information:

    EXEC SP_WHO

    click on SSMS and select "Run as Administrator". For more information regarding enable and disable Change Data Capture, please see:
    http://technet.microsoft.com/en-us/library/cc627369(v=sql.110).aspx

    Regards, 


    Elvis Long
    TechNet Community Support

    Monday, May 26, 2014 12:59 AM
  • Hi,

    Thanks for the reply, after troubleshooting, i found that a DDL trigger was enabled within the database, i disabled it and then tried to enable CDC on table. it worked.  

    Regards,

    Vamsi



    vamsi chaitanya

    • Marked as answer by Elvis Long Tuesday, May 27, 2014 6:04 AM
    Tuesday, May 27, 2014 5:32 AM