OpenRowSet (TRANSACT-SQL)

ArticleDirectory

• Use OpenRowSet with bulk options
• A. Use OpenRowSet with the select and SQL Server native client OLE DB access interfaces
• B. Use Microsoft OLE DB provider for jet
• C. Use another table in OpenRowSet and inner join
• D. Use OpenRowSet to insert large file data volumes into the varbinary (max) column.
• E. Use the OpenRowSet bulk access interface to format a file to retrieve rows in a text file.
• Other examples

Updated on: October 31, 2008

The package contains all the connection information required to access the remote data in the ole db data source. This method is an alternative to accessing tables on the linked server and a one-time temporary Method for connecting to and accessing remote data using ole db. If you frequently reference the ole db data source, use the linked server. For more information, see link server. The OpenRowSet function can be referenced in the from clause of the query as if the table name was referenced. Based on the functions of the ole db access interface, you can also reference the OpenRowSet function as the target table of the insert, update, or delete statement. Although a query may return multiple result sets, OpenRowSet returns only the first result set.

OpenRowSet also supports large-capacity operations through the built-in bulk access interface. With this access interface, you can read data from a file and return the data as a row set.

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]
 

 
[, Order ({column [ASC | DESC]} [,... n]) [unique]
 

Parameters

' Provider_name '

String, indicating the friendly name (or progid) of the ole db access interface specified in the registry ).Provider_nameNo default value.

' Datasource '

A String constant corresponding to a specific ole db data source.DatasourceIs to be passed to the access interfaceIdbpropertiesDbprop_init_datasource attribute of the interface, which is used to initialize the access interface. Generally, this string contains the name of the database file, the name of the database server, or the name that the access interface can understand for locating the database.

' User_id '

String constant, which is the user name passed to the specified ole db access interface.User_idSpecify the security context for the connection and pass it in as the dbprop_auth_userid attribute to initialize the access interface.User_idCannot be a Microsoft Windows login name.

' Password '

String constant, which is the user password passed to the ole db access interface. When initializing an access interface,PasswordPassed as the dbprop_auth_password attribute.PasswordIt cannot be a Microsoft Windows Password.

' Provider_string '

The access interface-specific connection string, passed in as the dbprop_init_providerstring attribute to initialize the ole db access interface. UsuallyProvider_stringEncapsulate all connection information required to initialize the access interface. For a list of keywords that can be recognized by the SQL Server native client OLE DB access interface, see initialization and authorization attributes.

Catalog

Specifies the directory or Database Name of the object.

Schema

The schema name or the name of the object owner of the specified object.

Object

Object Name, which uniquely identifies the object to be operated.

' Query '

Character String constant, which is sent to the access interface and executed by the access interface. The local instance of SQL server does not process the query, but processes the query results returned by the access interface (pass the query ). Some access interfaces provide table format data through the command language instead of using the table name. It is very useful to use the passed query interface. As long as the query access interface supports OLE DBCommandObject and its forced interface, the query can be passed on the remote server. For more information, see SQL Server native client (OLE DB) reference.

Bulk

Use the bulk row set access interface of OpenRowSet to read data in the file. In SQL Server, OpenRowSet can read data without loading data in the data file to the target table. In this way, OpenRowSet can be used in a single SELECT statement.

The parameters of the bulk option provide effective control over when to start and end Data Reading, how to handle errors, and how to interpret data. For example, you can specifyVarbinary,VarcharOrNvarcharTo read data files. For the default behavior, see the subsequent parameter descriptions.

For more information about how to use the bulk option, see the "Remarks" at the end of this topic ". For more information about the permissions required by the bulk option, see "Permissions" after this topic ".

Note:
OpenRowSet (bulk...) does not optimize logging when it is used to import data in full recovery mode.

For information about preparing data for large-capacity import, see prepare data for large-capacity export or large-capacity import.

' Data_file '

The complete path of the data file. The data of this file will be copied to the target table.

Formatfile = ' Format_file_path '

Specifies the full path of the formatted file. SQL Server supports two types of formatted files: XML and non-XML.

The formatting file is required for defining the column type in the result set. The only exception is when single_clob, single_blob, or single_nclob is specified. In this case, you do not need to format the file.

For information about formatting files, see import large data volumes using formatting files.

<Bulk_options>

Specify one or more parameters of the bulk option.

CodePage ={ 'ACP '| 'OEM '| 'Raw '| ' Code_page '}

SpecifyCodePage. Only when the data contains characters greater than 127 or less than 32Char,VarcharOrTextColumn, codePage is applicable.

Note:
We recommend that you specify a name for each column in the formatting file.

CodePage Value	Description
ACP	Set the data typeChar,VarcharOrTextThe column is converted from ANSI/Microsoft Windows code page (ISO 1252) to the SQL server code page.
OEM (default)	Set the data typeChar,VarcharOrTextFrom the system OEM code page to the SQL server code page.
Raw	The conversion from one code page to another is not performed. This is the fastest execution option.
Code_page	Indicates that the character data in the data file is encodedSource codePage, such as 850. Important:SQL Server does not support code page 65001 (UTF-8 encoding ).

