March 25, 2008 at 8:59 am
hi ,
I have this query to see all failed jobs but it not works on sql 7.0.
it works properly on 2000 and 2005 .
will u plz find our the prob
SELECT
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
WHERE
Step_ID = 0 --Comments this line if you want to see the status of each step of the job
AND run_status = 0 -- Failed Job only flag
ORDER BY run_date DESC, run_time DESC, step_ID DESC
this error comes out
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSDB..SysJobHistory'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSDB..SysJobs'.
plz solve this
March 25, 2008 at 9:37 am
The reason you're getting an error saying those objects don't exist is because those objects don't exist in 7.0.
I actually don't have any more copies of 7.0 installed around here to check out the system tables. Anyone else still have that stuff available?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply