Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Stored Procedures

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-26 : 09:55:25

JAB writes "I am looking to monitor a SQl Server table for jobs that run each night, and check the status (i.e. Successfull or Fail) of each job. What I need is a Stored Procedure or Trigger that will poll the table, look into one or two columns, and send an page if a determined number of jobs fail.

For example, there are 110 jobs that process between Midnight and 6:00 AM. If n number of jobs fail, SQL Server's alarm function will page the oncall person.

n = whatever number of jobs are determined to be critical.

Regards"

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-06-26 : 10:35:35

Here's what I used to get a count of failed jobs. You could throw this in a scheduled proc that checks the number for your specific threshold and then notifies you.

 
-- Get number of failed jobs
SELECT count(*) 
	FROM msdb.dbo.sysjobhistory
	WHERE run_status = 0
	AND step_id = 0
	AND cast(
		substring(cast(Run_Date AS varchar(8)), 5, 2) + '/' + 
		substring(cast(Run_Date AS varchar(8)), 7, 2) + '/' + 
		substring(cast(Run_Date AS varchar(8)), 1, 4) + ' ' + 
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 1, 2) + ':' +
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 3, 2) + ':' +
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 5, 2)
		AS DATETIME) <= @enddate
	AND cast(
		substring(cast(Run_Date AS varchar(8)), 5, 2) + '/' + 
		substring(cast(Run_Date AS varchar(8)), 7, 2) + '/' + 
		substring(cast(Run_Date AS varchar(8)), 1, 4) + ' ' + 
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 1, 2) + ':' +
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 3, 2) + ':' +
		substring(right('000000' + cast(Run_Time AS varchar(6)), 6), 5, 2)
		AS DATETIME) >= @begdate

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 06/26/2002 10:36:19

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources