Email script

Question

Post reply

Email script

brian.cook

Default port

Points: 1484
More actions
September 24, 2009 at 9:23 am

#138341

Hi All,
I have an SQL Agent script that runs at Midnight GMT every day. The script will send an email with a information on failures of a given number of errors per line item. Some days it does not have any alarms.
How do I modify the script so that it will not send the email if the number of errors are below the threshold?
Here is the script;
---------------------------------------------
USE NMS_RT
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'
MCP Alarms for previous 24 Hours
' +
N'
' +
N'
' +
N'
' +
CAST ( ( SELECT td = RT_MCP_Name, '',
td = RT_MCP_State, '',
td = RT_MCP_Alarm_Text, '',
td = COUNT(RT_MCP_Alarm_Text), '',
td = CONVERT(char(10), RT_MCP_Date_Time, 111), '',
td = CASE RT_MCP_Bit_State WHEN 1 THEN 'True' ELSE 'False' END, ''
FROM RT_MCP_Historic_Alarms
WHERE [RT_MCP_Date_Time] >= Convert(char(10), DateADD(DAY, -1, GETDATE()), 101) AND [RT_MCP_Date_Time] < Convert(char(10), GETDATE(), 101) and RT_MCP_Bit_State > 0
GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State, CONVERT(char(10), RT_MCP_Date_Time, 111)
HAVING COUNT(RT_MCP_Alarm_Text) >= 30
ORDER BY CONVERT(char(10), RT_MCP_Date_Time, 111), COUNT(RT_MCP_Alarm_Text) DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
Wayside Name State Alarm Message Error Count Error Date Alarm State
' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='usergroup@fabricam.com',
@subject = 'Alarms for Previous 24 Hours',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'test';
-----------------------------------
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Viewing 11 posts - 1 through 10 (of 10 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Just wrap the sql to send the email in a IF...BEGIN END

e.g:

if (Some SQL Statement that returns a count of errors) >= 1

begin

print 'send email'

end

else

print 'dont send email'

Lowell SSC Guru Points: 323504 More actions · Answer 2

i know it's kind of repetitive, but htis is how i've done it...use an exists to test for the condition, then the query to build the email, same as you've done:

IF EXISTS(SELECT 1

FROM RT_MCP_Historic_Alarms

WHERE [RT_MCP_Date_Time] >= Convert(char(10), DateADD(DAY, -1, GETDATE()), 101)

AND [RT_MCP_Date_Time] 0

GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State, CONVERT(char(10), RT_MCP_Date_Time, 111)

HAVING COUNT(RT_MCP_Alarm_Text) >= 30 )

BEGIN

--paste your commands here

END

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!

brian.cook Default port Points: 1484 More actions · Answer 3

Lowell are you saying it should look like this then;

-------------------------------------------------

USE NMS_RT

IF EXISTS(SELECT 1

FROM RT_MCP_Historic_Alarms

WHERE [RT_MCP_Date_Time] >= Convert(char(10), DateADD(DAY, -1, GETDATE()), 101) AND [RT_MCP_Date_Time] 0

GROUP BY RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State, CONVERT(char(10), RT_MCP_Date_Time, 111)

HAVING COUNT(RT_MCP_Alarm_Text) >= 30 )

BEGIN

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'

`MCP Alarms for previous 24 Hours`

' +

N'

' +
N'

' +

N'

' +

CAST ( ( SELECT td = RT_MCP_Name, '',

td = RT_MCP_State, '',

td = RT_MCP_Alarm_Text, '',

td = COUNT(RT_MCP_Alarm_Text), '',

td = CONVERT(char(10), RT_MCP_Date_Time, 111), '',

td = CASE RT_MCP_Bit_State WHEN 1 THEN 'True' ELSE 'False' END, ''

ORDER BY CONVERT(char(10), RT_MCP_Date_Time, 111), COUNT(RT_MCP_Alarm_Text) DESC, RT_MCP_Name, RT_MCP_State, RT_MCP_Alarm_Text, RT_MCP_Bit_State

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'

Wayside Name	State	Alarm Message	Error Count	Error Date	Alarm State

' ;

EXEC msdb.dbo.sp_send_dbmail

@recipients='usergroup@fabricam.com',

@subject = 'MCP Alarms for Previous 24 Hours',

@body = @tableHTML,

@body_format = 'HTML',

@profile_name = 'test';

END

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Lowell SSC Guru Points: 323504 More actions · Answer 4

brian yes, that is exactly how i would do it;

to test it, simply run the sql inside the EXISTS() portion....it probably returns nothing, so change the HAVING to1 instead of 30...then you could see that that would work....

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!

brian.cook Default port Points: 1484 More actions · Answer 5

When I execute the query, it returns this;

Msg 207, Level 16, State 1, Line 14

Invalid column name 'RT_MCP_Name'.

Msg 207, Level 16, State 1, Line 15

Invalid column name 'RT_MCP_State'.

Msg 207, Level 16, State 1, Line 16

Invalid column name 'RT_MCP_Alarm_Text'.

Msg 207, Level 16, State 1, Line 17

Invalid column name 'RT_MCP_Alarm_Text'.

Msg 207, Level 16, State 1, Line 18

Invalid column name 'RT_MCP_Date_Time'.

Msg 207, Level 16, State 1, Line 19

Invalid column name 'RT_MCP_Bit_State'.