Cannot create Maintenance Plan after 2005 SP3 install

Yesterday I came across a question in one of the forums. The user was unable create any Maintenance Plans after installing SQL Server 2005 SP3. When tried to create one, SSMS would throw the below error.

Invalid column name 'from_msx'
Invalid column name 'has_targets'

This usually happens when the service pack had not installed correctly. I had faced this issue once and I had run one script from the installation directory. But could not recollect the script name. After googling found out that it was sysdbupg.sql

The reason for this issue is that the view sysmaintplan_plans has not been upgraded properly. The definition of this view is available in sysdbupg.sql which is usually located in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install folder. Executing this script will resolve this error. Here is the Alter View statement which adds the from_msx and has_targets column to this view.

ALTER VIEW sysmaintplan_plans
AS
   SELECT
   s.name AS [name],
   s.id AS [id],
   s.description AS [description],
   s.createdate AS [create_date],
   suser_sname(s.ownersid) AS [owner],
   s.vermajor AS [version_major],
   s.verminor AS [version_minor],
   s.verbuild AS [version_build],
   s.vercomments AS [version_comments],
   ISNULL((select TOP 1 msx_plan from sysmaintplan_subplans where plan_id = s.id), 0) AS [from_msx],
   CASE WHEN (NOT EXISTS (select TOP 1 msx_job_id
                          from sysmaintplan_subplans subplans, sysjobservers jobservers
                          where plan_id = s.id
                          and msx_job_id is not null
                          and subplans.msx_job_id = jobservers.job_id
                          and server_id != 0))
        then 0
        else 1 END AS [has_targets]
   FROM
   msdb.dbo.sysdtspackages90 AS s
   WHERE
   (s.folderid = '08aa12d5-8f98-4dab-a4fc-980b150a5dc8' and s.packagetype = 6)
go

After executing this script the issue was resolved but the Maintenance Plan started failing with the below error.

 The package path referenced an object that cannot be found

The resolution for this error was very familiar to me. In the last one year I had seen this error at least 4 times, all of which occurred after installing Hotfix or SP. This happens because the DTS.dll is not registered correctly. This file is located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ folder. This file needs to be registered using regsvr32.exe.

As expected the maintenance plan started executing successfully and felt great to see a big smiley from the user!

2 thoughts on “Cannot create Maintenance Plan after 2005 SP3 install

  1. Owen JOHN

    running the sysdbupg.sql fixed my issue of not being able to create a maintenance plan. thank you very much – i’ve been struggling with it for hours

Comments are closed.