标签:
OpenRowSet 函数返回RowSet,可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。 依据 OLE DB 访问接口的功能,还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。 尽管查询可能返回多个结果集,但 OPENROWSET 只返回第一个结果集。
语法
OPENROWSET (‘provider_name‘
,{‘datasource‘;‘user_id‘;‘password‘
|‘provider_string‘}
,{[catalog.][schema.]object
|‘query‘}
)
参数
‘provider_name‘
字符串,它代表在注册表中指定的 OLE DB 提供程序的友好名。provider_name 没有默认值。
‘datasource‘
字符串常量,它对应着某个特定的 OLE DB 数据源。datasource 是将被传递到提供程序 IDBProperties 接口以初始化提供程序的 DBPROP_INIT_DATASOURCE 属性。通常,这个字符串包含数据库文件的名称、数据库服务器的名称,或者提供程序能理解的用于查找数据库的名称。
‘user_id‘
字符串常量,它是传递到指定 OLE DB 提供程序的用户名。user_id 为连接指定安全上下文,并将它作为 DBPROP_AUTH_USERID 属性传递进来以初始化提供程序。
‘password‘
字符串常量,它是将被传递到 OLE DB 提供程序的用户密码。当初始化提供程序时,将 password 作为 DBPROP_AUTH_PASSWORD 属性传递进来。
‘provider_string‘
提供程序特定的连接字符串,将它作为 DBPROP_INIT_PROVIDERSTRING 属性传递进来以初始化 OLE DB 提供程序。通常 provider_string 封装初始化提供程序所需的所有连接信息。
catalog
目录或数据库的名称,其中驻留着指定的对象。
schema
架构的名称或指定对象的对象所有者名称。
object
对象名称,它唯一地标识出将要操作的对象。
‘query‘
是字符串常量,发送到提供程序并由提供程序执行。SQL Server 的本地实例不处理该查询,但处理由访问接口返回的查询结果。
OpenRowSet还有bulk选项,参见MSDN:https://msdn.microsoft.com/zh-cn/library/ms190312.aspx
1,启用和停用 OpenRowSet,默认情况下,SSMS是disable状态
--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‘, 0;RECONFIGURE;exec sp_configure ‘Ad Hoc Distributed Queries‘, 0;RECONFIGURE;GO
2,OpenRowSet 能够执行 sql 的select 查询语句,返回结果集
select *
FROM OPENROWSET(‘SQLNCLI‘,‘Server=.;Trusted_Connection=yes;‘,‘select * from db_study.dbo.test‘ --query statement
) AS t;
3,OpenRowSet 能够在query 子句中,使用 exec 执行存储过程,返回结果集
create PROCEDURE [dbo].[usp_test]
AS
BEGIN
SET NOCOUNT ON;select *
fromdbo.testorder by code desc
END
select *
FROM OPENROWSET(‘SQLNCLI‘,‘Server=.;Trusted_Connection=yes;‘,‘exec db_study.[dbo].[usp_test]‘ --query statement
) AS t;
4,OpenRowSet能够从Excel中读取数据
OpenRowSet的第一个参数是引擎类型,如果是Excel 那么第一个参数可能是Excel的引擎,‘Microsoft.Jet.OLEDB.4.0‘,用于处理xls文件;‘Microsoft.ACE.OLEDB.12.0‘ ,用于处理xlsx文件。
注册‘Microsoft.ACE.OLEDB.12.0‘引擎,否则会报错:
“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
--exec sp_configure ‘show advanced options‘,1--reconfigure--go--exec sp_configure ‘Ad Hoc Distributed Queries‘,1--reconfigure--go--exec sp_configure ‘ole automation procedures‘,1--reconfigure--exec sp_configure ‘xp_cmdshell‘,1--reconfigure
select *
from OpenRowSet(‘Microsoft.ACE.OLEDB.12.0‘,‘Excel 12.0;HDR=Yes;IMEX=1;Database=D:\test.xlsx‘,‘select * from [sheet1$]‘)
标签: