行集函数：OpenRowSet 和 OpenQuery

在SQL Server中，行集函数是不确定性的，这意味着，每次调用，返回值不总是相同的。返回值是不确定的，这意味着，对于相同的输入值，不保证每次返回的值都是相同的。对行集函数的每次调用，行集函数都是单独计算。行集函数：OpenRowSet 和 OpenQuery 的行为和Base Table相似：

• 能够用于From子句，就像行集函数是一个Base Table，从行集函数中获取数据；
• 能够用于修改数据，修改命令（INSERT, UPDATE, or DELETE）直接作用于行集函数，对Base Table进行数据修改；

行集函数：OpenRowSet 语法

OPENROWSET 
( { 'provider_name' 
　　,{ 'datasource' ; 'user_id' ; 'password' | 'provider_string' } 
   ,{ [ catalog. ][ schema. ]object | 'query' } 
} )

‘Provider_name’ 参数：本文主要从SQL Server和Excel中访问数据，提供程序的名字分别是：SQLNCLI 和 Microsoft.ACE.OLEDB.12.0（处理 .xlsx文件）；

'query' 参数：指定一个穿透查询（Pass-Through Query）字符串，本地的SQL Server实例不会处理该查询，在提供程序（Provider）中执行，并返回查询的结果；

一，默认情况下，OpenRowSet 是Disable状态

OpenRowSet函数能够用于从OLEDB数据源访问远程数据的条件是：

• 对于指定提供程序（Provider），DisallowAdhocAccess 注册表选项显式设置为0；
• 启用 Ad Hoc Distributed Queries 高级选项，在SQL Server中，该选项默认是Disable的，需要显式启用（Enable）；

OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.

在SQL Server中启用 “Ad Hoc Distributed Queries” 高级选项的脚本是：

-- enable
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec  sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
--disable
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec  sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO

如果没有启用“Ad Hoc Distributed Queries” 高级选项，在执行OpenRowSet函数时，SQL Server会抛出错误信息：

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.

二，从SQL Server 中获取数据，'provider_name' 参数是 SQLNCLI .

1，使用OpenRowSet 执行 sql 查询语句，查询语句被发送到指定的Server上执行，将结果集返回到OpenRowSet 函数中。Query Statement 使用 Three-Part命名规则，即 Database_Name.Schema_Name.Table_Name。

select *
FROM OPENROWSET(
    'SQLNCLI', 
    'Server=Server_Name;Trusted_Connection=yes;',
    'select * from db_study.dbo.test'  --query statement
) AS t;

2，OpenRowSet 能够在query 子句中，使用 exec 执行存储过程，返回结果集

SELECT *
FROM OPENROWSET(
    'SQLNCLI', 
    'Server=Server_Name;Trusted_Connection=yes;',
    'exec db_study.[dbo].[usp_test]'  --query statement
) AS t;

三，从Excel中读取数据，把Excel作为数据源

从Excel中读取数据，'provider_name' 是Excel处理引擎的名字，主要有两个版本：

• 'Microsoft.Jet.OLEDB.4.0' ：用于处理xls文件
• 'Microsoft.ACE.OLEDB.12.0'：用于处理xlsx文件，用于从Microsoft Office Excel 2007中访问数据

1，注册'Microsoft.ACE.OLEDB.12.0'引擎

查看SQL Server 可用的Providers，依次点击 Server Objects-》Linked Servers-》Providers，如果存在Microsoft.ACE.OLEDB.12.0 或 Microsoft.ACE.OLEDB.16.0，说明ACE.OLEDB 引擎已经注册到系统中，在SQL Server能够执行OpenRowSet函数处理xlsx数据。

如果没有注册 'Microsoft.ACE.OLEDB.12.0'引擎，在从Excel中读取数据时，SQL Server会报错：The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

注册ACE OLE DB处理引擎的过程十分简单，从微软的官方站点下载：Microsoft Access Database Engine 2010 Redistributable，在安装驱动程序时，需要匹配机器的系统类型，如果安装的是是64位操作系统，请安装：AccessDatabaseEngine_X64.exe，如果遇到：未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序的错误，首先检查系统是否安装Excel数据库引擎，然后检查Excel数据引擎是否和本地的操作系统兼容，请在64位的机器上安装64位版本的Excel数据库引擎。

2，配置Excel引擎

在安装Excel处理引擎之后，在SQL Server中，需要配置AllowInProcess 和 DynamicParameters两个选项，否则SQL Server会抛出错误信息：

“OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.”

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

3，使用OpenRowSet从Excel中查询数据

使用OpenRowSet从Excel中读取数据，有两种格式：使用查询语句，使用sheet name，例如：

select * 
from OpenRowSet(
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\test.xlsx', 
    'select * from [sheet1$]'
) 
--or 
select * 
from OpenRowSet
(
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\test.xlsx', 
    [sheet1$]
) 

4，故障排除

在使用OpenRowSet时，经常会遇到以下错误消息：

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

这两种情况是由于权限不够导致，使用sa账号登陆，赋予sysadmin 权限即可；

引用《Using OPENROWSET instead of a Linked Server》

Windows Authentication            

select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;Trusted_Connection=yes;', 'select * from sys.databases') as a

SQL Server Authentication

select a.* from openrowset('SQLNCLI', 'Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a

行集函数：OpenQuery  语法

OpenQuery在特定的Linked Server上执行穿透查询，即，OpenQuery函数将查询语句发送到Remote Server上运行，然后返回查询结果，大幅提高了Linked Server的查询性能。（Executes the specified pass-through query on the specified linked server.） 

OPENQUERY ( linked_server ,'query' )

1，Executing an UPDATE pass-through query

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') 
SET name = 'ADifferentName';

2，Executing an INSERT pass-through query

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');

3，Executing a DELETE pass-through query

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

 

参考doc：

OPENROWSET (Transact-SQL) 

Excel Import to SQL Server using Distributed Queries

MS SQL SERVER READ EXCEL FILE (EXCEL 2010, 2013) - OPENROWSET (x64) - (T-SQL)

OPENQUERY (Transact-SQL)

Top 3 Performance Killers For Linked Server Queries