Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2030
  • Last Modified:

Unable to delete or access SQL 2008 maintenance plan

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
atlasdev
Asked:
atlasdev
  • 15
  • 12
  • 11
  • +2
1 Solution
 
dpiccoCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Restart SQL Server services and try once.
Try applying SP1 for SQL Server 2008 if it is not applied.
0
 
Eugene ZCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Chris MangusDatabase AdministratorCommented:
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
 
atlasdevAuthor Commented:
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
 
Eugene ZCommented:
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
 
atlasdevAuthor Commented:
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
 
Eugene ZCommented:
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
 
atlasdevAuthor Commented:
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
 
Eugene ZCommented:
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
 
atlasdevAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
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
 
atlasdevAuthor Commented:
I have removed all maintenance plans via query.
0
 
atlasdevAuthor Commented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
atlasdevAuthor Commented:
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
 
atlasdevAuthor Commented:
OK. I reinstalled the SSMS but I still cannot create a maintenance plan. I still get the same error message.
Any more suggestion?
0
 
Chris MangusDatabase AdministratorCommented:
Because the maintenance plans are truly SSIS I would also reinstall Integration Services and Business Intelligence Development Studio as well.
0
 
atlasdevAuthor Commented:
I have reinstalled Integration services before and my server does not have Business Intelligence Development Studio.
0
 
atlasdevAuthor Commented:
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
 
atlasdevAuthor Commented:
I also just did a full repair from the DVD for my SQL 2008 server. However, the same issue remains. :(
0
 
Chris MangusDatabase AdministratorCommented:
It may be time to open a case with Microsoft.
0
 
Eugene ZCommented:
try unistall tools and ssis  first - before install
also make sure to install sp1+ patches for sql2008 after the tools are installed ...
 
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
Chris MangusDatabase AdministratorCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
Chris MangusDatabase AdministratorCommented:
Are you asking me to check it out?  I do this on almost a daily basis.
0
 
Eugene ZCommented:
<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
 
Chris MangusDatabase AdministratorCommented:
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
 
atlasdevAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
Agreed, atlasdev.  That is exactly where your problem is; SSMS or SSIS on server ABC.
0
 
Eugene ZCommented:
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
 
Eugene ZCommented:
atlasdev:
are you sure that SSIS is installed on your ABCsql server?
0
 
atlasdevAuthor Commented:
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
 
Chris MangusDatabase AdministratorCommented:
EugeneZ, in post  33690799 rrjegan17 said that you *can't* create maintenance plans from another machine.

I posted that you *can*
0
 
Eugene ZCommented:
ok  - It seems like oposit :)
0
 
Eugene ZCommented:
oops typo

ok- It seems like opposite :)
0
 
Chris MangusDatabase AdministratorCommented:
Strange, my post at  33691334 shows that I told the poster that he *can*...
0
 
Eugene ZCommented:
I am sorry cmangus - > I meant  rrjegan17
Please accept my apology
0
 
Chris MangusDatabase AdministratorCommented:
No problem...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
atlasdevAuthor Commented:
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
 
Eugene ZCommented:
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
 
atlasdevAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 15
  • 12
  • 11
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now