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 ?
-
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– Will2013-05-13 10:56:10 +00:00Commented May 13, 2013 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– sequel.learner2013-05-14 10:18:37 +00:00Commented May 14, 2013 at 10:18
-
As an aside, i wonder if oracle, db2 and mysql etc. have a similar feature.sequel.learner– sequel.learner2013-05-14 10:22:16 +00:00Commented May 14, 2013 at 10:22
Add a comment
|
4 Answers
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')
1 Comment
gary
Sorry for necropost: can we be connected to more than one server simultaneously?
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
3 Comments
Stefan Brendle
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.
sequel.learner
Will this query work for accessing remote servers ? In your answer, is "server2" the connection string for the server 2 ?
Eugene
It looks like it. Here are the details on the procedure of running the sp_addlinkedserver procedure: msdn.microsoft.com/en-us/library/ff772782.aspx
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
Comments
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
1 Comment
Ben Personick
@@ are global variables, which would be why they change depending on which server you connect to.