Error while enabling CDC on table level

Question

I am enabling Change data capture (CDC) on SQL server 2012 Enterprise edition(11.0.2100.60)

. I am able to enable it on Database level with below SQL, but failed to enable on Table level.

Use DatabaseName
GO

Exec sys.sp_cdc_enable_db 
GO

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo'
                            ,@source_name = N'TableName'
                            , @role_name = NULL
GO

Got Error like,

'msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623

Could not update the metadata that indicates table [dbo].[TableName] 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 DatabaseName 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.'

Would anyone help me to out of this?

Thanks in advance..!!

Answer 1

I had the same problem recently.

In my opinion, it is highly probable that the name of the computer has changed. If it is true, read on ...

Name Changing the computer is automatically recognized by SQL. However, system metadata must be updated manually. (Source: http://msdn.microsoft.com/en-us/library/ms143799.aspx)

SQL Statement

sp_dropserver old_name\instancename;
GO
sp_addserver new_name\instancename, local;
GO

Example

sp_dropserver name-PC\SQLEXPRESS;
GO
sp_addserver CLT55\SQLEXPRESS, local;
GO

Reference problem and solution