Send Mass Emails with EASendMail Service Queue in SQL Server Stored Procedure¶

In previous section, I introduced how to send multiple emails with cursor. In this section, I will introduce how to send mass emails with EASendMail service queue.

Sections:

Introduction
Basic SQL stored procedure
[SQL Stored Procedure Example - Send Mass Emails with EASendMail Service Queue]

Introduction¶

EASendMail Service is a light and fast email delivery service which works with EASendMail SMTP Component to enable your application to send mass emails in background queue service.

Along with its ability to picking recipients from database in background and sending email in specified datetime, it eases your task in developing featured email application such as newsletter application. We strongly recommend you to use EASendMail Service with your ASP.NET/Web Application.

Important

To work with EASendMail Service, please download EASendMail and EASendMail Service at first, and then install both on your machine. If you are using web hosting service and you don’t have permission to install service on that server, EASendMail service is not suitable for you.

With EASendMail email queue feature, you do not have to code for multiple threadings. EASendMail Service can send email in background with multiple threadings automatically. You just need to adjust the maximum worker threads in EASendMail Service Manager to increase the performance. Please click here to learn more detail about EASendMail Service.

If your networking connection to your SMTP server is not very fast, EASendMail Service is absolutely solution for you. You just need to submit the email to EASendMail service queue, it is very fast because EASendMail service uses shared memory to accept email from EASendMail component, and then the service will send email in background service.

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.

Basic SQL stored procedure¶

First of all, let us create a SQL stored procedure like this:

CREATE PROCEDURE [dbo].[usp_SendMailToQueue]  @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

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

Now we can use above stored procedure like this:

[SQL Stored Procedure Example - Send Mass Emails with EASendMail Service Queue]¶

The following example codes demonstrate how to use EASendMail SMTP component to send mass emails with EASendMail Service Queue.

Note

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

DECLARE @email nvarchar(128)
DECLARE rcpt_cursor CURSOR FOR
SELECT email
FROM rcpts
OPEN rcpt_cursor
FETCH NEXT FROM rcpt_cursor
INTO @email

DECLARE @ServerAddr nvarchar(128)
Set @ServerAddr = 'smtp.emailarchitect.net'

DECLARE @From nvarchar(128)
Set @From = 'test@emailarchitect.net'

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

DECLARE @Port int
Set @Port = 25

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @email
    DECLARE @subject nvarchar(255)
    SELECT @Subject = 'test email for ' + @email
    EXEC usp_SendMailToQueue @ServerAddr,
            @from, @email, @subject, @BodyText, @User, @Password, @SSL, @Port
    FETCH NEXT FROM rcpt_cursor
    INTO @email
END
CLOSE rcpt_cursor
DEALLOCATE rcpt_cursor

Next Section

At next section I will introduce how to Send email with advanced database queue.

Tutorial Index

Send Mass Emails with EASendMail Service Queue in SQL Server Stored Procedure¶

Introduction¶

Basic SQL stored procedure¶

[SQL Stored Procedure Example - Send Mass Emails with EASendMail Service Queue]¶

Share

See Also