SQL 2005 Standard - cannot create maintenance plans

[jpotucek]

I cannot Create Maintenance Plans. All error messages below. Some of the links point to help which address possible wrong collation - but my Server Collation is Latin_General_CI_AI - which should be the default or Norm.

Has anyone ever seen this before or can anyone offer any suggestions?


Saving maintenance plan failed.

------------------------------
ADDITIONAL INFORMATION:

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

For help, click:

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

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

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)

For help, click:

http://go.microsoft.com/fwlink?Prod...042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
===================================

Saving maintenance plan failed.

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

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

------------------------------
For help, click:

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

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.GetSubPlanJob(ServerConnection userConnection)
at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.UpdateAgentJob(ServerConnection localConnObj, MaintenancePlan maintPlan)
at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlanSubPlan.OnMaintenancePlanSaved(MaintenancePlan maintPlan)
at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.OnSave()
at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.Save()
at Microsoft.SqlServer.Management.MaintenancePlanWizard.MaintenancePlanWizardForm.PerformActions()

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

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

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)

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

Invalid column name 'msx_job_id'. (.Net SqlClient Data Provider)

------------------------------
For help, click:

http://go.microsoft.com/fwlink?Prod...042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

------------------------------
Server Name: KOCSQLDEV01
Error Number: 207
Severity: 16
State: 1
Line Number: 5


------------------------------
Program Location:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)

 

[olahallengren]

I know that Service Pack 2 fixes a number of bugs related to the Maintenance Plans, but I don't know about this one specifically.

If you're interested I have a stored procedure based maintenance solution that you're welcome to use.

http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren

http://ola.hallengren.com

 

[mrdenny]

What build of SQL Server 2005 are you running?

I see that column in the sysmaintplan_subplans table in the msdb database. Do you see that column in there?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[aalmeida]

Do you have SSIS installed on the same server? SSIS is a requirement to create maintenance plans.

AL Almeida
Senior DBA

http://www.ajmedia.net/

"May all those that come behind us, find us faithfull"