Rowset Function – OPENDATASOURCE

OPENDATASOURCE is used to access data from OLEDB data sources. It references OLEDB data sources that are accessed infrequently. It is recommended use Linked server for the frequent OLEDB data source access. It does not accept any variables for its arguments.

Syntax

             OPENDATASOURCE (<provider_name>, <init_string>)

Provider_Name       -- PRODID of the registered OLEDB Provider.

Init_String              -- Below list of Keywords can be defined and separated by semicolon here. [keyword1=’’; keyword2=’’)

1)   Data Source      -- Indicates the name of the data source to connect. For SQL Server native client OLEDB provider, it specifies name of the SQL Server instance. For the JET OLEDB provider, it specifies the name of .mdb or xlsx file.

2)   Location          -- It tells the location of the database to connect to.

3)   Extended Properties  -- It is provider specific connection string.

4)   Connect timeout        -- It specifies timeout values when connection try fails.

5)   User ID                     -- It specifies User Id for the connection.

6)   Password                  -- It specifies password for the connection.

7)   Catalog                   -- It indicates the name of the initial or default catalog when connecting to the data source.

8)   Integrated Security   -- SSPI, to specify Windows Authentication.

 Pre-Configuration Setup

In order to use OPENDATASOURCE Rowset function, the following pre-configurations have to be set properly.

1.   Make sure that specific OLEDB Drive 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 OPENDATASOURCE 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 is fetching data from Employee_Details.xlsx [Sheet Name: Status] spreadsheet using OPENDATASOURCE function. It is using Microsoft ACE OLEDB data provider.

SELECT * FROM OPENDATASOURCE ('Microsoft.ACE.OLEDB.12.0',

'Data Source=C:\Data for Import\Employee_Details.xlsx;Extended Properties=EXCEL 12.0 Xml')...[Status$] ;

The following query is fetching records from [DBTechno\Instance-1] SQL Server instance. Table Name is [Salary Master]. It is using SQL Native Client OLEDB provider.

SELECT *

FROM OPENDATASOURCE('SQLNCLI',

    'Data Source=DBTechno\Instance-1;Integrated Security=SSPI')

    .[EMPDB].[dbo].[Salary Master]