OPENROWSET: sp_executesql statement failing at @param

Question

I am developing a program to pull in the XML file that WordPress enables you to download (essentially a backup copy).

At this point I was automating the process to allow frequent backup of my data on SQL Server, and for some reason I am stuck at developing the query to run the OPENROWSET where the XML file will be located.

DECLARE @SQL NVARCHAR(MAX)
DECLARE @ParamDefinition NVARCHAR(500) = N'@fstring NVARCHAR(MAX)'
DECLARE @string VARCHAR(MAX) = 
N'C:\[FilePath]\Reviews\thehesperian2016-07-29.xml'

SET @SQL = 
N'INSERT INTO #Temp (Extract_Date, XMLDATA)
SELECT GETDATE()
     , A.*
FROM OPENROWSET(BULK @fstring, SINGLE_BLOB, CODEPAGE = ' + '''RAW''' + ') AS A'

EXEC sp_executesql @SQL
                 , @ParamDefinition
                 , @fstring = @string

The error:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@fstring'.

I can turn this into a simple query on a table in the predicate, so I have reason to suspect it is the way the filepath is read. I've spent a few hours racking my brain trying to figure out why this is wrong. While I COULD use QUOTENAME as in this example in the BULKINSERT, I was hoping to embed all of that in the dynamic SQL (thus still use sp_executesql)

What or why am I doing this wrong? Any help will be greatly appreciated. - Regards,

ANSWER

OPENROWSET - MSDN declares in its own paragraph:

OPENROWSET does not accept variables for its arguments.

QUOTENAME is sufficient, although I did run a few minor REPLACEfunctions anyways.

In your dynamic SQL @fstring is being seen as a literal, not a variable. — S3S, Jul 29 '16 at 19:20
@scsimon right, forgot to mention I was calling fstring via the proc parameter, so SQL Server shouldn't be guessing from how I understand it. — clifton_h, Jul 29 '16 at 22:28
You'll have to Concat with + '''@var''' + for dynamic SQL but still not sure about it being in OPENROWSET. I don't think it'll work — S3S, Jul 30 '16 at 00:49
@scsimon I tried, but alas, there is no method. Still, QUOTENAME is just as useful. :) Thanks for the help, your first comment hit the spot where I was off. — clifton_h, Jul 30 '16 at 11:52

Dan Guzman · Accepted Answer · 2016-07-30T17:42:20.267

2

The data file path the OPENROWSET function does not allow a parameter. Instead, build the needed string with the literal:

DECLARE @string varchar(MAX) = N'C:\[FilePath]\Reviews\thehesperian2016-07-29.xml';
DECLARE @SQL nvarchar(MAX);
SET @SQL = 
N'INSERT INTO #Temp (Extract_Date, XMLDATA)
SELECT GETDATE()
     , A.*
FROM OPENROWSET(BULK ' + QUOTENAME(@string, '''') + ', SINGLE_BLOB, CODEPAGE = ''RAW'') AS A';
EXEC sp_execute @SQL;

--EXECUTE(@SQL);

UPDATE:

Added QUOTENAME in case the provided file path is from an untrusted source. Also, note that OPENROWSET queries are not autoparameterized. It makes no difference whether one executes the query with sp_executesql or EXECUTE here.

edited Jul 30 '16 at 17:42

answered Jul 29 '16 at 19:35

Dan Guzman

43,250
3
46
71

This does defeat the point, which was to use sp_executesql. Also, I flubbed by not mentioning @fstring is called in the proc as a parameter...so in theory, SQL server should be able to treat it as a variable, right? – clifton_h Jul 29 '16 at 22:30
@clifton_h, you can use `sp_executesql @SQL` instead of `EXEC(@SQL)` if that is your preference but there is no requirement to do so without a parameter. Only a literal string is allowed as the file name argument. `OPENROWSET` doesn't allow parameters, AFAIK. – Dan Guzman Jul 29 '16 at 22:42
I would accept your answer, except it needs to cleanse the variable first via `QUOTENAME(@string, NCHAR(39) )`. to avoid malicious use. – clifton_h Jul 30 '16 at 11:50
Good point about the AdHoc, unless I was using this in a SSIS package (where the filepath would be reused again), but that is highly unlikely. guess I passed that section on MSDN. Thanks! – clifton_h Jul 30 '16 at 14:12

OPENROWSET: sp_executesql statement failing at @param

1 Answers1