Selecting Job Info WITH Operator Name

SQL Server Administration
1

Hi experts,
I need to select job names as well as the name of the operator AND the type of notification (completion, Failure etc)

But I don't know how to add that to my query below. As written, it only returns the numerical value for the operator_id and the notify_level_email. Can this be done in one query?

Thanks!

SELECT @@SERVERNAME, J.name AS JobName, J.description as Description, J.date_created, L.name AS JobOwner,J.notify_level_email,J.notify_email_operator_id,J.enabled

FROM msdb.dbo.sysjobs J

LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid

WHERE L.name IS NOT NULL

ORDER by L.name

2 
SELECT 
    @@SERVERNAME, J.name AS JobName, J.description as Description, J.date_created, 
    L.name AS JobOwner, 
    CASE J.notify_level_email 
        WHEN 0 THEN 'Never' 
        WHEN 1 THEN 'Job succeeds' 
        WHEN 2 THEN 'Job fails'
        WHEN 3 THEN 'Job completes (regardless of succeed/fail)' 
        ELSE '?' END AS NotifyWhen,
    O.name AS NotifyEmailOperator, J.enabled
FROM msdb.dbo.sysjobs J
LEFT JOIN master.sys.syslogins L ON J.owner_sid = L.sid
LEFT JOIN msdb.dbo.sysoperators O ON O.id = J.notify_email_operator_id
WHERE L.name IS NOT NULL
ORDER by L.name, J.name
2 Likes