通过 SQL 查询连接到另一个 SQL Server？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16520331/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connect to another SQL Server via SQL Query?
提问by sequel.learner
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 ?
是否可以使用 SQL 查询连接到另一个 SQL Server（以便也可以在该服务器上运行查询并使用当前服务器中的结果集）。如果不是，那为什么？
回答by Ruchan
Yes, you can accomplish connecting to another SQL Server by using a Linked Server, or you can query through servers by using openquery:
是的，您可以使用链接服务器完成连接到另一个 SQL Server，或者您可以使用 openquery 通过服务器查询：
SELECT * FROM openquery([aa-db-dev01], 'Select * from TestDB.dbo.users')
SELECT * FROM openquery([aa-db-dev01], 'Select * from TestDB.dbo.users')
回答by Eugene
Microsoft SQL Server only:
仅限 Microsoft SQL Server：
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
对的，这是可能的。您必须配置链接的 Microsoft SQL Server。这是信息：http: //msdn.microsoft.com/en-us/library/ms188279.aspx
Once you have your servers configured, your query (on server1) would look like the following:
配置好服务器后，您的查询（在 server1 上）将如下所示：
SELECT TOP 10 * FROM server2.yourdatabase.yourschema.yourtable
回答by M-A Charlotte
It is notsimply 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)
如果您的查询需要在 2 个 SQL 服务器的数据库上运行以获取所需的结果集（分布式查询），则不可能通过一个查询同时连接到 2 个不同的 SQL 服务器
Then you musthave to create a "Linked Server"in your local SQL server "linked to"the 2nd SQL server (the remote SQL server)
然后，您必须在“链接到”第二个 SQL 服务器（远程 SQL 服务器）的本地 SQL 服务器中创建一个“链接服务器”
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 directlyor by using OPENQUERY.
可以直接或使用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 processthe filters locally while in OPENQUERY the process will be completed on the remote serverand local server will get only the filtered dataset
“直接链接服务器查询”和“链接服务器OPENQUERY”之间存在性能差异，因为在直接查询中，整个数据将从远程服务器飞回本地服务器，然后本地服务器将在本地处理过滤器，而在 OPENQUERY 中过程将在远程服务器上完成，本地服务器将只获取过滤后的数据集
回答by TechnoCaveman
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!!!!
这在 SQL 2012 中有效。显示在灰色框中 必须在 SQL 在另一台服务器上运行之前运行，否则代码将在查询窗口连接到的服务器/数据库上运行。@@Servername 和 SERVERPROPERTY 等局部变量返回的结果与连接的服务器相同。这真是出乎意料！！！！
:Connect servername
:连接服务器名
Example run from SQLTEST
从 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
