Job Notification Email

• Graham.Okely-876987

  Say Hey Kid

  Points: 694

  More actions

  January 26, 2009 at 9:03 pm

  #127591

  I would like to know that every job has an onfail email sent to the SQL DBA group.

  Something like:

  select @@Servername,Job_name,email_address

  from (system job tables)

  Any scripts or clues out there?

  select @@servername,getdate(),id,name,email_address

  from msdb.dbo.sysoperators

  where enabled=1 and email_address like 'sql.dba%'

  The above works but how does this table link to msdb.dbo.sysnotifications and what other tables are required?

  Thanks

• Graham.Okely-876987

  Say Hey Kid

  Points: 694

  More actions

  January 26, 2009 at 10:51 pm

  #932994

  This did the task:

  select ' ',@@servername,b.name,a.name,

  email_address,

  case a.[enabled]

  when '1' then 'Enabled'

  when '0' then 'Disabled *****'

  end, ' '

  from msdb.dbo.sysoperators a inner join msdb..sysjobs b

  on a.[id] = b.[notify_email_operator_id]

  where email_address not like 'sql.dba%'

  Formatted for some HTML style output.