none
Change Data Capture on table

    Question

  • I have been trying all day to make this thing work but it seems impossible!

    I need to implement an ETL for managing changes in SSIS, and as i have been reading around, there is need for CDC to be activated on DB and Table level. I managed to activate it on DB but  not on table.

    I get the following error:

    Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623
    Could not update the metadata that indicates table [dbo].[Köp] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database Elektronikkedja to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

    I would really appreciate some help! :( 

    Saturday, December 07, 2013 5:35 PM

Answers

  • Hello there, Blerina!

    There are a number of things you can try:

    1. Update your SQL Server Installation to the latest Service Pack and Cumulative Update.

    2. See if you have any pending Windows updates (for example on the .NET side), complete them and restart the machine

    3. Check the solution mentioned in this following link as it might be your case. I will copy/paste it for your convenience:

    If  you renamed your server recently after the cdc feature was enabled and have not update the sql server sys.servers view, you would have the issue. You can check sys.servers view to see the result. The workaround is

    1) exec sp_dropserver youroldservername

    2) sp_addserver yournewservername, local

    3) execute your cdc scripts.

    Even if you didn't rename your server, it is worth a try. Please let me know if it helped you!

    Best regards,

    Razvan


    Per aspera ad astra!
    journeyintobi.com

    Saturday, December 07, 2013 10:38 PM