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)
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
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 *
FROM MASTER.dbo.sysservers
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_dropserver 'HostName\InstanceName_incorrect'
GO
sp_addserver 'HostName\InstanceName', 'local'
GO
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)
24 Comments. Leave new
Dear Pinal
thanks for your valuble information and for your sharing i have the same issue and when i run this command
sp_dropserver ‘ELMASRY-PCSQLSERVER2014’
GO
I found this error
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 61
There are still remote logins or linked logins for the server ‘ELMASRY-PCSQLSERVER2014’.
Any advice please what is the meaning of this error
thanks
try below
sp_dropserver ‘ELMASRY-PCSQLSERVER2014′, ‘droplogins’;
Thanks Pinal i now now the issue
the issus this server used as Publisher so i should drop the replication at the first but i don’t know why sometimes when i drop the replication after i delete the subscription and succus i go for deleting the Publisher but at this time i received this error
TITLE: Microsoft SQL Server Management Studio
——————————
Could not delete publication ‘Replica’.
For help, click:
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission.
Changed database context to ‘AdventureWorks2014’. (Microsoft SQL Server, Error: 15517)
For help, click:
——————————
BUTTONS:
OK
——————————
check database owner. check if database ownership chaining is OFF.
why I must drop and add new name for server ?
I’ve already dropped old server and added new server with efferent name and error still shown
‘Could not update the metadata that indicates table [dbo].[ERP_Taxs] is enabled for Change Data Capture. The failure occurred when executing the command ‘sp_cdc_grant_select_on_change_enumeration_functions’. The error returned was 4617: ‘Cannot grant, deny or revoke permissions to or from special roles.’. Use the action and error to determine the cause of the failure and resubmit the request.’
Help me
I have a problem while EXECUTE sys.sp_cdc_enable_table with it’s paramters
EXECUTE sys.sp_cdc_enable_table @source_schema = N’dbo’ , @source_name = N’ERP_Taxs’ , @role_name =’db_owner’,@supports_net_changes = 1;
the error is
Could not update the metadata that indicates table [dbo].[ERP_Taxs] is enabled for Change Data Capture. The failure occurred when executing the command ‘sp_cdc_grant_select_on_change_enumeration_functions’. The error returned was 4617: ‘Cannot grant, deny or revoke permissions to or from special roles.’. Use the action and error to determine the cause of the failure and resubmit the request.
I’ve already enabled CDC on my db using EXECUTE sys.sp_cdc_enable_db and result was success
thanks all
Thanks a Ton Sir !! I was facing the same problem and was stuck for days. You have explained the cause and solution in very simple language.
Thanks mate, this saved my life
@Adam – I am no one to safe a life.
If I didn’t read this post, I would scratch my head hours to hours.
I’m new to MSSQL and in my case: I’m moving data from old docker container into a new one with data reused.
Many thanks for the helpful post
Oh.. it works for containers also? Thanks for sharing that.
But I need to guess the parameter, your content is a little bit hardly to understand because I’m not an expert.
Here how I did it:
SELECT CONVERT(SYSNAME,SERVERPROPERTY(‘ServerName’)); — Get ServerName current here 1b23b4b8a3ec
SELECT srvname, * from master.dbo.sysservers; — GET old srvname here: 61344098ae6a
sp_helpserver;
sp_dropserver ‘61344098ae6a’; — Remove old
sp_addserver ‘1b23b4b8a3ec’, ‘local’; — Add new
I have not used containers so thanks again for sharing this.
Awesome! Thanks.
my pleasure.
Why can’t we enable CDC for Azure sql databases?
You need to ask from Microsott.
same issue with Docker SQL, Pinal in addition to Neo help me, thanks.
Thanks for this. 3 years later and the information is still relevant today!
Thanks Dave, that did the trick, I totally forgot that I had to rename my machine.
Hello Pinal,
I am also facing the similar kind of issue on SQL 2019 community edition. I did not see the difference in names. Please see the below output. Any other cause ?
SELECT CONVERT(SYSNAME,SERVERPROPERTY(‘ServerName’))
output : EC2AMAZ-LMU2FJO
select srvname from master.dbo.sysservers
output :
srvid srvstatus srvname
0 1089 EC2AMAZ-LMU2FJO
Error :
Could not update the metadata that indicates table [dbo].[customer] 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 tpcc to indicate that a Change Data Capture job has been added. The failure occurred when executing the command ‘insert into msdb.dbo.cdc_jobs’. The error returned was 2627: ‘Violation of PRIMARY KEY constraint ‘PK__cdc_jobs__1D9AFDD37540A036’. Cannot insert duplicate key in object ‘dbo.cdc_jobs’. The duplicate key value is (5, capture).’. 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.
In my case, it was failing because “capture and cleanup” job entries were not deleted earlier from “msdb.dbo.cdc_jobs”. Now, I have deleted both the records from msdb.dbo.cdc_jobs and re-executed sys.sp_cdc_enable_db & sys.sp_cdc_enable_table successfully.
I’m getting below error while implementing CDC Error: “Problems when trying to get changed records from CDC. Reason – Invalid column name ‘__$command_id
In my case , getting this issue while enabling cdc on table level,
Could not update the metadata that indicates table [dbo].[care_team_prv] is enabled for Change Data Capture. The failure occurred when executing the command ‘sp_cdc_create_change_table’. The error returned was 229: ‘The EXECUTE permission was denied on the object ‘sp_send_dbmail’, database ‘msdb’, schema ‘dbo’.’. Use the action and error to determine the cause of the failure and resubmit the request.