<cp******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Deaa group,
I am using SQLServer 2000 in an XP Sp2. I would like to do the
following:
I have a program running on a database server that generates some data
which are loaded to the database. This program is used in a web
application, invoked by some java program and JSP scripts. (I am
frontend illiterated.)
The question is, is it possible to write a stored procedure to generate
output in excel spreadsheet? So that user could call this procedure
and get spreadsheet output on the client side.
Any pointer to a solution would be immensely apprecaited.
thanks,
charia
As far as I know, there's no direct way to export to an .xls from a stored
proc. DTS can export data to Excel, and you can execute a package from a
stored proc in various ways:
http://www.sqldts.com/default.aspx?210
By using ActiveX steps in a DTS package, you could control all the details
of the .xls file name, structure, column headers etc. via the Excel COM
interface, but you would need to actually install Excel on the server in
order to do that, which may not be possible (or desirable).
Another option would be calling bcp.exe via xp_cmdshell to create a CSV or
tab-delimited file. In the end, the easiest solution might be to find a Java
or JSP module of some sort which can export to Excel - then you just return
the result set to the client or middle tier as usual, and let it create the
file, which is probably a cleaner solution than dealing with presentation in
the database itself.
Simon
