包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时,这种方法是一种替代方法,并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 访问接口的功能,还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集,但 OPENROWSET 只返回第一个结果集。
OPENROWSET 还通过内置的 BULK 访问接口支持大容量操作,正是有了该访问接口,才能从文件读取数据并将数据作为行集返回。
语法
OPENROWSET ( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file' , { FORMATFILE = 'format_file_path' [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB } } ) <bulk_options> ::= [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ , ERRORFILE = 'file_name' ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH = rows_per_batch ] |
备注
只有在以下情况下才能使用 OPENROWSET 访问 OLE DB 数据源中的远程数据:指定访问接口的 DisallowAdhocAccess 注册表选项已显式设置为 0,并启用了 Ad Hoc Distributed Queries 高级配置选项。如果未设置这些选项,则默认行为不允许即席访问。
如果 OLE DB 访问接口在指定的数据源中支持多个目录和架构,那么就需要目录及架构名称。如果 OLE DB 访问接口并不支持多个目录和架构,那么可以忽略 catalog 和 schema 的值。
如果访问接口只支持架构名称,那么必须指定一个格式为 schema.object 的两部分名称。如果访问接口只支持目录名称,那么必须指定一个格式为 catalog.schema.object 的三部分名称。
OPENROWSET 不接受参数变量。
带有 BULK 选项的 OPENROWSET 在 FROM 子句中需要有一个相关名称,也称为范围变量或别名。可以指定列别名。如果未指定列别名列表,则格式化文件必须具有列名。指定列别名会覆盖格式化文件中的列名,例如:
FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
在将 OPENROWSET BULK 选项用于 INSERT 语句时可以使用表提示。除了 TABLOCK 等常规表提示之外,BULK 子句还可以接受下列专用表提示:IGNORE_CONSTRAINTS(仅忽略 CHECK 约束)、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。
有关如何使用 INSERT ...SELECT * FROM OPENROWSET(BULK...) 语句的信息,请参阅导入和导出大容量数据。
大容量导出或导入 SQLXML 文档
若要大容量导出或导入 SQLXML 数据,请使用下列数据类型之一:
数据类型 | 效果 |
---|---|
SQLCHAR 或 SQLVARYCHAR | 在客户端代码页或排序规则暗含的代码页中发送数据。 |
SQLNCHAR 或 SQLNVARCHAR | 以 Unicode 格式发送数据。 |
SQLBINARY 或 SQLVARYBIN | 不经任何转换即发送数据。 |
参数
-
'provider_name'
-
字符串,表示在注册表中指定的 OLE DB 访问接口的友好名称(或 PROGID)。provider_name 没有默认值。
-
'datasource'
-
与某个特定 OLE DB 数据源相对应的字符串常量。datasource 是将传递给访问接口的 IDBProperties 接口以初始化访问接口的 DBPROP_INIT_DATASOURCE 属性。通常,该字符串包含数据库文件的名称、数据库服务器的名称,或者访问接口能理解的用于定位数据库的名称。
-
'user_id'
-
字符串常量,它是传递给指定 OLE DB 访问接口的用户名。user_id 为连接指定安全上下文,并作为 DBPROP_AUTH_USERID 属性传入以初始化访问接口。user_id 不能是 Microsoft Windows 登录名称。
-
'password'
-
字符串常量,它是传递给 OLE DB 访问接口的用户密码。在初始化访问接口时,password 作为 DBPROP_AUTH_PASSWORD 属性传入。password 不能是 Microsoft Windows 密码。
-
'provider_string'
-
访问接口特定的连接字符串,作为 DBPROP_INIT_PROVIDERSTRING 属性传入以初始化 OLE DB 访问接口。通常 provider_string 封装初始化访问接口所需的所有连接信息。有关 SQL 本机客户端 OLE DB 访问接口可识别的关键字列表,请参阅Initialization and Authorization Properties。
-
catalog
-
指定对象所在的目录或数据库的名称。
-
schema
-
架构的名称或指定对象的对象所有者名称。
-
object
-
对象名称,它唯一地标识出将要操作的对象。
-
'query'
-
字符串常量,发送到访问接口并由访问接口执行。SQL Server 的本地实例不处理该查询,但处理由访问接口返回的查询结果(传递查询)。有些访问接口并不通过表名而是通过命令语言提供其表格格式数据,将传递查询用于这些访问接口是非常有用的。只要查询访问接口支持 OLE DB Command 对象及其强制接口,那么在远程服务器上就支持传递查询。有关详细信息,请参阅 SQL Native Client (OLE DB) Reference。
-
BULK
-
使用 OPENROWSET 的 BULK 行集访问接口读取文件中的数据。在 SQL Server 2005 中,OPENROWSET 无需将数据文件中的数据加载到目标表,便可读取这些数据。这样便可在单个 SELECT 语句中使用 OPENROWSET。
BULK 选项的参数可对何时开始和结束数据读取、如何处理错误以及如何解释数据提供有效控制。例如,可以指定以类型为 varbinary、varchar 或 nvarchar 的单行单列行集的形式读取数据文件。默认行为详见随后的参数说明。
有关 BULK 选项所需权限的信息,请参阅本主题后面的权限部分。
注意: 当用于以完整恢复模式导入数据时,OPENROWSET (BULK ...) 不优化日志记录。
-
' data_file '
-
数据文件的完整路径,该文件的数据将被复制到目标表中。
-
FORMATFILE = 'format_file_path'
-
指定格式化文件的完整路径。SQL Server 2005 支持两种格式化文件类型:XML 和非 XML。
格式化文件对定义结果集中的列类型是必需的。唯一的例外情况是指定 SINGLE_CLOB、SINGLE_BLOB 或 SINGLE_NCLOB 时;在这种情况下,不需要格式化文件。
有关格式化文件的信息,请参阅使用格式化文件大容量导入数据。
-
< bulk_options >
-
指定 BULK 选项的一个或多个参数。
-
CODEPAGE = { 'ACP '| 'OEM '| 'RAW '| 'code_page' }
-
指定该数据文件中数据的代码页。仅当数据含有字符值大于 127 或小于 32 的 char、varchar 或 text 列时,CODEPAGE 才是适用的。
CODEPAGE 值 说明 ACP
将数据类型为 char、varchar 或 text 的列由 ANSI/Microsoft Windows 代码页 (ISO 1252) 转换为 SQL Server 代码页。
OEM(默认值)
将数据类型为 char、varchar 或 text 的列由系统 OEM 代码页转换为 SQL Server 代码页。
RAW
不执行从一个代码页到另一个代码页的转换。这是执行最快的选项。
code_page
指示编码数据文件中的字符数据所在的源代码页;例如,850。该代码页对 SQL Server 2005 Database Engine 正确解释输入数据是必需的。
-
ERRORFILE = 'file_name'
-
指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。这些行将按原样从数据文件复制到此错误文件中。
错误文件在开始执行命令时创建。如果该文件已存在,将引发一个错误。此外,还创建了一个扩展名为 .ERROR.txt 的控制文件。此文件引用错误文件中的每一行并提供错误诊断。纠正错误后即可加载数据。
-
FIRSTROW = first_row
-
指定要加载的第一行的行号。默认值为 1,指示指定数据文件的第一行。通过对行终止符进行计数来确定行号。
-
LASTROW = last_row
-
指定要加载的最后一行的行号。默认值为 0,指示指定数据文件中的最后一行。
-
MAXERRORS = maximum_errors
-
指定格式化文件中定义的、在 OPENROWSET 引发异常之前可以发生的语法错误或格式有误行的最大数目。在达到 MAXERRORS 之前,OPENROWSET 会忽略每个错误行,不加载它,并将其计为一个错误。
maximum_errors 的默认值为 10。
注意: MAX_ERRORS 不适用于 CHECK 约束,也不适用于 money 和 bigint 数据类型的转换。
-
ROWS_PER_BATCH = rows_per_batch
-
指定数据文件中近似的数据行数量。该值应与实际行数相同。
OPENROWSET 始终以单批形式导入数据文件。但如果将 rows_per_batch 的值指定为 > 0,则查询处理器在查询计划中分配资源时将使用 rows_per_batch 的值作为提示。
默认情况下,ROWS_PER_BATCH 未知。指定 ROWS_PER_BATCH = 0 相当于忽略 ROWS_PER_BATCH。
-
SINGLE_BLOB
-
将 data_file 的内容作为类型为 varbinary(max) 的单行单列行集返回。
重要事项: 我们建议您仅使用 SINGLE_BLOB 选项(而不是 SINGLE_CLOB 和 SINGLE_NCLOB)导入 XML 数据,因为只有 SINGLE_BLOB 支持所有的 Windows 编码转换。
-
SINGLE_CLOB
-
通过以 ASCII 格式读取 data_file,使用当前数据库的排序规则将内容作为类型为 varchar(max) 的单行单列行集返回。
-
SINGLE_NCLOB
-
通过以 UNICODE 格式读取 data_file,使用当前数据库的排序规则将内容作为类型为 nvarchar(max) 的单行单列行集返回。
权限
OPENROWSET 权限由传递给 OLE DB 访问接口的用户名的权限确定。使用 BULK 选项需要具有 ADMINISTER_BULK_OPERATIONS 权限。
示例
A. 将 OPENROWSET 用于 SELECT 和 SQL 本机客户端 OLE DB 访问接口
以下示例使用 SQL 本机客户端 OLE DB 访问接口 (SQLNCLI
) 访问 HumanResources.Department
表,该表位于远程服务器 Seattle1
上的 AdventureWorks
数据库中。使用 SELECT
语句定义返回的行集。访问接口字符串包含 Server
和 Trusted_Connection
关键字。这些关键字由 SQL 本机客户端 OLE DB 访问接口识别。
SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;', 'SELECT GroupName, Name, DepartmentID FROM AdventureWorks.HumanResources.Department ORDER BY GroupName, Name') AS a; |
B. 使用 Microsoft OLE DB Provider for Jet
以下示例通过 Microsoft OLE DB Provider for Jet 访问 Microsoft Access Northwind
数据库中的 Customers
表。
注意: |
---|
该示例假定已经安装了 Access。若要运行该示例,则必须安装 Northwind 数据库。有关如何安装 Northwind 数据库的详细信息,请参阅下载 Northwind 和 pubs 示例数据库。
|
SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:/Program Files/Microsoft Office/OFFICE11/SAMPLES/Northwind.mdb'; 'admin';'',Customers) GO |
C. 使用 OPENROWSET 和 INNER JOIN 中的另一个表
以下示例从 SQL Server Northwind
数据库的本地实例中的 Customers
表以及存储在同一计算机上的 Access Northwind
数据库中的 Orders
表选择所有数据。
注意: |
---|
该示例假定已经安装了 Access。若要运行该示例,则必须安装 Northwind 数据库。有关如何安装 Northwind 数据库的详细信息,请参阅下载 Northwind 和 pubs 示例数据库。
|
USE Northwind GO SELECT c.*, o.* FROM Northwind.dbo.Customers AS c INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:/Program Files/Microsoft Office/OFFICE11/SAMPLES/Northwind.mdb';'admin';'', Orders) AS o ON c.CustomerID = o.CustomerID GO |
D. 使用 OPENROWSET 将文件数据大容量插入 varbinary(max) 列中
以下示例创建一个用于演示的小型表,并将名为 Text1.txt
的文件(位于 C:
根目录)中的文件数据插入 varbinary(max)
列中。
USE AdventureWorks GO CREATE TABLE myTable(FileName nvarchar(60), FileType nvarchar(60), Document varbinary(max)) GO INSERT INTO myTable(FileName, FileType, Document) SELECT 'Text1.txt' AS FileName, '.txt' AS FileType, * FROM OPENROWSET(BULK N'C:/Text1.txt', SINGLE_BLOB) AS Document GO |
E. 将 OPENROWSET BULK 访问接口用于格式化文件以检索文本文件中的行
以下示例使用格式化文件检索用制表符分隔的文本文件 values.txt
中的行,该文件包含下列数据:
1 Data Item 1 2 Data Item 2 3 Data Item 3 |
格式化文件 values.fmt
说明 values.txt
中的列:
9.0 2 1 SQLCHAR 0 10 "/t" 1 ID SQL_Latin1_General_Cp437_BIN 2 SQLCHAR 0 40 "/r/n" 2 Description SQL_Latin1_General_Cp437_BIN |
下面的语句是检索此数据的查询:
SELECT a.* FROM OPENROWSET( BULK 'c:/test/values.txt', FORMATFILE = 'c:/test/values.fmt') AS a; |