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 --  gt  Lists the linked server   - select top 10   from linkedserver database dbo table -- gt   Gets top 10 records   - exec linkedserver database dbo storedproc -- gt  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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to stored-procedures

Examples related to sql-server-2014

Examples related to linked-server