Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email script


Email script

Author
Message
brian.cook
brian.cook
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
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,
HowardW
HowardW
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1449 Visits: 9892
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
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18081 Visits: 39419
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

--
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
brian.cook
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
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'

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 NameStateAlarm MessageError CountError DateAlarm 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


Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18081 Visits: 39419
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
brian.cook
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
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
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18081 Visits: 39419
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

--
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!

Attachments
Brian_email.txt (10 views, 1.00 KB)
brian.cook
brian.cook
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
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.
brian.cook
brian.cook
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
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'

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 = 'MCP Alarms for Previous 24 Hours',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'test';
END


Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18081 Visits: 39419
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

--
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!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search