CODEWITHSUNDEEP
sqlsql-serveremail
pepr
pepr

Reputation: 20762

How to be alerted via e-mail when unexpected data appear in database?

I have a database table that collects some sensor values (think about temperatures). It happens sometimes that the sensor values are out of the expected range (like below zero when you measure heating in a furnace) which means that the sensor or its electronics is faulty or in a bad state. I would like to know about the situation, but I would like not to implement it via a trigger. I would like to do that more externally. The decision on how to fix the situation cannot be done/implement it by the application. The e-mail is perfect for the purpose, from my point of view. So, I tried:

CREATE PROCEDURE dbo.sp_check_and_mail_problems
AS
BEGIN
    DECLARE @q AS nvarchar(MAX) = N''
    + N'SELECT UTC, sensor_no, sensor_value'
    + N'FROM dbo.sensor_values '
    + N'WHERE sensor_value < 0 AND UTC > ''2014-09-09'' '
    + N'ORDER BY UTC ASC '

    DECLARE @message as nvarchar(MAX) = 'Negative sensor values found!';

    EXEC msdb.dbo.sp_send_dbmail @profile_name='xxx',
                                 @recipients='[email protected]',
                                 @subject='Alert: negative sensor values',
                                 @body=@message,
                                 @query=@q
END

However, the query is executed by the procedure, and the e-mail is always sent. The usual case is the one when the sensors work, and the alert should not be sent. This way I need to test first whether there is any unexpected record revealed by the SELECT, and only if there is anything to report, the e-mail should be sent.

Is there any simple way for conversion of a SELECT result into a simple text tabular form, that is to the form that the msdb.dbo.sp_send_dbmail returns?

If the sensor failed, then there is always rather long serie of negative values. How can I test effectively that there is at least one such value in the result? That is, how should I test the situation happened?

Upvotes: 0

Views: 86

Answers (1)

Tony
Tony

Reputation: 10327

Check the variable @@ROWCOUNT after a SELECT to see if any rows are returned. Only then send the email.

Of course this means you have to execute a second query to get the information you need in the email but if the error rate is low that should not be a problem.

As to formatting the output to be more "email friendly" have a look at this SO question, the highest voted but not selected answer seems to be what you need.

For the failed sensors you could modify the first SELECT statement to count the number of negative results per sensor, only returning rows where the HAVING clause returns a count greater than N (with N being set by you to indicate a failed sensor)

Upvotes: 1

Related Questions