Email alert based on table values

Question

Post reply

Email alert based on table values

mtz676

SSCrazy Eights

Points: 9261
More actions
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.

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

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

Lowell SSC Guru Points: 323524 More actions · Answer 1

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!

Email alert based on table values

Cookies on SQLServerCentral