This post originated from an RSS feed registered with .NET Buzz
by Richard Jonas.
Original Post: Export to excel from SQL Server Express
Feed Title: Richard Jonas
Feed URL: http://feeds.feedburner.com/blogspot/ouNA
Feed Description: Richard Jonas's blog about .NET, web development and agile methodologies.
Once upon a time, a big bad grizzly bear wanted to write a program to export a view from an SQL Server Express database (containing the names and addresses of sweet fluffy things he wanted to eat) to an Excel file. As this was SQL server express, he tried to use DTEXEC and integration services, but growled ferociously when he realised that integration services was not supplied with excel, promising to sharpen his claws and tear apart Bill Gates.
However, a passing raccoon came to his rescue, and told him about the following trade secret way to do this (subject to removing the raccoons name and address from his database):
1) Go to the Surface Area configuration tool, select "Surface Area configuration for features". Select "Ad Hoc Remote Queries", and turn on "Enable OPENROWSET and OPENDATASOURCE support".
2) Create a template excel file, with the first row containing the column names in your view. You can use integration services to export a file to Excel, and then delete the data from it, leaving the first row.
3) From your program, use the File.Copy command to copy the template file to your destination file.
4) Run the following SQL query to populate the Excel file
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\\aaa.xls;', 'SELECT * FROM [Query]') select * from ExportView
where c:\\aaa.xls is your file name, "Query" is the name of your worksheet and "ExportView" is the name of your table or view you want to export.
Previous Topic
