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)
	
	AS
	
	--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', 'me@mydomain.com', 'you', 'you@yourdomain.com', 
	'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 = 'me@mydomain.com'
	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)
	Select
		@FromName, @FromAddress, UserFirstName + ' ' + UserSurname, 
		UserEmail, @Subject, @Body
	FROM
		Users

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

	AS
	
	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 = "mail.mydomain.com"

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) 
		objRS.movenext
	loop

	'Clean up
	objRS.close
	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.

Conclusion

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!