Post reply

SQL job notification

  • November 10, 2006 at 1:54 am

    #62670

    Hi,  I have amended a number of SQL backup jobs to send an email to a SQL alerts email in the event of failure.  Previously these jobs where e mailing whenever they completed.  I did this by updating the SYSJOBS table, setting notify email level to 2.  The jobs are still emailing on completion and reporting success.  Any thoughts?

  • November 10, 2006 at 2:21 am

    #671023

    James,

    I wonder why you are sending an e-mail if a job runs OK? I'd rather send one when it fails.

    Scan you server(s) on fialed jobs and send an e-mail using SMTP_SEND_EMAIL ( http://sqldev.net/&nbsp

    example;

    ----------MAIL section ------------------------------------------------------------

    declare @rc int

    declare @sent_email_to varchar(80)

    declare @sent_email_cc varchar(80)

    declare @sent_email_subject varchar(140)

    declare @sent_email_message varchar(4000)

    declare @attachments_details varchar(1000)

    set @sent_email_to   = '<someone_at_microsoft.com>,<someonelse_at_microsoft.com>'

    set @sent_email_cc   = '<someone_at_microsoft.com>'

    set @sent_email_subject  = '<Here the e-mail title>'

    set @sent_email_message  = 'The "Server Summary" XLS file (vs2) has been copied to its destination. '+char(13)+char(10)+char(13)+char(10)+'Regards,'+char(13)+char(10)+'The DBA team  ' +char(13)+char(10)+char(13)+char(10)+   ' For more information see document XYZ chapter 4.1'

    exec @rc = master.dbo.xp_smtp_sendmail

      @FROM     = N'<senders e-mail adres>'

      @TO        = @sent_email_to,

      @cc        = @sent_email_cc,

      @subject  = @sent_email_subject,

      @message = @sent_email_message,

      @server    = N'<you e-mail server>'

    select RC = @rc

    ----------------------------------------------------------------------------------------

    Hope this pushes you forwards,

    Regards,

    GKramer

    The Netherlands

  • November 10, 2006 at 2:32 am

    #671027

    you should not make this change by updating the system table, that's why it's not working! Use the provided stored procedure sp_update_job.

    There is one downside of mail errors on failure only - if the mail server goes down - you'll never know if there's an error. It's best to have some sort of success notification so you know the mail is working. I've found some horrendous issues in my time with assuming things are working due to no error emails!! In fact I uncovered a non working email alert account not so long ago - hadn't been working for 6 months so no-one was aware backups were failing every two days!!!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 10, 2006 at 8:32 am

    #671110

    I generally use "failure" to send emails. To get around the problem of email not working, and thus not getting the failure, I send myself a generic email every day so I know that email is working.

  • November 10, 2006 at 9:04 am

    #671119

    Thanks for the advice, all sorted now.

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

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