March 7, 2009 at 3:22 am
Hi,
I had a few maintenance plan defined in SQL2005 server. Then I renamed my Server ( windows and SQLserver). Now, when I want to drop those maintenance plan I'm getting error.
I have even modified the owner of the related jobs but no success yet
Could you tell me how I can remove those maintenance plans in such situation?
March 7, 2009 at 8:33 am
This thread says you can't
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297149
But found this
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
Please check it out first before deleting anything
here the thread to it
March 7, 2009 at 12:16 pm
Hi,
It might be similar to an issue I encountered with SQL 2000 ..
http://sqllearnings.blogspot.com/2009/01/error-14274-cannot-add-update-or-delete.html
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 11:03 pm
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
That gave me the answer Thank you 🙂
March 7, 2009 at 11:06 pm
Mohit (3/7/2009)
Hi,It might be similar to an issue I encountered with SQL 2000 ..
http://sqllearnings.blogspot.com/2009/01/error-14274-cannot-add-update-or-delete.html
Mohit.
I had tried this before but didn't resolve my problem
Thanks anyway 🙂
March 7, 2009 at 11:29 pm
Heh I expected as much; I have yet to rename any SQL 2005 servers. I am sure I'll get around to it; I'll have to keep this little note in mind 🙂 Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 8, 2009 at 12:31 am
Question ...
Was error similar to this:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 53)
?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 8, 2009 at 12:45 am
Mohit (3/8/2009)
Question ...Was error similar to this:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 53)
?
nope 😉
March 8, 2009 at 12:53 am
Nuts :p. I guess I'll have to butcher a server LOL thanks for the post :). I made a note about this on me blog so I wouldn't forget it :). Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 8, 2009 at 8:55 am
Glad it helped i remembered doing the same thing in SQL 2000 with the same thing so i knew there be something similar in 2005.
June 8, 2009 at 2:36 pm
Thanks much.. fixed my issue:-P
June 8, 2009 at 4:03 pm
This is an area in SQL 2005 that just does not hang together as well as it did in SQL 2000, that is copying msdb to another server of a different name (which is effectively what you have done by renaming the server at the OS level). leastways there are no longer quick fixes in this scenario.
In SQL 2000 you could get round this with 'update msdb..sysjobs set originating_server = 'newname' but I have not found any simple equivalent of this for SQL 2005. Manually deleting plans\jobs via SQL commands and then rebuilding plans from scratch appears to be the only way short of renaming the server to match.
I have seen cases reported where people have copied msdb over to a new server and attempted to amend\delete plans and have in fact found the actions took effect on the original server, so be warned
What MS have failed to warn us of in my opinion is the effect this could have on peoples DR plans. There must be a lot of people out there who's DR plans include restoring msdb and have upgraded to SQL 2005, and are in for a nasty surprise if they ever have to failover in anger and run from the DR server for any length of time. If you are on SQL2005 and above and your DR plans are based on restoring system databases to a failover server I suggest the last phase of the process be rename the windows server to that of the primary.
---------------------------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply