Some times clients ask to enable alerts for the sql agent jobs, it will be difficult when we have more jobs, because we need to goto each and every job and enable the notifications.
Here is the script for enabling for the jobs,
Steps :
1. Create Mail Profile
2. Enable the mail profile for agent (SQL Server Agent Properties –> Alert System –>select Enable Mail profile & Select profile)
3. Create Operator with Mail ID. (Ex : DBA)
4. Run below script for enabling the alerts for all the jobs.
We need to decide when we want email to be trigger (i.e after Complete, Failed, Success) below table will give the details.
| Value | Description |
| 0 | Never |
| 1 | On Success |
| 2 | On Failure |
| 3 | Always |
In the below script i assume that , Alert is sent on 2 (On Failure) and alert will be sent to DBA (Operator).
use msdb
GO
DECLARE @operator varchar(50)
SET @operator = ‘DBA’
SELECT ‘EXEC msdb.dbo.sp_update_job @job_ID = ”’ + convert(varchar(50),job_id) + ”’ ,@notify_level_email = 2, @notify_email_operator_name = ”’ + @operator + ”’ ;’ FROM sysjobs
Execute the script which is generated by above script.