query 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

query to excel

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-20 : 06:16:02

Dear All,
i'm trying to export the data from database to excel file.

for a single query, it is providing the data but for multiple queries to run at a time, it is not exporting the complete data.

for example
i've two tables
table1 and table2
i'm getting the data for table1 even though i've given the select statement for both the tables.

select * from table1
select * from table2
i'm getting only the table1 data. please provide me the solution

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 06:23:18

quote:
Originally posted by sunsanvin

Dear All,
i'm trying to export the data from database to excel file.

for a single query, it is providing the data but for multiple queries to run at a time, it is not exporting the complete data.

for example
i've two tables
table1 and table2
i'm getting the data for table1 even though i've given the select statement for both the tables.

select * from table1
select * from table2
i'm getting only the table1 data. please provide me the solution

Arnav
Even you learn 1%, Learn it with 100% confidence.

How are you exporting to EXCEL?

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-20 : 07:02:35

through DTS...there i'm using the through query option

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 07:06:28

Do both tables have same schema?

Try

select * from table1
union all
select * from table2

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-20 : 08:26:36

no

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 08:30:42

quote:
Originally posted by sunsanvin

no

Arnav
Even you learn 1%, Learn it with 100% confidence.

Then do you want to export them to different sheets?

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-20 : 08:35:59

yes... evn for a single sheet not a problem

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-20 : 08:39:53

Use tow queries with different sheet name
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-20 : 08:43:36

Thankyou very much Madhi

Arnav
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-08-21 : 01:09:36

getting error: for the below query....my table is cities table in mycities database. i've created an excel file with name aa and created a sheet with name cities.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls;','SELECT * FROM [cities]')
select * from cities

getting the below error:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Arnav
Even you learn 1%, Learn it with 100% confidence.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 02:46:37

quote:
Originally posted by sunsanvin

getting error: for the below query....my table is cities table in mycities database. i've created an excel file with name aa and created a sheet with name cities.
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\aa.xls;','SELECT * FROM [cities]')
select * from cities

getting the below error:

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 3
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Arnav
Even you learn 1%, Learn it with 100% confidence.

Make sure EXCEL file is closed when you execute the query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources