Create maintenance plan failed

When you create a new maintenance plan that runs on a schedule in Microsoft SQL Server 2005, you may receive the following error message:

Create maintenance plan failed.

Additional information:
Create failed for JobStep 'Subplan'. (Microsoft.SqlServer.MaintenancePlanTasks)

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

The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error 14234)

This behavior may occur when you create the maintenance plan manually or by using the Maintenance Plan Wizard.

========================================

To resolve this behavior, install the Integration Services service from the SQL Server 2005 CD. To do this, follow these steps:

1. Run the SQL Server Installation Wizard.
2. On the System Configuration Check page, click Next after the scan is complete.
3. In the Name box and in the Company box, enter the appropriate information, and then click Next.
4. Click to select the Integration Services check box, and then click Next.
5. Click Next, and then click Install.
6. When the installation is complete, click Next, and then click Finish.

Enabling "Agent XPs" on SQL 2005

Chances are that if you have just done a fresh install of SQL 2005 you will get an error when you try to create your first SQL 2005 maintenance plan. This error tells you that the “‘Agent XPs component is turned off as part of the security configuration for this server” and refers you to your system administrator or the SQL Books Online.

In order to enable the “Agent XPs” component. Start up SQL Management Studio and click the “New Query” button on the tool bar. Execute the following script in the query window (on the right hand side of the screen) .

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO

This SQL script enables the advanced configuration options then turns on the “Agent XPs” component.

You are now free to create your SQL 2005 Maintenance plans.