Trying to use a variable file name in a SELECT statement using OPENDATASOURCE 1

[Baesucks]

I'm reading some excel spreadsheets into SQL Server tables. Can anyone tell me why I can do this...

SELECT * INTO TestImport FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=E:\MyFolder\MyExcelFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]


But I can't do this...

DECLARE @FileName NVARCHAR(250)
SET @FileName = 'E:\MyFolder\MyExcelFile.xls'

SELECT * INTO TestImport FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=' + @FileName + ';Extended Properties=Excel 8.0')...[Sheet1$]

 

[gmmastros]

I can't really explain it, but I'm pretty sure I know how to get around it.

Declare @SQL VarChar(8000)

Set @SQL = 'SELECT * INTO TestImport FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source=<FILENAME_HERE>;Extended Properties=Excel 8.0'')...[Sheet1$]'

DECLARE @FileName NVARCHAR(250)
SET @FileName = 'E:\MyFolder\MyExcelFile.xls'

Set @SQL = Replace(@SQL, '<FILENAME_HERE>', @FileName)

Exec(@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom