SQL 2000 - check job logs in various sysdts* tables

does anybody have a script I could use to check job logs in DTS with the sysdts* tables..I wanted to create a package that will send an alert if something doesnbt run...thanks...
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
 
BodestoneConnect With a Mentor Commented:
You can set up alerts directly on each job for when it fails.

Alternatively the below script will return all active jobs and those with a Status of 0 (failed) will return one row for each error generated for that last run. I do it this way so I can have my dashboard open up any server (we have a few) with failed jobs and have the failed job icon as a hover tooltip with the contents of the failure on hover.

I created this as a stored procedure for the passing of the server parameter but here it is as a view and then had a job to check if there were any 0 status jobs and send an email using dtabase mail:

SELECT *
INTO #temp
FROM ActiveJobStatus
WHERE Status = 0
IF @@ROWCOUNT > 0
BEGIN
DECLARE      @subject1 VARCHAR(50),
                  @body1 VARCHAR(1024)                  
      SET            @subject1 = 'Daily Dashboard'
      SELECT      @body1 = 'The following jobs are showing as failed.' + CHAR(10) + (SELECT char(10) + JobName FROM #temp WHERE status = 0 FOR XML PATH('')) + CHAR(10) + CHAR(10) + 'Click here for the dashboard: http://moominserver/jobdashboard'
      EXEC msdb.dbo.sp_send_dbmail
            @recipients='me@me.co.uk',
            @subject=@subject1,
            @body=@body1,
            @body_format = 'TEXT'
END

CREATE VIEW ActiveJobStatus AS
WITH fullJobHistory AS
(
	--This just formats the date properly (this is what MS use to read it into SSMS)
	SELECT	CAST(CAST(run_date AS VARCHAR(8)) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(run_time AS VARCHAR(6)),6),3,0,':'),6,0,':') AS datetime) AS RunDate,
			instance_id, job_id, step_id, [message] AS errorText, run_status, run_date, run_time
	FROM	msdb.dbo.sysjobhistory
),
baseJobRecord AS 
(
	--Single out failed jobs
	SELECT	ROW_NUMBER() OVER(PARTITION BY job_id ORDER BY Rundate DESC) AS [jobNumber],
			*
	FROM	fullJobHistory
	WHERE	step_id=0
),	
stepFailures AS
(
	SELECT	o.job_id,
			o.jobNumber,
			f.errorText AS [stepError]
			
	FROM	baseJobRecord o
	LEFT JOIN	baseJobRecord o2
		ON	o2.job_id = o.job_id
		AND	o2.jobNumber = o.jobNumber - 1
	JOIN	fullJobHistory f
		ON	f.job_id = o.job_id
		AND	f.step_id > 0
		AND	f.run_status = 0
		AND	(o2.instance_id IS NULL
		OR	f.instance_id BETWEEN o.instance_id AND o2.instance_id)
	WHERE	o.run_status = 0
	AND		o.jobNumber = 1
	GROUP	BY o.job_id,
			o.jobNumber,
			f.errorText
)

SELECT	DISTINCT
		o.run_status AS [Status],
		j.name AS [Job Name],
		o.RunDate AS [Last Ran],
		STUFF((SELECT '<br /><hr />' + CASE 
											WHEN o2.run_status = 1 THEN NULL
											ELSE isnull(s.stepError,o2.errorText)
										END 
										FROM	baseJobRecord o2
										LEFT JOIN	stepFailures s 
											ON	s.job_id = o2.job_id
											AND	s.jobNumber = o2.jobNumber
										WHERE o2.job_id = o.job_id 
										AND		o2.jobNumber = o.jobNumber
										FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,12,'') 
		
		 AS [Error]
FROM	msdb.dbo.sysjobs j
JOIN	baseJobRecord o
	ON	o.job_id = j.job_id
	AND o.jobnumber = 1
WHERE	j.enabled = 1
ORDER BY 1,2

Open in new window

0
 
BodestoneCommented:
oh. You can run this from the SQL 2005 database (since you have that as your tag)
You just need a linked server to the 2k DB and then change msdb.dbo.sysjobhistory to 2kservername.msdb.dbo.sysjobhistory etc.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.