question

Mohan Kumar avatar image
Mohan Kumar asked

Export Sql Server ResultSet to Excel Sheet

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
excelexport-dataopenquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
Not 100% on the OPENROWSET, but BCP will allow you to do this. [MSDN BCP][1] [1]: http://msdn.microsoft.com/en-us/library/ms162802.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
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 :
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot.
0 Likes 0 ·
pan_cbe avatar image
pan_cbe answered
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 ....
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.