Easy script to notify failed jobs [SQL Server]

The script provided below is a easy way to add notification for all failed jobs.

Requirements:
1- DatabaseMail Enabled
2- Enable DatabaseMail on the SQL Server Agent ; properties ; Alert System

USE [msdb]
GO
--create a N'DBA_Operator'
EXEC msdb.dbo.sp_add_operator @name=N'DBA_Operator',          
@enabled=1,
@email_address=N'DBAs@yourdomain.com'
GO
--add notifications for failure to all jobs
DECLARE @QuotedIdentifier char(1); SET @QuotedIdentifier = '' -- use '''' for single quote
DECLARE @ListDelimeter char(1); SET @ListDelimeter = ';'
DECLARE @CSVlist varchar(max) --use varchar(8000) for SQL Server 2000

--no event log, email on failure
SELECT     @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier +
'
EXEC msdb.dbo.sp_update_job @job_id=N'''
+ convert(varchar(max),[job_id]) +
''',
          @notify_level_eventlog=0,
          @notify_level_email=2,
          @notify_email_operator_name=N''DBA_Operator'''
+ @QuotedIdentifier
from msdb.dbo.sysjobs

3 thoughts on “Easy script to notify failed jobs [SQL Server]

  1. I’m working on some scenario based for moving SQL agent jobs from one server to another. Lets say there are 1000 jobs.
    Example: Job names:- SANFRAN_5000P,REDMOND_5000p,TEXAS_5000p,SEATTLE_5000P
    We have an option from GUI but looking for a PowerShell script or T-SQL to filter particular jobs that matches (5000P) and generate a script into a file and then execute it in another server. It would be great if you could help me on this task.

    Liked by 1 person

  2. Even I use the same option from object explorer details.
    How about if we can automate this task using T-SQL or PowerShell so that it will reduce the time and effort.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s