Sql-server – Getting the information that exists in “Job properties -> Notifications” page

msdbsql serversql-server-2008-r2

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?

Best Answer

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,
   j.notify_level_eventlog, 
   j.delete_level
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.

Related Question