OPENROWSET (Transact-SQL)

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead. For more information, see Linking Servers. The OPENROWSET function can be referenced in the FROM clause of a query as if it were a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query might return multiple result sets, OPENROWSET returns only the first one.

OPENROWSET also supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset.

Transact-SQL Syntax Conventions

Syntax

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 ] 

Arguments

• 'provider_name'
  Is a character string that represents the friendly name (or PROGID) of the OLE DB provider as specified in the registry. provider_name has no default value.

• 'datasource'
  Is a string constant that corresponds to a particular OLE DB data source. datasource is the DBPROP_INIT_DATASOURCE property to be passed to the IDBProperties interface of the provider to initialize the provider. Typically, this string includes the name of the database file, the name of a database server, or a name that the provider understands to locate the database or databases.

• 'user_id'
  Is a string constant that is the user name passed to the specified OLE DB provider. user_id specifies the security context for the connection and is passed in as the DBPROP_AUTH_USERID property to initialize the provider. user_id cannot be a Microsoft Windows login name.

• 'password'
  Is a string constant that is the user password to be passed to the OLE DB provider. password is passed in as the DBPROP_AUTH_PASSWORD property when initializing the provider. password cannot be a Microsoft Windows password.

• 'provider_string'
  Is a provider-specific connection string that is passed in as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider. provider_string typically encapsulates all the connection information required to initialize the provider. For a list of keywords that are recognized by the SQL Native Client OLE DB provider, see Initialization and Authorization Properties.

• catalog
  Is the name of the catalog or database in which the specified object resides.

• schema
  Is the name of the schema or object owner for the specified object.

• object
  Is the object name that uniquely identifies the object to work with.

• 'query'
  Is a string constant sent to and executed by the provider. The local instance of SQL Server does not process this query, but processes query results returned by the provider, a pass-through query. Pass-through queries are useful when used on providers that do not make available their tabular data through table names, but only through a command language. Pass-through queries are supported on the remote server, as long as the query provider supports the OLE DB Command object and its mandatory interfaces. For more information, see SQL Native Client (OLE DB) Reference.

• BULK
  Uses the BULK rowset provider for OPENROWSET to read data from a file. In SQL Server 2005, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

  The arguments of the BULK option allow for significant control over where to start and end reading data, how to deal with errors, and how data is interpreted. For example, you can specify that the data file be read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. The default behavior is described in the argument descriptions that follow.

  For information on how to use the BULK option, see "Remarks," later in this topic. For information about the permissions that are required by the BULK option, see "Permissions," later in this topic.

  Note

  When used to import data with the full recovery model, OPENROWSET (BULK ...) does not optimize logging.

  For information on preparing data for bulk import, see Preparing Data for Bulk Export or Import.

• 'data_file'
  Is the full path of the data file whose data is to be copied into the target table.

• FORMATFILE ='format_file_path'
  Specifies the full path of a format file. SQL Server 2005 supports two types of format files: XML and non-XML.

  A format file is required to define column types in the result set. The only exception is when SINGLE_CLOB, SINGLE_BLOB, or SINGLE_NCLOB is specified; in which case, the format file is not required.

  For information about format files, see Using a Format File to Bulk Import Data.

• < bulk_options >
  Specifies one or more arguments for the BULK option.

• CODEPAGE = { **'**ACP '| **'**OEM '| **'**RAW '| 'code_page' }
  Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values more than 127 or less than 32.

  CODEPAGE value	Description
  ACP	Converts columns of char, varchar, or text data type from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
  OEM (default)	Converts columns of char, varchar, or text data type from the system OEM code page to the SQL Server code page.
  RAW	No conversion occurs from one code page to another. This is the fastest option.
  code_page	Indicates the source code page on which the character data in the data file is encoded; for example, 850. The code page is required for the SQL Server 2005 Database Engine to correctly interpret the input data.

