Technical Article

Operators With Jobs and Notifications

,

No parameters are used. The code will NOT work with SQL 2000 or older.

-------------------------------------------------------
-- Sysoperators With Jobs
-- 03/29/212
-------------------------------------------------------
--This script will list the operators that have jobs 
--that are configured to notify them with alerts.
--The output indicates if the operator is enabled 
--or not and also if the job is enabled.
-- THIS SCRIPT DOES NOT WORK FOR SQL 2000
-------------------------------------------------------
SET NOCOUNT ON

SELECT o.[id]
 ,o.[name] AS Operator
 ,CASE WHEN o.[enabled] =1 THEN 'YES' ELSE 'NO' END AS OperatorEnabled
 ,o.[email_address]
 ,o.[last_email_date]
 ,o.[last_email_time] 
 ,CASE WHEN j.[enabled] =1 THEN 'YES' ELSE 'NO' END AS JobEnabled
 ,j.[name] AS JobName 
 ,j.[description] AS JobDescription
FROM 
    [msdb].[dbo].[sysoperators] o INNER JOIN
    [msdb].[dbo].[sysjobs_view] j ON o.[id] = j.[notify_email_operator_id]
WHERE 
    j.[notify_email_operator_id] != 0

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating