Hi all(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','Data Source=\\sgstore\Shared\operations\DM\Annual_Mobility_Update\''' + @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 etcThanks Philip