August 15, 2008 at 6:17 am
Hi ,
There is my script to see all failed job on sql server 2000 but its works fine with all the servers but i have one server on which this script is not working .will u plz tell me how can i solve the problem .
Script :
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
,[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
ERROR is here
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'.
please help me to resolve this error .
Regards
Jagpal singh
August 15, 2008 at 7:16 am
Are you sure that the SQL Server is SQL 2000? This script works fine on SQL 2005 as well. I don't have a 7.0 server to try it on.
Have you looked in the MSDB database on that server for the 2 tables?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 7:33 am
Hi,
I cant retrive from user tables .i will try to run this but i cat access
select * from dbo.sysjobhistory
still i got this error .
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.sysjobhistory'.
and i cant access ay of user table from msdb .But i can access from master or model table .
Thaxx
August 15, 2008 at 7:50 am
Sounds like a permissions issue. What account is this script running under?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 7:52 am
I have login as sql admin
August 15, 2008 at 7:59 am
jagpalsingh4 (8/15/2008)
I have login as sql admin
Doesn't sound like it on this server. Is the msdb database on-line? The only time I have seen a problem like this is when the msdb database was corrupted and could not come on-line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 15, 2008 at 8:04 am
Yes msdb is online bcuase i can retrieve details from systemtables .I used this query to see failed jobs otherwise i need to go through ssms and send the report to customer that which jobs are failed like name of jobs .
August 15, 2008 at 8:17 am
Jack Corbett (8/15/2008)
jagpalsingh4 (8/15/2008)
I have login as sql adminDoesn't sound like it on this server. Is the msdb database on-line? The only time I have seen a problem like this is when the msdb database was corrupted and could not come on-line.
At least in SQL Server 2005 the public role in msdb does not have rights to sysjobs and sysjobhistory tables so if you are not inteh sysadmin server role or some other role within msdb you can't query those tables. So I don't think you are sysadmin on that particular SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply