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.