在 SQL Server 中从两个不同的服务器中选择数据

Selecting data from two different servers in SQL Server

提问by

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

如何从 SQL Server 中两台不同服务器上的两个不同数据库中选择同一查询中的数据？

回答by Eric

What you are looking for are Linked Servers. You can get to them in SSMS from the following location in the tree of the Object Explorer:

您正在寻找的是链接服务器。您可以从对象资源管理器树中的以下位置在 SSMS 中访问它们：

Server Objects-->Linked Servers

Server Objects-->Linked Servers

or you can use sp_addlinkedserver.

或者您可以使用sp_addlinkedserver。

You only have to set up one. Once you have that, you can call a table on the other server like so:

您只需要设置一个。完成后，您可以像这样调用另一台服务器上的表：

select
    *
from
    LocalTable,
    [OtherServerName].[OtherDB].[dbo].[OtherTable]

Note that the owner isn't always dbo, so make sure to replace it with whatever schema you use.

请注意，所有者并不总是dbo，因此请确保将其替换为您使用的任何架构。

回答by Raging Bull

You can do it using Linked Server.

您可以使用链接服务器来完成。

Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel.

通常，链接服务器配置为使数据库引擎能够执行 Transact-SQL 语句，该语句包括另一个 SQL Server 实例或另一个数据库产品（如 Oracle）中的表。许多类型的 OLE DB 数据源可以配置为链接服务器，包括 Microsoft Access 和 Excel。

Linked servers offer the following advantages:

链接服务器具有以下优势：

• The ability to access data from outside of SQL Server.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

• 从 SQL Server 外部访问数据的能力。
• 在整个企业的异构数据源上发布分布式查询、更新、命令和事务的能力。
• 类似地处理不同数据源的能力。

Read more about Linked Servers.

阅读有关链接服务器的更多信息。

Follow these steps to create a Linked Server:

按照以下步骤创建链接服务器：

1. Server Objects -> Linked Servers -> New Linked Server

2. Provide Remote Server Name.

3. Select Remote Server Type (SQL Server or Other).

4. Select Security -> Be made using this security context and provide login and password of remote server.

5. Click OK and you are done !!

1. 服务器对象 -> 链接服务器 -> 新链接服务器

2. 提供远程服务器名称。

3. 选择远程服务器类型（SQL Server 或其他）。

4. 选择 Security -> Be made using this security context 并提供远程服务器的登录名和密码。

5. 点击确定，你就完成了！！

Hereis a simple tutorial for creating a linked server.

这是创建链接服务器的简单教程。

OR

或者

You can add linked server using query.

您可以使用查询添加链接服务器。

Syntax:

句法：

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

Read more about sp_addlinkedserver.

阅读有关sp_addlinkedserver 的更多信息。

You have to create linked server only once. After creating linked server, we can query it as follows:

您只需创建一次链接服务器。创建链接服务器后，我们可以如下查询：

select * from LinkedServerName.DatabaseName.OwnerName.TableName

回答by RSolberg

SELECT
        *
FROM
        [SERVER2NAME].[THEDB].[THEOWNER].[THETABLE]

You can also look at using Linked Servers. Linked servers can be other types of data sources too such as DB2 platforms. This is one method for trying to access DB2 from a SQL Server TSQL or Sproc call...

您还可以查看使用链接服务器。链接服务器也可以是其他类型的数据源，例如 DB2 平台。这是尝试从 SQL Server TSQL 或 Sproc 调用访问 DB2 的一种方法...

回答by super9

Querying across 2 different databases is a distributed query. Here is a list of some techniques plus the pros and cons:

跨 2 个不同数据库的查询是分布式查询。以下是一些技术以及优缺点的列表：

1. Linked servers:Provide access to a wider variety of data sources than SQL Server replication provides
2. Linked servers:Connect with data sources that replication does not support or which require ad hoc access
3. Linked servers:Perform better than OPENDATASOURCE or OPENROWSET
4. OPENDATASOURCEand OPENROWSETfunctions: Convenient for retrieving data from data sources on an ad hoc basis. OPENROWSET has BULK facilities as well that may/may not require a format file which might be fiddley
5. OPENQUERY: Doesn't support variables
6. Allare T-SQL solutions. Relatively easy to implement and set up
7. Allare dependent on connection between source and destionation which might affect performance and scalability

1. 链接服务器：提供对比 SQL Server 复制提供的更广泛的数据源的访问
2. 链接服务器：连接复制不支持或需要临时访问的数据源
3. 链接服务器：性能优于 OPENDATASOURCE 或 OPENROWSET
4. OPENDATASOURCE和OPENROWSET函数：方便临时从数据源检索数据。OPENROWSET 也有 BULK 设施，可能/可能不需要可能很麻烦的格式文件
5. OPENQUERY: 不支持变量
6. 所有的T-SQL的解决方案。相对容易实施和设置
7. 所有都依赖于源和目标之间的连接，这可能会影响性能和可扩展性

回答by Anna Karthi

try this:

