Exporting from Query Analyzer to Excel sheet

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 2000 Forums

Transact-SQL (2000)

Exporting from Query Analyzer to Excel sheet

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-15 : 15:50:53

How can save the results from query analyzer to a excel sheet with all the columns included as we do it through the DTS.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-15 : 16:01:04

Well you can do a save when your cursor is in the results pane, but it won't be in a good format for Excel. The best way is to use bcp if you don't want to use DTS. You can access bcp via Query Analyzer using xp_cmdshell.

Tara Kizer

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-15 : 16:43:25

How about

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

Srinika

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2006-08-15 : 17:37:58

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Documents and Settings\raj.shankar.DDC\Desktop\test\testing.xls;','SELECT * FROM [sheet1')select * from tbl_contact_types

When I execute this query Iam getting the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-15 : 23:22:11

[edit]Oh, nevermind...[/edit]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-16 : 02:58:01

[code]insert OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\Documents and Settings\raj.shankar.DDC\Desktop\test\testing.xls;','SELECT * FROM [sheet1$]')
select * from tbl_contact_types[/code]

Peter Larsson
Helsingborg, Sweden

ChrisT
Starting Member

1 Post

Posted - 2006-09-27 : 15:13:35

Hello,
I'trying to do the same thing (export the results of a query to an excel file) , however I'm getting the following error:

My TSQL query :-
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\dauid_null.xls;',
'SELECT * FROM [Sheet1$]') select * from supermarkets_2006_test

Corresponding error:-
"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider 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.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IColumnsInfo::GetColumnsInfo returned 0x80004005: ]. "

I created the excel spreadsheet, and it has the column names in row 1.

Can anybody help me with this as well ? Also would the fact that the database resides on a server and not my local machine be a factor in this situation ?

ChrisT

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-27 : 21:07:43

Make sure whether the sheet name is Sheet 1 or Sheet1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources