Cannot delete maintenance plan or scheduled job

[Scott]

Hi Folks,

SQL Server 2005 SP2 32bit
Windows 2003

I created a MP (backup, integrity) and later renamed the server. The MP jobs
started failing as a result, so I created another MP to replace the failing
one. The new MP works fine. The problem now is I cannot delete the older
failing MP or individual jobs. Even though I modified the connection
properties within the failing MP, it still seems to be trying to connect to
the renamed connection.

sysservers does not contain the previous server name - I changed it to
reflect the new server name after renaming the server.

Thanks in advance.

[Aaron Bertrand [SQL Server MVP]]

When you right-click the job in SQL Server Agent / Jobs in Object Explorer,
and click "Delete", what happens?





On 1/7/09 11:34 AM, in article

Post by Scott
Hi Folks,
SQL Server 2005 SP2 32bit
Windows 2003
I created a MP (backup, integrity) and later renamed the server. The MP jobs
started failing as a result, so I created another MP to replace the failing
one. The new MP works fine. The problem now is I cannot delete the older
failing MP or individual jobs. Even though I modified the connection
properties within the failing MP, it still seems to be trying to connect to
the renamed connection.
sysservers does not contain the previous server name - I changed it to
reflect the new server name after renaming the server.
Thanks in advance.

[Scott]

Aaron,

An error dialogue box pops up with the following message

“Exception has been thrown by the target of the invocation (mscorlib) An
error has occurred while establishing a connection to the server …..”

When I review Managed Connections from within the SSIS package (MP) , the
local connection is grayed out, but I can see the old server name is still
being referenced . I think that this is my issue – it is still trying to
connect to the previous server name. I tried adding another connection and
then deleting it, but to no avail.

I guess the real question is, how do you change the properties of the local
connection within the MP?

Post by Aaron Bertrand [SQL Server MVP]
When you right-click the job in SQL Server Agent / Jobs in Object Explorer,
and click "Delete", what happens?
On 1/7/09 11:34 AM, in article

Post by Scott
Hi Folks,
SQL Server 2005 SP2 32bit
Windows 2003
I created a MP (backup, integrity) and later renamed the server. The MP jobs
started failing as a result, so I created another MP to replace the failing
one. The new MP works fine. The problem now is I cannot delete the older
failing MP or individual jobs. Even though I modified the connection
properties within the failing MP, it still seems to be trying to connect to
the renamed connection.
sysservers does not contain the previous server name - I changed it to
reflect the new server name after renaming the server.
Thanks in advance.

[Aaron Bertrand [SQL Server MVP]]

³Exception has been thrown by the target of the invocation (mscorlib) An
error has occurred while establishing a connection to the server Š..²

When right-clicking the job and selecting DELETE? Have you established a
NEW connection to the server in Management Studio since renaming it? The
job shouldn't care about your maintenance plan or the connection(s) within
it... it is merely an extension. Kind of like changing the engine on your
Mustang and then expecting the tires to complain when you try to remove
them.

I guess the real question is, how do you change the properties of the local
connection within the MP?

Don't know. Not a big fan of maintenance plans (in fact recently eliminated
all of the maintenance plans at a client site), and further still, I am not
a big fan of renaming servers, too many things (like this) can go wrong and
are hard to test in advance.

A

[Scott]

Interesting. Care to elaborate Aaron as to why you are not a fan of MP's, and
why you would remove them from your clients site? For backups and integrity
checks MP's take care of things quite simply and without fail - at least this
has been my experience over the past 10 years or so.

Scott

Post by Aaron Bertrand [SQL Server MVP]

³Exception has been thrown by the target of the invocation (mscorlib) An
error has occurred while establishing a connection to the server Š..²

When right-clicking the job and selecting DELETE? Have you established a
NEW connection to the server in Management Studio since renaming it? The
job shouldn't care about your maintenance plan or the connection(s) within
it... it is merely an extension. Kind of like changing the engine on your
Mustang and then expecting the tires to complain when you try to remove
them.

I guess the real question is, how do you change the properties of the local
connection within the MP?

Don't know. Not a big fan of maintenance plans (in fact recently eliminated
all of the maintenance plans at a client site), and further still, I am not
a big fan of renaming servers, too many things (like this) can go wrong and
are hard to test in advance.
A

[Aaron Bertrand [SQL Server MVP]]

Post by Scott
Interesting. Care to elaborate Aaron as to why you are not a fan of MP's, and
why you would remove them from your clients site? For backups and integrity
checks MP's take care of things quite simply and without fail - at least this
has been my experience over the past 10 years or so.

Lack of control, buggy... I still see cases where the MPs don't delete files
older than what you specify. They also just flat-out fail if you check
"back up logs" and you have a single database that is in simple mode. If
you want to back up different databases on different schedules you need to
manage multiple plans. BACKUP DATABASE is not all that hard to script
yourself. Also if you need to manage your system from multiple machines,
you need to make sure all systems are patched with the latest SSMS updates.
Were you around for all the debacles around SQL Server 2005 SP2, and what a
mess they made with maintenance plans during several post-SP2 patches as
well?

[Aaron Bertrand [SQL Server MVP]]

(And when they fail you sometimes have to dig pretty hard to find the true
problem. The error messages leave a lot to be desired.)

Post by Aaron Bertrand [SQL Server MVP]

Post by Scott
Interesting. Care to elaborate Aaron as to why you are not a fan of MP's, and
why you would remove them from your clients site? For backups and integrity
checks MP's take care of things quite simply and without fail - at least this
has been my experience over the past 10 years or so.

Lack of control, buggy... I still see cases where the MPs don't delete files
older than what you specify. They also just flat-out fail if you check
"back up logs" and you have a single database that is in simple mode. If
you want to back up different databases on different schedules you need to
manage multiple plans. BACKUP DATABASE is not all that hard to script
yourself. Also if you need to manage your system from multiple machines,
you need to make sure all systems are patched with the latest SSMS updates.
Were you around for all the debacles around SQL Server 2005 SP2, and what a
mess they made with maintenance plans during several post-SP2 patches as
well?

[kaktusz]

Hi Scott!

I had the same problem, and succesfully resolved with this:
http://www.dbforums.com/microsoft-sql-server/1627954-renamed-sql-2005-server.html#post6325252

To get the MP IDs:
use msdb
go
select * from sysmaintplan_plans


Then:
---
use msdb
go
declare @abcd as varchar(100)
SET @abcd = 'FC752629-AEFA-43B8-A1A5-F14C6E788F12' -- Paste here
the next ID and then the next...
delete from sysmaintplan_log where plan_id = @abcd
delete from sysmaintplan_subplans where plan_id = @abcd
delete from sysmaintplan_plans where id = @abcd
---

bye,
Bálint