Building a Mail Queue System

By Damian Maclennen on 24 September 2001 | Tags: SQLMail

This article demonstrates an email queue system using SQL Server and VB Script. This overcomes the limitations of the Sending SMTP Mail using a Stored Procedure method.

In a previous article I demonstrated a way to send email from a stored procedure using a COM object. This article prompted a lot of comments and discussion. From this discussion a few things became evident.

  • This method was not terribly efficient. Calling COM objects like this is not particulaly fast.
  • The 255 character restiction. Unfortunately in SQL Server, when calling a COM object using sp_OACreate, we are limited to 255 characters when passing in parameters to properties.
  • No way to send many emails without using a cursor. This procedure had to be called for every email. This means that if you needed to send 10 emails, you would need to call it 10 times. To send an email to everyone in a table, you would need a cursor. A cursor, plus the speed of the sp_OACreate call would not make for a very efficient procedure.

In short, although this method is fine for say, sending alert emails to an administrator, for a large scale emailing solution it does not really work.

Another way

A different approach to this problem is to put all emails into a "Queue" table. Then use a scheduled job (running every minute or so) to poll this table and send any mail in the queue.

For the purpose of this article, I am going to demonstrate a simple solution for this, then outline some ways you could make it more robust.

The Queue Table

Firstly, create a "Mail Queue" table.

Create Table MailQueue(
	MailDate DateTime Default(GetDate()),
	FromName VarChar(200) NOT NULL,
	FromAddress VarChar(200) NOT NULL,
	ToName VarChar(200) NOT NULL,
	ToAddress VarChar(200) NOT NULL,
	Subject VarChar(200) NOT NULL,
	Body VarChar(6000) NOT NULL

This is fairly straightforward. We have a timestamp column, the details of the sender and recipient of the email, the subject and the body.

Getting Data In

The next step is getting data into the table. For this we can use two methods, firstly is a single email method, second is inserting a batch of emails.

For a single email, this procedure will work.

Create Procedure SendMail
	@FromName VarChar(200),
	@FromAddress VarChar(200),
	@ToName VarChar(200),
	@ToAddress VarChar(200),
	@Subject VarChar(200),
	@Body VarChar(6000)
	--Inserts mail into queue table	
	INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body)
	Values (@FromName, @FromAddress, @ToName, @ToAddress, @Subject, @Body)

Calling this procedure is easy.

	Exec SendMail 'Me', '', 'you', '', 
	'Hi', 'The body of the message'

Another way to get mail into the queue would be to do an insert using a select. Here is an example :

	Declare @FromName VarChar(200),
			@FromAddress VarChar(200),
			@Subject VarChar(200),
			@Body VarChar(200)
	Select @FromName = 'Me', 
	Select @FromAddress = ''
	Select @Subject = 'Hi there'
	Select @Body = 'The body of the message'
	--Insert mail into queue table	
	INSERT Into MailQueue (FromName, FromAddress, ToName, ToAddress, Subject, Body)
		@FromName, @FromAddress, UserFirstName + ' ' + UserSurname, 
		UserEmail, @Subject, @Body

As you can see, this will insert a row in the Queue table for every user in the fictional Users table.

Sending mail

O.K. we have rows in our queue table, now what ?

What we want to do is write a program that will get all the rows in our queue table and send an email for each one, we would typically want to run continuously. In this case, I am going to write some VBScript and run it as a SQL Server scheduled job.

I am going to write a stored procedure to get the rows out of the table. Once I have retrieved them, I want to delete them from the queue. To avoid any rows being inserted between my select and my delete, I am going to use a temp table.

Create Procedure GetMailQueue

	Set Nocount on
	--Select from queue into a temp table. Then delete from Queue.
	--This gets around any issues with mail being inserted between select and delete
	Create Table #tempmail(
		MailDate DateTime Default(GetDate()),
		FromName VarChar(200) NOT NULL,
		FromAddress VarChar(200) NOT NULL,
		ToName VarChar(200) NOT NULL,
		ToAddress VarChar(200) NOT NULL,
		Subject VarChar(200) NOT NULL,
		Body VarChar(6000) NOT NULL
	Insert Into #tempmail
	Select * From MailQueue

	Delete From MailQueue
	From MailQueue M
	INNER JOIN #tempmail t ON t.MailDate = M.MailDate
	Select * From #tempmail
	drop table #tempmail
	Set Nocount Off

