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.
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 |
|
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_idWHERE h.run_status = 0 -- Failure AND h.run_date > @FinalDate ORDER BY h.instance_id DESCEnd |
 |
|
|
|
|
|
|