Unable to Create SQL Maintenance Tasks

  • I am having problems creating maintenace tasks in the Maintenance Applet in Server Management Studio. When I try to open the applet, I get the following error:

    ---------------------------------------------------------------------------------------------------------------------

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

    Additional information:

    An exception occured 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)

    ---------------------------------------------------------------------------------------------------------------------

    My setup is as follows:

    SQL Server 2005 SP2 (9.0.3042) Clustered instance (x64)

    Visual Studio 2005 SP1 Update

    Windows Server 2008 Enterprise Edition (x64)

    What I find odd is that I have three seperate clustered instances of SQL2005 SP2 running on the same clustered servers and only one instance allows me to create maintenance tasks with no problems. All of these were clean installs of SQL 2005 SP2.

    Please see additional information below which may help

    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.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

    ------------------------------

    BUTTONS:

    OK

    Any Ideas

  • Just a guess: Do you have SSIS installed on this server? It is required to run maintenance plans.

  • got onto the phone to the chaps at Microsoft and they were able to determine that the Resource Database had not been upgraded to the SP2 version 9.00.3042.00. It was still 9.00.1399. No errors were picked up in the SP2 installation logs. They reported a successful installation/upgrade.

    This is a SQL2005 SP2 bug and is resolved in SP3, which is due when SQL Server 2008 goes RTM. If you are experiencing similar problems, a quick way to tell would be to run the following SQL statement that will tell you the ResourceDB version, when it was last updated and the SQL ServerVersion. The SQL Server Version should be the same as ResourceDB:

    SELECT SERVERPROPERTY('ResourceVersion') as ResourceDB, SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceDBLastUpdate,

    SERVERPROPERTY('ProductVersion') as Ver, SERVERPROPERTY ('ProductLevel') as SP;

    GO

    As a temp work around, I am creating jobs for critical tasks i need done on the server e.g. backup jobs for databases

    I will keep you posted as to the update for this once resolved by M$

Viewing 4 posts - 1 through 3 (of 3 total)

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