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


i have database table collects sensor values (think temperatures). happens sensor values out of expected range (like below 0 when measure heating in furnace) means sensor or electronics faulty or in bad state. know situation, not implement via trigger. more externally. decision on how fix situation cannot done/implement application. e-mail perfect purpose, point of view. so, tried:

create procedure dbo.sp_check_and_mail_problems begin     declare @q nvarchar(max) = n''     + n'select utc, sensor_no, sensor_value'     + n'from dbo.sensor_values '     + n'where sensor_value < 0 , utc > ''2014-09-09'' '     + n'order utc asc '      declare @message 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, query executed procedure, , e-mail sent. usual case 1 when sensors work, , alert should not sent. way need test first whether there unexpected record revealed select, , if there report, e-mail should sent.

is there simple way conversion of select result simple text tabular form, form msdb.dbo.sp_send_dbmail returns?

if sensor failed, there rather long serie of negative values. how can test there @ least 1 such value in result? is, how should test situation happened?

check variable @@rowcount after select see if rows returned. send email.

of course means have execute second query information need in email if error rate low should not problem.

as formatting output more "email friendly" have @ this question, highest voted not selected answer seems need.

for failed sensors modify first select statement count number of negative results per sensor, returning rows having clause returns count greater n (with n being set indicate failed sensor)


Comments

Post a Comment

Popular posts from this blog

javascript - RequestAnimationFrame not working when exiting fullscreen switching space on Safari -

request animation frame stops working when exiting fullscreen clicking on original space's safari window. fine if fullscreen mode canceled escape key or calling cancelfullscreen(). steps reproduce: open https://dl.dropboxusercontent.com/u/769042/prezi/safari-fullscreen.html click "draw raf", kittie appears click "fullscreen", go fullscreen click "draw raf", kittie appears go space original safari was, showing "click exit fullscreen mode", click anywhere, out fullscreen click "draw raf", nothing happens what handling click calling: window.requestanimationframe(draw); which draws on canvas context: function draw() { ctx.drawimage(img, math.random()*500|0, math.random()*400|0, 100, 100); } i checked .hidden , .visibilitystate, updated correctly. tested on osx 10.9.3, safari 7.0.4 (9537.76.4). has workaround/solution other switching old settimeout? this sounds webkit bug 88940 : using reque...
Read more

jsf - How to ajax update an item in the footer of a PrimeFaces dataTable? -

this visual of table have: +---------+------------+-------------------+ | header1 | header2 | header3 | +---------+------------+-------------------+ | row | input | calc'ed output | | row b | input b | calc'ed output b | | etc.. | etc.. | etc.. | +---------+------------+-------------------+ | total: total calc'ed output | +------------------------------------------+ and stripped-down code: <p:datatable id="mytable" value="#{mybean.somelist}" var="currentitem"> <p:column headertext="header1"> <h:outputtext value="#{currentitem.name}" /> </p:column> <p:column headertext="header2"> <pe:inputnumber value="#{currentitem.inputval}"> <p:ajax event="change" listener="#{mybean.changelistener}" update="outputval outputtotal...
Read more

django - CSRF verification failed. Request aborted. CSRF cookie not set -

error: reason given failure: csrf cookie not set. in general, can occur when there genuine cross site request forgery, or when django's csrf mechanism has not been used correctly. post forms, need ensure: browser accepting cookies. view function uses requestcontext template, instead of context. in template, there {% csrf_token %} template tag inside each post form targets internal url. if not using csrfviewmiddleware, must use csrf_protect on views use csrf_token template tag, accept post data. you're seeing section of page because have debug = true in django settings file. change false, , initial error message displayed. can customize page using csrf_failure_view setting. did find, nothing helped, must foolish(( my template <form method="post">{% csrf_token %} {% field in form %} <div class="control-group"> {{ field }} </div> {% endfor %} <input type=...
Read more