sending an email, but not now.

Question

I'm writing an application where the user will create an appointment, and instantly get an email confirming their appointment. I'd also like to send an email the day of their appointment, to remind them to actually show up.

I'm in ASP.NET (2.0) on MS SQL . The immediate email is no problem, but I'm not sure about the best way to address the reminder email. Basically, I can think of three approaches:

1. Set up a SQL job that runs every night, kicking off SQL emails to people that have appointments that day.
2. Somehow send the email with a "do not deliver before" flag, although this seems like something I might be inventing.
3. Write another application that runs at a certain time every night.

Am I missing something obvious? How can I accomplish this?

Answer 1

Your option 2 certainly seems like something you are inventing. I know that my mail system won't hold messages for future delivery if you were to send me something like that.

I don't think you're missing anything obvious. You will need something that runs the day of the appointment to send emails. Whether that might be better as a SQL job or as a separate application would be up to your application architecture.

Answer 2

I would recommend the first option, using either an SQL or other application to run automatically every day to send the e-mails. It's simple, and it works.

Answer 3

Microsoft Office has a delivery delay feature, but I think that is an Outlook thing rather than an Exchange/Mail Server thing, so you're going to have to go with option 1 or 3. Or option 4 would be to write a service. That way you won't have to worry about scheduled tasks to get the option 3 application to run.

Answer 4

Choice #1 would be the best option, create a table of emails to send, and update the table as you send each email. It's also best not to delete the entry but mark it as sent, you never know when you'll have a problem oneday and want to resend out emails, I've seen this happen many times in similar setups.

Answer 5

One caution - tightly coupling the transmission of the initial email in the web application can result in a brittle architecture (e.g. SMTP server not available) - and lost messages.

You can introduce an abstraction layer via an MSMQ for both the initial and the reminder email - and have a service sweeping the queue on a scheduled basis. The initial message can be flagged with an attribute that means "SEND NOW" - the reminder message can be flagged as "SCHEDULED" - and the sweeper simply needs to send any messages that it finds that are of the "SEND NOW" or that are "SCHEDULED" and have a toBeSentDate >= the current date. Once the message is successfully sent - the unit of work can be concluded by deleting the message from the queue.

This approach ensures messages are not lost - and enables the distribution of load to off-peak hours by adjusting the service polling interval.

As Rob Williams points out - my suggestion of MSMQ is a bit of overkill for this specific question...but it is a viable approach to keep in mind when you start looking at problems of scale - and you want (or need) to minimize/reduce database read/write activity (esepcially during peak processing periods).

Hat tip to Rob.

Answer 6

For every larger project I usually also create a service which performs regular or periodical tasks.

The service updates its status and time of last execution somewhere in the database, so that the information is available for applications.

For example, the application posts commands to a command queue, and the service processes them at the schedule time.

I find this solution easier to handle than SQL Server Tasks or Jobs, since it's only a single service that you need to install, rather than ensuring all required Jobs are set up correctly.

Also, as the service is written in C#, I have a more powerful programming language (plus libraries) at hand than T-SQL.

If it's really pure T-SQL stuff that needs to be handled, there will be a Execute_Daily stored procedure that the service is going to call on date change.

Answer 7

Create a separate batch service, as others have suggested, but use it to send ALL of the emails.

The web app should record the need to send notifications in a database table, both for the immediate notice and for the reminder notice, with both records annotated with the desired send date/time.

Using MSMQ is overkill--you already have a database and a simple application. As the complexity grows, MSMQ or something similar might help with that complexity and scalability.

The service should periodically (every few minutes to a few hours) scan the database table for notifications (emails) to send in the near future, send them, and mark them as sent if successful. You could eventually leverage this to also send text messages (SMS) or instant messages (IMs), etc.

While you are at it, you should consider using the Command design pattern, and implement this service as a reusable Command executor. I have done this recently with a web application that needs to keep real estate listing (MLS) data synchronized with a third-party provider.

Answer 8

If you are planning on having this app hosted at a cheap hosting service (like GoDaddy), then what I'd recommend is to spin off a worker thread in Global.asax at Application_Start and having it sleep, wake-up, send emails, sleep...

Because you won't be able to run something on the SQL Server machine, and you won't be able to install your own service.

I do this, and it works fine.