May 13, 2013 at 1:57 pm
Hello,
I am trying to put in place a job which will email me when a query fails to return results: This is query and if it returns 0 rows then email
SELECT * FROM ::fn_trace_geteventinfo(2)
==============
I know this is the body of the send_email exec. but how to code it together?
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = 'email recipient,
@subject = blabla,
@body_format = 'HTML',
@importance = 'High',
@body = @EmailTXT
thx
May 14, 2013 at 5:44 am
place the mail procedure inside an IF statement
IF not exists(SELECT * FROM ::fn_trace_geteventinfo(2))
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = 'email recipient',
@subject = blabla,
@body_format = 'HTML',
@importance = 'High',
@body = @EmailTXT
END
ELSE
BEGIN
PRINT 'Results found'
END
May 14, 2013 at 6:35 am
Thank you !
JR
May 15, 2013 at 8:29 am
Actually I would need to restart the trace automatically if it stops for whatever reason.
So would this be the syntax ?
IF not exists(SELECT * FROM ::fn_trace_geteventinfo(2))
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = 'email recipient',
@subject = blabla,
@body_format = 'HTML',
@importance = 'High',
@body = @EmailTXT
END
ELSE
BEGIN
EXEC sp_trace_setstatus 2, 1
GO
END
May 16, 2013 at 12:19 am
No, you should put the "EXEC sp_trace_setstatus 2, 1" command in the IF block, just before or after the mail command. Because of the "not exists" check, the first IF block will be executed when no results are found. When no results found, the trace is likely to be stopped (and must be restarted). The ELSE part will be executed when the trace_event is being traced.
But to determine if a trace is running you better make use of "::fn_trace_getinfo()" instead of "::fn_trace_geteventinfo()". The first will return info like status and file settings about all (or specified) traces. The second will check if an event is traced.
May 16, 2013 at 7:15 am
Great thx hanShi...
would something like this work as i want an email and restart to happen ?
IF not exists( SELECT * FROM ::fn_trace_getinfo(2)
where traceid= 2 and property = 5 and value = 1)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = 'xxxx',
@subject = 'The audit tracing was NOT running on server ABC and has been restarted.Please Verify',
@body_format = 'HTML',
@importance = 'High'
EXEC sp_trace_setstatus 2, 1
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply