CODEWITHSUNDEEP
sqlsql-server
sequel.learner
sequel.learner

Reputation: 3941

Connect to another SQL Server via SQL Query?

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 ?

Upvotes: 6

Views: 53021

Answers (4)

M-A Charlotte
M-A Charlotte

Reputation: 363

It is not simply possible to connect to 2 different SQL servers simultaneously with one query if you have a query that needs to run on 2 SQL servers' databases to get a required resultset (distributed query)

Then you must have to create a "Linked Server" in your local SQL server "linked to" the 2nd SQL server (the remote SQL server)

Once the Linked Server is created in your local server you may query both servers with one query from your local server's connection.

The linked Servers can be queried directly or by using OPENQUERY.

There is a difference of performance between 'Direct Linked Server Query' and 'Linked Server OPENQUERY' as in the direct query entire data will be flown back to local server from remote server and then the local server will process the filters locally while in OPENQUERY the process will be completed on the remote server and local server will get only the filtered dataset

Upvotes: 1

Ruchan
Ruchan

Reputation: 3194

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

Upvotes: 6

TechnoCaveman
TechnoCaveman

Reputation: 1

This works in SQL 2012. Shows up in a grey box Has to be run prior to the SQL to be run on the other server, else the code runs on which ever server/database the query window is connected to. Local variables like @@Servername and SERVERPROPERTY return the same results as the server connected to. Which was darn unexpected!!!!

:Connect servername

Example run from SQLTEST

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

:Connect CSQL2008

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

SELECT CONVERT(sysname, SERVERPROPERTY('servername'));

Produces: 

CSQL2008

CSQL2008

CSQL2008

While 

select distinct( server_name) from msdb.dbo.backupset 
go

:Connect CSQL2012

select distinct( server_name) from msdb.dbo.backupset ;

go

select distinct( server_name) from msdb.dbo.backupset ;

produces:

SQLTEST

CSQL2012

CSQL2012

Upvotes: -1

Eugene
Eugene

Reputation: 2985

Microsoft SQL Server only:

Yes, it is possible. You have to configure a linked Microsoft SQL Server. Here is the info: http://msdn.microsoft.com/en-us/library/ms188279.aspx

Once you have your servers configured, your query (on server1) would look like the following:

SELECT TOP 10 * FROM server2.yourdatabase.yourschema.yourtable

Upvotes: 2

Related Questions