0

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='me@something.cz',
                                 @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?

pepr
  • 20,112
  • 15
  • 76
  • 139
  • Please, leave a comment when downvoting. Or do not spend your energy on downvoting. – pepr Oct 21 '14 at 11:10

1 Answers1

1

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)

Community
  • 1
  • 1
Tony
  • 9,672
  • 3
  • 47
  • 75
  • Thanks for answering of the part of the question. – pepr Sep 09 '14 at 20:52
  • Thanks again. Finally, I use one `SELECT` for extracting the `@count` (for using it also in the mail subject), and then I do compose another `SELECT` passed as a string to the `sp_send_dbmail` as the `@query` argument (with more details). – pepr Sep 10 '14 at 10:48
  • Yes, that's what I would do. You can optimise the query to test for bad data, returning a minimal amount of information so it executes quickly. If you are going to return a count of rows you will have to check if the result is greater than zero, instead of checking @@ROWCOUNT. – Tony Sep 10 '14 at 10:57
  • Yes, that is what I did :) I have also limited the quantity of the records included in the mail using `TOP(100)`. The @count is part of the subject and of the message. And the message body is for a human with brain ;) – pepr Sep 10 '14 at 18:38