email alert on job failure

Hi all,
Is there a way to send an email alert when a sql server job fails?
Thanks in advance.Hi
If you are using SQL 2000 check the notifications tab on the job properties.
John
"helpful sql" wrote:
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>
>|||Hi
Add two additional steps. One is for success (OK) and another is for failure
(NO)
Job's step is going to the (OK) on success action and to (NO) on failure
action
"helpful sql" <nospam@.stopspam.com> wrote in message
news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Is there a way to send an email alert when a sql server job fails?
> Thanks in advance.
>|||This example job may help!
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Error
Handling') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Error Handling'
-- Delete the job with the same name (if it exists)
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Error Handling Example')
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Error Handling Example'' since there
is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Error Handling Example'
SELECT @.JobID = NULL
END
BEGIN
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = N'Error Handling Example', @.owner_login_name = N'sa',
@.description = N'Error Handling Example.', @.category_name = N'Error
Handling', @.enabled = 0, @.notify_level_email = 0, @.notify_level_page = 0,
@.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =1, @.step_name = N'SQL DUMP', @.command = N'BACKUP DATABASE [MyDatabase] TO
DISK = N''C:\Backup\MyDatabase.bak'' WITH INIT , NOUNLOAD , NAME =N''Database Backup'', NOSKIP , STATS = 10, NOFORMAT', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 2,
@.on_success_action = 4, @.on_fail_step_id = 3, @.on_fail_action = 4
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =2, @.step_name = N'Inform Success', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Success - Error Handler Job'',
@.message = N''Error Handler Test Succeeded'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Completed Successfully'' , 16,1)', @.database_name =N'MyDatabase', @.server = N'', @.database_user_name = N'', @.subsystem =N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts = 0,
@.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =3, @.step_name = N'Inform Failure', @.command = N'EXEC
master.dbo.xp_smtp_sendmail
@.FROM = N''MyAccount@.SQLServer'',
@.TO = N''MyAccount@.Home'',
@.subject = N''Failure - Error Handler Job'',
@.message = N''Error Handler Test Failed'',
@.type = N''text/plain'',
@.server = N''MyRelay''
RAISERROR( ''Process Failed see log ''' for details'' , 16,1)',
@.database_name = N'MyDatabase', @.server = N'', @.database_user_name = N'',
@.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 6, @.retry_attempts
= 0, @.retry_interval = 1, @.output_file_name = N'', @.on_success_step_id = 0,
@.on_success_action = 2, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Error Handling Example', @.enabled = 1, @.freq_type = 4, @.active_start_date
= 19900101, @.active_start_time = 214500, @.freq_interval = 1,
@.freq_subday_type = 1, @.freq_subday_interval = 0, @.freq_relative_interval =0, @.freq_recurrence_factor = 0, @.active_end_date = 99991231, @.active_end_time
= 235959
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
"Uri Dimant" wrote:
> Hi
> Add two additional steps. One is for success (OK) and another is for failure
> (NO)
> Job's step is going to the (OK) on success action and to (NO) on failure
> action
>
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:%237ApnAMXGHA.1220@.TK2MSFTNGP02.phx.gbl...
> > Hi all,
> > Is there a way to send an email alert when a sql server job fails?
> >
> > Thanks in advance.
> >
>
>