Post reply

Mailing DBA when sql server Agent Fails

  • September 6, 2006 at 8:56 am

    #173807

    Hello

    I would like to know a script which mails the DBA mail box when the sql server agent fails ( I am working on sql server 2000) using SMTP. I have got SMTP server configured for all my other job failure notifications.

    Please let me know any scripts that can be run on the command prompt or any ideas of how I can do it.

     

    Thanks

     

  • September 6, 2006 at 11:28 am

    #658818

    You can run a query from system table and send you an email with attachment:

    SELECT * FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

    and enabled = 1

    The smtp email gave you the capability to attach the query result set.

    http://www.sqldev.net/xp/xpsmtp.htm

    Hope that help.

    Minh Vu

     

  • September 7, 2006 at 7:39 am

    #659074

    I hate using SQL Mail on Prod DB servers - I use Wsendmail or Blat from a remote machine to accomplish most of these tasks

  • September 7, 2006 at 8:53 am

    #659122

    Hello

    I am not getting output when I run that query in the sql analyzer.

    What should I do , should I make any changes.

    Thanks

     

  • September 7, 2006 at 9:00 am

    #659126

    This will only return data if any jobs failed

     

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply