Skip Navigation LinksHome > Categories > Code from a Category

Building a Mail Queue System



User Name: codelecturer
Name: Mike Chauhan
Contact Me: www.datawebcoder.com/ContactUs.aspx
Home Page: www.datawebcoder.com
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1382
Add Date: 12/15/2013
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.
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.

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.
Calling this procedure is easy.


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

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.

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.

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Details**:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
 
    
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.