0

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

Dale K
  • 25,246
  • 15
  • 42
  • 71
Alex K
  • 89
  • 1
  • 10

1 Answers1

0

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
JC Castro
  • 51
  • 5
  • Thank you for answering! I did as you suggested. and it semms that It solved the syntax error BUT now I am getting another error: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot update. Database or object is read-only.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". The file and the database are not read only....Any ideas?Thank you! – Alex K Oct 08 '15 at 17:12
  • Take a look at this: [link] http://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null – JC Castro Oct 08 '15 at 18:59