Every environment is different and it is no doubt some of these environment changes can give you results that are not expected. Recently one of my blog reader followed below blogs:
- SQL SERVER – Introduction to Change Data Capture (CDC) in SQL Server 2008
- SQL SERVER – Download Script of Change Data Capture (CDC)
and they reported that when he is trying to enable CDC for a table, he was getting below error:
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623
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.’. Use the action and error to determine the cause of the failure and resubmit the request.
So the best way to learn these new things is by exploring the events how it happened. Below is the script he was using (taken from my blog)
-- You can run this stored procedure in the context of each database to enable CDC at database level.
-- (The following script will enable CDC in AdventureWorks2014 database)
-- Following script will enable CDC on HumanResources.Shift table.
@source_schema = N'HumanResources',
@source_name = N'Shift',
@role_name = NULL
First command was working fine. Error was raised by second command. I asked him to capture the profiler and share with me. I found that first SQL Server get instance name using below query:
SELECT @server = CONVERT(SYSNAME,SERVERPROPERTY('ServerName'))
The value is then passed to create the job for CDC. In procedure sp_verify_jobstep, below is the condition which was failing.
IF (@server IS NOT NULL) AND (NOT EXISTS (SELECT *
WHERE (UPPER(srvname) = UPPER(@server))))
RAISERROR(14234, -1, -1, '@server', 'sp_helpserver')
Notice that this is the same error in error message (which I have highlighted) I asked him to check and he verified that SQL Server name was changed but sp_dropserver and sp_addserver was not executed. Here is the command to fix the issue. Please change the parameter values as per your SQL Instance.
sp_addserver 'HostName\InstanceName', 'local'
In short, error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers
Have you ever done similar troubleshooting in your environments using Profiler? Do let me know what you did and what was the learning. I am sure a lot of readers are going to benefit from your stories and scenarios.
Reference: Pinal Dave (https://blog.sqlauthority.com)