locked
How can the Subject field within emails sent by the SQL Server 2005 Agent be customized RRS feed

  • Question

  • I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

     

    All my jobs send email based on jobs completing.

     

    My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

     

    I get this:

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

     

    I really would like to get this:

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

    -OR-

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

     

    Thanks

    Frank

    Friday, August 3, 2007 11:33 PM

All replies

  • You can declare a variable and dynamically assign the value "success or failure", then call the variable in mail procedure as below

    Eg:


    Code Snippet

    Declare @test varchar(20)
    select @test=count(*) from master..sysdatabases -- you can provide your command here

    exec msdb.dbo.sp_send_mail @profile_name='Profile Name',
    @recipients='email@email.com',
    @subject=@test


    Saturday, August 4, 2007 3:10 AM
  • Hi Vidhya,

     

    I generated the following code from one of my subtasks of my maintenance plan (see below).

     

    How can I use your solution with it?

     

    It looks like the actual mail is sent from within the execution of the job.

     

    Thanks,

    Frank

     

    USE [msdb]
    GO
    /****** Object:  Job [ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup]    Script Date: 09/13/2007 17:59:07 ******/
    BEGIN TRANSACTION
    DECLARE @ReturnCode INT
    SELECT @ReturnCode = 0
    /****** Object:  JobCategory [Database Maintenance]    Script Date: 09/13/2007 17:59:07 ******/
    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
    BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)
    EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
      @enabled=1,
      @notify_level_eventlog=2,
      @notify_level_email=3,
      @notify_level_netsend=0,
      @notify_level_page=0,
      @delete_level=0,
      @description=N'No description available.',
      @category_name=N'Database Maintenance',
      @owner_login_name=N'sa',
      @notify_email_operator_name=N'sqlsrv_servicesadmin_oper', @job_id = @jobId OUTPUT
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    /****** Object:  Step [Database_and_Transaction_Log_Backup]    Script Date: 09/13/2007 17:59:08 ******/
    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Database_and_Transaction_Log_Backup',
      @step_id=1,
      @cmdexec_success_code=0,
      @on_success_action=1,
      @on_success_step_id=0,
      @on_fail_action=2,
      @on_fail_step_id=0,
      @retry_attempts=0,
      @retry_interval=0,
      @os_run_priority=0, @subsystem=N'SSIS',
      @command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\ACCTV9P_Instance_Maintenance_Plan" /set "\Package\Database_and_Transaction_Log_Backup.Disable;false"',
      @flags=0
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup',
      @enabled=1,
      @freq_type=4,
      @freq_interval=1,
      @freq_subday_type=1,
      @freq_subday_interval=0,
      @freq_relative_interval=0,
      @freq_recurrence_factor=0,
      @active_start_date=20070728,
      @active_end_date=99991231,
      @active_start_time=210000,
      @active_end_time=235959
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
    COMMIT TRANSACTION
    GOTO EndSave
    QuitWithRollback:
        IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
    EndSave:

    Thursday, September 13, 2007 10:06 PM
  • Hi,

    In the code above you are using Email operator (@notify_email_operator_name=N'sqlsrv_servicesadmin_oper') to send mail. There are three options available to send mail, you can use any one condition that suites you.

    a.) When the job fails
    b.) when the job succeeds
    c.) when the job completes

    Goto Job properties, then click on Notifications from there you can get these there options. If this doesnt work, reply me i'll write a code and give it to you.


    We can also use the mail procedure as i said earlier by checking sysjobhistory and then to drop a mail.
    Friday, September 14, 2007 1:11 AM
  • I selected "When Job Completes" when defining the subtask. By selecting this, the subject only includes a "completed on" phrase in the email subject.

     

    What I want to do is set some property or select a setting that can place the status of the job that is in the email body in the subject.

     

    For example, the following email has a STATUS of Succeeded in the body. What I want to do is have this redundantly included in the subject.

     

    Subject:

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

     

    Body:

    JOB RUN: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' was run on 9/13/2007 at 9:00:00 PM

    DURATION: 0 hours, 2 minutes, 9 seconds

    STATUS: Succeeded

    MESSAGES: The job succeeded. The Job was invoked by Schedule 4 (ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup). The last step to run was step 1 (Database_and_Transaction_Log_Backup).

     

    In SQL Server 2000, the email subject includes the status (see example below):

     

    Subject:

    SQL Server DB Maintenance Report - CEPROD6\BOPRODDB\BOPRODDB Instance Maintenance Plan (Success)

     

    Thanks for your help,

    Frank

     

    Friday, September 14, 2007 8:58 PM
  • Hi,

    1.) Disable the Email operator in job first
    2.) Use the below code as step 2 in the job "ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup".
    3.) Please update your email address shown in bold letters

    Code Snippet

    declare @sub varchar(200),@complete varchar(20), @bdy varchar(250)
    declare @stat char(1), @rundate varchar(10), @runtime varchar(10), @rundur varchar(15)
    declare @mess varchar(300)
    select @stat=max(run_status),@rundate=max(run_date),@rundur=max(run_duration),@mess=max(message),@runtime=max(run_time) from sysjobhistory where job_id=(select job_id from msdb..sysjobs where name='ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup') and step_id=1
    select @complete = case @stat when 1 then 'Succeeded' else 'Failed' end
    select @sub ='SQL Server Job System: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup job '''+ @complete + ' on ' + convert(varchar(25),@@servername) + ' at ' + @rundate
    select @bdy='JOB RUN: ''ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup'' was run on '+ @rundate + ' at ' + @runtime + char(10)
                +'DURATION:'+ @rundur + ' Seconds' + char(10)+ 'STATUS:' +@complete+char(10)+ @mess
    Exec msdb.dbo.sp_send_dbmail @profile_name='CITOS',
    @recipients='emailaddress',
    @subject=@sub,
    @body=@bdy


    Now try whether you are getting the mail as mentioned.
    • Proposed as answer by LearnerSql Thursday, October 6, 2011 6:19 AM
    Saturday, September 15, 2007 4:21 PM
  • After hours of searching I have found nobody answering this hard hitting question.

     

    Is there no way to enhance the notification details themselves. Instead of the all to often path of 'bypass the wizard and do it by hand' - can anyone please supply any internal hooks on this topic yet?

     

    I specifically have an issue with the body of the standard backup notification message containing the word, "fail" even though the job didn't fail - this reference is embedded within the TSQL block due to my selection of the checkbox "verify backup integrity"; this makes it difficult to use standard Outlook filtering to force only failed messages to not get filed (highlighted for me).

     

    I found the "log extended information" checkbox in the "reporting and logging" dialog but disabling that kills the very neat feature of hosting the TSQL within the log tables; the lessor of two evils - kill the verify choice or kill the extended logging choice - purely evil choices.

     

    Thanks in advance for any pointers.

    Thursday, February 21, 2008 9:03 PM
  •  

    Best option is if Maintenance plan job failed .

     

    Create another Maintenance plan jobs and run it.

    Note: make sure you disable old mantanance plan jobs)

    Thanks

    Faiz Farazi

    Tuesday, August 5, 2008 1:42 PM
  •  FWB wrote:

    I have a fully functioning SQL Server 2005 SE instance in terms of Database Mail, Maintenance Plans and the SQL Server Agent. All emails are working fine.

     

    All my jobs send email based on jobs completing.

     

    My question is how can I customize the Subject field of emails so that the Status (i.e. Success, Failed) value can be included?

     

    I get this:

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P

     

    I really would like to get this:

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Success

    -OR-

    SQL Server Job System: 'ACCTV9P_Instance_Maintenance_Plan.Database_and_Transaction_Log_Backup' completed on \\ASGARD\ACCTV9P - Failed

     

    Thanks

    Frank

     

    SELECT name, DATABASEPROPERTYEX(name, 'Recovery')
    FROM master.dbo.sysdatabases
    ORDER BY 1

     

    let's change our maintenance plan task for transaction log backups to not include these databases that are in the SIMPLE recovery mode.

    If we run the job again, the job is successful

    NoteTongue TiedQL Server 2000 you need to either select the databases that are not in the SIMPLE recovery model or change your databases to the FULL or Bulk-Logged recovery models for this to work

    Tuesday, August 5, 2008 5:53 PM