'Could not find server 'server name' in sys.servers - sql server 2017 v14.0.3436.1

We're having trouble with running SQL 'exec' but if we run 'select' we don't have a problem.

  1. We had Win 2012 Servers with SQL Server 2014 -
  2. We are upgrading WinOS and SQL version, so now have identical servers with Win2019 + SQL 2017 Server v14.0.3436.1
  3. We restored the databases from the old servers to the new servers

We have a linked server setup, and we can run the following 'select' command against the remote server and it works fine and returns results.

i.e.:

select * from CSDB23.systemlive.dbo.dt_currency

enter image description here

However when we run the 'exec' command:

exec csdb23.systemlive.dbo.p_transfer 3,'SystemProd1'

we get the following issue:

Msg 7202, Level 11, State 2, Procedure systemlive.dbo.p_transfer, Line 31 [Batch Start Line 0]
Could not find server 'CSDb23' in sys.servers. Verify that the correct server name was specified."

enter image description here

We see support issues suggested we drop and add the server using commands from SQL Server 2016 (but the commands given are depreciated from SQL 2016 so not relevant in 2017

sp_dropserver 'Server_A'
GO
sp_addserver  'Server',local
GO

but the linkserver is already setup:

[enter image description here

A bit lost on how or what I need to check so we don't get this issue?

Thank you

sql-serversql-server-2017


Solution 1:[1]

We found the issue to be with the Server Name.

When the server was created in Azure, it was created with a Market Place image with SQL already a part of the image.

The server was named (just an example) "Sever-DB10". Once the servers were deployed, the name of the sever was changed to "MainDB10" - However, the SQL instance was still seeing the server as "Server-DB10" when we looked up the names of the SQL instances using:

select name from sys.syservers

We see the server was referencing the old name of the server and not the new name. We manually dropped the name and readded it back via the linked server with the correct name. - we no longer then had the main issue "Cannot find server X)

(We hada raft of other Access issues after this and eventually reinstalled SQL which seemed to completely resolve all issues we had.)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Awsming

Related Questions