Rowset Function – OPENROWSET

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]