Tuesday, April 26, 2016

CDC error: 'The specified '@server' is invalid (valid values are returned by sp_helpserver)

I encountered the following error when I was enabling the CDC for the db1.dbo.pat_table


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

After researching around I  understood that the above error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers

I solved the issue as follows -

select @@SERVERNAME --oldname

select SERVERPROPERTY('ServerName') --defaultname


--1 drop the old servername
 
 
exec sp_dropserver 'oldservername'




--2 add new servername
 
 
exec sp_addserver 'newservername', 'local'




--3 see changes
 
 
exec sp_helpserver

3 comments: