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

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

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

|||

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:

|||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.
|||

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

|||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.