SQLServerCentral is supported by Red Gate Software Ltd.

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,

Post #793371

HowardW

Posted Thursday, September 24, 2009 9:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 1,035, Visits: 7,671

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

Lowell

Posted Thursday, September 24, 2009 9:38 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

brian.cook

Posted Thursday, September 24, 2009 11:03 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 05, 2012 8:26 AM
Points: 49, Visits: 81

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

Lowell

Posted Thursday, September 24, 2009 11:13 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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

brian.cook

Posted Thursday, September 24, 2009 11:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 05, 2012 8:26 AM
Points: 49, Visits: 81

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

Lowell

Posted Thursday, September 24, 2009 11:29 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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 (2 views, 1.77 KB)

Post #793457

brian.cook

Posted Thursday, September 24, 2009 11:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 05, 2012 8:26 AM
Points: 49, Visits: 81

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

brian.cook

Posted Thursday, September 24, 2009 12:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, March 05, 2012 8:26 AM
Points: 49, Visits: 81

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

Lowell

Posted Thursday, September 24, 2009 12:22 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 11,627, Visits: 27,692

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions