mysql openrowset用法_OpenRowSet 用法

标签：

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$]‘)

标签：