Errorfile = ' File_name '

Specifies the file used to collect rows in an incorrect format and cannot be converted to an ole db row set. These rows will be copied from the data file as they are to this error file.

The error file is created when you start executing the command. If the file already exists, an error is thrown. In addition, a control file with the extension of .error.txt is created. This file references each row in the error file and provides error diagnosis. After the error is corrected, the data can be loaded.

Firstrow = First_row

Specifies the row number of the first row to be loaded. The default value is 1. This indicates the first line in the specified data file. Determine the row number by counting the row Terminator. Firstrow starts from 1.

Lastrow = Last_row

Specifies the row number of the last row to be loaded. The default value is 0. This indicates the last row in the specified data file.

Maxerrors = Maximum_errors

Specifies the maximum number of syntax errors or incorrectly formatted rows defined in the formatting file before an OpenRowSet exception is thrown. Before reaching maxerrors, OpenRowSet ignores every error row, does not load it, and counts it as an error.

Maximum_errorsThe default value is 10.

Note:
Max_errors does not apply to check constraints orMoneyAndBigintData type conversion.

Rows_per_batch = Rows_per_batch

Specify the number of similar data rows in the data file. This value should be the same as the actual number of rows.

OpenRowSet always imports data files in a single batch. HoweverRows_per_batchIf the value is greater than 0, the query processor will useRows_per_batchAs a prompt.

By default, rows_per_batch is unknown. Specifying rows_per_batch = 0 is equivalent to ignoring rows_per_batch.

Order ({ column [ASC | DESC]} [ , ... n ] [unique])

an optional prompt for specifying the data sorting method in the data file. By default, large-capacity operations assume that data files are not sorted. If the query optimizer can use the specified sequence to generate more effective query plans, the performance may be improved. Examples of the benefits of specifying a sort:

• Insert rows to a table with clustered indexes. The row set data is sorted by the clustered index key.
• join the row set to another table, where the sorting column matches the joined column.
• sort columns to aggregate row set data.
• use the row set as the source table in the from clause of the query. The sorting column matches the joined column.

the specified unique data file cannot have duplicate entries.

If the actual rows in the data file are not sorted in the specified order, or if Unique is specified and duplicate keys exist, an error is returned.

the column alias is required when order is used. The column Alias List must reference the derived table being accessed by the bulk clause. The column name specified in the order clause references this column alias list. The sequence type ( varchar (max) , nvarchar (max) , and varbinary (max) cannot be specified) and XML ) and large objects (LOB) type ( text , ntext , and image ) columns.

Single_blob

SetData_fileAs the typeVarbinary (max).

Important:
We recommend that you only use the single_blob option (instead of single_clob and single_nclob) to import XML data, because only single_blob supports all windows encoding and conversion.

Single_clob

Read in ASCII formatData_file, Use the sorting rules of the current database to set the content as the typeVarchar (max).

Single_nclob

Read in unicode formatData_file, Use the sorting rules of the current database to set the content as the typeNvarchar (max).

Note

You can use OpenRowSet to access remote data in the ole db data source only in the following situations:DisallowadhocaccessThe registry option has been explicitly set to 0, and the "ad hoc Distributed Query" Advanced Configuration option is enabled. If these options are not set, ad hoc access is not allowed by default.

When you access a remote ole db data source, the server does not automatically delegate the logon ID of a trusted connection. The client can use this logon ID to connect to the server being queried. The authentication delegate must be configured. For more information, see configure a linked server for delegation.

If the ole db access interface supports multiple directories and architectures in the specified data source, the directory and architecture name are required. If the ole db access interface does not support multiple directories and architectures, ignoreCatalogAndSchema. If the access interface only supports schema names, you must specify a formatSchema.Object. If the access interface only supports directory names, you must specify a formatCatalog.Schema.Object. You must specify a name that consists of three parts for the transfer query using the SQL Server native client OLE DB access interface. For more information, see Transact-SQL syntax conventions (TRANSACT-SQL ).

OpenRowSet does not accept parameter variables.

Use OpenRowSet with bulk options

The following Transact-SQL enhancements support the OpenRowSet (bulk...) function:

• The from clause used with select can call OpenRowSet (bulk...) instead of the table name, and can implement the complete select function.
  OpenRowSet with the bulk option must have a related name in the from clause, also known as a range variable or Alias. You can specify the column alias. If the column Alias List is not specified, the formatted file must have a column name. The specified column alias overwrites the column name in the formatted file. For example:
  From OpenRowSet (bulk...) as table_alias
From OpenRowSet (bulk...) as table_alias (column_alias,... n)
• The Select... from OpenRowSet (bulk...) statement directly queries the data in the file without importing the data into the table. Select... The from OpenRowSet (bulk...) statement can also use a formatted file to specify the column name and data type to list large-capacity column aliases.
• Use OpenRowSet (bulk...) as the source table in the insert or merge statement to import data in the data file to the SQL Server table in large capacity. For more information, see use Bulk insert or OpenRowSet (bulk...) to import large data volumes.
• When the OpenRowSet bulk option is used with the insert statement, the bulk clause supports table prompts. In addition to regular table prompts such as tablock, bulk clauses can also accept the following special table prompts: ignore_constraints (ignore check and foreign key constraints only), ignore_triggers, keepdefaults, and keepidentity. For more information, see Table prompts (TRANSACT-SQL ).

