OPENROWSET (Transact-SQL)

更新日期： 2006 年 12 月 12 日

包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。对于较频繁引用 OLE DB 数据源的情况，请改为使用链接服务器。有关详细信息，请参阅链接服务器。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 访问接口的功能，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，但 OPENROWSET 只返回第一个结果集。

OPENROWSET 还通过内置的 BULK 访问接口支持大容量操作，正是有了该访问接口，才能从文件读取数据并将数据作为行集返回。

Transact-SQL 语法约定

语法

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 ] 

参数

• '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 Native Client 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 选项的信息，请参阅本主题后面的“备注”部分。有关 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 数据库引擎正确解释输入数据是必需的。

• 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 数据源中的远程数据：指定访问接口的 DisallowAdhocAccess 注册表选项已显式设置为 0，并启用了 Ad Hoc Distributed Queries 高级配置选项。如果未设置这些选项，则默认行为不允许即席访问。

访问远程 OLE DB 数据源时，服务器不会自动委托可信连接的登录标识，客户端通过此登录标识才能连接到正在查询的服务器。必须配置身份验证委托。有关详细信息，请参阅为委托配置链接服务器。

如果 OLE DB 访问接口在指定的数据源中支持多个目录和架构，那么就需要目录及架构名称。如果 OLE DB 访问接口并不支持多个目录和架构，那么可以忽略 catalog 和 schema 的值。如果访问接口只支持架构名称，那么必须指定一个格式为 schema**.object 的两部分名称。如果访问接口只支持目录名称，那么必须指定一个格式为 catalog.schema.**object 的三部分名称。必须为使用 SQL Native Client OLE DB 访问接口的传递查询指定三部分名称。有关详细信息，请参阅 Transact-SQL 语法约定 (Transact-SQL)。

OPENROWSET 不接受参数变量。

使用带有 BULK 选项的 OPENROWSET

以下 Transact-SQL 增强功能支持 OPENROWSET(BULK…) 函数：

• 与 SELECT 一起使用的 FROM 子句可以调用 OPENROWSET(BULK…) 而非表名，同时可以实现完整的 SELECT 功能。
  带有 BULK 选项的 OPENROWSET 在 FROM 子句中需要有一个相关名称，也称为范围变量或别名。可以指定列别名。如果未指定列别名列表，则格式化文件必须具有列名。指定列别名会覆盖格式化文件中的列名，例如：
  FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
• SELECT...FROM OPENROWSET(BULK...) 语句将直接查询文件中的数据，无需将数据导入表中。SELECT...FROM OPENROWSET(BULK...) 语句还可以通过使用格式化文件指定列名和数据类型，从而列出大容量列别名。
• INSERT...SELECT * FROM OPENROWSET(BULK...) 语句将数据文件中的数据大容量导入 SQL Server 表中。有关详细信息，请参阅使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据。
• OPENROWSET BULK 选项与 INSERT 语句一起使用时，BULK 子句支持表提示。除了 TABLOCK 等常规表提示之外，BULK 子句还可以接受下列专用表提示：IGNORE_CONSTRAINTS（仅忽略 CHECK 和 FOREIGN KEY 约束）、IGNORE_TRIGGERS、KEEPDEFAULTS 和 KEEPIDENTITY。有关详细信息，请参阅表提示 (Transact-SQL)。

有关如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 语句的信息，请参阅导入和导出大容量数据。有关何时在事务日志中记录由大容量导入执行的行插入操作的信息，请参阅在大容量导入中按最小方式记录日志的前提条件。

注意：
使用 OPENROWSET 时，请务必了解 SQL Server 2005 是如何处理模拟的。有关安全注意事项的信息，请参阅使用 BULK INSERT 或 OPENROWSET(BULK...) 导入大容量数据。

大容量导出或导入 SQLXML 文档

若要大容量导出或导入 SQLXML 数据，请在格式化文件中使用下列数据类型之一。

数据类型	效果
SQLCHAR 或 SQLVARYCHAR	在客户端代码页或排序规则隐含的代码页中发送数据。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式发送数据。
SQLBINARY 或 SQLVARYBIN	不经任何转换即发送数据。

权限

OPENROWSET 权限由传递给 OLE DB 访问接口的用户名的权限确定。若要使用 BULK 选项，则需要有 ADMINISTER BULK OPERATIONS 权限。

示例

A. 将 OPENROWSET 用于 SELECT 和 SQL Native Client OLE DB 访问接口

以下示例使用 SQL Native Client OLE DB 访问接口 (SQLNCLI) 访问 HumanResources.Department 表，该表位于远程服务器 Seattle1 上的 AdventureWorks 数据库中。使用 SELECT 语句定义返回的行集。访问接口字符串包含 Server 和 Trusted_Connection 关键字。这些关键字由 SQL Native Client 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;

其他示例

有关使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 的其他示例，请参阅下列主题：

• 大容量导入和导出 XML 文档的示例
• 大容量导入数据时保留标识值
• 在大容量导入期间保留空值或使用默认值
• 使用格式化文件大容量导入数据
• 使用字符格式导入或导出数据
• 使用格式化文件跳过表列
• 使用格式化文件跳过数据字段
• 使用格式化文件将表列映射到数据文件字段

请参阅

参考

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
行集函数 (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

其他资源

分布式查询
导入和导出大容量数据
用户定义函数（数据库引擎）

帮助和信息

获取 SQL Server 2005 帮助

更改历史记录

发布日期	历史记录
2006 年 12 月 12 日	新增内容： 添加了 IGNORE_CONSTRAINTS 表提示将同时忽略 CHECK 和 FOREIGN KEY 约束。
2006 年 7 月 17 日	新增内容： 添加了有关在较频繁引用 OLE DB 数据源的情况下应使用链接服务器而非 OPENROWSET 的内容。 在“备注”部分做了以下更改： 添加了一段内容，指出通过双跃点访问远程 OLE DB 数据源时需要配置身份验证委托。 添加了有关使用 SQL Native Client OLE DB 访问接口的传递查询必须指定三部分名称的内容。 添加了有关使用 BULK 选项的详细信息。