May 18, 2011 at 4:57 pm
Hi,
I have a problem with this query when I run on one particular server on all other servers it works OK. can any one tell me what might be the problem and here is the error message. I am sa on this server.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'MSDB..SYSJOBS'.
SELECT DISTINCT SJ.NAME,
CAST(MAX(CAST(CAST(SJH.RUN_DATE AS VARCHAR(100))+ CAST (SJH.RUN_TIME AS VARCHAR(100))AS BIGINT))AS VARCHAR(1000))AS LAST_RUN,
CASE
WHEN SJ.ENABLED=0 THEN 'DISABLED'
ELSE 'ENABLED'
END AS STATUS
FROM MSDB..SYSJOBS SJ
INNER JOIN MSDB..SYSJOBHISTORY SJH
ON SJ.JOB_ID=SJH.JOB_ID
INNER JOIN MSDB..SYSJOBSCHEDULES SJS
ON SJ.JOB_ID=SJS.JOB_ID
WHERE SJ.ENABLED=0
GROUP BY SJ.NAME,SJH.SERVER,SJ.ENABLED,CAST(SJS.NEXT_RUN_DATE AS VARCHAR(100))+ CAST (SJS.NEXT_RUN_TIME AS VARCHAR(100))
ORDER BY SJ.NAME
Thanks,
Ravi
May 18, 2011 at 11:02 pm
I suspect that you have case sensitive collation on that server. could you pls check the server collation.
Regards,
Sachin
May 19, 2011 at 6:03 am
sachnam (5/18/2011)
I suspect that you have case sensitive collation on that server. could you pls check the server collation.Regards,
Sachin
Yup, I have the same "delight" here.
Here's a tested version with correct case :
SELECT DISTINCT
SJ.[name]
, CAST(MAX(CAST(CAST(SJH.run_date AS VARCHAR(100))
+ CAST(SJH.run_time AS VARCHAR(100)) AS BIGINT)) AS VARCHAR(1000)) AS LAST_RUN
, CASE WHEN SJ.enabled = 0 THEN 'DISABLED'
ELSE 'ENABLED'
END AS STATUS
FROM
msdb..sysjobs SJ
INNER JOIN msdb..sysjobhistory SJH
ON SJ.job_id = SJH.job_id
INNER JOIN msdb..sysjobschedules SJS
ON SJ.job_id = SJS.job_id
WHERE
SJ.enabled = 0
GROUP BY
SJ.name
, SJH.server
, SJ.enabled
, CAST(SJS.next_run_date AS VARCHAR(100))
+ CAST(SJS.next_run_time AS VARCHAR(100))
ORDER BY
SJ.name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply