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