Post reply

Alert when job fails

  • August 4, 2008 at 9:33 pm

    #122797

    Gurus

    When we create a job in Sql Server 2005 we have a option in properties table,where we can setup a alert by email ,whenever a job fails.

    Is this email enabled by using sql mail?Do we need to have that configured?

    My requirement is that i should receive a mail wheneve my job fails

    Thanks

    Nitin

  • August 4, 2008 at 9:42 pm

    #852981

    First, you need to enable Database Mail (not SQL Mail, which requires a MAPI client be installed on the server - e.g. Outlook). Once you have configured Database Mail, you need to setup SQL Server Agent to use Database Mail (right-click on SQL Server Agent, Properties - select option for alert system).

    And finally, once all that is done - create an Operator and you can then setup the job to notify that operator when it fails. Or, you can modify your code to use sp_send_dbmail to send a mail notification.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 4, 2008 at 9:49 pm

    #852983

    Thanks a lot

  • August 7, 2008 at 12:48 am

    #854229

    SQL Mail - Requires MAPI client like outlook installed on the sql box.

    Database Mail - Does not require MAPI client like outlook installed on the sql box.

    On SQL 2005, you need to enable Database Mail or SQL Mail via 'Surface Area configurations for features'. Both of them are not enabled by default after the initial sql install.

    After you enable Database Mail via 'Surface Area configurations for features', you will now need to configure it.

    How to Configure Database Mail

    1. Assign the account [The account under which the mail would be sent] to the DatabaseMailUsers role in the msdb database.

    2. Using SSMS, choose Configure Database Mail which would be in Management --> Database Mail.

    3. Select Setup Database Mail. It is wizard based configuration and hence is easy for configuration.

    Once a new Database Mail profile is setup, you need to configure SQL Agent to use the new database mail profile.

    Configure SQL Agent to use new mail profile

    1. In SSMS --> Under SQL Server Agent create operator

    2. In SSMS --> Choose properties on SQL Server Agent

    3. In Alert System --> Enable Mail Profile

    4. Make sure Mail system = Database Mail, and Mail Profile = 'Profile created in the previous steps'

    5. Ensure fail safe operator is enabled, check the operator created above is listed and then notify using email.

    6. Restart SQL Agent service

  • August 7, 2008 at 12:52 am

    #854230

    Thanks Again

  • August 7, 2008 at 2:27 am

    #854271

    Cant i use the send mail task in SSIS?

  • August 7, 2008 at 3:13 am

    #854289

    you can use it.

Viewing 7 posts - 1 through 6 (of 6 total)

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