Using parametrised query with OPENROWSET

Question

I have the following code:

con.Open();

string exportQuery = "INSERT INTO OPENROWSET(@oledbType,@filepath,@query)";

string filepath = "'Excel 12.0;Database=C:\\Temp\\TestExcel.xlsx;";
string oledbType = "Microsoft.ACE.OLEDB.12.0";

string query = "SELECT * FROM categoryData";

SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.Parameters.AddWithValue("@filepath", filepath);
cmd.Parameters.AddWithValue("@oledbType", oledbType);
cmd.Parameters.AddWithValue("@query", query); 

cmd.ExecuteNonQuery();
con.Close();

I'm getting an error:

System.Data.SqlClient.SqlException: 'Incorrect syntax near '@oledbType'.'

So I think the syntax at

string exportQuery = "INSERT INTO OPENROWSET(@oledbType,@filepath,@query)";

is incorrect. Any ideas?

I've managed to get this working in SQL Server using a more advanced version of the previous query

Provided the file exists the following code exports the data correctly:

INSERT INTO OpenRowSet( 
'Microsoft.ACE.OLEDB.12.0' 
, 'Excel 12.0;Database=M:\TestExcel.xlsx;' 
, 'SELECT * FROM [Sheet1$]' 
) 
SELECT category as Category, SUM(ButtonClick) as Count FROM 
( SELECT COUNT(id) as id, category as category, locationName as location, sum(counter) as ButtonClick FROM categoryData AS t 
WHERE locationName IN ('all', 'loc1', 'loc2') 
AND date BETWEEN '2018-04-03' AND '2018-04-04'
GROUP BY locationName, category) 
AS SUBQUERY GROUP BY category ORDER BY Count ASC

Exporting over to C# I get an error:

string exportQuery = @"INSERT INTO OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=M:\TestExcel.xlsx;' , 'SELECT * FROM [Sheet1$]') 
SELECT category as Category, SUM(ButtonClick) as Count FROM 
                    ( SELECT COUNT(id) as id, category as category, locationName as location, sum(counter) as ButtonClick FROM categoryData AS t 
WHERE locationName IN ('all', 'loc1', 'loc2') 
AND date BETWEEN '2018-04-03' AND '2018-04-04'
GROUP BY locationName, category) 
AS SUBQUERY GROUP BY category ORDER BY Count ASC";
SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.ExecuteNonQuery();

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".'

Really annoying error and scratching my head over this one. :( :(

Why go trough SQL Server just to write an Excel file? Do it from client-side right away. — Alejandro
– Alejandro, Commented Apr 13, 2018 at 11:06
Why do you need to parameterized that query? All values are hardcoded, so what are you trying to achieve by making them parameters? — Racil Hilan
– Racil Hilan, Commented Apr 13, 2018 at 11:23
Sadly you can't use parameters here; see stackoverflow.com/a/13831792/361842 — JohnLBevan
– JohnLBevan, Commented Apr 13, 2018 at 11:48
Also it looks like you'd need to use opendatasource instead of openrowset: stackoverflow.com/a/29078719/361842 — JohnLBevan
– JohnLBevan, Commented Apr 13, 2018 at 11:51
1) Because this code is part of a click event that exports data from a c# application after it has changed the data. 2) Because manually adding strings into the query string is difficult with the escape sequences and so forth — David Folksman
– David Folksman, Commented Apr 13, 2018 at 12:02

JohnLBevan · Accepted Answer · 2018-04-13 13:07:04Z

1

@Praveen states that you cannot use expressions, so have to use dynamic SQL: https://stackoverflow.com/a/13831792/361842

Per the above, I believe this is what you need:

string filepath = @"Excel 12.0;Database=C:\Temp\TestExcel.xlsx;";
string oledbType = "Microsoft.ACE.OLEDB.12.0";
string querySheet = "SELECT * FROM [Sheet1$]"; //amend to match the sheet name: https://stackoverflow.com/a/910199/361842
string query = "SELECT * FROM categoryData"; //this is the SQL to get the data from your SQL DB to send to your sheet.

string exportQuery = @"
declare @sql nvarchar(max) = '
    INSERT INTO OpenRowSet(
    ' + quotename(@oledbType,'''') + '
    , ' + quotename(@filepath,'''') + '
    , ' + quotename(@querySheet,'''') + '
    )' +
    @query + ';'
exec (@sql)
";

SqlCommand cmd = new SqlCommand(exportQuery, con);
cmd.Parameters.AddWithValue("@filepath", filepath);
cmd.Parameters.AddWithValue("@oledbType", oledbType);
cmd.Parameters.AddWithValue("@querySheet", querySheet); 
cmd.Parameters.AddWithValue("@query", query); 

cmd.ExecuteNonQuery();
con.Close();

The above generates and executes the dynamic SQL:

INSERT INTO OpenRowSet(
    'Microsoft.ACE.OLEDB.12.0'
    , 'Excel 12.0;Database=C:\Temp\TestExcel.xlsx;'
    , 'SELECT * FROM [Sheet1$]'
)
SELECT * FROM categoryData;

(It's not functionality I've played with myself, so can't confirm whether this will work; just going off answers elsewhere on this site).

Update

Earlier I'd said

@Stephan states that to update data you should use OpenDataSource instead of OpenRowSet: https://stackoverflow.com/a/29078719/361842

I've since seen that that's not the case. i.e. see @AleksandrFedorenko at https://stackoverflow.com/a/13891752/361842. As such, amended the above.

edited Apr 13, 2018 at 13:07

answered Apr 13, 2018 at 12:05

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

David Folksman Over a year ago

Thanks for the reply. Im totally lost on all the concatenation and escape sequence characters and quotation characters. Why is there an @ sign at the beginning of the string? Why is there a ' before the concatenation + sorry for the newbie questions.

JohnLBevan Over a year ago

No worries. The @ makes it a "verbatim literal string". See stackoverflow.com/a/1100265/361842. In the above case it's not really needed; but I tend to use this when dealing with complex strings just so I don't have to thing about escape characters when I don't need backslash expressions. (e.g. stuff like \r\n)

David Folksman Over a year ago

ah ok useful. What about the ' + (code) + ' and (@variable,'''')

JohnLBevan Over a year ago

The quotes and pluses are essentially to concatenate the following strings: 'INSERT INTO OpenRowSet(', quotename(@oledbType,''''), ', ', quotename(@filepath,''''), ', ', quotename(@query,''''), ');'

JohnLBevan Over a year ago

It looks a bit more confusing as I've put line breaks within the joined strings; that makes it easier to read in SO (i.e. so code doesn't go off the right of the page), and would make the dynamic SQL easier to read if you did a print @sql to view the value.

|

Collectives™ on Stack Overflow

Using parametrised query with OPENROWSET

1 Answer 1

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related