export data to .xls from stored procedure

Hi
I was told to write an "application" that sends emails with some data stored
in database to our company's departments. I'm trying to do this using stored
procedures and DTS. Problem is that every file must have differrent name
(depertment's ID is a part of it). So, I considered doing it by:
- export tables/views to .xls file directly from stored procedure (by
some method/function) giving filename as an argument but I don't know
how to do this;
OR
- create DTS - this way is quite easy but I have no idea how I can
change output file'a name depending on current department;
thanks for help
MarcinYou can dynamically assign the export filename using a VBScript task. At the
very least, the destination connection can export to a static temporary
filename and then on completion use VBScript and the File System Object to
rename it.
Using DTS to Detect and Process a File
http://www.databasejournal.com/feat...cle.php/1459691
Read / write property for retrieving or setting the name of the file.
http://www.sloppycode.net/Reference/FSO/?ref=116
"mati" <mati@.wp.pl> wrote in message
news:dmhp5g$gj0$1@.nemesis.news.tpi.pl...
> Hi
> I was told to write an "application" that sends emails with some data
> stored in database to our company's departments. I'm trying to do this
> using stored procedures and DTS. Problem is that every file must have
> differrent name (depertment's ID is a part of it). So, I considered doing
> it by:
> - export tables/views to .xls file directly from stored procedure (by
> some method/function) giving filename as an argument but I don't know
> how to do this;
> OR
> - create DTS - this way is quite easy but I have no idea how I can
> change output file'a name depending on current department;
> thanks for help
> Marcin
>|||I far better application than that is already available - SQL Reporting
Services, and you can add SQL Notification Services if need be.
Or would "they" rather prefer you re-invent the wheel? :)
If, however, you have no other options but to desing your own solution,
you'll have to include into your DTS:
1) an ActiveX Script to create XLS documents with dynamically asigned names;
2) a stored procedure in an Execute SQL step to fill the Excel File with
data. One of the parameters should be the path to the destination XLS.
Lookup OPENROWSET in Books Online on how to insert data into an Excel
Worksheet.
3) sending e-mails from SQL 2000 is another story. Look up xp_sendmail in
Books Online, or Google for xp_smtp_sendmail.
And that's just a rough description what you need.
MLsql