在sys.servers中找不到服务器“服务器名称". SQL Server 2014 [英] Could not find server 'server name' in sys.servers. SQL Server 2014

问题描述

我最近将SQL Server从2005年升级到了2014年(链接服务器)，并且我注意到调用exec命令以在升级的链接服务器上执行存储过程的存储过程之一失败，并显示错误

I recently upgraded our SQL Server from 2005 to 2014 (linked server) and I am noticing that one of the stored procedures which calls the exec command to execute a stored procedure on the upgraded linked server is failing with the error

在sys.servers中找不到服务器服务器名称".验证是否指定了正确的服务器名称.如有必要，执行存储过程sp_addlinkedserver将服务器添加到sys.servers.

Could not find server 'server name' in sys.servers.Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

问题是链接服务器存在，并且我已经进行了测试以确保可以从链接服务器中查询表.这是我所做的检查，以查看链接服务器的配置是否正确.

The issue is that the linked server exists and I have done tests to ensure I can query the tables from the linked server. Here are the checks I did to see if the linked server is configured correctly.

 - select name from sys.servers -- > Lists the linked server 
 - select top 10 * from linkedserver.database.dbo.table -->  Gets top 10 records 
 - exec linkedserver.database.dbo.storedproc --> Executes the stored procedure (I created a test stored procedure on the linked server and I can execute it)

但是失败的原因是下面的

However the one that is failing with the error is below

exec linkedserver.database.dbo.failing_storedprocedure  @id,'load ','v2',@file_name, @list_id = @listid output;

我重新创建了链接服务器并启用了RPC.我已授予对存储过程的执行权限.我可以选择记录并在链接的服务器上执行其他存储过程，但是上面的exec失败了(它在升级之前可以工作).SQLServer 2005和SQL Server 2014之间的语法差异是否导致失败?

I've recreated the linked server and RPC is enabled.I've granted execute permission on the stored procedure. I can select records and execute other stored procedures on the linked server but the above exec is failing(it worked before the upgrade).Is there a syntax difference between SQL Server 2005 and SQL Server 2014 that is causing this to fail?

推荐答案

我发现了问题所在.链接服务器已正确创建.但是，在升级并切换服务器后，sys.servers中的服务器名称仍具有旧的服务器名称.

I figured out the issue. The linked server was created correctly. However, after the server was upgraded and switched the server name in sys.servers still had the old server name.

我必须删除旧服务器名称，并将新服务器名称添加到新服务器上的sys.servers

I had to drop the old server name and add the new server name to sys.servers on the new server

sp_dropserver 'Server_A'
GO
sp_addserver  'Server',local
GO

这篇关于在sys.servers中找不到服务器“服务器名称". SQL Server 2014的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！