Using dynamic sql in openrowset produces error

Question

I need to create a stored procedure that gets a path as a parameter and inserts from file into table via OPENROWSET command.

After lots of searching and trying, I learned that OPENROWSET does not support parameters and thus needs to be called with dynamic SQL.

That is the part that doesn't work, it shows me a strange error.

It could be caused by the OPENROWSET not accepting the string parameter but - I saw many code snippets that are built similarly and users say they work.

Please help me understand what I'm missing here and how do I make this work?

Here is my code:

Declare @string varchar(MAX) = 'C:\Users\akoga_000\Desktop\test1.xlsx'
DECLARE @sqlString AS varchar(MAX)=

'insert into gameIt_DBSummer.dbo.tblUser 
select * from openrowset(
''Microsoft.ACE.OLEDB.12.0'',
 ''EXCEL 12.0;DataBase=''
 '+cast(@string as varchar(max))+'
 '';Extended Properties="EXCEL 12.0 Xml;HDR=YES'',
 ''SELECT * FROM [Sheet1$]''
)';

EXEC (@sqlString)

//I tried also with EXEC sp_executesql and a nvarchar variable among other options

Here is the error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'C:'.

Answer 1

I think you are getting that error because you need double extra '' character surrounding the path (@string variable). Try this:

Declare @string varchar(MAX) = 'C:\Users\akoga_000\Desktop\test1.xlsx'
DECLARE @sqlString AS varchar(MAX)=
'insert into gameIt_DBSummer.dbo.tblUser 
select * from openrowset(
''Microsoft.ACE.OLEDB.12.0'',
 ''EXCEL 12.0;DataBase=''''
 '+@string+'
 '''';Extended Properties="EXCEL 12.0 Xml;HDR=YES'',
 ''SELECT * FROM [Sheet1$]''
)';
select @sqlString