Data Management

Automatically send mail in SQL Server with xp_SMTPSendMail80

If you want to be able to send mail from within a clustered SQL Server environment, your best solution could be xp_SMTPSendMail80.

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

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.

SMTP server

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 jhoskins@testdomain.com from sqlserver@testdomain.com 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.

Final word

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.

Editor's Picks

Free Newsletters, In your Inbox