Using the SELECT statement below one can list notications that occur for jobs scheduled on SQLServer.
SELECT –Job name
sj.name As JobName,
–Job owner
SUSER_SNAME(sj.owner_sid) As owner,
–Job category e.g. “Database Maintenance”, “Report Server”, “[Uncategorized (Local)]”
c.name as Category,
–Operator to be e-mailed (may be NULL)
o.name as EmailOperator,
–notification in Event log (notify when job fails, succeeds, always, never)
CASE sj.notify_level_eventlog
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END As EventLogNotification,
–e-mail notification (notify when job fails, succeeds, always, never)
CASE sj.notify_level_email
WHEN 0 THEN 'Never'
WHEN 1 THEN 'When the job succeeds'
WHEN 2 THEN 'When the job fails'
WHEN 3 THEN 'When the job completes (regardless of the job outcome)'
ELSE ‘UNKNOWN’ END AS EmailNotification
FROM msdb.dbo.sysjobs sj
–E-Mail Operator
LEFT OUTER JOIN msdb.dbo.sysoperators o ON sj.notify_email_operator_id = o.id
–Job categories
LEFT OUTER JOIN msdb.dbo.syscategories C ON sj.category_id = c.category_id
WHERE –ignore auto-created jobs (Reporting Services schedules) uncomment if you want ignore
— NOT (sj.name LIKE '_____________-____-____-____________') AND
–only enabled Jobs
sj.enabled = 1
ORDER BY o.name, sj.name;
Sample Output:
JobName,Owner,Category,EmailOperator,EventLogNotification,EmailNotification
Test,sa,[Uncategorized (Local)],NULL,When the job fails,Never