You are currently offline, waiting for your internet to reconnect

Error message when you create a new maintenance plan in SQL Server 2005: "Create maintenance plan failed"

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
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.
CAUSE
This behavior occurs when you install SQL Server 2005 without installing the SQL Server 2005 Integration Services (SSIS) service. When you create the maintenance plan, the following code is run.
EXEC msdb.dbo.sp_update_jobstep @job_name=N'System DB Maintenance Plan', @step_id=1 , @subsystem=N'SSIS', @command=N'/SQL "\Maintenance Plans\System DB Maintenance Plan" /SERVER ASHBSQL701 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF'
This code tries to access the Integration Services service.
RESOLUTION
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.
STATUS
This behavior is by design.
MORE INFORMATION
For more information, visit the following Microsoft Developer Network (MSDN) Web sites:
Properties

Article ID: 909036 - Last Review: 03/20/2006 22:17:59 - Revision: 1.1

Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition

  • kbsql2005ssis kbtshoot kbinfo kbbug kbprb KB909036
Feedback