email alert if job failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

email alert if job failed

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2010-07-22 : 16:48:39

Hi Group:

How can I set an email alert in sql if any of my job(s) failed. Let me know, Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-22 : 17:13:08

Go to the job and add the notifications. You'll need to setup Database Mail as well as an operator.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

aravindt77
Posting Yak Master

120 Posts

Posted - 2010-07-23 : 02:44:08

First you should cofiguer Database mail from the option under the Management from Management Studio..after wards in the job specify in the notifications ..or else you can provide as a step in the job itself
DECLARE @Msg NVARCHAR(MAX)
SET @Msg = 'The Differential/ Backup Job FAILED ON ' + CAST(GETDATE() AS NVARCHAR(100))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile Name Created in the Database Mail,
@recipients = mail id u created under the profile,
@copy_recipients = '',
@body = @Msg,
@subject = 'Messaeg u like, Automated Failure Message' ;

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2010-07-23 : 16:02:36

Thank you All for your help....It is working Fantastic...I am enclosing the solution for anyone that needs to set this up for a client.

If @SQLType = 'S'
BEGIN

-- Variable Declarations
DECLARE @PreviousDate datetime
DECLARE @Year VARCHAR(4)
DECLARE @Month VARCHAR(2)
DECLARE @MonthPre VARCHAR(2)
DECLARE @Day VARCHAR(2)
DECLARE @DayPre VARCHAR(2)
DECLARE @FinalDate INT

-- Initialize Variables
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 3 days
SET @Year = DATEPART(yyyy, @PreviousDate)
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)
SET @FinalDate = CAST(@Year + @Month + @Day AS INT)

-- Final Logic
SELECT j.[name],
s.step_name,
h.step_id,
h.run_date,
h.run_time,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources