Build your Microsoft SQL Server skills with TechRepublic's Fast Track: Microsoft SQL Server CD-ROM. Learn at your own pace, no matter your SQL skill level, utilizing 87 computer-based lessons to develop the knowledge needed to program, administer, back up, and optimize SQL Server 7.0 and 2000.
When my company decided to move to clustering on our SQL servers, we could no longer rely on SQL Mail because of a bug in that software that can cause it to fail when working in a clustered SQL environment. An alternative was needed that could be implemented without great cost or a steep learning curve for our developers. In my search, I came across xp_SMTPSendMail80. This little program from Thorpe Software allows you to send mail directly from a SQL Server 2000 system to any SMTP-compliant mail server, and it works with clustering.
Installation is simple. (You can download a fully functional trial version of the dll from Thorpe.) Once the file is downloaded, extract smtpsendmail80.dll into any directory on your SQL Server system, then run this script to create the extended stored procedure.
This registers the extended stored procedure for use.
The only other requirement is an SMTP server that you can relay mail on. If you have a Microsoft Exchange server, you can simply authorize your SQL server's IP address for relaying. As an alternative, you can install the IIS SMTP service on your local SQL Server machine.
Once you have the SMTP server set up, sending e-mail through it is very easy. This example will send an e-mail to email@example.com from firstname.lastname@example.org with a subject line of "Midnight job failed."
You can also call it without the named procedures, like this.
Keep in mind that calling it with named procedures will give you access to more options, such as sending HTML-formatted mail and running queries in your mail, as shown here
You can also send attachments through this program using a script such as this, which is useful for e-mailing yourself your SQL maintenance logs, or any other log files you generate.
As I mentioned earlier, I discovered this program when my company needed an alternative to SQL Mail. We have been using xp_SMTPSendMail90 for about a year, and it has replaced all usage of SQL Mail. In the time we have been using this program, we have not had any problems with it. Since the first installation it has operated flawlessly, with no intervention or maintenance needed. I love its ease of use and easy integration, the developers love its features and claim they will never go back to SQL Mail, and management loves its price—only $70.00 per server. It is a robust and affordable solution for any size SQL Server environment, and it is a viable replacement for SQL Mail in a clustered SQL Server environment.