Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Email script Expand / Collapse
Author
Message
Posted Thursday, September 24, 2009 9:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 54, Visits: 90
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 NameStateAlarm MessageError CountError DateAlarm 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,
Post #793371
Posted Thursday, September 24, 2009 9:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 3, 2014 8:05 AM
Points: 1,191, Visits: 9,892
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'

Post #793386
Posted Thursday, September 24, 2009 9:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
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] < 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 )
BEGIN
--paste your commands here
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #793387
Posted Thursday, September 24, 2009 11:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 54, Visits: 90
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] < 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 )
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>MCP Alarms for previous 24 Hours</H1>' +
N'<table border="1">' +
N'<tr><th>Wayside Name</th><th>State</th><th>Alarm Message</th>' +
N'<th>Error Count</th><th>Error Date</th><th>Alarm State</th></tr>' +
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'</table>' ;

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

Post #793442
Posted Thursday, September 24, 2009 11:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #793448
Posted Thursday, September 24, 2009 11:21 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 54, Visits: 90
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'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_Date_Time'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_Alarm_Text'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_Name'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_State'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_Alarm_Text'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RT_MCP_Bit_State'.


I am guessing I need to change something else also.

Thanks,

Brian
Post #793453
Posted Thursday, September 24, 2009 11:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
that looks more like an error from running the script on the wrong database.

if the original SQL you pasted was valid, the rest should be valid too...

what you pasted is not the same as the original...what you pasted does not have a FROM statement...so it would fail.

the forum seems to chop some stuff out due to HTML that you want as part of your email.
the attached file passes syntax, so if the table and column objects are there, you should be good to go.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son


  Post Attachments 
Brian_email.txt (3 views, 1.77 KB)
Post #793457
Posted Thursday, September 24, 2009 11:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 54, Visits: 90
Yes, it was the correct database. I can take the original code, open a new query and past the original code into it and it completes fine.

Post #793463
Posted Thursday, September 24, 2009 12:10 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 8:19 AM
Points: 54, Visits: 90
Your right. I grabbed the wrong copy.
And here is the revised code. It completes however it still sends the email out;

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] < 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) >= 1 )
BEGIN
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>MCP Alarms for previous 24 Hours</H1>' +
N'<table border="1">' +
N'<tr><th>Wayside Name</th><th>State</th><th>Alarm Message</th>' +
N'<th>Error Count</th><th>Error Date</th><th>Alarm State</th></tr>' +
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'</table>' ;

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


Post #793479
Posted Thursday, September 24, 2009 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 12,963, Visits: 32,510
yes...because the test is HAVING COUNT(RT_MCP_Alarm_Text) >= 1, it probably sends for testing purposes...change it back to 30, and it won't until you finally have 30 or more messages and also call your routine.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #793493
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse