January 29, 2015 at 6:47 am
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
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy