Msg 208, Level 16, State 1, Line 1

  • 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

  • I suspect that you have case sensitive collation on that server. could you pls check the server collation.

    Regards,

    Sachin

  • 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