Export Sql Server ResultSet to Excel Sheet

Question

Hi

I need the Sql Server Resultsets to be exported to Excel Sheets. There will be multiple queries and it is not fixed. Hence we CANNOT create the destination Excel Sheets in advance.

I'm aware of the following script, but for this the excel sheet has to be created in advance...

INSERT INTO OPENROWSET 
('MICROSOFT.JET.OLEDB.4.0', 
'Excel 8.0; Database=\\dba\Share\Test.xls;HDR=NO;IMEX=2',
'select * from [Sheet1$]' 
)
 SELECT * from category

Rgds
Mohan Kumar VS

Answer 1

0

Not 100% on the OPENROWSET, but BCP will allow you to do this.

MSDN BCP

answered Jul 01 '11 at 05:49 AM

sp_lock
8.1k ● 20 ● 26 ● 29

Thanks a lot.

Jul 03 '11 at 11:39 PM Mohan Kumar

(comments are locked)

Answer 2

0

Another option is to use Powershell. Within Powershell you can create Excel objects (sheets), run sql and export data - seems like a candidate!

Maybe start with this link : http://sqlserverpedia.com/blog/sql-server-bloggers/powershell-and-sql/

answered Jul 01 '11 at 09:01 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

Thanks a lot.

Jul 03 '11 at 11:39 PM Mohan Kumar

(comments are locked)

Answer 3

0

Hi could you please let me know some more info about this query....

INSERT INTO OPENROWSET 
('MICROSOFT.JET.OLEDB.4.0', 
'Excel 8.0; Database=\\dba\Share\Test.xls;HDR=NO;IMEX=2',
'select * from [Sheet1$]' 
)
 SELECT * from category ....

answered Nov 19 '12 at 10:24 AM

pan_cbe
0

I would suggest you ask this as a new question, indicating what you want to know...

Nov 19 '12 at 11:10 AM ThomasRushton ♦

(comments are locked)

Export Sql Server ResultSet to Excel Sheet

Follow this question

Related Questions