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. :( :(

Answer 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.