Implementing SQL Emails in SQL 2000

Stop with the Polling

In lots of legacy systems there is a problem of polling.  A job or routine comes along on a regular basis and checks to see if there is some work to do.  When it finds something, it actions it and moves on thinking everything is great.  In even more disjointed systems, they will then have another polling job that should run as part of the other job, but for whatever reason decides to do its own polling to see if work should be done.

This creates a problem in many cases because Job A may come and poll again before Job B has had a chance to do its bit.  What if Job A is cleaning up after itself or overwrites what it did the first time and Job B hasn't had a chance to get what it needed to complete?  This is a common scenario for polling and one of the reasons it is bad in modern computer and why event driven processing is preferred.  Quite often this is solved by implementing a listening pattern but my problem didn't need to go that far.

Legacy Emails

One of the features of SQL Server 2008 is the ability to send out emails using the inbuilt stored procedures for handling mail.  This can be a very useful feature as it saves you having to have a mail client installed on the server and saves another failure point to check if emails aren't being sent.  My problem was the Jobs in question weren't on SQL 2008, they were on SQL 2000 which doesn't natively support this email functionality.  My initial thought was to write a client that would interface to the smtp server and I could call into this with xp_cmdshell.  I stopped myself short at this point because of code smell and realised this problem must have been solved before me.  We're in 2012 FFS!

Sure enough after some searching I was able to find the article that MS have written on this very matter
http://support.microsoft.com/kb/312839

Golden Hammer

The trouble is, now my work colleagues think this is great and have started flooding email addresses when jobs complete, fail, take too long etc.  I need to remember them that there's a time and place for everything and not all solutions can be managed with a simple email.  Plus if you're getting a lot in one day, surely a report on a daily basis would give a better indication of how big your problem is.  You may even want to keep an audit trail of when these things happen so you can get some meaningful stats in the longrun.