(BTW, this has also been posted on Stack Overflow)
I want to build a paremtized SQL Query that retrieves data from an EXCEL file.
The name of the Excel file will change every year.
Here is the query that runs fine in T-SQL (on SQL Server 2008 R2 Express):
SELECT * INTO MobilityData FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=\\UNCPath\Annual_Mobility_Update\DAM2013-3.xls;Extended Properties=Excel 8.0')...[Sheet1$]
(UNC Path is a network UNC Path, works fine)
The string 'DAM2013-3.xls' will be changing year on year. What I'd like to do would be execute a parametized stored procedure and build the Dyanamic SQL on the fly...
Something like this:
SELECT @sQry='SELECT * INTO MobilityData FROM OPENDATASOURCE(''''Microsoft.Jet.OLEDB.4.0',
+ @XLFileSource + ';Extended Properties=Excel 8.0'')...[Sheet1$]'
but I seem to be in quote hell and can't get it to compile.
What should I do to fix this?
I'd be grateful for any tips, fishing rods, or even whole a fish etc