SQL Server Performance Forum – Threads Archive
EscalationScenerio: I want to send email to top managers, if one customer order is pending for a certain period of time, lets say 40 minutes. How can I accomplish that?
Check this article on database mail in SQL 2005
you need to have a job running which checks for the date created field for all the records where customer order is pending and if it croses 40 days then send a mail.
How can I get more help on "job", this is something new for me.
Adding more info to my question, I like the email alert to be sent whenever an order crosses a threshold value. For example, the order is pending for 40 minutes, nothing will happen unless it cross that threshold i.e. 40 in this case. There are many threshold value, e.g. if it crosses 40 minutes, it should go to Front managers, if it crosses 60 minutes, alert should go to middle managers, if it crosses 80 minutes, it will reach the top management. My confusion is that, the order is lying unattended in the table. Alert will be generated as soon as the order date is > 40 minutes since it was placed. Is it helpful?
Check the following steps Step1>Write a script to query the order table for all pending orders.
Based on the pending time you need to send mail to concerned management.
Explanation for Sending a mail from script can be found from the link i had posted before. Step2> Create a JOB which will fire the above script and then scedule that JOB.
About creating and editing JOBS, please refer to Books Online which gets installed with SQL server.
Else you need to download it from Microsoft.