Microsoft SQL Server forum

Author

urgent

Kalyan

2005-03-31, 8:03 pm

can any one give me a stored procedure which on execution creates an
excel file in one of my drives(like c:\temp.xls)that contains records
from a table selected which should be the argument of the stored
procedure.

David Portas

2005-03-31, 8:03 pm

Although it might be possible to write that purely in TSQL (using the
sp_OA procs) you will probably find it much easier and more efficient
to use DTS and then run the DTS package from a stored proc. If you
aren't familiar with DTS, use the Import/Export Wizard to create a
package for you and then adapt it in the designer.

Alternatively, write an Excel macro to do the same thing. Excel can
connect directly to SQL via ODBC. Technically you could open an Excel
application object from an SP and run the macro that way (sp_OA_create
again). However, I wouldn't recommend doing that as part of a
production process on a server because it is hard to avoid leaking
resources and RAM to dormant instances of Excel if and when the process
ever fails.

--
David Portas
SQL Server MVP
--

Sponsored Links