Click here to Skip to main content
13,591,259 members
Click here to Skip to main content
Add your own
alternative version


32 bookmarked
Posted 13 May 2004
Licenced CPOL

MS SQL Server mail queue

, 13 May 2004
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),
[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.


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


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.


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


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:


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

 -- select mails to send
INTO #tmpRows
FROM MailQueue
AND DateToProcess > GETDATE()
AND AttemptsRemaining > 0

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

 -- select rows
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.


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


About the Author

Dan Glass
Australia Australia

You may also be interested in...


Comments and Discussions

QuestionImplementation fix Pin
Member 55581315-Feb-12 2:55
memberMember 55581315-Feb-12 2:55 
GeneralThis article is only half-done... Pin
aaava30-Jun-06 18:03
memberaaava30-Jun-06 18:03 
GeneralGood Work Pin
tkrafael_net25-Apr-06 6:20
membertkrafael_net25-Apr-06 6:20 
GeneralC# Example Pin
GermanDM26-Jan-06 23:15
memberGermanDM26-Jan-06 23:15 
GeneralRe: C# Example Pin
jackieyandd24-Sep-06 15:01
memberjackieyandd24-Sep-06 15:01 
GeneralRe: C# Example Pin
Patrickzhao21-Mar-07 18:34
memberPatrickzhao21-Mar-07 18:34 
GeneralGood Concept Pin
sides_dale13-Oct-05 17:17
membersides_dale13-Oct-05 17:17 
GeneralImplementation Pin
Paul Watson15-May-04 2:03
sitebuilderPaul Watson15-May-04 2:03 
GeneralRe: Implementation Pin
JustinDong12-Dec-05 10:06
memberJustinDong12-Dec-05 10:06 

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

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

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.180618.1 | Last Updated 14 May 2004
Article Copyright 2004 by Dan Glass
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid