Search code examples
sqlsql-serverfilepath

How to concatenate a string with variable in my case

I am trying to concatenate file path and folder name variable like below

Declare @FileName int

SET @FileName = 1

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',  
'Excel 8.0;Database=D:\ExportData\'+ CAST(@FileName AS varchar(10))+'.xlsx;',  //Incorrect syntax near + expecting ',' or ';'
'SELECT top 100 ItemNo,Name FROM [Sheet1$]') select top 100 ItemNo,Name from product

Normally when I do concatenate with + operator, I am not getting any error as I defined like above. Only in this case I am getting an error

Solution

  • OPENROWSET() does not support expression or variable as input to the parameter, it must be string literal.

    One workaround is to generate the entire OPENROWSET query dynamically, use exec() or sp_executesql

    This limitation has been feedback in Feedback Forum