Maintenance Plan Error

Question

Post reply

Maintenance Plan Error

johnb-904405

SSC Enthusiast

Points: 155
More actions
November 12, 2008 at 7:46 am

#215163

Recently we installed SQL Server 2005 Standard 64 bit and upgraded to SP2 as well as a few other updates. Now when I create a new maintenance plan I recieve the error.
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum).
Additional Info -
An exception occurred while executing a Transact-SQL Statement or batch. (Microsoft.SqlServer.Connectioninfo).
Invalid Column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
I've done some research on this and I've seen some outdated articles but apparantly noone has seen this after the newer SP2 is loaded. My version of microsoft.SqlServer.MaintenancePlanTask.dll is (9.0.3282.0)
Any information anyone might have would be appreciated.... I'm new to the SQL world (Used to be an Oracle DBA).
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mits_jaykhare Newbie Points: 8 More actions · Answer 1

please change compatibility level for databases which are using in new maintenance plan .

johnb-904405 SSC Enthusiast Points: 155 More actions · Answer 2

There were 3 of them not set to 90...

I just ran a

SELECT NAME,COMPATIBILITY_LEVEL FROM SYS.DATABASES

GO

and all of them show 90 now but I still get the error when creating a new plan. I think I need to reload the 3282 hotfix because I was in the process of backing it out to reinstall SP2.

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 3

You also need to verify that your client tools have been upgraded to the same version. If your client tools are not upgraded - you will see some issues like this.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mike.lenz Grasshopper Points: 13 More actions · Answer 4

This problem occurs if some of the stored procedures while installing SP2 has not been applied successfully. So manually run the stored procedure via the process below. This resolved our issue of being unable to add maintenance plans with the same error.

1) First go to the folder Install . The default location is this = “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install “ .

2)Then in this folder find this “sysdbupg.sql” .

3)Please open this script with a nodepad.

4)Then copy the whole script and then paste it in the Query window of the Management studio.

5)Then execute this script.

6)Once this script has been successfully executed, The issue should be resolved.

johnb-904405 SSC Enthusiast Points: 155 More actions · Answer 5

mike.lenz (12/18/2008)
This problem occurs if some of the stored procedures while installing SP2 has not been applied successfully. So manually run the stored procedure via the process below. This resolved our issue of being unable to add maintenance plans with the same error.
1) First go to the folder Install . The default location is this = “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install “ .
2)Then in this folder find this “sysdbupg.sql” .
3)Please open this script with a nodepad.
4)Then copy the whole script and then paste it in the Query window of the Management studio.
5)Then execute this script.
6)Once this script has been successfully executed, The issue should be resolved.

I read about that online but I just wanted to add to this thread.

In my instance it was a newer install of SQL server, but the SP2 install only partially finished because I had the resource database and log files on different drives.

I had to uninistall all of my patches, uninstall SP2, and put the resources DB and logs in the same working directory and reattach them at that location. Then I ran SP2 and all of the patches and now I am able to create maintenance plans.

Thanks,

John