This was the labor of love of one of my former coworkers. I've meant to send this into SQL Server Mag for a while, but never got around to a full writeup. Hope you can use this.
What it does: in jobs, there are what are called Tokens. If you have Token Substitution turned on in the Agent settings, then this SP can be called as a job step, and it'll email the full results of the job failure step. In your job you'd create a new step (set to return failure on success/failure, since it's just reporting details of the failure), that would only be called on failure, with the "sample syntax" enclosed below. If all is set up correctly (it requires, among other things, xp_smtp_mail - but who _doesn't_ have that in a 32-bit environment?) you'll get a large email with the results of the failure.
That beats the heck out of having to dig through a job failure message that usually reads "....the step failed"
/******************************************************************
Author: Mark A. Hill
Object: usp_SQLAgentTokenMail
Description: This stored procedure uses SQL Agent tokens which can
only be used inside a scheduled job, it will not work in
Query Analyzer. An operator must be set up to send emails,
pages, net sends. Use the Notifications tab of the job to
enable alerts to be written to the Windows event log.
When setting up the job use the "Sample Syntax" below as
the last step of the job, then set all of the other steps
of the job to use "GO TO [step name]" (which contains the
the usp_SQLAgentTokenMail), the stored procedure will
determine if the job failed or succeded and if an email
should be sent or not. Note: The job log information may
appear different then what you are used to when viewing
job history, the stored procedure is doing the logging
instead of the SQL Agent (But it is probably more info
then SQL Agent normally provides!).
xp_smtp_sendmail also needs to be installed, it can be found
at http://SQLDev.Net You may also want to change the [from],
[from_name] and [server] of the xp_smtp_sendmail code below
to match your companies specific needs.
Sample Syntax:
exec msdb.dbo.usp_SQLAgentTokenMail
@job_id = [JOBID]
, @Date = [STRTDT]
, @Time = [STRTTM]
, @Instance = [INST]
, @stepct = [STEPCT]
, @stepid = [STEPID]
Legal Stuff:
You May use this code in whole or in part as public domain
as long as you don't remove these comments.
System Requirements: This has been tested on SQL Server 2000 SP3a
I would imagine that it would work on SQL 7.0 as well but
I have no reason to test it on there. Oh and you will need
a mail server running SMTP, and don't forget to install
xp_smtp_sendmail and verify that it is working properly.
*******************************************************************/
alter proc [dbo].[usp_SQLAgentTokenMail]
@job_id uniqueidentifier
, @Date int = 0
, @Time varchar(8) = 0
, @Instance varchar(128) = ''
, @stepct varchar(10) = ''
, @stepid varchar(10) = ''
as
set nocount on
Declare @varmessage nvarchar(4000)
, @job_name nvarchar(255)
, @varsubject nvarchar(255)
, @datetime varchar(20)
, @vardate varchar(10)
, @vardatetime varchar(20)
, @runduration varchar(10)
, @command varchar(1000)
, @message varchar(1000)
, @step_name varchar(128)
, @subsystem varchar(128)
, @run_status int
, @database_name varchar(128)
, @new_stepid varchar(10)
, @status varchar(30)
, @send_email int
, @send_page int
, @send_netsend int
, @send_eventlog int
, @notify_netsend_operator_id int
, @notify_page_operator_id int
, @notify_email_operator_id int
, @notify_email_operator_name varchar(128)
, @notify_netsend_operator_name varchar(128)
, @notify_page_operator_name varchar(128)
, @notify_level_eventlog int
, @notify_level_email int
, @notify_level_netsend int
, @notify_level_page int
, @weekday_pager_start_time int
, @weekday_pager_end_time int
, @saturday_pager_start_time int
, @saturday_pager_end_time int
, @sunday_pager_start_time int
, @sunday_pager_end_time int
, @dw int
, @operator_time int
, @page_time_valid int
, @email_address_string varchar(255)
, @netsend_string nvarchar(4000)
, @pager_days int
, @page_day_valid int
, @page_dw INT
, @servername sysname
SET @send_email = 0
SET @send_page = 0
SET @send_netsend = 0
SET @send_eventlog = 0
SET @pager_days = 0
set @page_day_valid = 0
select @job_name = name from msdb.dbo.sysjobs with (nolock) where job_id = @job_id
SELECT @servername = @@SERVERNAME
/******************************************************
Selecting the Job History
******************************************************/
select top 1
@new_stepid = sjh.step_id
, @step_name = isnull(sjs.step_name,'')
, @subsystem = isnull(sjs.subsystem,'')
, @database_name = isnull(sjs.database_name,'')
, @command = isnull(sjs.command,'')
, @message = isnull(sjh.message,'')
, @run_status = isnull(sjh.run_status,'')
from msdb.dbo.sysjobs sj with (nolock)
inner join msdb.dbo.sysjobsteps sjs with (nolock) on sj.job_id = sjs.job_id
inner join msdb.dbo.sysjobhistory sjh with (nolock) on sjs.job_id = sjh.job_id
where sj.job_id = @job_id
order by sjh.instance_id desc
/******************************************************
Checking if date passed is valid
******************************************************/
IF isdate(@date) = 1
BEGIN
set @vardate = convert(varchar(10),convert(datetime,convert(varchar(8),@date)),101)
END
IF len(convert(varchar(10),@time)) = 6
BEGIN
SET @time = substring(@time,1,2) + ':' + substring(@time,3,2) + ':' + substring(@time,5,2)
SET @datetime = (@vardate + ' ' + @time)
END
IF isdate(@datetime) = 1
BEGIN
SET @vardatetime = @datetime
END
ELSE
BEGIN
SET @vardatetime = @vardate
END
select @runduration = convert(varchar(10),datediff(ms,@datetime,getdate()))
/******************************************************
Building the subject and message
******************************************************/
if @run_status = 0
BEGIN
SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Failed.')
SET @status = 'Failed'
END
ELSE
BEGIN
SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Completed.')
SET @status = 'Completed'
END
SET @varmessage = ('
Job Name: ' + @job_name + '
Status: ' + @status + '
Server: ' + @@servername + '
Database: ' + @database_name + '
Instance: ' + @instance + '
Date Time: ' + @vardatetime + '
Run Duration: ' + @runduration + ' (Milliseconds)
Step: ' + @step_name + '
Command Type: ' + @subsystem + '
Command Executed: ' + @command + '
Error Message: ' + @message
)
/******************************************************
Selecting the operator to notify
******************************************************/
select @notify_email_operator_id = notify_email_operator_id
, @notify_netsend_operator_id = notify_netsend_operator_id
, @notify_page_operator_id = notify_page_operator_id
, @notify_level_eventlog = notify_level_eventlog
, @notify_level_email = notify_level_email
, @notify_level_netsend = notify_level_netsend
, @notify_level_page = notify_level_page
from msdb.dbo.sysjobs with (nolock)
where job_id = @job_id
IF @notify_email_operator_id = null PRINT 'No Operator exists, create an operator using Enterprise Manager!'
select @notify_page_operator_name = pager_address
, @weekday_pager_start_time = weekday_pager_start_time
, @weekday_pager_end_time = weekday_pager_end_time
, @saturday_pager_start_time = saturday_pager_start_time
, @saturday_pager_end_time = saturday_pager_end_time
, @sunday_pager_start_time = sunday_pager_start_time
, @sunday_pager_end_time = sunday_pager_end_time
, @pager_days = pager_days
from msdb.dbo.sysoperators with (nolock)
where id = @notify_page_operator_id
and enabled = 1
select @notify_email_operator_name = email_address
from msdb.dbo.sysoperators with (nolock)
where id = @notify_email_operator_id
and enabled = 1
select @notify_netsend_operator_name = netsend_address
from msdb.dbo.sysoperators with (nolock)
where id = @notify_netsend_operator_id
and enabled = 1
/******************************************************
Checking which alerts actions to perform.
******************************************************/
/*
notify_level_email
0 = Never
1 = When the job succeeds
2 = When the job fails
3 = Whenever the job completes (regardless of the job outcome)
Run_status
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
*/
IF @notify_level_email = 0
BEGIN
SET @send_email = 0
END
ELSE
BEGIN
IF @notify_level_email = 3
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_email = 2))
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_email = 1))
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
SET @send_email = 0
END
END
END
END
IF @notify_level_page = 0
BEGIN
SET @send_page = 0
END
ELSE
BEGIN
IF @notify_level_page = 3
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_page = 2))
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_page = 1))
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
SET @send_page = 0
END
END
END
END
IF @notify_level_netsend = 0
BEGIN
SET @send_netsend = 0
END
ELSE
BEGIN
IF @notify_level_netsend = 3
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_netsend = 2))
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_netsend = 1))
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
SET @send_netsend = 0
END
END
END
END
IF @notify_level_eventlog = 0
BEGIN
SET @send_eventlog = 0
END
ELSE
BEGIN
IF @notify_level_eventlog = 3
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_eventlog = 2))
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_eventlog = 1))
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
SET @send_eventlog = 0
END
END
END
END
/******************************************************
Checking which Days pages should be sent
******************************************************/
set @dw = datepart(dw,getdate())
if @pager_days & 1 = 1 set @page_dw = 1
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 2 = 2 set @page_dw = 2
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 4 = 4 set @page_dw = 3
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 8 = 8 set @page_dw = 4
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 16 = 16 set @page_dw = 5
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 32 = 32 set @page_dw = 6
IF @page_dw = @dw set @page_day_valid = 1
if @pager_days & 64 = 64 set @page_dw = 7
IF @page_dw = @dw set @page_day_valid = 1
/******************************************************
Checking if operator is on pager duty.
******************************************************/
set @operator_time = (select convert(int,replace(convert(varchar(10),getdate(),108),':','')))
set @page_time_valid = 0
IF @dw between 2 and 6 -- Weekdays
BEGIN
IF @operator_time BETWEEN @weekday_pager_start_time and @weekday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end
IF @dw = 1 -- Sunday
BEGIN
IF @operator_time between @sunday_pager_start_time and @sunday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end
IF @dw = 7 -- Saturday
BEGIN
IF @operator_time between @saturday_pager_start_time and @saturday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end
/******************************************************
Checking which email addresses to use.
******************************************************/
IF ((@page_time_valid = 1) and (@page_day_valid = 1) and (@send_page = 1))
BEGIN
IF @send_email = 1
BEGIN
SET @email_address_string = (@notify_page_operator_name + ', ' + @notify_email_operator_name)
END
ELSE
BEGIN
SET @email_address_string = @notify_page_operator_name
END
END
ELSE
BEGIN
IF @send_email = 1
BEGIN
SET @email_address_string = @notify_email_operator_name
END
END
/******************************************************
Sending the email to the operator.
******************************************************/
IF ((@send_email = 1) or (@send_page = 1))
BEGIN
exec master.dbo.xp_smtp_sendmail
@from = N'sqlservice@yourcompanyname.com'
, @FROM_NAME = @servername
, @to = @email_address_string
, @subject = @varsubject
, @message = @varmessage
, @server = N'mail'
END
/******************************************************
Sending the netsend to the operator
******************************************************/
IF @send_netsend = 1
BEGIN
SET @netsend_string = ('net send ' + char(39) + @notify_netsend_operator_name + '
' + replace(@varsubject,char(39),'') + '
' + replace(@varmessage,char(39),'') + char(39))
exec master.dbo.xp_cmdshell @netsend_string
END
/******************************************************
Passing error messages to Windows eventlog
******************************************************/
IF @send_eventlog = 1
BEGIN
RAISERROR (@varmessage, 16, 1) with log
END
/******************************************************
Passing error messages to SQL Agent
So the job will fail and show an red "x"
******************************************************/
IF @run_status != 0
BEGIN
RAISERROR (@varmessage, 16, 1)
END
/******************************************************
Passing error messages to job output log
******************************************************/
select @varsubject
select @varmessage
Select ('email sent to: ' + @email_address_string)
/*
SELECT @send_email as 'Send Email'
SELECT @send_page as 'Send Page'
SELECT @send_netsend as 'Send Netsend'
SELECT @send_eventlog as 'Write to Event Log'
SELECT @pager_days as 'Pager Days'
SELECT @dw as 'Day of Week'
SELECT @page_dw as 'Day of Week to Page'
SELECT @page_day_valid as 'Page Day Valid'
*/
