Could not find server 'server name' in sys

问题:

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

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;

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?

回答1:

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.

回答2:

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.

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

回答3:

I had the problem due to an extra space in the name of the linked server. "SERVER1, 1234" instead of "SERVER1,1234"