SQL Server function: OPENROWSET

Enabling ad hoc queries

In order to perform ad hoc queries, they need to be enabled.

The following statement shows if ad hoc queries are enabled:

exec sp_configure 'show advanced options', 1
-- reconfigure -- ?

If they're not enabled, SQL Server will throw the following error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.

Ad hoc queries can be enabled with

exec sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Select from an Excel Spreadsheet

If Microsoft Access database engine is installed (which comes with the Microsoft OLE DB Provider for Microsoft Access database engine), OpenRowset can query data from an Excel spreadsheet:

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess'   , 1;
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1;
go

select * from openrowset(
   'Microsoft.ACE.OLEDB.12.0',                     -- ProgID of OLE DB Provider
   'Excel 12.0;Database=C:\Users\Rene\data.xlsx',
   'select * from [Sheet1$]'
);

Microsoft.ACE.OLEDB.12.0 is the ProgId for the Microsoft OLE DB Provider for Microsoft Access database engine.

The available OLE DB Providers for an SQL Server instance can be seen, for example, in the registry under the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\instance name\Providers.

Select content of a file

The content of a file that is located on the SQL Server marchine or is accessible from the SQL Servier instance, can also be selected with openrowset:

select
   ors.bulkColumn
from
   openrowset(
      bulk N'C:\Users\rene\AppData\Local\Temp\tq84.txt', single_clob
   ) as ors;

When selected into a variable and then printed, even new lines are visible in SQL Server Studio:

declare @content nvarchar(max);

select
   @content = ors.bulkColumn
from
   openrowset(
      bulk N'C:\Users\rene\AppData\Local\Temp\tq84.txt', single_clob
   ) as ors;

print(@content);

Select from a CSV file

The following example tries to demonstrate how openrowset can be used to select from a CSV file.

CSV File

This is the CSV (but semicolon separated) file I want to select from:

42;forty-two;2019-08-28T22:23:15
99;ninety-nine;2020-02-05T07:08:09
3;three;
0;;2011-12-13T14:15:16

Format file

In this case, openrowset needs a format file that describes the input file and the order of columns in the resulting table.

The 12.0 in the first indicates the version. I can't think of a case where this is relevant, but it is required. Without it, SQL Server gives an error message like Cannot bulk load. Unknown version of format file ….

The 3 in the next line specifies the number of fields in the CSV file.

This is followed by the specification for each line, which is

Position (Here: 1, 2 and 3)
Data type (which apparently must be SQLCHAR when using openrowset)
Prefix bytes (can be set to 0 for convinience or does not have any effect(?))
Maximal length in bytes (also seems to not have any effect, therefore set to 0 for my convenience)
Position in result table (I have reordered the input columns to 2, 1, 3)
Name of resulting colunns (Here: num, txt and dat).
Collation string (not relevant for this example)

12.0
3
1 SQLCHAR 0 0 ";"    2 num ""
2 SQLCHAR 0 0 ";"    1 txt ""
3 SQLCHAR 0 0 "\r\n" 3 dat ""

bulk collect statement

With the CSV file and the corresponding format file, I can create a view that selects from the data.

The view also casts the text data into an integer and the date into a datetime. (The 127 in the convert() function indicates an ISO-8601 date format (YYYY-MM-DDThh:mi:ss).

create or alter view data_csv as
select
   txt,
   cast(num as integer) num,
   convert(datetime,    dat, 127) dat
from openrowset (
   bulk          N'c:\users\rene\data.csv',
   formatfile =  N'c:\users\rene\data.format'
) csv;

Selecting from the view

select
   txt,
   dat + num
from
   data_csv;

openrowset(table …)

Some sys.… views select from openrowset(table …). For example, the definition of sys.user_token is:

exec sp_helptext 'sys.user_token'

CREATE VIEW sys.user_token AS  
 SELECT * FROM OpenRowset(TABLE USERROLES)

It is unclear to me, what exactly openrowset(table userroles) does. When trying to execute select * from openRowset(table userroles), SQL Server just reports the error message Msg 102… Incorrect syntax near 'TABLE'

Mark S. Rasmussen has also investigated this openrowset(table…) syntax - and comes to the conclusion that

The OPENROWSET(TABLE RSCPROP, x) obviously performs some dark magic.