Send Email in SQL Server Stored Procedure - Tutorial

This tutorial introduces how to send email in SQL Server Stored Procedure using SMTP. It also demonstrates SSL, Cursor and Email Queue usage.

Installation

EASendMail is a SMTP component which supports all operations of SMTP/ESMTP protocols (RFC 821, RFC 822, RFC 2554). Before you can use the following sample codes, you should download the EASendMail Installer and install it on your machine at first.

Prepare SQL Environment

MS SQL Server 2000 or later version provides several system Stored Procedures for invoking ActiveX COM object.

sp_OACreate
Creates an instance of the COM object on an instance of MS SQL Server

sp_OADestroy
Destroys a created COM object

sp_OAGetProperty
Gets a property value of a COM object.

sp_OASetProperty
Sets a property value of a COM object to a new value.

sp_OAMethod
Calls a method of a COM object.
sp_OAGetErrorInfo
Gets the exception information from OLE automation.

To use EASendMail SMTP ActiveX Object in MS SQL server, the first step is “Enable Ole Automation”. If you use SQL 2000, you can simply ignore this step. For SQL 2005, 2008 or later version, please open your SQL Server Management Studio and connect your server by Database Administrator at first, then open a new SQL Query window and input the following SQL statement:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Enable OLE in SQL

After you ran above statement, your “Ole Automation” environment is enabled, now you can use ActiveX Object in your SQL server.

Send email in a simple SQL stored procedure

To better demonstrate how to use send email using SMTP protocol, let’s create a simple stored procedure at first. First of all, select your database, then right click -> New Query to open a new SQL query window.

SQL query window

[SQL Stored Procedure Example - Send email]

Now add the following SQL statement to the query window and run it:

CREATE PROCEDURE [dbo].[usp_SendTextEmail]  @ServerAddr nvarchar(128),
@From nvarchar(128),
@To nvarchar(1024),
@Subject nvarchar(256),
@Bodytext nvarchar(max) = 'This is a test text email from MS SQL server, do not reply.',
@User nvarchar(128) = '',
@Password nvarchar(128) = '',
@SSLConnection int = 0,
@ServerPort int = 25
AS
DECLARE @hr int
DECLARE @oSmtp int
DECLARE @result int
DECLARE @description nvarchar(255)
EXEC @hr = sp_OACreate 'EASendMailObj.Mail',@oSmtp OUT
If @hr <> 0
BEGIN
    PRINT 'Please make sure you have EASendMail Component installed!'
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End
EXEC @hr = sp_OASetProperty @oSmtp, 'LicenseCode', 'TryIt'
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', @ServerPort
EXEC @hr = sp_OASetProperty @oSmtp, 'UserName', @User
EXEC @hr = sp_OASetProperty @oSmtp, 'Password', @Password
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @From
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL,  @To, 0
EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @oSmtp, 'BodyText', @BodyText

/* Most mordern SMTP servers require SSL/TLS connection now*/
/* 1 means use SSL/TLS based on server port, 4 means if server supports SSL/TLS connection, SSL/TLS is used automatically */
If @SSLConnection > 0
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1
    END
ELSE
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4
    END

/* you can also add an attachment like this */
/*EXEC @hr = sp_OAMethod @oSmtp, 'AddAttachment', @result OUT, 'd:\test.jpg'*/
/*If @result <> 0 */
/*BEGIN*/
/*   EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT*/
/*    PRINT 'failed to add attachment with the following error:'*/
/*    PRINT @description*/
/*END*/
PRINT 'Start to send email ...'
EXEC @hr = sp_OAMethod @oSmtp, 'SendMail', @result OUT
If @hr <> 0
BEGIN
    EXEC @hr = sp_OAGetErrorInfo @oSmtp, NULL, @description OUT
    IF @hr = 0
    BEGIN
        PRINT @description
    END
    RETURN
End
If @result <> 0
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT
    PRINT 'failed to send email with the following error:'
    PRINT @description
END
ELSE
BEGIN
    PRINT 'Email was sent successfully!'
END
EXEC @hr = sp_OADestroy @oSmtp
Go

After you ran above SQL statement, you can find a new stored procedure named “usp_SendTextEmail” under your database->Programmability->Stored Procedures. Now we can use this stored procedure to send a simple test email like this:

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'
DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'
DECLARE @To nvarchar(1024)
/*You can input multiple recipients and use comma (,) to separate multiple addresses */
Set @To = 'support@emailarchitect.net'
DECLARE @Subject nvarchar(256)
Set @Subject = 'simple email from SQL server'
DECLARE @Bodytext nvarchar(512)
Set @BodyText = 'This is a test text email from MS SQL server, do not reply.'
/*User and password for ESMTP authentication, if your server doesn't require
User authentication, please set @User and @Password to '' */
DECLARE @User nvarchar(128)
Set @User = 'test@emailarchitect.net'
DECLARE @Password nvarchar(128)
Set @Password = 'testpassword'
/* If your smtp server requires SSL connection, please set @SSL = 1*/
DECLARE @SSL int
Set @SSL = 0
exec usp_SendTextEmail @ServerAddr, @From, @To, @Subject, @BodyText, @User, @Password, @SSL

