Renaming SQL 2005 maint plans and jobs problem

Last Post 26 Apr 2010 06:05 AM by Markus_SQL. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Markus_SQL
New Member
New Member

--
23 Apr 2010 05:55 AM
We had to rename a SQL Server and now the maint plans cannot be deleted or jobs cannot run... Get error of login failed for user... the user not associated with a trusted SQL Server connection.... In SQL 2000 if a sql server was renamed we had to go into msdb and update jobs..... is this the same type of thing in SQL 2005? We are on SQL 2005 SP3 win 2003 sp2
rm
New Member
New Member

--
23 Apr 2010 10:07 AM
Tried modify connection setting in the plan?
gunneyk
New Member
New Member

--
23 Apr 2010 10:48 AM
Did you run the sps to rename the server? Have a look here: http://msdn.microsoft.com/en-us/lib...43799.aspx
Markus_SQL
New Member
New Member

--
23 Apr 2010 12:08 PM
I tried following the Microsoft Doc but it did not help.  I DID end up going into the Maintence plan and updating the connection string and that appears to not have helped...

OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2010-04-23 16:06:17.99 Code: 0xC0024104 Source: Check Database Integrity ... The package execution fa... The step failed.

Why does Microsoft not take into account people renaming servers or refreshing prod to test.  UG!!!!
gunneyk
New Member
New Member

--
23 Apr 2010 02:28 PM
Just one more reason why I dont like the built in Maintenance plans .
rm
New Member
New Member

--
24 Apr 2010 04:36 PM
Tried change job server with sp_add_jobserver?
Markus_SQL
New Member
New Member

--
26 Apr 2010 06:05 AM
I ended up just deleting the Maint plans via this:

-- steps to delete maintenance plans if server renamed or msdb moved to a different server
Step 1: Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS

Step 2: Replace the Id obtained from Step 1 into the below query and delete the entry from log table DELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' '

Step 3: Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below, DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '

Step 4: Finally delete the maintenance plan using the below query where ID is obtained from Step1 DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
You are not authorized to post a reply.

Acceptable Use Policy