• ERRORFILE ='file_name'
  Specifies the file used to collect rows that have formatting errors and cannot be converted to an OLE DB rowset. These rows are copied into this error file from the data file "as is."

  The error file is created at the start of the command execution. An error will be raised if the file already exists. Additionally, a control file that has the extension .ERROR.txt is created. This file references each row in the error file and provides error diagnostics. After the errors have been corrected, the data can be loaded.

• FIRSTROW **=**first_row
  Specifies the number of the first row to load. The default is 1. This indicates the first row in the specified data file. The row numbers are determined by counting the row terminators.

• LASTROW **=**last_row
  Specifies the number of the last row to load. The default is 0. This indicates the last row in the specified data file.

• MAXERRORS **=**maximum_errors
  Specifies the maximum number of syntax errors or nonconforming rows, as defined in the format file, that can occur before OPENROWSET throws an exception. Until MAXERRORS is reached, OPENROWSET ignores each bad row, not loading it, and counts the bad row as one error.

  The default for maximum_errors is 10.

  Note

  MAX_ERRORS does not apply to CHECK constraints, or to converting money and bigint data types.

• ROWS_PER_BATCH **=**rows_per_batch
  Specifies the approximate number of rows of data in the data file. This value should be of the same order as the actual number of rows.

  OPENROWSET always imports a data file as a single batch. However, if you specify rows_per_batch with a value > 0, the query processor uses the value of rows_per_batch as a hint for allocating resources in the query plan.

  By default, ROWS_PER_BATCH is unknown. Specifying ROWS_PER_BATCH = 0 is the same as omitting ROWS_PER_BATCH.

• SINGLE_BLOB
  Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).

  Important

  We recommend that you import XML data only using the SINGLE_BLOB option, rather than SINGLE_CLOB and SINGLE_NCLOB, because only SINGLE_BLOB supports all Windows encoding conversions.

• SINGLE_CLOB
  By reading data_file as ASCII, returns the contents as a single-row, single-column rowset of type varchar(max), using the collation of the current database.

• SINGLE_NCLOB
  By reading data_file as UNICODE, returns the contents as a single-row, single-column rowset of type nvarchar(max), using the collation of the current database.

Remarks

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.

When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured. For more information, see Configuring Linked Servers for Delegation.

Catalog and schema names are required if the OLE DB provider supports multiple catalogs and schemas in the specified data source. Values for catalog and schema can be omitted when the OLE DB provider does not support them. If the provider supports only schema names, a two-part name of the form schema**.object must be specified. If the provider supports only catalog names, a three-part name of the form catalog.schema.**object must be specified. Three-part names must be specified for pass-through queries that use the SQL Native Client OLE DB provider. For more information, see Transact-SQL Syntax Conventions (Transact-SQL).

OPENROWSET does not accept variables for its arguments.

Using OPENROWSET with the BULK Option

The following Transact-SQL enhancements support the OPENROWSET(BULK…) function:

• A FROM clause that is used with SELECT can call OPENROWSET(BULK…) instead of a table name, with full SELECT functionality.
  OPENROWSET with the BULK option requires a correlation name, also known as a range variable or alias, in the FROM clause. Column aliases can be specified. If a column alias list is not specified, the format file must have column names. Specifying column aliases overrides the column names in the format file, such as:
  FROM OPENROWSET(BULK...) AS table_alias
FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
• A SELECT…FROM OPENROWSET(BULK...) statement queries the data in a file directly, without importing the data into a table. SELECT…FROM OPENROWSET(BULK…) statements can also list bulk-column aliases by using a format file to specify column names, and also data types.
• An INSERT...SELECT * FROM OPENROWSET(BULK...) statement bulk imports data from a data file into a SQL Server table. For more information, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) .
• When the OPENROWSET BULK option is used with an INSERT statement, the BULK clause supports table hints. In addition to the regular table hints, such as TABLOCK, the BULK clause can accept the following specialized table hints: IGNORE_CONSTRAINTS (ignores only the CHECK and FOREIGN KEY constraints), IGNORE_TRIGGERS, KEEPDEFAULTS, and KEEPIDENTITY. For more information, see Table Hint (Transact-SQL).

