Solved

Unable to delete or access SQL 2008 maintenance plan

Posted on 2010-09-08
44
1,849 Views
Last Modified: 2012-08-13
When I use the sa account to access the maintenance plan, I got the follow error attached in this question. I tried the other account but still has the same issue.

There is also a popup window message,

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

Value cannot be null.
Parameter name: component (System.Design)


Please help me.  Thanks.
error.GIF
0
Comment
Question by:atlasdev
  • 15
  • 12
  • 11
  • +2
44 Comments
 
LVL 3

Expert Comment

by:dpicco
ID: 33632650
I would try deleting it from the command line. See if this helps you:

http://www.tech-forums.net/pc/f57/delete-sql-maintenance-plan-210902/
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33632895
Restart SQL Server services and try once.
Try applying SP1 for SQL Server 2008 if it is not applied.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33635312
make sure you have at least sp1 for sql 2008 installed ...
can you create and use any maint plans there? just 1 is corrupted?
---
if it is you need to delete maint plan job;
you need to delete all your maint plan records from msdb database tables and delete maint plan job;
like this one
http://gudenas.com/2007/04/20/sql-server-2005-delete-maintenance-plan-error/
 
more
http://www.mssqltips.com/tip.asp?tip=1137
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33636927
This is a repost of the same question where I gave the same advice that EugeneZ gave:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26456606.html
0
 

Author Comment

by:atlasdev
ID: 33668089
Hi, I posted the wrong title of my question so I repost it again.
I can delete the maintenance plan by query now. However, I cannot create new maintenance plan by SQL management studio and I have SP 1 installed on my SQL 2008 server.

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33670793
you need delete all data from all tables from the above link
--did you reboot box after sp1 was installed?
check MS Security patches - Sql server ..
---
if all done and you still have problems to created maint plan via SSMS gui from the box where your sql server:
please post sql error log errors; event viewer errors; popup errors if you see any
0
 

Author Comment

by:atlasdev
ID: 33677315
My SQL  2008 has SP 1 and rebooted.
I could delete all of my maintenance jobs by query. However, I still cannot create a new maintenance plan.
When I tried to create a new maintenance plan from Management Stuido, I will get an error attached in this comment.
My SQL server version is Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64).

2.GIF
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33677579
I can not see anything from the gif
Can you please type here the error? do you see any related errors in eventviewer , sqlerror log? did you try to create main plan from the server it self or from remote PC?
0
 

Author Comment

by:atlasdev
ID: 33677648
You may click on the picture to see the error in full screen. Below is the copied text of the error message.
TITLE: Microsoft SQL Server Management Studio
------------------------------

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

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

An OLE DB error 0x80004005 (Login timeout expired) occurred while enumerating packages. A SQL statement was issued and failed.


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

An OLE DB error 0x80004005 (Login timeout expired) occurred while enumerating packages. A SQL statement was issued and failed.


I tried to create the maintenance plan from the server's RDP console mode. I do not see other logs about my failure to create the maintenance plans in server event viewer.

I also reinstalled intergration services.
Nothing worked so far.
By the way, I also found one job I cannot delete in SQL agent.
The error I got is copied below

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

Drop failed for Job 'SEABackup.Subplan_1q'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1045+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476

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

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

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

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=547&LinkId=20476
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33677898
you need to review posted link(s) above and make sure all msdb maint tables (from the link) are clean from old maint plan:
looks like you forgot at least 1 table:
in database "msdb", table "dbo.sysmaintplan_subplans
"The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
 
0
 

Author Comment

