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'