[sql] 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?

The answer is


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.


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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to stored-procedures

How to create temp table using Create statement in SQL Server? How do I pass a list as a parameter in a stored procedure? SQL Server IF EXISTS THEN 1 ELSE 2 Stored procedure with default parameters Could not find server 'server name' in sys.servers. SQL Server 2014 How to kill all active and inactive oracle sessions for user EXEC sp_executesql with multiple parameters MySQL stored procedure return value SQL Server: use CASE with LIKE SQL server stored procedure return a table

Examples related to sql-server-2014

Could not find server 'server name' in sys.servers. SQL Server 2014 How do I create a local database inside of Microsoft SQL Server 2014? SQL Server after update trigger Possible to restore a backup of SQL Server 2014 on SQL Server 2012? How to restore SQL Server 2014 backup in SQL Server 2008 Partition Function COUNT() OVER possible using DISTINCT

Examples related to linked-server

Could not find server 'server name' in sys.servers. SQL Server 2014 "Cannot create an instance of OLE DB provider" error as Windows Authentication user SQL Server Linked Server Example Query How to select data of a table from another database in SQL Server? How to find a text inside SQL Server procedures / triggers? Querying a linked sql server