Sqlserver007

Problem:

I was trying to configure the backups on Sql Server using the Maintenace Plan.

When I opened it . I got the following error

TITLE: Microsoft SQL Server Management Studio

——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

Invalid column name ‘from_msx’.
Invalid column name ‘has_targets’. (Microsoft SQL Server, Error: 207)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

——————————
BUTTONS:

OK
——————————

Analysis:

The resource DB has not been Upgraded to  SP2 version 9.00.3042.00.

The following Script should prove it.

SELECT SERVERPROPERTY(‘ResourceVersion’) as ResourceDB, SERVERPROPERTY(‘ResourceLastUpdateDateTime’) as ResourceDBLastUpdate,

SERVERPROPERTY(‘ProductVersion’) as Ver, SERVERPROPERTY (‘ProductLevel’) as SP;

GO

A specific “msx” table found in the master database, that holds details of maintenance jobs, did not have certain additional columns added to it after the upgrade to SP2.

Solution :

There are 2 solutions to the problem depending on the risk .

Solution 1

Creating the jobs instead of Maintenance Plans for important Tasks

Solution 2

Go to the SQL Install Folder

“C:\Program Files\Microsoft SQL Server\<instance-installation -folder>MSSQL\Install”

like

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install

and run the sysdbupg.sql script.

That should add the colums to the maintenance tables and the maintenance tasks can be created.