Thursday, December 14, 2017

T-SQL Database Alert/Mail System

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

 

Archives

Powered by mod LCA