尝试这个：

SELECT * FROM OPENROWSET('SQLNCLI', 'Server=YOUR SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a
UNION
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=ANOTHER SERVER;Trusted_Connection=yes;','SELECT * FROM Table1') AS a

回答by Paul

These are all fine answers, but this one is missing and it has it's own powerful uses. Possibly it doesn't fit what the OP wanted, but the question was vague and I feel others may find their way here. Basically you can use 1 window to simultaneously run a query against multiple servers, here's how:

这些都是很好的答案，但是缺少这个答案，并且它有自己强大的用途。可能它不符合 OP 想要的，但问题很模糊，我觉得其他人可能会在这里找到方法。基本上，您可以使用 1 个窗口同时对多个服务器运行查询，方法如下：

In SSMS open Registered Servers and create a New Server Groupunder Local Server Groups.

在 SSMS 中打开 Registered Servers 并在Local Server Groups下创建一个新的服务器组。

Under this group create New Server Registrationfor each server you wish to query. If the DB names are different ensure to set a default for each in the properties.

在该组下，为您要查询的每个服务器创建新服务器注册。如果数据库名称不同，请确保在属性中为每个名称设置默认值。

Now go back to the Group you created in the first step, right click and select New Query. A new query window will open and any query you run will be executed on each server in the group. The results are presented in a single data set with an extra column name indicating which server the record came from. If you use the status bar you will note the server name is replaced with multiple.

现在回到您在第一步中创建的组，右键单击并选择新建查询。将打开一个新查询窗口，您运行的任何查询都将在组中的每台服务器上执行。结果显示在单个数据集中，并带有一个额外的列名，指示记录来自哪个服务器。如果您使用状态栏，您会注意到服务器名称被替换为多个.

回答by MarcM

I had same issue to connect an SQL_server 2008 to an SQL_server 2016 hosted in a remote server. Other answers didn't worked for me straightforward. I write my tweaked solution here as I think it may be useful for someone else.

我在将 SQL_server 2008 连接到远程服务器中托管的 SQL_server 2016 时遇到了同样的问题。其他答案对我来说并不直接。我在这里写我的调整解决方案，因为我认为它可能对其他人有用。

An extended answer for remote IP db connections:

远程 IP 数据库连接的扩展答案：

Step 1: link servers

第 1 步：链接服务器

EXEC sp_addlinkedserver @server='SRV_NAME',
   @srvproduct=N'',
   @provider=N'SQLNCLI',   
   @datasrc=N'aaa.bbb.ccc.ddd';

EXEC sp_addlinkedsrvlogin 'SRV_NAME', 'false', NULL, 'your_remote_db_login_user', 'your_remote_db_login_password'

...where SRV_NAMEis an invented name. We will use it to refer to the remote server from our queries. aaa.bbb.ccc.dddis the ip address of the remote server hosting your SQLserver DB.

...哪里SRV_NAME是一个虚构的名字。我们将使用它从我们的查询中引用远程服务器。aaa.bbb.ccc.ddd是托管 SQLserver 数据库的远程服务器的 IP 地址。

Step 2: Run your queriesFor instance:

第 2 步：运行您的查询例如：

SELECT * FROM [SRV_NAME].your_remote_db_name.dbo.your_table

...and that's it!

……就是这样！

Syntax details: sp_addlinkedserverand sp_addlinkedsrvlogin

语法详细信息：sp_addlinkedserver和sp_addlinkedsrvlogin

回答by user3586922

Server 2008:

服务器 2008：

When in SSMS connected to server1.DB1 and try:

当在 SSMS 连接到 server1.DB1 并尝试：

SELECT  * FROM
[server2].[DB2].[dbo].[table1]

as others noted, if it doesn't work it's because the server isn't linked.

正如其他人指出的那样，如果它不起作用，那是因为服务器未链接。

I get the error:

我收到错误：

Could not find server DB2 in sys.servers. Verify that the correct server name was specified. If necessary, execute stored procedure sp_addlinkedserver to add the server to sys.servers.

在 sys.servers 中找不到服务器 DB2。验证是否指定了正确的服务器名称。如有必要，执行存储过程 sp_addlinkedserver 将服务器添加到 sys.servers。

To add the server:

添加服务器：

reference: To add server using sp_addlinkedserver Link: [1]: To add server using sp_addlinkedserver

参考：使用 sp_addlinkedserver 添加服务器 链接：[1]：使用 sp_addlinkedserver 添加服务器

To see what is in your sys.servers just query it:

要查看 sys.servers 中的内容，只需查询它：

SELECT * FROM [sys].[servers]

回答by RBarryYoung

Created a Linked Server definition in one server to the other (you need SA to do this), then just reference them with 4-part naming (see BOL).

在一台服务器到另一台服务器中创建链接服务器定义（您需要 SA 来执行此操作），然后只需使用 4 部分命名引用它们（请参阅 BOL）。

回答by Masum

 select * 
 from [ServerName(IP)].[DatabaseName].[dbo].[TableName]