Export SQL to Excel - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Export SQL to Excel

Author

Topic

peitech
Starting Member

10 Posts

Posted - 2008-03-13 : 05:16:24

I have written a query which dumps the output to a table, ideally I would like to select everything from this table in to an Excel file.

Looking @ http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

Made me think this would be quite easy... so

1. I created a spreadsheet named test.xls with the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:

USE [PEI];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\DELL\test.xls;',
'SELECT * FROM [Sheet1$]')
SELECT *
FROM tblCFPooled
GO

Where C:\DELL\test.xls is where I saved test.xls, tblCFPooled is the table I have populated in the firstplace and PEI is the database name.

When I run this the following error crops up:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. 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)".

I then found something on a different site:
http://www.mssqltips.com/tip.asp?tip=1202

So based on this I tried:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\DELL\test.xls;',
'SELECT * FROM [T$]') select * from tblCFPooled

And obtained the following 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 'T$'. 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)".

(I renamed the sheet1 to T if you are wondering where T$ comes in!)

Before running either of these 'export' queries I did as instructed in the first link:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Which produced results:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?

Both methods seem to throw up a similar error, an error which makes me think its some kind of SQL authentication issue.

Has anyone successfully exported from SQL to Excel - if so, any tips?!

(For the record I am using Management Studio, we are running SQL Server 2005, I am doing all this on a Vista Business machine, the SQL server is on a local server on our network here. )

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-13 : 05:32:20

Check this thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources