OPENROWSET is used to access data
from OLEDB data sources. It references OLEDB data sources that are accessed
infrequently. It is recommended to use Linked server for the frequent OLEDB data
source access. It does not accept any variables for its arguments.
OPENROWSET also supports bulk
operations through a built-in BULK provider which enables data from a file to
be read and returned as a rowset.
Syntax
OPENROWSET
(<Provider Name>, <provider String>, <Query>)
Provider Name --
It indicates name of the OLEDB Provider or PROGID of OLEDB Provider.
1) SQLNCLI is SQL Server Native Client
OLEDB Provider
2) Microsoft.Jet.OLEDB.4.0 is Microsoft OLEDB provider for JET
3)
Microsoft.ACE.OLEDB.12.0
is Microsoft OLEDB provider for ACE
4)
BULK ‘data file’
is the OPENROWSET built-in provider for BULK operations
Provider String -- It varies based on the type of provider.
It includes {Data source; user ID; Password} for SQLNCLI OLEDB Provider.
It includes {FORMATFILE = ‘Format file path’} for BULK Operations.
Also below mentioned arguments are used only in BULK Operations
[, CODEPAGE = {‘ACP’ | 'OEM' | 'RAW' | 'code_page’} ]
- It specifies code page of the data in the data file.
- It is relevant, only if the data in the data file has CHAR, VARCHAR or TEXT column data in which character values greater than 127 or less than 32.
- ACP [It Converts columns of CHAR, VARCHAR, or TEXT data type from ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server Code Page].
- OEM [It converts columns of CHAR, VARCHAR, or TEXT data type from 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 [It indicates the source code page on which the character data in the data file is encoded]
[, ERRORFILE = <Specifies the file used to collect formatting error rows during conversion> ]
[, FIRSTROW = <Default is 1 which indicates first row of data file> ]
[, LASTROW = <Default is 0 which indicates last row of data file>]
[, MAXERRORS = <Default is 10 which specifies number of syntax error rows or non-confirming rows as defined in the format file> ]
[, ROWS_PER_BATCH = <Default is UNKNOWN which is total number of rows in the data file. By default all rows in the data file is considered as single Batch> ]
[, ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ]
- It specifies in which order data
should be sorted in the data file.
Query -- It can be
in the form of [Catalog]. [Schema]. [Object] or as SQL Query statement.
Pre-Configuration
Setup
In order to use OPENROWSET Rowset
function, the following pre-configurations have to be set properly.
1. Make sure that specific OLEDB Driver
has been installed on your system.
2. Make sure that Ad Hoc Distributed
Queries advanced configuration option has been enabled on your SQL Server
instance. If not yet enabled, then use the below script to enable it.
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE;
EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
RECONFIGURE;
3. Make sure that DisallowAdhocAccess registry option is explicitly set to 0 for
the specified driver. When DisallowAdhocAccess is set to 1, SQL Server does not
allow adhoc access through OPENROWSET function against the OLEDB provider. Use
the below steps to configure this registry option.
To add
the DisallowAdHocAccess value, follow these steps:
a) Start Registry Editor.
b) Locate, and then click the
following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL
c) On the Edit menu,
click Add Value, and then add this registry value:
Value name:
DisallowAdHocAccess
Data
type: REG_DWORD
Radix: Hex
Value data: 0
d) Quit Registry Editor.
To modify an
existing DisallowAdHocAccess value, follow these steps:
a) Start Registry Editor.
b) Locate, and then click the DisallowAdHocAccess value
under the following key in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB
On the Edit menu,
click DWORD, type 0, and then click OK.
c) Quit Registry Editor.
For a named instance,
the registry key is different:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft
SQL Server\<Instance Name>\Providers\MSDASQL
Examples
The
following query uses Microsoft ACE OLEDB provider to fetch data from
Employee_List spread sheet.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0
Xml;HDR=YES; Database=C:\Test\Data for Import\Employee_List.xlsx',
'SELECT * FROM
[Status1$]');
The
following query uses Microsoft SQL Server Native client to fetch data from SQL
Server instance.
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=DBTechno\Instance-1;Trusted_Connection=yes;',
'SELECT Name,
salary, ReportsTo
FROM EmployeeDB.dbo.Employee
ORDER BY Name') AS a;
The
following query uses OPENROWSET BULK provider to return bulk data from test.txt
file.
SELECT a.* FROM OPENROWSET( BULK 'c:\Bulk_Test\test.txt',
FORMATFILE = 'c:\Bulk_Test\format.fmt') AS a;
Contents
of test.txt file
1
Moses
2
Suraj
3
Yoseph
4
Karthick
Contents of format.fmt file
9.0
2
1 SQLCHAR
0 10 "\t" 1
ID SQL_Latin1_General_Cp437_BIN
2 SQLCHAR
0 40 "\r\n" 2 Employee_Name SQL_Latin1_General_Cp437_BIN
Structure of format file is given below for
reference.
[Version
Number]
[Number
of Columns in the data file]
[Field
Order in Source File] [Source File Data Type] [Prefix Length] [Source File Column
Length] [Destination Column Order] [Destination Column Name] [Destination
Collation Name]
