March 16, 2009 at 3:44 pm
We moved from Windows Server 2003 x32 / SQL 2005 Standard to Windows Server 2008 X64 / SQL 2005 Standard x64. Also, the name of the computer was changed during this move.
We copied the master, msdb, and resource databases over. We had to detach and reattach all the databases and they started working fine. However, the jobs created by the maintenance plans do not work. They get a connection error (see log below). I think this is because the server specified in the Manage Connections is the old computer name. How do I change it? or fix my problem without redoing all the maintenance plans?
Date3/16/2009 3:27:00 PM
LogJob History (TransactionLogs.MAUC)
Step ID1
Serverxxxxxx
Job NameTransactionLogs.MAUC
Step NameMAUC
Duration00:00:46
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: xxxxxx\SYSTEM. ...sion 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 3:27:00 PM Error: 2009-03-16 15:27:15.53 Code: 0xC00291EC Source: {61F009BD-20C0-4C29-9EDE-B1C777804C04} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2009-03-16 15:27:15.53 Code: 0x80019002 Source: 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: 2009-03-16 15:27:32.23 Code: 0xC0024104 Source: TranLogs Description: ... The package execution fa... The step failed.
March 16, 2009 at 3:57 pm
You will have to delete the maintenance plans and redo them.
March 16, 2009 at 5:32 pm
It is much easier to recreate the plans, but - there is one option that I know of that may fix the problem.
Open BIDS and create a new Integration Services project. Doesn't matter what you name it.
Once open, from Solution Explorer - right click the package node and Add Existing Package.
From the dialog box, select your server - find the maintenance plan and select that plan.
Once the maintenance plan has been imported into your new project - double click on it to open it. Modify the connection - but do not change the name. Only change the server information for the connection and change the name to the correct server.
Now, from the file menu - save a copy of the maintenance plan back to the server. I would suggest saving it with a new name just to be safe.
Once the package has been saved, refresh the maintenance node in SSMS and see if the new maintenance plan exists. Open the new maintenance plan and verify everything (connection information, databases selected, etc...). Once all is verified - save the plan and verify that the jobs have been created.
And finally, once all of that is done - test, test, test - and once again, test...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 18, 2009 at 7:18 pm
In my case when I am trying to save the newly created plan(copy of old plan as u suggested) its not creating the job and failing with message "Rename failed for planname_subplan1)". After that old plan gets deleted along with the job.
Why is this happening? Plz help.
Thanks,
MJ
March 18, 2009 at 7:46 pm
Unfortunately, you are going to have to recreate the plans. BTW - do you realize that you have probably already spent more time on trying to fix these than it would have taken to just recreate them?
Just a thought...;)
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 26, 2009 at 2:31 pm
Thank you for the responses.
Jeffrey Williams: I did as you suggested and it almost worked! Right at the end of the job I get an unknown error.
And so I attempted to delete the plans, but that was not without problems. But someone else had the same problem on this list and I was able to get them all deleted.
You are correct that I spend more time working on this than creating new plans, however, I was hoping to have more knowledge the next time I had to move servers. Well, I do have more knowledge! Migrating is not easy and forget out about the maintenance plans!
Thank you for your help.
March 27, 2009 at 6:11 am
You could tried registering dts.dll
here is what u need to do Just copy this and run it
regsvr32 "\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply