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

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

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