Post reply

Email alert based on table values

  • January 29, 2015 at 6:47 am

    #314836

    I have a SQL table with several rows and 5 columns with values from 1 to 100 in col 5 and the table

    gets updated every 5 minutes.

    How do I send an email if one of the values falls below a certain number in col 5.

    Lets say if value is less than 40 Sql integration services should send out an email alert.

  • January 29, 2015 at 6:57 am

    #1773978

    well, first you need to make sure you've set up database mail, but a it's just a simple conditional logic.

    here's a template that i use a lot:

    IF EXISTS (SELECT * FROM SOMETABLE WHERE SSIS < 40)

    BEGIN

    DECLARE @HTMLBody VARCHAR(MAX),

    @TableHead VARCHAR(MAX),

    @TableTail VARCHAR(MAX);

    SET @TableTail = '</table></body></html>';

    SET @TableHead = '<html><head>'

    + '<style>'

    + 'td {white-space:nowrap;font-family: "Verdana","Helvetica","MS Sans Serif",verdana; font-size: x-small; vertical-align: top;border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;} '

    + '</style>'

    + '</head>'

    + '<body>'

    +'<h4> SSIS Value Too Low!! ;<br /><br /></h4>'

    + '<table cellpadding=0 cellspacing=0 border=1>'

    + '<tr bgcolor=#FFEFD8>'

    + '<td><b>This is a simple Alert notifying stakeholders that an specific Problem Exists.</b></td>'

    + '</tr>';

    SET @HTMLBody = '';

    SELECT @HTMLBody = @TableHead + @HTMLBody + @TableTail;

    --only send Monday thru Friday

    IF DATENAME(dw,GETDATE()) NOT IN ('Saturday','Sunday')

    BEGIN

    -- return output

    --Select @HTMLBody

    --now the email itself:

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='My Database Mail Profile',

    @recipients='Lowell The Code Monkey<lowell@somedomain.com>;',

    @subject = 'SSIS Value Too Low',

    @body = @HTMLBody,

    @body_format = 'HTML';

    END; --IF

    END --IF

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

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