Every environment is different, and it is not uncommon to encounter unexpected results when making changes to your SQL Server setup. In this article, we will explore a common issue related to enabling Change Data Capture (CDC) and discuss how to troubleshoot and resolve it.

A reader recently reached out to me after encountering an error while trying to enable CDC for a table. The error message they received was:

“Could not update the metadata that indicates table [HumanResources].[Shift] 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 AdventureWorks2014 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.'”

To understand the cause of this error, we need to examine the script that was used to enable CDC:

-- You can run this stored procedure in the context of each database to enable CDC at the database level.
-- (The following script will enable CDC in AdventureWorks2014 database)
USE AdventureWorks2014
GO
EXEC sys.sp_cdc_enable_db
GO

-- Following script will enable CDC on HumanResources.Shift table.
USE AdventureWorks2014
GO
EXEC sys.sp_cdc_enable_table @source_schema = N'HumanResources', @source_name = N'Shift', @role_name = NULL
GO

The first command, which enables CDC at the database level, executed successfully. However, the error was raised by the second command, which enables CDC for the specific table.

Upon further investigation, it was discovered that the error was caused by a mismatch between the SQL Server instance name obtained using the following query:

SELECT @server = CONVERT(SYSNAME, SERVERPROPERTY('ServerName'))

The obtained value is then used to create the CDC job. In the procedure sp_verify_jobstep, the following condition was failing:

IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT * FROM MASTER.dbo.sysservers WHERE (UPPER(srvname) = UPPER(@server)))))
    RAISERROR(14234, -1, -1, '@server', 'sp_helpserver')

It was discovered that the SQL Server name had been changed, but the sp_dropserver and sp_addserver commands were not executed to update the server name. To resolve the issue, the following commands were executed:

sp_dropserver 'HostName\InstanceName_incorrect'
GO
sp_addserver 'HostName\InstanceName', 'local'
GO

By executing these commands with the correct parameter values for your SQL Server instance, the issue can be resolved.

In conclusion, when encountering errors related to enabling Change Data Capture in SQL Server, it is important to check for any mismatches between the SQL Server instance name obtained using SERVERPROPERTY('ServerName') and the server name stored in the master.dbo.sysservers table. By ensuring that these values match, you can successfully enable CDC for your tables.

Have you ever encountered similar issues while working with CDC in SQL Server? How did you troubleshoot and resolve them? Share your experiences and insights in the comments below, as it can greatly benefit other readers facing similar challenges.