EASendMail SMTP Component > Developer Center > Send Email in SQL Server Stored Procedure - Tutorial

Send Email in SQL Server Stored Procedure - Tutorial

Date: July 15, 2013

Introduction

EASendMail is a SMTP component which supports all operations of SMTP/ESMTP protocols (RFC 821, RFC 822, RFC 2554). This tutorial introduces how to send email in SQL Server Stored Procedure using SMTP. It also demonstrates SSL, Cursor and Email Queue usage.

Installation

Before you can use the following sample codes, you should download the EASendMail Installer and install it on your machine at first.

How does it work

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.
Prepare SQL Server environment

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.

A simple SQL stored procedure

To better demonstrate how to use send email using EASendMail, 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 

If @SSLConnection > 0 
BEGIN
    EXEC @hr = sp_OAMethod @oSmtp, 'SSL_init', NULL
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.

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.

When you execute above example code, if you get error about "Networking connection" or "No such host", it is likely that your SMTP server address is not correct. If you get an error like "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.

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> or 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

Reply-To, Return-Path and Mail Priority

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.

If you want to set Higher or Lower priority to your email, you can use Priority prority

[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
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.

Next: Send email over SSL in SQL stored procedure ->

Comments

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

Download

Send Email - C# - VB6 - Visual Basic - VC++ - C++/CLI - Delphi

Retrieve Email and Parse Email - C# - VB6 - Visual Basic - VC++ - C++/CLI - Delphi

Email Solution - Email Server - DomainKeys and DKIM - Disclaimer

2003 - 2014 © Copyright AdminSystem Software Limited. All rights reserved.   About us     Follow emailarchitect on Twitter   Follow emailarchitect on facebook