Connect to another SQL Server via SQL Query?

Question

Is it possible to connect to another SQL Server using a SQL query (so that one can also run queries on that server and use the result set in current server). If not, then why ?

Answer 1

Yes, you can accomplish connecting to another SQL Server by using a Linked Server, or you can query through servers by using openquery:

SELECT * FROM openquery([aa-db-dev01], 'Select * from TestDB.dbo.users')

Querying a linked sql server