Check Jobs

This script allows you to check whether any scheduled jobs have failed and email the errors to the administrator(s).
The script is perfect for SQL Server 2000 deployments which are clustered. This is because clustering does not support SQL Mail simply because applications like Outlook etc. are not cluster aware.
One of the main advantages of this script is that unlike normal SQL Server job error notifications, the failures are emailed in HTML and the error message is clearly displayed.

Requirements:

Deployment:

  1. Download or copy the script below.
  2. Create a new job in SQL Server Agent called "Check Jobs" for example.
  3. Create a new Step and give it a name.
  4. Copy the script excluding the comments and paste into the new step.
    NOTE: Make sure that the entire script is pasted in as SQL Server will truncate anything over 3,200 bytes.
  5. Change the configuration variables to suit you requirements.
  6. IMPORTANT: The @MinutesAgo variable is set to be the number of minutes interval when the job is next scheduled to reoccur. For the @MinutesAgo variable, I use 5 minutes as a guide but it depends upon your requirements. If you specify less time than when the job actually last ran then you will only pickup errors for the time specified (ie. you will miss out in finding all potential errors since the job last ran). If you specify more time than when the job last ran then you may be notified of errors that were already previously detected.
  7. Schedule the job to reoccur at the same interval as specified for the @MinutesAgo variable. Example below..
  8. Save and enable the job.
  9. You should test this job. One way is to temporarily create and run a job with a job step that has the On Success Action set to Quit reporting a failure.

Download/Script:

CheckJobs.zip

------------------------------------------------------------------------------
-- Check Jobs.sql
-- Written by John Buoro
-- This script checks the sysjobs table and reports and jobs which have failed.
-- This script is used on clustered SQL 2000 servers that are unable to properly use SQL Mail.
-- To use, place this script in a scheduled job which runs every 5 minutes.
-- This script requires xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm to be installed.
-- The value of @MinutesAgo must be set to the same repeat interval as when the job runs.
-- You will need to strip out these comments before pasting into a job step otherwise the script 
-- will be truncated.

-- 26/06/2007 - John Buoro - Script created.
-- 08/10/2007 - John Buoro - Added %TEMP% environment variable.

DECLARE @MinutesAgo int
DECLARE @OutputFileName varchar(50)
DECLARE @MailAlertTo varchar(100)
DECLARE @SMTPServer varchar(30)

-- Configuration variables.
SET @MinutesAgo = 5 -- Number of minutes when this job will next run (ie. the repeat frequency of the job)
SET @OutputFileName = 'CheckJobs.html' -- Temporary output file (do not add path)
SET @MailAlertTo = 'DBAdmin@somewhere.com' -- Email address to send alerts to.
SET @SMTPServer = 'SMTPSERVER' -- SMTP server name.

-- Drop temp table.
IF OBJECT_ID('tempdb..##tmp1') IS NOT NULL
BEGIN
 DROP TABLE ##tmp1
END

-- Drop temp table.
IF OBJECT_ID('tempdb..##tmp2') IS NOT NULL
BEGIN
 DROP TABLE ##tmp2
END

-- Find all failed jobs.
SELECT server as 'Server', [name] as 'Job Name', 
CONVERT(datetime,
 SUBSTRING(CAST(run_date as varchar(8)),1,4) + '-' + 
 SUBSTRING(CAST(run_date as varchar(8)),5,2) + '-' + 
 SUBSTRING(CAST(run_date as varchar(8)),7,2) + ' ' +
 SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),1,2) + ':' + 
 SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),3,2) + ':' + 
 SUBSTRING(RIGHT('000000'+CAST(run_time as varchar(8)),6),5,2) ) as RunDateTime,
message as 'Error Message'
INTO ##tmp1
FROM msdb..sysjobhistory jh join msdb..sysjobs j ON jh.job_id=j.job_id
WHERE run_status = 0 and step_id = 0

-- Find all failed jobs in the last @MinutesAgo.
SELECT * INTO ##tmp2 FROM ##tmp1
WHERE RunDateTime >= DATEADD(mi, -@MinutesAgo, GETDATE())

-- Any results found?
IF @@ROWCOUNT > 0
BEGIN
	-- Get the %TEMP% environment variable.
 DECLARE @tempvar varchar(1000)
 CREATE TABLE #tempvartable(info VARCHAR(1000))
 INSERT #tempvartable EXEC master..xp_cmdshell 'echo %temp%'
 SET @tempvar = (SELECT top 1 info FROM #tempvartable)
 IF RIGHT(@tempvar, 1) <> '\' SET @tempvar = @tempvar + '\'
 DROP TABLE #tempvartable
 DECLARE @sub varchar(100)
 DECLARE @svr varchar(100)
 DECLARE @rc int
 SET @svr = (select @@servername) + '@tabcorp.com.au'
 SET @sub = 'Scheduled job failure on SQL server ' + @@servername
 SET @OutputFileName = @tempvar + @OutputFileName

	-- Generate HTML results for query.
 EXEC @rc = master..sp_makewebtask 
	@outputfile = @OutputFileName, 
	@query = 'select * from ##tmp2',
	@lastupdated = 0,
	@HTMLheader = 3,
	@webpagetitle = @sub,
	@resultstitle = @sub

	-- Email the results.
 -- xp_smtp_sendmail http://www.sqldev.net/xp/xpsmtp.htm
 EXEC @rc = master.dbo.xp_smtp_sendmail
	@FROM = @svr,
	@FROM_NAME = @@servername,
	@TO = @MailAlertTo,
	@priority = 'HIGH',
	@subject = @sub,
	@type = 'text/html',
	@messagefile = @OutputFileName,
	@server = @SMTPServer
END

-- Drop temp tables.
DROP TABLE ##tmp1
DROP TABLE ##tmp2

------------------------------------------------------------------------------