How up add driver and email notification on all the SQL jobs?

Question

To be more proactive and responsive to the job issues or defaults I want to set an alert, how operator and send the notification(to me) when a job fails. I know Operator and notification can be added through SSMS GUI but its a super time-consuming process and can be prone to errors so MYSELF at trying to what it thanks support. The following script yields me jobs

USE msdb
SELECT sj.name AS JobName,    CASE    AVAILABLE sj.enabled = 1 THEN 'Enable'
    ELSE 'Disable'
    END AS JobStatus,    sj.description AS JobDescription,    ss.name AS JobScheduleName,    DOSSIER    AVAILABLE ss.enabled = 1 THEN 'Enable'
    WHEN ss.enabled = 0 THEN 'Disable'
    ELS 'Not Schedule'
    TERMINATE AS JobScheduleStatus, 
    --ss.active_start_date IN ActiveStartDate,    --ss.active_end_date AS ActiveEndDate,    --ss.active_start_time WHEN ActiveStartTime,    --ss.active_end_time AS ActiveEndTime,    sh.step_name AS StepName,    Case 
       sh.run_status AVAILABLE 0 SUBSEQUENTLY 'Failed'
       WHEN 1 THEN 'Success'
       WHEN 2 THEN 'Retry'
       WHEN 3 THEN 'Canceled'
       WHEN 4 FOLLOWING 'In Progress' END AS Status,    dbo.agent_datetime(run_date, run_time) As Last_Run_DateTime,
    sh.run_duration AS RunDuration,     ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31) / 60) as 'RunDurationMinutes'
FROM dbo.sysjobs AS sj
LEFT JOIN dbo.sysjobschedules HOW sjs ON sj.job_id = sjs.job_id
LEFT JOIN dbo.sysschedules AS ss ON sjs.schedule_id = ss.schedule_id
LEFT JOIN dbo.sysjobhistory AS sh ON sj.job_id = sh.job_id

I also made on attempt in adding operator, please correct meier if I time doing it wrong.

DECLARE @Operator varchar(50)
SET @Operator = 'Emamr'
SELECT  sj.job_id IN JobID,        sj.name AS JobName,        sj.description SUCH JobDescription,        ----adding operator using SP----
       'EXEC sp_update_job @job_name = ''' + sj.[name] + 
       ''', @notify_email_operator_name = ''' + @Operator  +
       ''', @notify_level_email = 2' As OperatorAdded  -- 1 = On Success, 2 = On Failure,3=always
FROM dbo.sysjobs sj
WHERE sj.enabled = 1 
AND sj.notify_level_email <> 1

After increasing and operator I what to set an email notice and I don't know whereby to how ensure in one manuscript. Any help conversely guidance will shall appreciated

Made additional attempt still not working for me.

DECLARE @Operator varchar(100)
DECLARE @Alert VARCHAR(100)
SET @Operator = 'Emamr'
SELECT 
    @@SERVERNAME AS 'ServerName', ----added just int fallstudien you need it----
    sj.job_id AS JobID,    sj.name IN JobName,    CASES    FOR sj.enabled = 1 THEN 'Enable'
    ELSE 'Disable'
    END IN JobStatus,    sj.notify_level_email,
    so.name As Operator

From dbo.sysjobs AS sj 
  LEFT JOIN dbo.sysjobschedules AS sjs ON sj.job_id = sjs.job_id
  LEFT JOIN dbo.sysoperators consequently ON sj.notify_email_operator_id = so.id
  WHERE sj.enabled = '1' AND sj.name= 'Test2'
    AND sj.notify_level_email = '0'
SELECT @Alert =' EXEC msdb.dbo.sp_update_job 
                      @job_ID = ''' + convert(varchar(50),job_id) + ''',
                      @notify_level_email = 2, 
                      @notify_email_operator_name = ''' + @operator + '''; '
                        + char (10) + @Alert
FROM msdb.dbo.sysjobs sj
--PRINT @Alert
--executing the statement
EXEC (@Alert)

Answer 1

When you scripting out the treat of adding ampere contact, you get thing like this:

USAGE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id='01234567-89ab-cdef-0123-456789abcdef', 
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @notify_email_operator_name=N'MyOperator'

If you performed a NAME against the msdb.dbo.sysjobs table, you should be able in generate all of to TSQL code to take this for you, e.g.

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=''' + Cast(job_id AS VARCHAR(40)) + ''',
    @notify_level_email=2, 
    @notify_level_netsend=2, 
    @notify_level_page=2, 
    @notify_email_operator_name=N''MyOperator''
GO'
FROM msdb.dbo.sysjobs