Send Mass Emails with Advanced Database Queue in SQL Server Stored Procedure

In previous section, I introduced how to send email EASendMail Service Queue. In this section, I will introduce how to send mass emails with advanced database queue in SQL stored procedure.

Introduction

Although EASendMail service provides a faster way to send email in background, but there are thousands of emails in a task, the SendMailToQueue will be invoked for thousands times, obviously it is not effect way.

Therefore, EASendMail service provides a more effective way to send mass emails. In short, you just need to submit your database connection and record set once, EASendMail service will pick up the record set in background and send email to each record one by one.

send email using database queue in sql server

Create Database Table

To better understand the database queue, we need to create three tables in your database like this:

CREATE TABLE [dbo].[rcpts](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](50) NULL,
    [email] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_rcpts] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[errorlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [email] [nvarchar](128) NULL,
    [server] [nvarchar](50) NULL,
    [errorcode] [nvarchar](50) NULL,
    [errordescription] [nvarchar](255) NULL,
 CONSTRAINT [PK_errorlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[sentlog](
    [uid] [bigint] IDENTITY(1,1) NOT NULL,
    [server] [nvarchar](50) NULL,
    [email] [nvarchar](128) NULL,
 CONSTRAINT [PK_sentlog] PRIMARY KEY CLUSTERED
(
    [uid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Then insert two records in table ‘rcpts’ like this:

SQL table sample

Note

Remarks: All of samples in this section are based on first section: Send email in a simple SQL stored procedure. To run the following example codes successfully, please click here to learn how to create the test enivronment and use EASendMail in your project.

[SQL Stored Procedure Example - Send mass emails with advanced database queue]

The following example codes demonstrate how to use EASendMail SMTP component to send email with advanced database queue.

Note

To get the full sample projects, please refer to Samples section.

/* change it to your server address */
DECLARE @ServerAddr nvarchar(128)
SET @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
SET @From = 'Tester <test@adminsystem.com>'

/* change it to your sql server address, database, user and password */
/* The server/instance name syntax used in the server option is the same for all SQL Server connection strings.*/
/* e.g.: Server=serveraddress\instancename;*/
/* SQL 2005 */
DECLARE @DataConn nvarchar(255)
SET @DataConn = 'Driver={SQL Native Client};Server=serveraddress;Database=database;Uid=user;Pwd=password;'

/* MS SQL Server 2005 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* using SQL 2008 Driver*/
/*SET @DataConn = 'Driver={SQL Server Native Client 10.0};Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* MS SQL Server 2008 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI10;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* using SQL 2005/2012 Driver*/
/*SET @DataConn = 'Driver={SQL Server Native Client 11.0};Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* MS SQL Server 2012 Native Provider */
/*SET @DataConn = 'Provider=SQLNCLI11;Server=serveraddress;Database=database;Uid=user;Pwd=password;'*/
/* select all records from this table */
DECLARE @SQLSelect nvarchar(255)
SET @SQLSelect = 'SELECT uid, name, email from rcpts'

/* insert all successful record to this table */
DECLARE @SQLSuccess nvarchar(255)
SET @SQLSuccess = 'INSERT INTO sentlog ( server, email )'
    + ' VALUES( ''{$var_server}'', ''{$var_rcptaddr}'' )'

/* insert all failure record to this table */
DECLARE @SQLError nvarchar(255)
SET @SQLError = 'INSERT INTO errorlog( email, server, errorcode, errordescription )' +
    ' VALUES( ''{$var_rcptaddr}'', ''{$var_server}'', ''{$var_errcode}'',  ''{$var_errdesc}'' )'

DECLARE @Subject nvarchar(256)
DECLARE @Bodytext nvarchar(max)
SET @Bodytext = ''

/*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

DECLARE @Port int
Set @Port = 25

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_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Data-Connection', @DataConn
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-Select', @SQLSelect
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-OnSentSuccess', @SQLSuccess
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Sql-OnSentError', @SQLError
EXEC @hr = sp_OAMethod @oSmtp, 'AddHeader', NULL, 'X-Rcpt-To', '{$var_srecord:email}'
EXEC @hr = sp_OASetProperty @oSmtp, 'DisplayTo', '"{$var_srecord:name}" <{$var_srecord:email}>'

EXEC @hr = sp_OASetProperty @oSmtp, 'ServerAddr', @ServerAddr
EXEC @hr = sp_OASetProperty @oSmtp, 'ServerPort', @Port
EXEC @hr = sp_OASetProperty @oSmtp, 'UserName', @User
EXEC @hr = sp_OASetProperty @oSmtp, 'Password', @Password
EXEC @hr = sp_OASetProperty @oSmtp, 'FromAddr', @From
SET @Subject = 'test email with data pick'
EXEC @hr = sp_OASetProperty @oSmtp, 'Subject', @Subject
SET @BodyText = 'Test email, do not reply, your id in database is {$var_srecord:uid}.'
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 @SSL > 0
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 1
    END
ELSE
    BEGIN
        EXEC @hr = sp_OASetProperty @oSmtp, 'ConnectType', 4
    END

EXEC @hr = sp_OAMethod @oSmtp, 'SendMailToQueue', @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
    PRINT 'Please make sure you have EASendMail Service installed!'
    EXEC @hr = sp_OAMethod @oSmtp, 'GetLastErrDescription', @description OUT
    PRINT @description
END
ELSE
BEGIN
    PRINT 'Message has been submitted to EASendMail Service!'
END
EXEC @hr = sp_OADestroy @oSmtp
Go

With above SQL statement, no matter how many records in your table, SendMailToQueue is only invoked once and EASendMail service will pick up records in background and send the email based on each record to recipient one by one. It also inserts the results back to “errorlog” and “sentlog” tables automatically. You can open EASendMail Service Manager to monitor the queue and view Journal -> System Error to check if there is error with your database connection and SQL statement.

Database Server Driver

In X-Data-Connection header, you should specify a database driver to connect database. You can open “Control Panel” -> “Administrative Tools” - > “ODBC Data Sources” - “Drivers” to check current installed database drivers.

odbc drivers

Common SQL Driver Download

If SQL Server is installed on a remote server, and you don’t have SQL driver installed on local machine, then you need to download and install corresponding driver on local machine.

Next Section

Total sample projects in EASendMail SMTP Component installation package.

Appendix

Comments

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