sp_dropserver @server = '<serverName>'
sp_addserver '<machineName>\<instanceName>', local
sp_addserver '<machineName>', @local= 'local'
Here's another take on the same fix:
Machines were being re-imaged and the name of the SQL Server in the image didn't always match the computer / server name. When there was a mis-match
linked server queries to the machine would fail since they were going out to the OLE DB provider which couldn't find the server name.
Make sure that the computer / server name and the SQL Server name match.
-- check that the output of this query is the same as your computer name
-- if @@servername does not match your computer name, then execute these steps
--- backup master ....
exec sp_helpserver -- to see what you have now
exec sp_dropserver 'ResultsOfServerName'
exec sp_dropserver 'YourMachineName'
exec sp_addserver 'YourMachineName', 'local'
exec sp_helpserver -- to see what you have after the changes
-- restart SQL Server
-- Computer/machine name is CurrentComputer
-- select @@servername returns DifferentComputer
-- backup master --
exec sp_dropserver 'DifferentComputer'
exec sp_dropserver 'CurrentComputer'
exec sp_addserver 'CurrentComputer', 'local'