Send SMTP Email from SQL Server with an extended stored procedure and a free COM component By Peter A. Bromberg, Ph.D.

SQL Server has a built - in system stored procedure, xp_sendmail, that allows you to send out email. Unfortunately, it requires the use of the MAPI provider through either Exchange or Outlook (a.k.a. "Windows Messaging"). Although some third party mail tools, such as Lotus Notes, offer MAPI providers, I have found that even the Microsoft MAPI interface can be both bulky and buggy at times. It's also very difficult to find a stand-alone installable version of Windows Messaging for Windows 2000, because it seems that Microsoft has conveniently decided to leave it out. At least, I couldn't find one!

So, how can we reliably send SMTP outgoing email if we can't have Outlook installed on the server that runs our SQL Server? Well, we can use a third - party COM SMTP component, along with the sp_OACreate system extended stored procedure family, to write our own stored procedure. Once this is done, we can find all kinds of useful applications for this. For example, you may have a table that holds certain version information on new builds that your department installs on a software application you're developing. You'd like to be able to send the proper personnel a notification email whenever a new "build" record has been inserted in this table.

With this sproc, you can add an "ON INSERT" trigger to the table, which will call your email sproc and automatically send out the required email. Pretty nifty. And when you add in the FREEWARE email component I'm going to show you, it gets even better. (I did say "FREE", didn't I - I've included it in the download zip file for this article because it's hard to find).

So, let's get started. The email component I've chosen is Rainking's "RKMAIL". It's only about 110K, and offers both SMTP and POP functions. It supports a lot of methods including attachments and multiple outbound mailservers (in case the first fails, it keeps going down the list). I'm only using a basic subset of features here, but certainly enough to get you going if you want to build added functionality.

First, we need to understand how the sp_OACreate extended sproc and it's brothers and sisters work.

Here's the syntax to create a registered COM component instance:

sp_OACreate progid, | clsid, objecttoken OUTPUT [ , context ]

Once it's created, we can access its properties and methods with the sp_OAGetProperty, sp_OASetProperty, and sp_OAMethod brethren.

So, for example to instantiate the RKMAIL component, the code looks like this:

EXEC @intResult = sp_OACreate 'RKMAIL.SMTP', @intObject OUT

intResult is the return code that we check for errors (and I'll get to that code in a moment), and intObject OUT is the output parameter that gives us a "handle" to the object we've created. We'll use this when we call the related sprocs to invoke methods, set or get properties.

To invoke the "add host" method of the RKMAIL component, we would do like this:

EXEC @intResult = sp_OAMethod @intObject, 'AddSMTPHost', NULL, @strSMTPServer

In the stored procedures I've included in the downloadable code accompanying this article, I've interspersed plenty of comments as well as providing error handling / checking code in the proc. I've also provided a sample ASP page using VBscript that has a form allowing the user to enter the email details, then calls the stored procedure and passes the form variables to it. The stored procedure sp_SendRKMail also allows you to pass in a comma - delimited list of multiple recipients. The way we handle this is as follows:

-- Parse and Add Recipients from comma - delimited @RecipientList
select @substring = ''
select @string = @RecipientList
select @string_length = len(@string)
select @startpos = 1
select @endpos = 1

while @startpos <= @string_length and @endpos <= @string_length + 1
begin
if substring(@string,@endpos,1) = ',' or @endpos > @string_length
begin
select @strToEmail = substring(@string,@startpos,@endpos-@startpos)
-- add recipient here to RKMail
EXEC @intResult = sp_OAMethod @intObject, 'AddRecipient', NULL, @strToName, @strToEmail
IF @intResult <> 0
BEGIN
EXEC sp_displayoaerrorinfo @intObject, @intResult
RETURN
END
-- set @strToName to null string so doesn't get repeated for multiple recips
select @strToName= ''
select @startpos = @endpos + 1
select @endpos = @startpos + 1
end
select @endpos = @endpos + 1
end

Finally when all the methods and properties are set, we call the Send() method and destroy our object:

--Send the mail
EXEC @intResult = sp_OAMethod @intObject, 'SendMail'
IF @intResult <> 0
BEGIN
EXEC sp_displayoaerrorinfo @intObject, @intResult
RETURN
END
-- Destroy mail object.
EXEC @intResult = sp_OADestroy @intObject
IF @intResult <> 0
BEGIN
EXEC sp_displayoaerrorinfo @intObject, @intResult
RETURN
END

The complete code for the above sproc as well as the error-handling sproc, OAErrorInfo are included in the download. Also included is the readme.txt file for all the methods and properties for both SMTP and POP in the control, the ASP test page I mentioned above, and my special ADOFunctions.asp "include" page that has a VBScript class that "genericizes" most of the methods a developer needs to work with SQL Server through ADO.

To get this going, simply register the included RKMAIL.DLL component (a good idea is to put it in a COM+ application), run the OAErrorInfo.sql and the sp_SendRKMail.sql SQL Server script files against your MASTER database, and put the ADOfunctions.asp and the SendRKMailSample.asp page in an IIS http-addressable virtual folder. ADOFunctions.asp is looking for Application("strConn") to be holding the connection string to your SQL Server database, so you'll need to set your virtual root to be an application and put that into a global.asa file. Otherwise, you'll need to hard-code in the connection string details.

Enjoy.

Nota Bene:
Reader Robert Klaproth was kind enough to send this valuable update:

"Thanks for the useful code for sending mail through SQL using RKMail. Unfortunately, there is one correction that I think you need to make to your article. There is a bug in SQL 6.5 and SQL 7 with the sp_OASetProperty that only allows 250 characters to be passed. This works fine for most of the paremeters needed to send the mail, but in the actual text of the mail, if you exceed 250 characters, it truncates your mail. This is unfortunate, because I was hoping to use SQL Server to send the mails instead of another methood. Unfortunately, this bug applies to ALL mailer .dll's including the CDONTS.
It is documented on Microsoft's site if you want to see the KBASE article it's: http://support.microsoft.com/support/kb/articles/Q278/4/48.ASP"