by:atlasdev
ID: 33677907
Hi, I checked that table, dbo.sysmaintplan_subplans and manually deleted a row that has the job ID. Then I could delete the SQL agent job by manament studio.
However, I still cannot create a maintenance plan by Manament Studio. :(
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33677967
Did you remove all maintenance plans via query?  You might need to do that before you can create a new one because of how buggy this situation is.
0
 

Author Comment

by:atlasdev
ID: 33677971
I have removed all maintenance plans via query.
0
 

Author Comment

by:atlasdev
ID: 33678043
Hi, everyone,
I just found that I could create a maintenance plan by a different management studio on a different computer.
I will try to reinstall my management studio now.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33679306
>> However, I still cannot create a maintenance plan by Manament Studio.

What is the error message you obtain when you are trying to create maintenance plans..

>> I will try to reinstall my management studio now.

Resinstalling SSMS might not help as I suspect somewhere some other thing is going wrong.
0
 

Author Comment

by:atlasdev
ID: 33683925
The error message I got is attached in this comment.

If no other solution is out, I am going to try to reinstall the SSMS today.
err.GIF
0
 

Author Comment

by:atlasdev
ID: 33684358
OK. I reinstalled the SSMS but I still cannot create a maintenance plan. I still get the same error message.
Any more suggestion?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33684388
Because the maintenance plans are truly SSIS I would also reinstall Integration Services and Business Intelligence Development Studio as well.
0
 

Author Comment

by:atlasdev
ID: 33684401
I have reinstalled Integration services before and my server does not have Business Intelligence Development Studio.
0
 

Author Comment

by:atlasdev
ID: 33684522
I can create a maintenance plan from a SSMS on a different computer.
After I created that plan, I tried to go back to my own server to access it. However, I got an error attached.
3.GIF
0
 

Author Comment

by:atlasdev
ID: 33684698
I also just did a full repair from the DVD for my SQL 2008 server. However, the same issue remains. :(
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 500 total points
ID: 33684732
It may be time to open a case with Microsoft.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 42

Expert Comment

by:EugeneZ
ID: 33687468
try unistall tools and ssis  first - before install
also make sure to install sp1+ patches for sql2008 after the tools are installed ...
 
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33690799
>> I can create a maintenance plan from a SSMS on a different computer.
After I created that plan, I tried to go back to my own server to access it. However, I got an error attached.

You can't create or edit Maintenance plans from another machine..
Kindly try remoting into your SQL server machine and try creating or modifying Maintenance plans..
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33691334
Sure you can create and edit maintenance plans via SSMS from any machine, I do it across my entire enterprise of over 140 servers.

I think rrjegan17 is missing what the poster is saying.  He is opening SSMS on another server or client and connecting to the affected server and creating a maintenance plan that way.

Because it's working fine his server is ok.  It's his local installation of SSMS/SSIS that has the problem.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33691678
>> Sure you can create and edit maintenance plans via SSMS from any machine
>> He is opening SSMS on another server or client and connecting to the affected server and creating a maintenance plan that way

Can you kindly check this once as I doubt it.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33691929
Are you asking me to check it out?  I do this on almost a daily basis.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33693324
<You can't create or edit Maintenance plans from another machine..>
 you can create  Maintenance plans from another machine by using: try SSMS 2008 tool and create Maint plan for sql server2008.
--
if you use SSMS to create sql server 2000 plan - >  you can not ...
---
If in sql 2008 environment you can notcreate or edit Maintenance plans from another machine..
you have installation problem (for example , missed SSIS, maybe not patched sql server 2008)
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33693597
Nobody is talking about SQL 2000.  The poster is using SQL 2008 on his server and SSMS 2008 on his PC.

I think you are still missing what the poster said.  He has SSMS installed on his desktop machine.  He is connecting to the server in SSMS and creating a maintenance plan.  That isn't working.

From another PC with SSMS installed he is connecting to the server in SSMS and *is* able to create a maintenance plan.
0
 

Author Comment

by:atlasdev
ID: 33693697
Hi, everyone,
I really appreciate your effort on my case here.
My problem is that I cannot create a maintenance plan on my SQL 2008 server, ABC. However, on a different server, DEF, I could connect to my server ABC via the SSMS installed on DEF. From the SSMS on DEF, I could create a maintenance plan and this plan is working perfectly now.  
However, currently on server ABC, I still cannot create nor modify any maintenance plan.
If I try, I would get an error saying value cannot null like the last picture I attached in this thread.
I think there is a problem with the locally installed SSMS on my server, ABC.
 
 
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33693841
Agreed, atlasdev.  That is exactly where your problem is; SSMS or SSIS on server ABC.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33693996
cnmangus:
did you tell :
<You can't create or edit Maintenance plans from another machine..
>?
if you said- it is wrong in this case
 
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33694011
atlasdev:
are you sure that SSIS is installed on your ABCsql server?
0
 

Author Comment

by:atlasdev
ID: 33694074
Yes. Here is the picure of my SQL 2008 server's config manager.
I also made sure I applied the service pack after the reinstallation of SSMS. The current version of my SQL server is
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)  
4.GIF
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33694091
EugeneZ, in post  33690799 rrjegan17 said that you *can't* create maintenance plans from another machine.

I posted that you *can*
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33695578
ok  - It seems like oposit :)
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33695602
oops typo

ok- It seems like opposite :)
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33697677
Strange, my post at  33691334 shows that I told the poster that he *can*...
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33697878
I am sorry cmangus - > I meant  rrjegan17
Please accept my apology
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33697925
No problem...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33698257
cmangus and Eugenez,

Thanks for that and I researched on that..
Do we need to have the servers registered from the Remote machines SSMS in order to create Maintenance plans on the server remotely ( Have seen this from BOL).
Kindly confirm.
0
 

Author Comment

by:atlasdev
ID: 33701322
rrjegan17:

Shouldn't need to do anything special.  In my case, I just hopped onto another computer with SQL 2k8 installed and operational, started SSMS, but told it to connect to another system - the one I had been having issues on.  It connected with no issues when I used the sa account/PW.  I then tried to create a Maintenance Plan and it worked, while when I did it on the local system it would error out the minute I tried to create a new plan or when I went to complete a Wizard.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33708521
atlasdev:
it is not clear - can you or not create new maint plan on local system ?
probably - not - as per posts above you probably did not install ssis
please check and confirm.
 
0
 

Author Closing Comment

by:atlasdev
ID: 33833518
Although the advice could not help me resolve the issue, at least it helps me eliminate all the other possible causes of this issue.

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 Syntax Error 5 25
Auditing in Azure SQL Database 3 28
Log Backup 2 13
Caste datetime 2 25
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now