T SQL Query Remote Servers

To search remote servers you can use this syntax:

select *
from [serverinstance].database.owner.tablename

I found this on a stackoverflow question:

quering remote database using sql server?

When I attempted to run this without any tweaks on my servers I got this error:

Msg 7202, Level 11, State 2, Line 1
Could not find server MyServer’ 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.

To enable searching you have to add the remote server as a "linked" server. In effect, linking servers appears to merely enable remote connectivity by adding it to the sys.servers table for the instance from which you are calling.

Related Posts by Categories