Click here to Skip to main content
Click here to Skip to main content

MS SQL Server mail queue

, 13 May 2004 CPOL
Rate this:
Please Sign up or sign in to vote.
Mail queue using a SQL database

There are many ways to implement queuing in the Microsoft environment, from message queues to file systems, but we will discuss the option of using MS SQL Server. This article should equally apply to any relational database and on any platform.

Advantages of using a database:

  • Transactional integrity
  • Concurrency control
  • Failure recovery
  • Scalable
  • Easy to code
  • Statistics/Reporting
  • Batching

There are many ways to approach the solution, and input/criticisms are encouraged. For the purpose of this article, the solution will take on the simplest form.

Some issues will need to be addressed in a good, simple, scalable design. Let’s start with the code that needs to send an email. Besides the usual stuff (to, from, body), there may need to be priorities, retries and status. From these requirements, we can design a simple table in the database:

CREATE TABLE [dbo].[MailQueue] (
[ID] [int] IDENTITY (10000, 1),
[dtStamp] [datetime],
[DateToProcess] [datetime],
[DateProcessed] [datetime],
[FromName] [varchar] (100),
[FromAddress] [varchar] (400),
[ToAddress] [varchar] (400),
[CC] [varchar] (400),
<BCC> [varchar] (400),
[Status] [varchar] (800),
[ThreadLock] UNIQUEIDENTIFIER,
[AttemptsRemaining] [int],
[Priority] [int],
<Body> [text],
)

This simple table schema will provide the queue. Code will insert rows into the table, one per email to send. The real work will be done in a windows service that reads from the queue. Let’s go into what the ambiguous columns are for.

Status

  • The status will start as ‘UnSent’ when the row is inserted.
  • The status will change to ‘Sent’ if the email has been sent successfully.
  • The status will represent some error if the mail wasn’t sent.

AttemptsRemaining

This will decrement each time the email service tries to send the email and fails. When picking up mail from the queue, the email service will only read rows where this value is greater that zero.

Priority

By making this number larger, the email service will use this to choose email rows by there priority first.

ThreadLock

This field will be set while the email service is trying to send the email. After the service has finished with the row, the field will be set back to null.

The SQL to get rows out:

BEGIN TRANSACTION

 -- retry old mails that failed
UPDATE MailQueue
SET ThreadLock = NULL
WHERE ThreadLock IS NOT NULL
AND DateProcessed < DATEADD( minute, 15, GETDATE() )
AND Status != 'Sent'

 -- select mails to send
SELECT TOP 10 *
INTO #tmpRows
FROM MailQueue
WHERE ThreadLock IS NULL
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0
ORDER BY Priority DESC

 -- update to lock them
UPDATE MailQueue
SET ThreadLock = '<MY GUID>',
DateProcessed = GETDATE()
FROM #tmpRows r
WHERE r.ID = MailQueue.ID
AND ThreadLock IS NULL
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0
COMMIT TRANSACTION

 -- select rows
SELECT *
FROM MailQueue
WHERE ThreadLock = '<MY GUID>'

Three variables will need to be passed in; the amount of rows per batch (in this case 10), a GUID generated by the thread that will process the batch (in this case ‘<MY GUID>’), and the timeout on threads for retries of emails (in this case 15 minutes).

The Email Service

The service will run the above SQL and iterate the rows, sending out emails. The service may be multi-threaded, in which case each thread will run the SQL and process its own batch. The service may be run on many machines – still no two threads should ever process the same row at the same time.

It is the job of the email service to update the database. The following are example SQL for successes and failures:

 -- success
UPDATE MailQueue
SET ThreadLock = NULL,
DateProcessed = GETDATE(),
Status = 'Sent'
WHERE ID = 10001<BR>
  -- failure
UPDATE MailQueue
SET ThreadLock = NULL,
DateProcessed = GETDATE(),
Status = 'Cannot access CDO.Message object'
AttemptsRemaining = AttemptsRemaining - 1
WHERE ID = 10002

If the email service or thread dies for some reason, the row will be put back in the queue by the next run select statement after the timeout period.

Coming up next, a C# example.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dan Glass
Architect support.com
Australia Australia

Comments and Discussions

 
QuestionImplementation fix PinmemberMember 55581315-Feb-12 2:55 
GeneralThis article is only half-done... Pinmemberaaava30-Jun-06 18:03 
GeneralGood Work Pinmembertkrafael_net25-Apr-06 6:20 
GeneralC# Example PinmemberGermanDM26-Jan-06 23:15 
GeneralRe: C# Example Pinmemberjackieyandd24-Sep-06 15:01 
GeneralRe: C# Example PinmemberPatrickzhao21-Mar-07 18:34 
GeneralGood Concept Pinmembersides_dale13-Oct-05 17:17 
GeneralImplementation PinsitebuilderPaul Watson15-May-04 2:03 
GeneralRe: Implementation PinmemberJustinDong12-Dec-05 10:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 14 May 2004
Article Copyright 2004 by Dan Glass
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid