Question

Cannot Add or Delete Corrupt Maintenance Plans

Asked by: 243johnm

I setup some Maintenance Plans in SQL Server 2005 Workgroup for a Maximizer CRM client last year. When working on their server recently, I noticed that the transaction logs were no longer truncating after backups. Furter investigation revealed that the two mainteance plans appeared to be blank.

When I tried to modify one of the maintenance plans in Management Studio, I received the following error message:

Microsoft SQL Server Management Studio
Value cannot be null
Parameter name: component (System.Desgn)
Microsoft SQL Server Management Studio Value cannot be null Parameter name: component (System.Desgn)



In the Error List, I found the following:
Microsoft SQL Server Management Studio is unable to load this document:
Error      1      Error loading 'System Database Maintenance Plan - sa' : Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSApplication90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{7591CC39-276A-4973-97C7-4EFEA5A5788F}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).. server=SERVEUR;package=Maintenance Plans\System Database Maintenance Plan;username=sa;      1      1      



Despite that, I found this in the Event Viewer:
SQL Server Scheduled Job 'System Database Maintenance Plan.Subplan_1' (0x39B25A98DD7D55428F9F2B6B16BECF3A) - Status: Failed - Invoked on: 2009-09-18 13:55:00 - Message: The job failed.  The Job was invoked by Schedule 10 (System Database Maintenance Plan).  The last step to run was step 1 (Subplan_1).



When trying to delete the defective maintenance plans, I get this:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

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

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

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

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)



Advanced Information shows this:

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

Exception has been thrown by the target of an invocation. (mscorlib)

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

   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropMaintPlans.DoDropMaintPlan90(String planID, String planName)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropMaintPlans.DoDropObject(Int32 objectRowIndex)
   at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)

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

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

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

   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.InitFromExistingPackage(Package package, ServerConnection localConnObj)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan..ctor(Package package, String serverName, String userName, SqlSecureString pwd)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintenancePlan.GetMaintenancePlanFromServer(String maintPlanName, String serverName, String userName, SqlSecureString pwd)

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

The connection manager "Local server connection" will not acquire a connection because the package OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.
 (User Database Maintenance Plan)

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

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)



When trying to add a new maintenance plan, I get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Exception has been thrown by the target of an invocation. (mscorlib)

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

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)



Advanced Information provides this:

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

Exception has been thrown by the target of an invocation. (mscorlib)

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

   at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandle& ctor, Boolean& bNeedSecurityCheck)
   at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean fillCache)
   at System.RuntimeType.CreateInstanceImpl(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean fillCache)
   at System.Activator.CreateInstance(Type type, Boolean nonPublic)
   at Microsoft.SqlServer.Management.DatabaseMaintenance.MaintDesignerMenuHandler.Invoke()

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

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSContainer90'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{8BDFE892-E9D8-4D23-9739-DA807BCDC2AC}' failed due to the following error: Interface not registered (Exception from HRESULT: 0x80040155). (Microsoft.SqlServer.ManagedDTS)

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

   at Microsoft.SqlServer.Dts.Runtime.Package..ctor()



Here are the SQL Server application details:
Microsoft SQL Server Management Studio: 9.00.4035.00
Microsoft Analysis Services Client Tools: 2005.090.4035.00
Microsoft Data Access Components (MDAC): 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML: 2.6 3.0 4.0 6.0
Microsoft Internet Explorer: 8.0.6001.18702
Microsoft .NET Framework: 2.0.50727.3082
Operating System: 5.2.3790

Any ideas on how to delete the corrupt maintenance plans and create new ones? SQL Server is working just fine...

Thanks very much,

John

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

  • Plus...
  • 30 Day FREE access, no risk, no obligation
  • Collaborate with the world's top tech experts
  • Unlimited access to our exclusive solution database
  • Never be left without tech help again

Subscribe Now

Asked On
2009-09-19 at 07:36:54ID24745587
Tags

Maintenance Plans

Topic

SQL Server 2005

Participating Experts
2
Points
500
Comments
9

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

  • "The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.
  • "Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
  • "Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Screenshot of Experts Exchange Knowledgebase

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Screenshot of an Article

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Screenshot of Experts Exchange Knowledgebase

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Related Solutions

  1. XML to PDF with MSXML
    halo there i have a simple xml file say for e.g <book> <name>abc</name> <author>xyz</author> </book> i wanted to be connverted to a PDF file (Acrobat Reader) using MSXML technology(not JAVA and cocoons/servlets and apache) how wud ...
  2. Maintenance Plan
    Since we been using SQL 2000 to host our DB, I have never used the maintenance plan wizard. I've just created a job to perform the full backups every night. Our developers have recommended to run a maintenance plan to perform the optimize job & integrity check against...
  3. Integrate SharePoint 2007 and CRM 3.0 (SQL 2005)
    Hi, I'm using SharePoint 2007 and CRM 3.0 with an SQL 2005 DB, how can I get SharePoint to retrieve data from the CRM database for KPIs, lists, etc...?

