OK, here's the deal...
Using SQL Server 7.0 on NT4 Server with Exchange 5.5 and all the various SP's installed, I have a database of around 100,000 users that I wish to send regular emails to.
I have SQLMail running correctly but would like to know if this is a suitable application to use for this project and if so, some pointers on how to get started (have only ever used xp_sendmail for simple stuff before).
There are a few things that add complication - one of the databases containing user details is an external one on a separate IP address, only accessible via OLEDB. I need to somehow get SQLMail to read the data in from this database before sending emails out (is DTS the way to go?).
I would also like to create the email message (the @message component of xp_sendmail) in a separate text file that xp_sendmail would just read in as part of the process - this way I won't have to change lengthy stored procedures each time I want to send a different message.
Finally, the users must have the option to opt out of receiving the emails, so I would need to have some way that they could reply to SQLMail to have a flag (column) automatically updated in the appropriate database (so they could be ignored from future SELECT queries as part of the process).
Can anyone advise me whether SQLMail is the right thing for this type of job? If so, can you give me some ideas on how to get it started?
If it shouldn't be used for this, what is a better tool for the job (ie: is there a way to get Exchange to do all this)?
I appreciate that this is probably rather complicated, but any help that anyone could give would be greatly appreciated.
Many thanks,
Chris Depper
Using SQL Server 7.0 on NT4 Server with Exchange 5.5 and all the various SP's installed, I have a database of around 100,000 users that I wish to send regular emails to.
I have SQLMail running correctly but would like to know if this is a suitable application to use for this project and if so, some pointers on how to get started (have only ever used xp_sendmail for simple stuff before).
There are a few things that add complication - one of the databases containing user details is an external one on a separate IP address, only accessible via OLEDB. I need to somehow get SQLMail to read the data in from this database before sending emails out (is DTS the way to go?).
I would also like to create the email message (the @message component of xp_sendmail) in a separate text file that xp_sendmail would just read in as part of the process - this way I won't have to change lengthy stored procedures each time I want to send a different message.
Finally, the users must have the option to opt out of receiving the emails, so I would need to have some way that they could reply to SQLMail to have a flag (column) automatically updated in the appropriate database (so they could be ignored from future SELECT queries as part of the process).
Can anyone advise me whether SQLMail is the right thing for this type of job? If so, can you give me some ideas on how to get it started?
If it shouldn't be used for this, what is a better tool for the job (ie: is there a way to get Exchange to do all this)?
I appreciate that this is probably rather complicated, but any help that anyone could give would be greatly appreciated.
Many thanks,
Chris Depper
