在 sys.servers 中找不到服务器“服务器名称”。SQL Server 2014
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35799905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Could not find server 'server name' in sys.servers. SQL Server 2014
提问by VK_217
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
我最近将我们的 SQL Server 从 2005 年升级到 2014 年（链接服务器），我注意到其中一个调用 exec 命令以在升级后的链接服务器上执行存储过程的存储过程失败并出现错误
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.
在 sys.servers 中找不到服务器“服务器名称”。请验证是否指定了正确的服务器名称。如有必要，请执行存储过程 sp_addlinkedserver 将服务器添加到 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;
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?
我重新创建了链接服务器并启用了 RPC。我已授予存储过程的执行权限。我可以在链接服务器上选择记录并执行其他存储过程，但上面的 exec 失败（它在升级前工作）。SQL Server 2005 和 SQL Server 2014 之间是否存在导致此失败的语法差异？
采纳答案by VK_217
I figured out the issue. The linked server was created correctly. However, after the server was upgraded and switched the server name in sys.serversstill had the old server name.
我想通了这个问题。链接服务器已正确创建。但是，在服务器升级并切换服务器名称后，sys.servers仍然是旧的服务器名称。
I had to drop the old server name and add the new server name to sys.serverson the new server
我不得不删除旧服务器名称并将新服务器名称添加到新服务器sys.servers上
sp_dropserver 'Server_A'
GO
sp_addserver 'Server',local
GO
回答by Humayoun_Kabir
At first check out that your linked server is in the list by this query
首先通过此查询检查您的链接服务器是否在列表中
select name from sys.servers
If it not exists then try to add to the linked server
如果它不存在，则尝试添加到链接服务器
EXEC sp_addlinkedserver @server = 'SERVER_NAME' --or may be server ip address
After that login to that linked server by
之后登录到该链接服务器
EXEC sp_addlinkedsrvlogin 'SERVER_NAME'
,'false'
,NULL
,'USER_NAME'
,'PASSWORD'
Then you can do whatever you want ,treat it like your local server
然后你可以做任何你想做的事，把它当作你的本地服务器
exec [SERVER_NAME].[DATABASE_NAME].dbo.SP_NAME @sample_parameter
Finally you can drop that server from linked server list by
最后，您可以通过以下方式从链接服务器列表中删除该服务器
sp_dropserver 'SERVER_NAME', 'droplogins'
If it will help you then please upvote.
如果对你有帮助，请点赞。
回答by Ken Mc
I had the problem due to an extra space in the name of the linked server. "SERVER1, 1234" instead of "SERVER1,1234"
由于链接服务器的名称中有多余的空格，我遇到了问题。“SERVER1, 1234”而不是“SERVER1,1234”