This procedure will return a set containing all the mails from the queue. Here is the VBScript code to retrieve the set and send it. Once again, I have used the ASPMail component from ServerObjects, but you could modify it to use CDONTS or JMail or whatever other component you use.

For a complete VBScript reference go to The Microsoft Scripting site.

'ADO Constants
Const adCmdStoredProc = &H0004

'System config
Const ConnString = "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=testing; 
User ID=test; Password=test"
Const MailServer = ""

Call Main

Sub Main()

	dim sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody
	dim objCmd, objRs
	'ADO Command Object
	Set objCmd = CreateObject("ADODB.Command")
	objCmd.ActiveConnection = ConnString
	objCmd.CommandType = adCmdStoredProc
	objCmd.CommandText = "GetMailQueue" 'Our Stored procedure
	Set objRS = objCmd.execute 'Gets an ADO recordset of all the emails
	do until objRs.EOF 'Loop through the emails

		sFromName = objRs("FromName")
		sFromAddress = objRs("FromAddress")
		sToName = objRs("ToName")
		sToAddress = objRs("ToAddress")
		sSubject = objRs("Subject")
		sBody = objRs("Body")
		'Call our mail subroutine
		Call SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody) 

	'Clean up
	Set objRS = nothing
	Set objCmd = nothing	

End Sub

Sub SendMail(sFromName, sFromAddress, sToName, sToAddress, sSubject, sBody)
	dim objMail, errCode

	'Create the mail object
	Set objMail = CreateObject("SMTPsvg.Mailer")
	'Set all the properties for this email
	objMail.RemoteHost = MailServer
	objMail.FromName = sFromName
	objMail.FromAddress = sFromAddress
	objMail.AddRecipient sToName, sToAddress
	objMail.Subject = sSubject
	objMail.BodyText = sBody
	'Send it
	errCode = objMail.SendMail
	'Clean up
	Set objMail = nothing
End Sub

Running It

The next step is to set this script up as a job. You can do this in Enterprise Manager, schedule a script task to fire every minute.


There you have it, a simple mail queue system. As I said before there are ways of making it more robust but I wanted to make the example simple to understand. Some ways of making it better:

  • Write an NT service in VB or C++ rather than using script.
  • Rather than getting all the rows and deleting them, open up a recordset in ADO and try to send each one. If the mail doesn't succeed, mark the message as failed. Perhaps have a number of attempts until marking it as failed.
  • Attachments. Add a field in the table with the path to a file to attach.

I hope this example has got you thinking about other features you could incorporate. The main point of this aricle as well as the last one on mail is to remind you that there is always another way to do things, and with a little bit of lateral thinking you can come up with a solution to fit your needs.

The source code to these examples is here. Have fun with it.

Happy coding!

Related Articles

Sending SQL notifications with CDOSYS (4 August 2004)

INF: Common SQL Mail Problems (22 October 2002)

Send E-Mail Without Using SQL Mail in SQL Server (SMTP) (11 April 2002)

INF: How to Configure SQL Mail (5 September 2001)

Sending SMTP Mail using a Stored Procedure (19 July 2001)

SQLMail in Depth (15 September 2000)

How to set up SQLMail (11 September 2000)

Other Recent Forum Posts

Need Help in SQL query optimization (2d)

Info needed on field "user_scans" of table "sys.dm_db_index_usage_stats" (3d)

Suspect Parameter Sniffing? (3d)

See values of a proc when it is called from another proc? (4d)

Varchar() datatype and C# SqlDataReader truncates string (4d)

Find the extra rows (5d)

How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table (5d)

EPR updates issues (6d)

- Advertisement -