sqlsql-serverstored-proceduressql-server-2014linked-server

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

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?

Solution

  • 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