Sending emails automatically using SQL Server job

Question

I'm developing a .NET desktop application with SQL Server as the database backend. One of the requirements of the application is that if a record status, for example, remains inactive for 30 days, there will be a reminder email sent to the user associated to that record.

This could be done pretty easily within the application, as long as it is started and running. However, assume that for a certain period of time, nobody starts up the application, the reminder email won't be sent, because nothing / nodody triggers the action.

How about creating a job in SQL Server which can monitors the records and sends emails as needed? Has anyone ever done that?

Thanks a lot!

Answer 1

Given the requirements of your task, I suggest that you create a console program (w/ C# or VB.NET) that checks for inactive (30 days) row condition and then generates the appropriate email notification message. Then run this program every hour or so (depending on the urgency involved in detecting an inactive row condition) using a SQL Server Agent Job.

The following image shows how the SQL Server Agent Jobs are displayed in the Object Explorer for SQL Server 2008 R2.

This SO entry covers some aspects on creating a console program that runs at certain times. The SQL Server Job Agent has several scheduling options that should facilitate your needs.

You might be reluctant to create a console program for this, but you are apt to find that doing so gives you options that are simply not easily implemented with a pure SQL Server based approach. Plus, you may have future needs that require similar processing that this approach provides.