SQL Server 2012 Change Data Capture Error 14234

42fyovps 于 2023-11-16 发布在 SQL Server

关注(0)|答案(6)|浏览(145)

I am having problems setting up change data capture on a SQL Server 2012 instance. Whenever I attempt to enable CDC on a table I get the following error:
Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623
Could not update the metadata that indicates table [dbo].[TableName] 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 [database name] 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.

The name of the server has not changed, I tried the sp_dropserver / sp_addserver solution and receive the following error:

Msg 15015, Level 16, State 1, Procedure sp_dropserver, Line 42
The server 'ServerName' does not exist. Use sp_helpserver to show available servers.

Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74
The server 'ServerName' already exists.

As I've stated, I'm trying to set up CDC and not replication. The version of SQL Server is: 11.0.5058.0 (SQL Server 2012 SP2)

I've looked at Error while enabling CDC on table level and tried that solution.

I've also tried:

exec sys.sp_cdc_add_job @job_type = N'capture'

I receive the following error:

Msg 22836, Level 16, State 1, Procedure sp_cdc_add_job_internal, Line 282
Could not update the metadata for database [DatabaseName] 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.

Any help would be greatly appreciated.

sql-server

来源：https://stackoverflow.com/questions/29033706/sql-server-2012-change-data-capture-error-14234

6条答案

按热度按时间

bbmckpt71#

As listed here, check the names match

SELECT srvname AS OldName FROM master.dbo.sysservers
SELECT SERVERPROPERTY('ServerName') AS NewName

If not, fix with:

sp_dropserver '<oldname>';  
GO  
sp_addserver '<newname>', local;  
GO

赞(0）回复(0）举报 2023-11-16

k2arahey2#

The error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers

赞(0）回复(0）举报 2023-11-16

snvhrwxg3#

Check these SQLs:

SELECT * FROM master.dbo.sysservers
SELECT SERVERPROPERTY('ServerName')

If your SERVERPROPERTY('ServerName') is not any of the sysservers , then the fix is to change your computer name to match one of those.

赞(0）回复(0）举报 2023-11-16

6jjcrrmo4#

Adding the server fixes the issue:

DECLARE @ServerName NVARCHAR(128) = CONVERT(sysname, SERVERPROPERTY('servername'));
EXEC sp_addserver @ServerName, 'local';
GO

赞(0）回复(0）举报 2023-11-16

ktca8awb5#

I had a similar situation, where I have restored a bak file which I got from another windows machine,which retained windows user account with the old PC name. I had delete the backup, create an empty data base and restore into that. This solved my issue

赞(0）回复(0）举报 2023-11-16

cl25kdpy6#

Query the following database table:

select * from msdb.dbo.cdc_jobs;

Clean all entries in that instance:

delete from msdb.dbo.cdc_jobs where database_id='<database_id>';

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server 2012 Change Data Capture Error 14234

6条答案

相关问题

热门标签

最新问答