I love Database Mail. The new mail system that was introduced in SQL Server 2005 is head and shoulders above the old SQL Mail functionality of SQL 2000. Setting up email alerts for failed jobs and error conditions is now a breeze.
Unfortunately, I still have a couple SQL Server 2000 machines in use that I have to take care of. Furthermore, I know there are other DBAs out there in the same situation. For those that never had to deal with it, with SQL Server 2000, in order to send email, you had to have Outlook 2000 installed on your server. It was ugly. Email in SQL 2000 felt like a cludge, an afterthought. Not only that, installing Outlook on your server required purchasing an additional Office license for each server, something that was hard to justify to upper management.
There are some nice community-developed solutions, such as xp_smtp_sendmail. This works great if you need to send email from a stored procedure or via T-SQL. If you wanted to send email if one of your jobs failed, however, it was fairly cumbersome. Because it was not built in to SQL Server, you needed to add a step to each and every job which would make a call to xp_smtp_sendmail to send out the failure notification. Then you had to modify each step of each job to jump to this new step on failure. If you had more than a handful of jobs, it was tedious at best, a nightmare at worst. And if you had several SQL Servers, each with many jobs, well.. Just forget about it.
When I was brought on board at my current company last year, I found out I had two SQL 2000 servers that I would be responsible for. Of course, SQL Mail was never set up on these machines. I had no desire to set it up now, especially since I was going to be pushing to upgrade or replace these servers with SQL 2008 soon. But until that happened, I needed some way to be notified if a job failed.
Luckily, our IT department uses monitoring software that is used to watch other critical systems such as websites and file servers. Your company probably does as well. The particular product we use is called Orion from SolarWinds, but there are many others out there that provide similar functionality. One of the things Orion can monitor is the Windows event log. We can use this to monitor for failed SQL Server jobs.
If you configure your SQL Server job to write to the windows event log on failure, you will see an entry in the application event log like this when a job fails:
Note the event ID value of 208, which signifies a failed SQL Server job. Now all you need to do is configure your monitoring software to monitor the application event logs of your SQL 2000 Servers for event ID 208. Congratulations! You’ve now got email notification for failed jobs on SQL 2000 without buying an additional Office license and without having to deal with SQL Mail!
Note that you can do this with your 2005 and 2008 SQL Servers as well, but Database Mail is so easy to setup and use, I don’t see a reason to do so.