Free Tech Articles

  1. WARNING: 5 Reasons why you should NEVER fix a computer for free.
    It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
  2. SCCM OSD Basic troubleshooting
    SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
  3. Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
    This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
  4. Create a Win7 Gadget
    This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
  5. Outlook continually prompting for username and password
    There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
  6. Backup Exchange 2010 Information Store using Windows Backup
    There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

  1. Avoiding Bugs in Microsoft Access
    Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
  2. Top 10 Best New Features in Visio 2010
    Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
  3. IT Consultant Business Secrets Revealed
    Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
  4. Disaster Recovery and Business Continuity
    Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
  5. Organize Your Visio Diagrams with Containers and Lists
    Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
  6. How to Us Objects, Properties, Events and Methods in Microsoft Access
    Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

 

by: mSharfiPosted on 2009-09-19 at 07:47:46ID: 25373200

Hi
I hope you fine, please try to use this T-SQL and check the result:
dbcc checkdb (dbname) with no_infomsgs
thanks

 

by: rrjegan17Posted on 2009-09-19 at 07:48:56ID: 25373205

All these Maintenance plans will be stored as a Package in Integration Services Instance.
Just login to your SSIS Instance service and expand Stored Packages option..

You can see all  your Maintenance Plans over there.
Just Delete your Corrupted Maintenance Plans and recreate it

 

by: mSharfiPosted on 2009-09-19 at 07:53:40ID: 25373215

also try to install SP2.and make sure  the Integration services. SSIS is running properly

 

by: rrjegan17Posted on 2009-09-19 at 07:56:36ID: 25373225

mSharfi,
     243johnm is already at SP3..

 

by: 243johnmPosted on 2009-09-19 at 11:33:29ID: 25373953

Thanks very much mSharfi & rrjegan17. I have some more questions for you...

mSharfi: I thought "dbcc checkdb (dbname)" was to check for corruption in a database. Does it also check Maintenance Plans?

rrjegan17: you say to "Just login to your SSIS Instance service and expand Stored Packages option.." I checked and I didn't see SSIS in the under Microsoft SQL Server 2005 in Start > Programs. It also wasn't on a menu in Management Studio. Is it an optional install component or can I start it from the command line?

Regards,

John

 

by: 243johnmPosted on 2009-09-23 at 13:09:52ID: 25407377

I never rec'd an answer to this question:

rrjegan17: you say to "Just login to your SSIS Instance service and expand Stored Packages option.." I checked and I didn't see SSIS in the under Microsoft SQL Server 2005 in Start > Programs. It also wasn't on a menu in Management Studio. Is it an optional install component or can I start it from the command line?

If rrjegan17 can't answer it, can anyone else?

Thanks very much,

John

 

by: rrjegan17Posted on 2009-09-24 at 02:32:38ID: 25411358

>> you say to "Just login to your SSIS Instance service and expand Stored Packages option.." I checked and I didn't see SSIS in the under Microsoft SQL Server 2005 in Start > Programs. It also wasn't on a menu in Management Studio. Is it an optional install component or can I start it from the command line?

Have been bit busy over here..
Ok.. While trying to connect to some server in SSMS, Choose Integration Services instead of the Default Database Engine and then point to your server to see your Stored Packages.

Does that answer's you out..

 

by: 243johnmPosted on 2009-09-26 at 12:40:50ID: 25431251

Thanks very much rrjegan17!  Re "Have been bit busy over here" - not a problem at all, work comes before Exp-Exch!

Looks like SSIS isn't installed: I get an error message: Connect to SSIS Service on machine "Machine-Name" failed: The specified service does not exist as an installed service.

I'm going to start a new thread on how to get that component installed...

Thanks again,

John

 

by: rrjegan17Posted on 2009-09-26 at 18:21:21ID: 25432356

Welcome..

>> I'm going to start a new thread on how to get that component installed...

Just Click Change / Remove SQL Server 2005 on Add/ Remove Programs in Control Panel and then include Integration Services as part of your Installation.

It would ask for your Installation DVD and providing so will get you Integration Services installed.

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...