email reports

[shaili]

Hi,

We get lots of work requests to send out emails using diferent accounts ohter than sql service email account.
e.g. Our sql notification emails are sent from 'enrollnotify@cnsinc.com'.

The requirement is to run the queries, capture student name and enrollment info and send out emails to the students from 'success@cnsinc.com' or 'reminder@cnsinc.com' or other accounts like this depending upon the business group request.

What is the proper way to do it without stopping and restarting email services all the time? Are there any third party of Microsoft tool available to accopmlish this in sql server? We use sql 2000 sp3 standard version.

[MAK]

if you need to use different "From" option then you have to use SMTP mail
or cdosysmail

http://www.databasejournal.com/sqlet...le.php/1585201

http://forums.databasejournal.com/sh...light=smtpsend

[shaili]

Hi MAK,

I usually run the procedures with to capture the data. Once I have data, I have to create customized emails like

'Dear <student_name>,

Your license is going to expire on <specific date>..

etc

No of emails to be sent out varies from 5 to 5000.

Do you think using CDOSYS procedure help me with this?

I am still doubtful that the entire project can be done using only sql server.

[skhanal]

You can use CDOSYS object from transact sql to do this.

[k2kataria]

Hey,

U can create a package. Run all ur queries, get the data using activex script. (vbscript) and change the data being populated in the email component.

Check it out. I guess its the best thing to do!

Ketan

[k2kataria]

Reading ur question again I got this idea.

1. Have a table to contain the email addresses in it. With a flag which would mean that the record should be deleted once an email is sent
2. Have the query that needs to be executed as a part of this table.

So when the business group needs to get a certain kind of data and needs to send it to say x number of email addressed. you populate the table with some front end

3. write an activex script that will read the table. take the sql script and execute it. Get the result set and populate email content. Mail it and delete the email address from the table depending on the flag.

This will make the applciation dynamic. so that you can change the queries and the email addresses anytime.

Let me know what u think!
Ketan

[MAK]

Too many process involved if it is a DTS package.