I was working with a client and they had set up one sql server for an ETL process. When we tried to get the data from the database we got the error:
"Could not find server 'server name' in sys.servers in SQL Server"
How to resolve this
First you need to check if the server exists in sys servers,
- select name from sys.servers
You will get the servers list here, if the server does not exist in the list, then add it using the command,
- EXEC sp_addlinkedserver @server = 'New_Server_Name'
Once the server is added to the linked server, then you can log in like this,
- EXEC sp_addlinkedsrvlogin 'New_Server_Name','false',NULL,'USERNAME','PASSWORD'
Now you can do whatever you want to do, you can use your local server now,
- exec [New_Server_Name].[Database_Name].dbo.Procedure_NAME
Finally, you can drop this server from the linked server list using this command,
- sp_dropserver 'New_Server_Name', 'droplogins'