May 8, 2009
The benefits of using SQL Server 2008 to send mail
While writing our SwiftStore we had a requirement to send emails from a number of points in the application. While this is easy to do it does present a number of technical issues:
- Error handling on sending mail - you normally don’t want your app to fail because it can’t send a mail
- Changing the mail content - you will want to change the mail from time to time but you would not want to redeploy your app. Likewise it can be messy to hold content in your database.
- Tracking of mails sent - you could use an application logging system, but its more code
- Application performance - sending mail from the application is another step in your process and so will slow performance
We were using SQL Server 2008 so we decide to investigate getting it to send our mails. We found this article really helpful in setting it up. Once we had it up and running we then started to build on it. We scheduled a number of jobs to run at different intervals (from minutes to hours). In these jobs we execute stored procs which query on created date minus our interval. Using a cursor we then send emails to the various receiptients we require. Here is an example proc:
Once we started to use it we found more and more uses. Not only do we use it for transaction application emailing, we are now using it to handle our application trial expiry and all follow up correspondence.
Add a comment