none
[Forum FAQ] How to send a notification when a SQL Agent job is started

    General discussion

  • Introduction

    When I schedule the job, we can set the notification to send the email to the operator when the job succeeds/fails/completes.

    However, if the job fails to execute on its schedule time due to some issue, there is no email notification will be sent out.

    How do I check whether the job is started on its schedule time or not?

    Solution

    Method 1:
    Add the sp_send_dbmail in the first step of the job. For example:
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Profilename',
        @recipients = 'abc@xzy.com',
        @query = 'query result' ,
        @subject = 'Job start Notification',
        @Body = 'Body'

    For some cases, the email fail to send out when the first step does not finished. In this cases, you may refer to method 2.

    Method 2:
    Modify the system stored procedure sp_start_job in the MSDB.
    Note: Please backup the sp_start_job before modifying it.

    1. Expand msdb > Programmability > System Stored Procedures > dbo.sp_start_job.
    2. Right-click dbo.sp_start_job and click Modify.
    3. Add the below code in bold into your stored procedure in the corresponding position.
    4. Replace the job id to the specific job so that the email notification will send out only if the specific job is started. You can find the job id in the sysjobs.

    USE [msdb]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER PROCEDURE [dbo].[sp_start_job]
      @job_name    sysname          = NULL,
      @job_id      UNIQUEIDENTIFIER = NULL,
      @error_flag  INT              = 1,    -- Set to 0 to suppress the error from sp_sqlagent_notify if SQLServerAgent is not running
      @server_name sysname          = NULL, -- The specific target server to start the [multi-server] job on
      @step_name   sysname          = NULL, -- The name of the job step to start execution with [for use with a local job only]
      @output_flag INT              = 1     -- Set to 0 to suppress the success message
    AS

    If @job_id='jobid'
    BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Profilename',
        @recipients = 'abc@xzy.com',
        @query = 'query result' ,
          @subject = 'Job start Notification ',
           @Body = 'Body' ,
        @attach_query_result_as_file = 0 ;

    End
    ELSE
    BEGIN
      DECLARE @job_id_as_char VARCHAR(36)
      DECLARE @retval         INT
      DECLARE @step_id        INT
      DECLARE @job_owner_sid  VARBINARY(85)

      SET NOCOUNT ON

    More Information

    sp_send_dbmail
    http://msdn.microsoft.com/en-us/library/ms190307.aspx
    sp_start_job
    http://msdn.microsoft.com/en-us/library/ms186757.aspx

    Applies to

    SQL Server 2012

        

    Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.

    Monday, August 4, 2014 9:17 AM