Could not find server 'server name' in sys.servers. SQL Server 2014

Question

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?

Answer 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.

Answer 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