If you set everything right, you can get “email was sent successfully”. If you get “failed to send email with the following error:”, then please have a look at the following section.

Common SMTP Transport Error

When you execute above example code, if it returned an error about “Networking connection/Socket” or “No such host”, it is likely that your SMTP server address is not correct. If it threw an exception about “5xx Relay denied”, it is likely that you did not set user authentication. Another common error is “5xx Must issue a STARTTLS command first” or “No supported authentication marshal found!”, that is because your SMTP server requires user authentication under SSL connection. You can set the SSL connection to solve this problem. You can learn more detail in Troubleshooting section.

TLS 1.2

TLS is the successor of SSL, more and more SMTP servers require TLS 1.2 encryption now.

If your operating system is Windows XP/Vista/Windows 7/Windows 2003/2008/2008 R2/2012/2012 R2, and you got connection error with SSL/TLS connection, you need to enable TLS 1.2 protocol in your operating system like this:

Enable TLS 1.2 on Windows XP/Vista/7/10/Windows 2008/2008 R2/2012

Where can I get my SMTP email server address, user and password?

Because each email account provider has different server address, so you should query your SMTP server address from your email account provider. To prevent spreading email from the server, most SMTP servers also require user authentication. User name is your email address or your email address without domain part, it depends on your email provider setting.

Finally, if you have already set your account in your email client such as Outlook or Window Mail, you can query your SMTP server address, user in your email client. For example, you can choose menu -> “Tools” - > - “Accounts” - > “Your email account” - > “Properties” - > “Servers” in Outlook express or Windows Mail to get your SMTP server, user. Using EASendMail to send email does not require you have email client installed on your machine or MAPI, however you can query your exist email accounts in your email client.

VB 6.0 console email sample

Email Address Syntax and Multiple Recipients

Mail Address Syntax in EASendMail SMTP Component:

// For single email address (From, ReplyTo, ReturnPath), the syntax can be:
// ["][display name]["]<email address>.
// For example:
"Tester, T" <test@adminsystem.com>
Tester <test@adminsystem.com>
<test@adminsystem.com>
test@adminsystem.com

// For mulitple email address (To, CC, Bcc), the syntax can be:
// [single email],[single email]...
// (,;\r\n) can be used to separate multiple email addresses.
// For example:
"Tester, T" <test1@adminsystem.com>, Tester2 <test2@adminsystem.com>,
   <test3@adminsystem.com>, test4@adminsystem.com

[SQL Stored Procedure Example - Email syntax]

To better understand the email address syntax, please refer to the following codes.

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'Tester<test@adminsystem.com>'
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'test@adminsystem.com'
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', '<test@adminsystem.com>'
/* Using AddRecipientEx to add To, Cc and Bcc in SQL
 Multiple addresses are separated with (,)
 The syntax is like this: "test@adminsystem.com, test1@adminsystem.com"*/
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'test1@adminsystem.com, test2@adminsystem.com', 0
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL,
                        'Test1<test@adminsystem.com>, Test2<test2@adminsystem.com>', 0
/* You can also add carbon copy (CC) or blind carbon copy (BCC) in the email.*/
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'CC recipient<cc@adminsystem.com>', 1
EXEC @hr = sp_OAMethod @oSmtp, 'AddRecipientEx', NULL, 'Bcc recipient<bcc@adminsystem.com>', 2

From, ReplyTo, Sender and Return-Path and Priority

From, Reply-To, Sender and Return-Path are common email headers in email message. You should always set From property at first, it is a MUST to identify the email sender. The following table lists the header and corresponding properties:

Header Property
From Mail.FromAddr
Reply-To Mail.ReplyTo
Sender Mail.Sender
Return-Path Mail.ReturnPath
  • From

    This property indicates the original email sender. This is what you see as the “FROM” in most mail clients.

  • Reply-To

    This property indicates the reply address. Basically, when the user clicks “reply” in mail client, the Reply-To value should be used as the recpient address of the replied email. If you don’t set this property, the Reply address is same as From address.

  • Sender

    This property indicates the who submit/send the email. When the user received the email, the email client displays: From: “sender address” on behalf of “from address”. If you don’t set this property, the Sender address is same as From address. Sender property is common used by mail listing provider. This property also takes effect to DKIM/DomainKeys signature, if Sender is different with From address, then you should sign DKIM/DomainKeys based on Sender domain instead of From address domain.

  • Return-Path

    This property indicates the delivery notification report address. If you don’t set this property, the Return-Path address is same as From address. This property also takes effect to SPF record, if Return-Path is different with From address, then remote SMTP server checkes SPF record of Return-Path instead of From address.

