Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred. You may receive below error while enabling CDC on a database table on SQL Server 2012 instance.

Error 22832, Severity 16, State 1,
Msg 22832, Level 16, State 1,
Procedure sp_cdc_enable_table_internal, Line 623
Could not update the metadata that indicates table TBLName 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 TestDB 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.

– This error occurs if you have changed the computer or server name after installing SQL Server, but have not followed the complete steps of making changes to SQL Server after renaming the computer. For example, if the computer or server name is TestSQL and you have installed a default instance of SQL Server on this server. Later you changed the named of this computer to NewTestSQL, but have not made any changes to the SQL Server. Now if you try to enable CDC on any database table, you will hit the above mentioned error.

To resolve the error, you will have to run the commands to drop the old server name and add new server name so that the SQL Server understands that the server name has changed. So, in this case we have to run the below commands

sp_dropserver ‘TestSQL’
GO
sp_addserver ‘NewTestSQL’, ‘local’
GO

As a best practice, restart the SQL Server instance once and then try to enabled the CDC and it should work fine now. Missing to run sp_dropserver or sp_addserver will not only affect CDC, but affects many other features like replication, mirroring, alwaysON, etc. So, just be mindful when you are making changes like renaming a computer or server, etc.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings