I am working on a T-SQL "system" to send emails. This will help so that in all the things that I use to send update emails, (ETL, sql jobs, etc.), I can just write a record in a table somewhere and the email will be handled for me. Some benefits:
- I can have the templates and recipients pre-defined so I dont have to worry about what to say.
- I dont have to worry about updating my SSIS packages or sql jobs when the text or recipients of the notification email needs to be changed.
- A history of the emails sent, to whom, and when (purely for taking up space, I am sure noone would doubt me if I said an email was for sure sent even though they didnt get it).
- A queue that will allow the emails to be sent even if services are down (after they are recovered).
I will make sure to share it with others when I am ready. I am getting pretty close, but I want to test it out a lot first. I will get a LOT of comments about it and improvements I know!
Here is an informative article on turning on database mail in SQL 2005, I will add another for 2000 and 2008 later.
http://sqltidbits.com/scripts/configure-database-mail-reusable-script-sql-job-alerts