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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BodestoneCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.