Post reply

Alert Based on Query Result

  • November 14, 2011 at 12:10 pm

    #246060

    I want to create a SQL Alert that triggers according to a query result.

    I have a query that's I'd like to run once an hour. This is easy enough to schedule as an SQL Job. If the query returns no rows, nothing should happen. If the query returns rows, I want to send an email or possibley a page (to an address defined as an Opertator is fine).

    What is the best way to go about this?

  • November 14, 2011 at 1:19 pm

    #1407499

    not sure about an Alert, but certainly from a scheduled job; i simply use IF EXISTS...sp_send_dbmail

    i use this a lot actually, here's a rough example:

    IF EXISTS(SELECT *

    FROM SandBox.dbo.TallyCalendar cal

    LEFT OUTER JOIN SandBox.dbo.TallyEvents evt

    ON cal.[TheDate] = evt.[TheDate]

    WHERE ( cal.TheDate BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, 8, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0))

    AND cal.[isholiday] = 1 ))

    BEGIN

    EXEC msdb.dbo.SP_SEND_DBMAIL

    @profile_name='DBMail Profile',

    @recipients='lowell@someomain.com',

    @subject = 'Holiday And Event Notifications for the Next 7 Days',

    @body = '@MailBody',--trimmed for clarity, a nice HTML message was built previously

    @body_format = 'HTML'

    END --IF EXISTS

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 14, 2011 at 1:42 pm

    #1407505

    I like Lowell's technique, but I'd add that if you are sending this off, I'd send to a group, even if it's just a group of one. Usually I find once someone wants an alert or report of some action (administrative or business action), someone else will.

  • November 14, 2011 at 1:58 pm

    #1407520

    Or someone else should if the first one is not available for any reason whatsoever.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply