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 FilesMicrosoft SQL ServerMSSQL.1MSSQLInstall 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 FilesMicrosoft SQL Server90DTSBinn 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!