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
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$]'
);
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.
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' The OPENROWSET(TABLE RSCPROP, x) obviously performs some dark magic.