I use SQL Server 2008 R2 and I need get some properties from the msdb database.

I need a query to get information that exists in the Job properties -> Notifications page.

In which table I can find this information?


The dbo.sysjobs and dbo.sysoperators views provide you the details. Some simple code (cleaned up from coding errors in first post.):

SELECT j.name AS JobName,
   j.notify_level_email, e.name AS EmailOperator,
   j.notify_level_netsend, n.name AS NetSendOperator,  
   j.notify_level_page, p.name AS PagerOperator,
FROM dbo.sysjobs j 
  LEFT JOIN dbo.sysoperators e 
     on j.notify_email_operator_id = e.id
  LEFT JOIN dbo.sysoperators n 
     on j.notify_netsend_operator_id = n.id
  LEFT JOIN dbo.sysoperators p 
     on j.notify_page_operator_id = p.id

I did not flesh out the notify levels: 0 = none, 1 = succeeds, 2 = fails, 3 = completes. The delete_level is 0 if the job is not to automatically delete under any circumstance.


Because of where the information is stored in msdb, part of the information is obtained when the Job Properties window is initially opened, then more information is queried when you actually click on the Notifications page. SSMS uses an INSERT/EXEC on the sp_help_ stored procedures to obtain the operator information, which is pretty unwieldy... so unfortunately, using Profiler in this case to trap the queries isn't particularly helpful.

Here's a completed query that returns all the information from the page in one shot:

USE [msdb];

WITH jobStates AS
    SELECT 0 AS Level,  'Disabled' AS Description UNION ALL
    SELECT 1,           'On Success' UNION ALL
    SELECT 2,           'On Failure' UNION ALL
    SELECT 3,           'On Completion'
    es.Description AS EmailOnJobState, e.name AS EmailOperatorName, e.email_address AS EmailOperatorEmailAddress,
    ps.Description AS PageOnJobState, p.name AS PageOperatorName, p.pager_address AS PageOperatorPagerAddress,
    nss.Description AS NetSendOnJobState, ns.name AS NetSendOperatorName, ns.netsend_address AS NetSendOperatorNetSendAddress,
    els.Description AS EventLogOnJobState,
    ds.Description AS DeleteJobOnJobState
    FROM [dbo].[sysjobs] j
    INNER JOIN jobStates es ON es.Level = j.notify_level_email
    INNER JOIN jobStates ps ON ps.Level = j.notify_level_page
    INNER JOIN jobStates nss ON nss.Level = j.notify_level_netsend
    INNER JOIN jobStates els ON els.Level = j.notify_level_eventlog
    INNER JOIN jobStates ds ON ds.Level = j.delete_level
    LEFT OUTER JOIN [dbo].[sysoperators] e ON (j.notify_level_email > 0) AND (e.id = j.notify_email_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] p ON (j.notify_level_page > 0) AND (p.id = j.notify_page_operator_id)
    LEFT OUTER JOIN [dbo].[sysoperators] ns ON (j.notify_level_netsend > 0) AND (ns.id = j.notify_netsend_operator_id);

I am not really sure what you are looking for but, have you tried sp_help_job or sp_helpjobstep?

USE msdb;

EXEC dbo.sp_help_jobstep
    @job_name = N'JobName_Here',
    @step_id = 1;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.