SQL Server – Error While Enabling CDC on a Table – The specified ‘@server’ is invalid (valid values are returned by sp_helpserver)

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:

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)

Previous Post
Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder! – Notes from the Field #077
Next Post
SQL SERVER – Generate Different Random Number for Each Group Using RAND() Function

Related Posts

20 Comments. Leave new

  • Mustafa EL-Masry
    April 26, 2015 11:31 am

    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

    Reply
    • try below

      sp_dropserver ‘ELMASRY-PCSQLSERVER2014′, ‘droplogins’;

      Reply
      • Mustafa EL-Masry
        April 29, 2015 12:57 pm

        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

    Reply
  • 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

    Reply
  • 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.

    Reply
  • Thanks mate, this saved my life

    Reply
  • 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

    Reply
  • 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

    Reply
  • Vitor Gonçalves
    January 23, 2018 4:56 pm

    Awesome! Thanks.

    Reply
  • Why can’t we enable CDC for Azure sql databases?

    Reply
  • same issue with Docker SQL, Pinal in addition to Neo help me, thanks.

    Reply
  • Thanks for this. 3 years later and the information is still relevant today!

    Reply
  • Thanks Dave, that did the trick, I totally forgot that I had to rename my machine.

    Reply

Leave a Reply

Menu