Export query result into Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Export query result into Excel

Author

Topic

SQLS33ker
Starting Member

16 Posts

Posted - 2015-01-23 : 22:35:57

hi there,

I am trying to run a query then export the result back to an Excel file on a regular basis. I am going to tackle the routine export as a separate task but right now I am having trouble setting up the OLE DB part.

This is what I have right now


insert into openrowset('Microsoft.Jet.OLEDB.4.0',
'EXCEL 8.0; Database = C:\D:\My Misc\FH\test_sql_to_excel.xlsx;',
'select * from [Data]') select a.* from test.test.Contacts as a

The error message I get is
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Did I miss anything in the code?

Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-26 : 10:49:57

You can download and install only the ACE provider from the following link, this supports office 2007 files both xls and xlsx file.

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en,

also use RECONFIGURE:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

You can use BCP from the command window:

bcp test.test.Contacts out "C:\My Misc\FH\test_sql_to_excel.csv" -c -T

We are the creators of our own reality!

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-28 : 02:49:16

Better you can write macro in Excel.
Otherwise Export using SSIS

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources