在SQL Server中查询远程数据源

A common activity when writing T-SQL queries is connecting to local databases and processing data directly. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing.

编写T-SQL查询时的常见活动是连接到本地数据库并直接处理数据。 但是在某些情况下，您需要连接到位于同一服务器或不同物理服务器中不同实例中的远程数据库，并与本地数据处理并行地处理其数据。

SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. In this article, we will discuss these four methods and how to use it to query remote SQL Server databases.

SQL Server为我们提供了四种有用的方法来连接到远程数据库服务器，甚至其他数据库服务器类型，并在您的T-SQL语句中查询其数据。 在本文中，我们将讨论这四种方法以及如何使用它来查询远程SQL Server数据库。

OPENDATASOURCE (OPENDATASOURCE)

The first method to query a remote SQL Server database is the OPENDATASOURCE T-SQL function below:

查询远程SQL Server数据库的第一种方法是下面的OPENDATASOURCE T-SQL函数：

OPENDATASOURCE ( provider_name as char, init_string )

OPENDATASOURCE（provider_name为char，init_string）

Where the provider_name is the OLE DB provider used to access the data source. And the init_string is the connection string of the remote server.

其中provider_name是用于访问数据源的OLE DB提供程序。 init_string是远程服务器的连接字符串。

To be able to use the OPENDATASOURCE statement, you need to make sure that the DisallowAdhocAccess registry key is set to 0 for the provider you want to connect to other than the SQL Server, which can be found in the below path of the Registry Keys :

为了能够使用OPENDATASOURCE语句，您需要确保将要连接到除SQL Server之外的提供程序的DisallowAdhocAccess注册表项设置为0，该注册表项可以在注册表项的以下路径中找到：

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\<ProviderName>

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ MSSQLServer \ Providers \ <ProviderName>

Also you need to enable the Ad Hoc Distributed Queries advanced configuration option which is disabled by default in SQL Server. If you try to run the below simple query that is using the OPENDATASOURCE T-SQL statement you will get the error:

另外，您还需要启用Ad Hoc Distributed Queries高级配置选项，该选项在SQL Server中默认为禁用。 如果您尝试运行以下使用OPENDATASOURCE T-SQL语句的简单查询，则会收到错误消息：

SELECT *  
FROM OPENDATASOURCE('SQLNCLI',  
    'Data Source=DEV_SQL;Integrated Security=SSPI')  
    .testdb.dbo.AddressBook

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

消息15281，第16级，状态1，第1行
SQL Server阻止访问组件“临时分布式查询”的STATEMENT“ OpenRowset / OpenDatasource”，因为此服务器的安全配置已将此组件关闭。 系统管理员可以使用sp_configure启用“临时分布式查询”的使用。 有关启用“临时分布式查询”的更多信息，请在SQL Server联机丛书中搜索“临时分布式查询”。

As you can see from the error message, the Ad Hoc Distributed Queries advanced configuration option should be enabled in order to open connection to a remote server using the OPENDATASOURCE. This can be achieved by using the sp_configure query below:

从错误消息中可以看到，应启用“临时分布式查询”高级配置选项，以便使用OPENDATASOURCE打开到远程服务器的连接。 这可以通过使用以下sp_configure查询来实现：

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully. OPENDATASOURCE can replace the server name in the four-part name of the table or view in a SELECT, INSERT, UPDATE, or DELETE statement. It can be also used in the EXECUTE statement to run a remote stored procedure.