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.
Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.
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!
One reply on “Exporting Data from SQL Server to Excel Directly”
If you're running Sql Server 2008 Surface Area Configuration has moved.http://www.mssqltips.com/tip.asp?tip=1673