6

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 ?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
sequel.learner
  • 3,421
  • 7
  • 22
  • 24
  • The short answer to this is no, and because you can't. Can you give a bit more detail about what you're trying to do, maybe then people can help more usefully. – Will May 13 '13 at 10:56
  • @Will - I am looking for an SQL query in which you can put the IP, username, password etc. of a remote SQL server and then query that database on your local computer without having to type all those things into management studio and such. – sequel.learner May 14 '13 at 10:18
  • As an aside, i wonder if oracle, db2 and mysql etc. have a similar feature. – sequel.learner May 14 '13 at 10:22

4 Answers4

6

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

user219454
  • 81
  • 1
  • 8
Ruchan
  • 3,124
  • 7
  • 36
  • 72
2

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
Eugene
  • 2,965
  • 2
  • 34
  • 39
  • This works in MSSQL. I prefer to use `openquery` or the linked server name in EXEC-Statements like `EXEC('Select * FROM ABC') AT LinkdedServerName`. Don't know, if you're syntax is working, if you are using a mssql database with a mysql database via ODBC. – Stefan Brendle May 13 '13 at 10:59
  • Will this query work for accessing remote servers ? In your answer, is "server2" the connection string for the server 2 ? – sequel.learner May 14 '13 at 10:19
  • It looks like it. Here are the details on the procedure of running the sp_addlinkedserver procedure: http://msdn.microsoft.com/en-us/library/ff772782.aspx – Eugene May 14 '13 at 12:01
1

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

M-A Charlotte
  • 325
  • 1
  • 3
  • 10
-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
Michael Yaworski
  • 13,410
  • 19
  • 69
  • 97
TechnoCaveman
  • 1
  • 1