Had a request to export data out from SQL Server to Excel directly. There are a few ways you can do this. BCP is one, another is OPENROWSET. SSIS, etc. Here is the OPENROWSET method, using Jet (I think this only works on 32bit servers as well)
First you need to enable a setting on SQL, so.. Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration, then click on the “Surface Area Configuration for Features” at the bottom.
Then, you need to create an empty excel workbook, with the columns of the query you want to export out. Create one on C:Testing.xls with “Name” and “Rating” columns
Then run this query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:testing.xls;', 'SELECT Name, Date FROM [Sheet1$]') SELECT 'Steve','1' Union SELECT 'Joel','2' GO
And if everything worked, your xls should have 2 rows in it.
Now, if you want output to other spreadsheets, you could dynamically set the filename in the statement, or other trickery!