For more information about how to use the insert... select * From OpenRowSet (bulk...) statement, see import and export large data volumes. For information about when to record row insertion operations performed by large-capacity import in transaction logs, see prerequisites for logging in the minimal mode in large-capacity import.

Note:
When using OpenRowSet, be sure to understand how SQL server handles simulation. For security considerations, see use Bulk insert or OpenRowSet (bulk...) to import large data volumes.

Large-capacity import of sqlchar, sqlnchar, or sqlbinary data

OpenRowSet (bulk ...) It is assumed that (if not specified) the maximum length of sqlchar, sqlnchar, or sqlbinary data cannot exceed 8000 bytes. If the lob data field of the data to be imported contains more than 8000 bytesVarchar (max),Nvarchar (max)OrVarbinary (max)Object, you must use an XML format file that defines the maximum length for the data field. To specify the maximum length, edit the format file and declare the max_length attribute. For more information, see the Schema syntax of an XML formatted file.

Note:
The automatically generated format file does not specify the length or maximum length for the lob field. However, you can manually edit the format file and specify the length or maximum length.

Export or import SQL XML documents in Large Capacity

To export or import SQL XML data in large capacity, use one of the following data types in the format file.

Data Type	Effect
Sqlchar or sqlvarychar	Send data in the client code page or the implied code page of the sorting rule.
Sqlnchar or sqlnvarchar	Send data in unicode format.
Sqlbinary or sqlvarybin	Data is sent without any conversion.

Permission

The OpenRowSet permission is determined by the permission of the user name passed to the ole db access interface. To use the bulk option, you must have the administer bulk operations permission.

Example A. Use OpenRowSet with the select and SQL Server native client OLE DB access interfaces

The following example uses the SQL Server native client OLE DB access interface to accessHumanResources. DepartmentTable, which is located on the remote serverSeattle1OnAdventureworksDatabase. (Use sqlncli and the SQL server will be redirected to the latest version of the SQL Server native client OLE DB access interface .) UseSelectStatement. The access interface string containsServerAndTrusted_connectionKeyword. These keywords are identified by the SQL Server native client OLE DB access interface.

 
Select .*
From OpenRowSet ('sqlncl', 'server = seattle1; trusted_connection = yes ;',
'Select groupname, name, specified mentid
From adventureworks. HumanResources. Department
Order by groupname, name') as;
 

B. Use Microsoft OLE DB provider for jet

The following example uses Microsoft ole db provider for jet to access Microsoft AccessNorthwindIn the databaseMERsTable.

Note:
This example assumes that access has been installed. To run this example, you must install the northwind database. For more information about how to install the northwind database, see download the 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'; '', MERS mers)
Go
 

C. Use another table in OpenRowSet and inner join

The following example is from SQL ServerNorthwindIn the local instance of the databaseMERsTable and accessNorthwindIn the databaseOrdersSelect all data from the table.

Note:
This example assumes that access has been installed. To run this example, you must install the northwind database. For more information about how to install the northwind database, see download the 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. Use OpenRowSet to insert large file data volumes into the varbinary (max) column.

The following example creates a small table for demonstration and namedText1.txtFile (located inC:File data in the root directory)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. Use the OpenRowSet bulk access interface to format a file to retrieve rows in a text file.

The following example uses a formatted file to retrieve text files separated by tabsValues.txtThe file contains the following data:

 
1 Data Item 1
2 Data Item 2
3 data item 3
 

Format a fileValues. FMTDescriptionValues.txtColumn in:

 
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
 

The following statement queries the data:

 
Select a. * From OpenRowSet (bulk 'C: \ test \ values.txt ',
Formatfile = 'C: \ test \ values. FMT ') as;
 

Other examples

For other examples of using insert... select * From OpenRowSet (bulk...), see the following topics:

• Examples of large-capacity Import and Export of XML documents
• Mark value retained during data import in Large Capacity
• Retain null values or use default values during mass import
• Use a formatted file to import large data volumes
• Import or export data using character formats
• Skip table columns using formatted files
• Skip data fields using formatted files
• Map a table column to a data file field using a formatted File

See

Delete (TRANSACT-SQL)
From (TRANSACT-SQL)
Insert (TRANSACT-SQL)
OpenDataSource (TRANSACT-SQL)
Openquery (TRANSACT-SQL)
Dataset functions (TRANSACT-SQL)
Select (TRANSACT-SQL)
Sp_addmediaserver (TRANSACT-SQL)
Sp_serveroption (TRANSACT-SQL)
Update (TRANSACT-SQL)
Where (TRANSACT-SQL)
Other resources

Distributed Query
Import and export large data volumes
User-Defined Functions (database engine)