Take the 2-minute tour ×

How do I setup a scheduled job on SQL Server database that checks for an alert condition say change in the table column variance and executes a cmd that sends an email alert when data in the variance column is in the 10-15 range

The columns in the database tables will be dynamic...the user must be able to select and set alert on any of the database table columns dynamically and check for an alert condition


share|improve this question
Why did you create a second account? –  Martijn Pieters Oct 9 '12 at 12:34

1 Answer 1

Variance is the square of the standard deviation, so you could:

if (select stddev(col1)*stddev(col1) from YourTable) > 0.15
    exec sp_send_dbmail ... 

You can find instructions on how to configure DB mail here.

share|improve this answer
That's the general idea, but you also need 1) to schedule this check periodically (SQL Agent) and 2) some mechanism to prevent a flood of mails once the variance exceed 15% (and the condition will met every time is checked). –  Remus Rusanu Sep 12 '12 at 8:55
@RemusRusanu: Agreed. I prefer to keep sending mails until the alert condition has gone away. Code that only mails when an error condition changes tends to be complex and error prone. Exactly what you'd want to avoid in alerting code. –  Andomar Sep 12 '12 at 9:08
Day of cal_date,Difference "September 10, 2012",1.975019397% "August 27, 2012",3.809065154% "August 20, 2012",0.045856430% - The last column has the variance we need to trigger an alert when the variance is 15% Hope this helps to understand the problem better –  Gallop Sep 12 '12 at 9:29
I have a simular email sending alert thing. I have a table where users can set the condition and amount of emails they want to recieve. They can add 10 (or any number) times the email tricker should fire before sending another email again. If the condition is back to normal again, it resets everything. –  Romo Oct 9 '12 at 12:47

Your Answer


