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.
6 comments:
Hi Richard, I was shouting when I saw your blog!!!! My colleagues thought I was mad! lol! I have been looking around for the right way to do table export from the 2005 Express. Somebody from the local community forums pointed your blog.
By the way, do you think there is any syntax for exporting to dbf file?
hi I did what you said , but its giving me an error.
please look to my code:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=e:\\exportdata.xls;', 'SELECT * FROM [export]') select * from testdb.dbo.menu
the error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'export'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Is "export" the name of a worksheet in your database. You will need to rename the default "Sheet1" to "export".
Hi Richard, I tried as you suggested and got same result as elena_85. As far as I can see all is correct.
Any ideas?
Would be very useful if I could get this to work.
Thanks
Andrew
put a $ after the sheet name! so if your sheet is named export reference it as [export$]
In this situation recommend to use this software-corrupted repair Excel recovery tool,because when my friends were in same situation,program helped their,tool is free as far as I can see,it can be easily solved with Excel recovery software for recovery xls,will allow you to Excel files recovery and make sure, that it works and that your work results can now be recovered just in several clicks,allows to save many hours of your work for recovery for Excel download and recover everything, that was done before.
Post a Comment