For information about how to use INSERT...SELECT * FROM OPENROWSET(BULK...) statements, see Importing and Exporting Bulk Data. For information about when row-insert operations that are performed by bulk import are logged in the transaction log, see Prerequisites for Minimal Logging in Bulk Import.

Note

When you use OPENROWSET, it is important to understand how SQL Server 2005 handles impersonation. For information about security considerations, see Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...).

Bulk Exporting or Importing SQLXML Documents

To bulk export or import SQLXML data, use one of the following data types in your format file.

Data type	Effect
SQLCHAR or SQLVARYCHAR	The data is sent in the client code page or in the code page implied by the collation).
SQLNCHAR or SQLNVARCHAR	The data is sent as Unicode.
SQLBINARY or SQLVARYBIN	The data is sent without any conversion.

Permissions

OPENROWSET permissions are determined by the permissions of the user name that is being passed to the OLE DB provider. To use the BULK option requires ADMINISTER BULK OPERATIONS permission.

Examples

A. Using OPENROWSET with SELECT and the SQL Native Client OLE DB Provider

The following example uses the SQL Native Client OLE DB provider (SQLNCLI) to access the HumanResources.Department table in the AdventureWorks database on the remote server Seattle1. A SELECT statement is used to define the row set returned. The provider string contains the Server and Trusted_Connection keywords. These keywords are recognized by the SQL Native Client OLE DB provider.

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. Using the Microsoft OLE DB Provider for Jet

The following example accesses the Customers table in the Microsoft Access Northwind database through the Microsoft OLE DB Provider for Jet.

Note

This example assumes that Access is installed. To run this example, you must install the Northwind database. For information about how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.

SELECT CustomerID, CompanyName
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
      'admin';'',Customers)
GO

C. Using OPENROWSET and another table in an INNER JOIN

The following example selects all data from the Customers table from the local instance of SQL Server Northwind database and from the Orders table from the Access Northwind database stored on the same computer.

Note

This example assumes that Access is installed. To run this example, you must install the Northwind database. For information about how to install the Northwind database, see Downloading Northwind and pubs Sample Databases.

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. Using OPENROWSET to bulk insert file data into a varbinary(max) column

The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

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. Using the OPENROWSET BULK provider with a format file to retrieve rows from a text file

The following example uses a format file to retrieve rows from a tab-delimited text file, values.txt that contains the following data:

1     Data Item 1
2     Data Item 2
3     Data Item 3

The format file, values.fmt, describes the columns in 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

This is the query that retrieves that data:

SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt', 
   FORMATFILE = 'c:\test\values.fmt') AS a;

Additional Examples

For additional examples of using INSERT...SELECT * FROM OPENROWSET(BULK...), see the following topics:

• Examples of Bulk Importing and Exporting XML Documents
• Keeping Identity Values When Bulk Importing Data
• Keeping Nulls or Using Default Values During Bulk Import
• Using a Format File to Bulk Import Data
• Using Character Format to Import or Export Data
• Using a Format File to Skip a Table Column
• Using a Format File to Skip a Data Field
• Using a Format File to Map Table Columns to Data-File Fields

See Also

Reference

DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENQUERY (Transact-SQL)
Rowset Functions (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)

Other Resources

Distributed Queries
Importing and Exporting Bulk Data
User-defined Functions (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added that the IGNORE_CONSTRAINTS table hint ignores both CHECK and FOREIGN KEY constraints.
17 July 2006	New content: Added the sentence that states linked servers should be used instead of OPENROWSET for more frequent references to OLE DB data sources. In the "Remarks" section, the following changes were made: Added the paragraph about the need to configure authentication delegation when accessing remote OLE DB data sources via a double hop. Added the statement that three-part names must be specified for pass-through queries that use the SQL Native Client OLE DB provider. Added more information about using the BULK option.