Creating an Excel Spreadsheet from a Stored Procedure

[jpower69]

I am using sql server 2005 and was wondering if it is possible to create an excel spreadsheet from within a stored procedure..

I have been looking at books, thru google and can't find anything...i might be looking in the wrong place(s)...
if it is possible to passs the results from a sql stmt in a stored procedure to a spreadsheet, how does one do it>>or where can i go and find examples of how it is done...

thanks in advance for any suggesitons/comments...

 

[RiverGuy]

You most definitely can do this, but I would not recommend it as the SQL Server database engine really isn't best used to create Excel files. I'd suggest creating a VBScript or something similar (ask in the VBScript forum if this is the approach you take)--connect to the database, open a recordset (the recordset can be based on your stored procedure) and render the file. You can schedule it as a job if need be.

Another option would be to create a SQL Server Integration Services package, but this might be overkill for one file.

 

[jpower69]

thanks for the input...greatly appreciated...

 

[ousoonerjoe]

Well.... I know it is possible to set up the export in SSIS and then call that job via a stored procedure, but I'm not exactly sure how to export directly to an XLS via Stored Procedure.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[TysonLPrice]

You can also run it directly from Excel. Go to Data, Import External Data, New DataBase Query. Do your connections and go into the mode where you can edit the query. Run the SP in there.

 

[jpower69]

thank you for your imputss...never thought about going thru excel to create the file...

thanks again...appreciate the help...