Maintenance Plan won't run, delete, or modify

Had to replace leased server hardware running Server2003, Sql2005. Had
machine Server B, set up sql2005, set up maintenance plan to do full backup
on all user dbs every night to Server C network share. Worked great for over
a week. Came time to do the swamp, brought down old server, renamed Server B
to Server A, ran:
EXEC sp_dropserver '<old_name>'
GO
EXEC sp_addserver '<new_name>', 'local'
GO
All other functions to the database seem to be working fine. Was using a
network administrator account - same one that worked before the name change
so the SID is the same. Unfortunately, I decided to try and delete and start
over in case it was just a gliche. Now, the history seems to be gone, but I
can't actually delete the job or the maintenance plan. Get an error "Does
not allow remote connections." even when I'm physically sitting at the
machine.
Now, maintenance plans won't run - can't delete them, can't modify them.
Other dts packages that execute a query or bring in data from other
datasources work just fine.
Help!?Have a litte more information. I successfully recreated the two maintenance
plans and with one minor problem, they work fine. I'll deal with the new
ones later.
But, since I've pretty much got new plans for system and user databases, I'd
like to delete the originals, but can't. For the systemMtnc, when I try to
delete, I get this error when I'm physically sitting at the machine:
"Exception has been thrown by the target of an invocation(mscorlib). An
error has occurred while establishing a connection to the server. When
connecting to sql server 2005, this failure may be cause by the fact that
under the default settings, sql server does not allow remote connecitons.
(provider: tcp provider, error: 0 no such host is know) error 11001
I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
won't let me. Any ideas?
"Janet" wrote:
> Had to replace leased server hardware running Server2003, Sql2005. Had
> machine Server B, set up sql2005, set up maintenance plan to do full backup
> on all user dbs every night to Server C network share. Worked great for over
> a week. Came time to do the swamp, brought down old server, renamed Server B
> to Server A, ran:
> EXEC sp_dropserver '<old_name>'
> GO
> EXEC sp_addserver '<new_name>', 'local'
> GO
> All other functions to the database seem to be working fine. Was using a
> network administrator account - same one that worked before the name change
> so the SID is the same. Unfortunately, I decided to try and delete and start
> over in case it was just a gliche. Now, the history seems to be gone, but I
> can't actually delete the job or the maintenance plan. Get an error "Does
> not allow remote connections." even when I'm physically sitting at the
> machine.
> Now, maintenance plans won't run - can't delete them, can't modify them.
> Other dts packages that execute a query or bring in data from other
> datasources work just fine.
> Help!?|||Found this and it worked like a charm - hope it helps someone else.
1. Select the ID with the select statement
select * from sysmaintplan_plans
2. Replace with the selected ID and run the delete statements
delete from sysmaintplan_log where plan_id = ''
delete from sysmaintplan_subplans where plan_id = ''
delete from sysmaintplan_plans where id = ''
3. Delete the SQL Server Jobs with the Management Studio
"Janet" wrote:
> Have a litte more information. I successfully recreated the two maintenance
> plans and with one minor problem, they work fine. I'll deal with the new
> ones later.
> But, since I've pretty much got new plans for system and user databases, I'd
> like to delete the originals, but can't. For the systemMtnc, when I try to
> delete, I get this error when I'm physically sitting at the machine:
> "Exception has been thrown by the target of an invocation(mscorlib). An
> error has occurred while establishing a connection to the server. When
> connecting to sql server 2005, this failure may be cause by the fact that
> under the default settings, sql server does not allow remote connecitons.
> (provider: tcp provider, error: 0 no such host is know) error 11001
> I've disabled both jobs, but would prefer to delete the plan/jobs. But, it
> won't let me. Any ideas?
>
> "Janet" wrote:
> > Had to replace leased server hardware running Server2003, Sql2005. Had
> > machine Server B, set up sql2005, set up maintenance plan to do full backup
> > on all user dbs every night to Server C network share. Worked great for over
> > a week. Came time to do the swamp, brought down old server, renamed Server B
> > to Server A, ran:
> > EXEC sp_dropserver '<old_name>'
> > GO
> > EXEC sp_addserver '<new_name>', 'local'
> > GO
> >
> > All other functions to the database seem to be working fine. Was using a
> > network administrator account - same one that worked before the name change
> > so the SID is the same. Unfortunately, I decided to try and delete and start
> > over in case it was just a gliche. Now, the history seems to be gone, but I
> > can't actually delete the job or the maintenance plan. Get an error "Does
> > not allow remote connections." even when I'm physically sitting at the
> > machine.
> >
> > Now, maintenance plans won't run - can't delete them, can't modify them.
> > Other dts packages that execute a query or bring in data from other
> > datasources work just fine.
> >
> > Help!?