error with msb

[niall5098]

HI All,

I am running the following sql:

SELECT CONVERT(VARCHAR(30), plan_name) AS Plan_Name,
CONVERT(VARCHAR(20), Database_Name) AS Database_Name,
sj.name AS Job_Name,
sj.enabled,
CASE freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly Relative'
WHEN 64 THEN 'SQL Server Restarts'
END AS Frequency,
CASE freq_interval
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Sun, Mon'
WHEN 4 THEN 'Tues'
WHEN 5 THEN 'Sun, Tues'
WHEN 6 THEN 'Mon, Tues'
WHEN 7 THEN 'Sun, Mon, Tues'
WHEN 8 THEN 'Wed'
WHEN 9 THEN 'Sun, Wed'
WHEN 10 THEN 'Mon, Wed'
WHEN 11 THEN 'Sun, Mon, Wed'
WHEN 16 THEN 'Thur'
WHEN 32 THEN 'Fri'
WHEN 62 THEN 'Mon - Fri'
WHEN 64 THEN 'Sat'
WHEN 126 THEN 'Mon - Sat'
END AS "Weekday(s)",
active_start_time/10000 AS "start time"
FROM msdb..sysdbmaintplans smp
LEFT JOIN msdb..sysdbmaintplan_databases smpd ON smp.plan_id = smpd.plan_id
LEFT JOIN msdb..sysdbmaintplan_jobs smpj ON smp.plan_id = smpj.plan_id
LEFT JOIN msdb..sysjobs sj ON smpj.job_id = sj.job_id
LEFT JOIN msdb..sysjobschedules sjs ON sj.job_id = sjs.job_id
WHERE Plan_Name <> 'All ad-hoc plans'
ORDER BY [Job_Name]

and getting the following error.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Database_Name'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Plan_Name'.

The columns exist, and if i query each table individually there is no problem.

i have also ran the script on other MSSQL 2000 server and it ran fine.

i ran dbcc check on the msdb and it returned no errors,

Anyone got any ideas/solutions?

 

[mrdenny]

Several tables in msdb where modified as different service packs were installed. Are you sure that the correct service pack has been applied.

Keep in mind that your script won't run against SQL 2005 or newer versions of SQL Server.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog