locked
To get email notifiacation from SQL server Agent? RRS feed

  • Question

  • Hi!

     

    I want to be notified when my package fails at any point during execution. I have scheduled a package using sql server agent. There is a notification tab in the Job but I haven't any idea how to configure it?

    I have created operator and try to configure using notification tab but it's not working. Can you please tell what needs to be done in order to make it works?

     

    Thanks

    Monday, May 24, 2010 7:44 PM

Answers

  • I can think of two ways to do this.

    1. You can configure mail on the server (i.e. In SSMS --> Management --> Right-Click Database Mail and configure) you should be able to send and recieve a test email from the server before notifications in a SQL Agent Job will work.

    2. You can add two final steps to the end of your SQL Agent job that is running the SSIS package. The 2nd step would be a failure step, if Step1 (SSIS) fails, the job goes to this step. In it you simply enter the recipients and message via TSQL.

     exec msdb..sp_send_dbmail

    @recipients = 'DBAs@SomeCompany.com',

    @subject

    = 'SQL Job BLAH, BLAH, BLAH has failed on Some server please investigate',

    @body

    = 'SQL Job BLAH, BLAH, BLAH has failed on Some server please investigate'

    In the 3rd step you create a Success email (if needed). When Step1 (SSIS package) is complete, the job continues onto step3. Of course you can setup email notification within the SSIS package itself but if you dont feel comfortable this is another option.

    exec

     

    msdb..sp_send_dbmail

    @recipients = 'Someone@SomeCompany.com;AnotherPerson@SomeCompany.com',

    @subject

    = 'SQL Job BLAH, BLAH, BLAH has completed on Some server',

    @body

    = 'SQL Job BLAH, BLAH, BLAH has completed on Some server'

     

     

     


     


    AndrePorter
    Monday, May 24, 2010 8:17 PM

All replies

  • If you can modify ur package use the On Error event and put a send mail task in for mail notification containing the error details like error description, error source etc.
    Sudeep's Domain
    Monday, May 24, 2010 7:47 PM
  • Thanks fro reply,

     

    I want to do this within sql server agent. So, If you have any idea about how to configure within sql server agent job, please let me know...

     

    Thanks,

     

    Monday, May 24, 2010 8:04 PM
  • I can think of two ways to do this.

    1. You can configure mail on the server (i.e. In SSMS --> Management --> Right-Click Database Mail and configure) you should be able to send and recieve a test email from the server before notifications in a SQL Agent Job will work.

    2. You can add two final steps to the end of your SQL Agent job that is running the SSIS package. The 2nd step would be a failure step, if Step1 (SSIS) fails, the job goes to this step. In it you simply enter the recipients and message via TSQL.

     exec msdb..sp_send_dbmail

    @recipients = 'DBAs@SomeCompany.com',

    @subject

    = 'SQL Job BLAH, BLAH, BLAH has failed on Some server please investigate',

    @body

    = 'SQL Job BLAH, BLAH, BLAH has failed on Some server please investigate'

    In the 3rd step you create a Success email (if needed). When Step1 (SSIS package) is complete, the job continues onto step3. Of course you can setup email notification within the SSIS package itself but if you dont feel comfortable this is another option.

    exec

     

    msdb..sp_send_dbmail

    @recipients = 'Someone@SomeCompany.com;AnotherPerson@SomeCompany.com',

    @subject

    = 'SQL Job BLAH, BLAH, BLAH has completed on Some server',

    @body

    = 'SQL Job BLAH, BLAH, BLAH has completed on Some server'

     

     

     


     


    AndrePorter
    Monday, May 24, 2010 8:17 PM