I am successfully exporting to excel with the following statement:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'SELECT * FROM [SheetName$]')
select * from myTable
You'd have to use dynamic SQL.
OPENROWSET etc only allows literals as parameters.
DECLARE @myfile varchar(800) SET @myfile = 'C:\template.xls' EXEC (' insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @myfile + ';'', ''SELECT * FROM [SheetName$]'') select * from myTable ')
Remember: the path is relative to where SQL Server is running