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

explanation needed - DECLARE CURSOR

hi!
i need (preferably 0:)) short explanation about difference between declare cursor a
nd declare cursor with hold.
if i'm working in multiuser regime, and need to pay attention to locks, do i hav
e to use declare with hold?Hmm, don=B4t know about a switch for the WITH HOLD lock, I think you are
refering to the syntax of antoher database or you missing up something
right ? But in common you should consider using a non cursor solution,
because setbased operations are in general faster.
HTH, Jens Suessmeyer.|||Hi, cvetic
You posted in the wrong group. There is no "WITH HOLD" syntax in
Microsoft SQL Server. I believe it is used in IBM DB2, PostgreSQL and
some other SQL variants. An equivalent option in SQL Server is "SET
CURSOR_CLOSE_ON_COMMIT" and the "cursor close on commit" database
option.
Razvan