Create user reminder

Published on: 8th Oct 2012

Updated on: 18th May 2021

Overview

There are lots of automation that we can do with MSSQL. One of my favorite subject is generating user reminder periodically.

The tasks can be scheduled with

• SQL Agent or
• Create a web service in C# which triggered by a Powershell script. This Powershell script will be scheduled to run in the Windows Task Scheduler.

For example, the following business process requires user reminder,

• To inform the collection department about the overdue invoices.
• To inform the staff on submitting their reimbursement form before the pay day.
• To inform the store keeper to do weekly stock check.

The process of creating the user reminder

1. To develop a stored procedure that will search through the records and create reminder if the conditions met. The conditions can be either configured by the user or predefined.

2. Create a chedule to execute this stored procedure periodically. You may want to run this stored procedure on a daily basis, weekly basis or monthy basis.

3. Upon running this stored procedure, it creates the reminder record and saved into tb_mail (please refers to this article: Design a custom mail process).

   To make the reminder looks good and have the proper header and footer, it's best to use the mail merge method.

   • You need a email subject and body template which stores in tb_sys_prop table. If the reminder is to be sending through email, the body template should be formatted in HTML.

     For how to load the email subject and body template, please refers to this article: Load the system settings using SQL stored procedure or SQL function

   • In the body template, it has many placeholders. Usually, the placeholder is wrapped with the curly bracket. For example, you want to greet the user by his user name and the body template in HTML will look like this,

     Helo <b>{user_name}<b/>, this is a HTML email reminder.
     

   • After you have retrieve the necessary data (mentioned in step 1), you will have to concatenate the data into a variable. Please refers to this article: Concatenate all records into one line text

   • To merge the data with template, it can be done by calling replace() function.

     set @body_template = replace(@body_template, '{user_name}', @user_name)
     

   • Finally, save the merged result into tb_mail table.

4. We develop a program in C# application to send the reminders.

   In case you don't want to notify the user through email, you may append this reminder record into an "alert" table. Then, display the alerts upon user login.

   For those alert requires immediate attention, you may send the reminder or alert through SMS (short messaging).

Related posts

• Design a custom mail process
• Concatenate all records into one line text

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.