Mail Priority

  • If you want to set Higher or Lower priority to your email, you can use Priority prority
  • If you want to set another email address to get the replied email rather than your From address, you can use ReplyTo property.
  • If you want to set another email address to get the delivery report rather than your From address, you can use ReturnPath property.

[SQL Stored Procedure Example - ReplyTo, ReturnPath and Priority]

EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', 'Tester <test@emailarchitect.net>'
/* Set the Reply-To address*/
EXEC @hr = sp_OASetProperty @oSmtp, 'ReplyTo', 'replyto@@emailarchitect.net'
/* Set the email address to receive delivery report */
EXEC @hr = sp_OASetProperty @oSmtp, 'ReturnPath', 'report@emailarchitect.net'
/* Set high priority */
EXEC @hr = sp_OASetProperty @oSmtp, 'Priority', 1

HTML Email

If you want to send email in HTML format, you can enable HTML format like this:

EXEC @hr = sp_OASetProperty @oSmtp, 'BodyFormat', 1

Troubleshooting

When you send email in above simple SQL stored procedure, if it returned an error, please have a look at the following tips:

“No Such Host” Error

This error means DNS server cannot resolve SMTP server, you should check if you input correct server address. If your server address is correct, you should check if your DNS server setting is correct.

Common “Socket/Networking Connection” Error

This error means there is a problem with networking connection to SMTP server. You can use Windows built-in Telnet command to detect the networking connection.

Using Telnet to detect networking connection to SMTP server

Note

Notice: in Windows 2008/Windows 8 or later version, Telnet Client is not installed by default, you should enable this command in Control Panel -> Programs and Features -> Turn Windows feature on or off -> have Telnet Client checked.

Under DOS command prompt, input “telnet [serveraddress] [port]”:

telnet mail.emailarchitect.net 25
press enter.

If the networking connection to your SMTP server is good, it should return a message like 220 .... If it returns Could not open connection to ..., that means the networking connection to SMTP server is bad, or outbound 25 port is blocked by anti-virus software, firewall or ISP. Please have a look at the following screenshot:

detect SMTP connection using telnet

SMTP 25, 587, 465 port

25 port is the default SMTP server port to receive email. However, some ISP block outbound 25 port to prevent user to send email directly to other SMTP server. Therefore, many email providers also provide an alternative port 587 to receive email from such users. 465 port is the common port used to receive email over implicit SSL connection. If you use telnet to test 465 port, it doesn’t return the “220…”, because it requires SSL hand shake. But if the connection is ok, telnet returns a flash cursor.

“5xx … IP address block or on black list or bad reputation” Exception

This error means SMTP server blocks your IP address or email content. You can try to set user/password in your codes to do user authentication and try it again. If email client set user authentication, most SMTP servers do not check client source IP address in black list.

“5xx user authenticaton” Error

TThis error means user authentication is failed, you should check whether you input correct user/password. Password is always case-sensitive.

“5xx relay denied” Error

For anti-spam policy, most SMTP servers do not accept the email to outbound domain without user authentication. You should set user/password in the codes and try it again.

“5xx Must issue a STARTTLS command first”

This error means SMTP server requires SSL/TLS connection. You should enable SSL/TLS connection like this:

/* If your smtp server requires TLS connection, please add this line*/
EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1

“No supported authentication marshal found!”

This error means SMTP server doesn’t support user authentication or it requires user authentication over SSL/TLS connection. You can try to remove user/password in your codes and try it again.

Other error returned by SMTP server

If SMTP server returns an error, it usually returns description about this error. Some descriptions also include a HTTP link, you can go to this linked web page to learn more detail. You can also use the following codes to generate a log file to learn all SMTP session between client and server.

[SQL Server - Using log file to detect SMTP server response - Example]

EXEC @hr = sp_OASetProperty @oSmtp, 'LogFileName', 'd:\smtp.txt'

32bit/x64 ActiveX DLL

Seperate builds of run-time dll for 32 and x64 platform

File Platform
Installation Path\Lib\native\x86\EASendMailObj.dll 32 bit
Installation Path\Lib\native\x64\EASendMailObj.dll 64 bit

Distribution

  • Standard EXE

    For VB6, C++, Delphi or other standard exe application, you can distribute EASendMailObj.dll with your application to target machine without COM-registration and installer. To learn more detail, please have a look at Registration-free COM with Manifest File.

  • Script

    For ASP, VBScript, VBA, MS SQL Stored Procedure, you need to install EASendMail on target machine by EASendMail installer, both 32bit/x64 DLL are installed and registered.

Next Section

In this section, I introduced the basic things of sending email in SQL stored procedure with EASendMail. At next section I will introduce how to send email over SSL connection.

Appendix

Comments

If you have any comments or questions about above example codes, please click here to add your comments.