3

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tab87vn
  • 389
  • 5
  • 17
  • I've used [sp_send_dbmail](https://msdn.microsoft.com/en-us/library/ms190307.aspx) for this before. Set it up in a stored procedure and then schedule to run periodically to check whether any emails need to be sent. I guess you could equally use it within a trigger if you want real-time email alerts. – 3N1GM4 Nov 25 '16 at 16:09
  • What version of SQL Server are you using? – JohnH Nov 25 '16 at 19:38
  • @JohnH I'm using the 2008 @3N1GM4 I'm not so much knowledgeable of both SqlServer and t-sql, is there any tutorial on `sp_send_dbmail`? I'll try to have a look still. – tab87vn Nov 28 '16 at 09:39

1 Answers1

3

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.

enter image description here

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.

Community
  • 1
  • 1
JohnH
  • 1,920
  • 4
  • 25
  • 32
  • Thanks. As a C# developer, I do find this approach more doable than one purely implemented on the server side (SqlServer and T-SQL are not really my things). And so, this approach means I could use classes like [MailMessage](https://msdn.microsoft.com/en-us/library/system.net.mail.mailmessage(v=vs.110).aspx) to connect to an SMTP service rather than T-SQL's `sp_send_dbmail`. What remains the same is that the service is still scheduled and triggered by the SQL Server agent. Is my understanding correct? – tab87vn Nov 28 '16 at 16:32
  • 1
    Correct, the SQL Server Agent provides a scheduling function so that you can run a console program at regular time intervals. This allows you to create a program that does one pass through the data at a given point in time. The next run will be determined from the schedule that you create in the SQL Server Agent for the program. I would expect that this program would run in a short burst of a few seconds or so. Then the program can remain idle until the scheduler starts it again for the next run. – JohnH Nov 28 '16 at 16:42
  • Due to the security requirement, I am supposed to use the first approach, that is, sending email using server side stored procedure. I've setup the database mail profile and successfully sent test email using the `sp_send_dbmail`. Do you have any advice of how to do this with the job agent? Something that can periodically scan database records and figure out outdated ones to send a reminder email. – tab87vn Nov 29 '16 at 13:50
  • By the "first approach" do you mean a SQL Server only approach without creating a console program? If so, that makes your task more challenging from my perspective. Since I have not used sp_send_dbmail and you already have, you have an advantage on me there. Just post another question on that topic as needed. I would also suggest that you nail down the scope as far as how the emails are to be formatted, what information must they convey, how often are reminder emails sent out, are more than one sent per user and how is the DB to be updated to facilitate these requirements. Good luck to you. – JohnH Nov 29 '16 at 14:11
  • Actually I haven't used `sp_send_dbmail` but creating a job that periodically sends an email is the easy part, I could easily figure it out myself. As I am no good at t-sql, the main challenge now for me is to create one or more transactions to (1) select records that matches certain condition from, say, a `user` table, (2) put the emails those records into the `sp_send_dbmail`-based transaction, and (3) update something like `send_email` status of each of these records from `false` to `true`. The content is not important, just some static text and I was able to set the time to hourly. – tab87vn Nov 29 '16 at 14:42
  • I've created a follow-up question in http://stackoverflow.com/questions/40886618/automates-sending-email-with-mssqlserver-2008 – tab87vn Nov 30 '16 at 11:00