Maint Plan - Failed to acquire connection "Local server connection"
I created a single step plan that does integrity checks. It fails with the error below. I created a new connection using our clusters virtual sql name.
Executed as user: MYDOMAIN\agentaccount. ...n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:10:06 PM Error: 2008-02-15 13:10:49.02 Code: 0xC00291EC Source: {0CF32F3D-A8D1-492A-9C0F-AD4E0FC67D14} 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: 2008-02-15 13:10:49.02 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: 2008-02-15 13:11:31.26 Code: 0xC0024104 Source: Check Database Integrity Ta... The package execution fa... The step failed.
The agent and db are running under domain accounts.
Thanks for your help.
February 15th, 2008 11:33pm
What proxy and credentionals are you using?
Free Windows Admin Tool Kit Click here and download it now
February 16th, 2008 2:21pm
That sounds like a permission issue. Check that the account running the SQL Server agent have access to the resources specified in the connection managers. Check the way the authentication mode in connection manager. This KB article describe common issues when running packages via SQL Server agent:: http://support.microsoft.com/kb/918760
February 16th, 2008 4:17pm
Did you find a solution for this? I'm having the same problem.
Thanks!
Ann
Free Windows Admin Tool Kit Click here and download it now
March 4th, 2008 7:41pm
What do you mean by Connection Manager? Do you mean Configuration Manager? Where would I look in the Config?
Thanks!
ann
March 4th, 2008 7:52pm
I got the same error.
But this is on a Maintenance Plan that
1) Removes old backup files
2) Backup Transaction Log - All user databases
Databases have been created after the Maintenance Plan was created. Bothe in 2000 (8.0)and 2005 (9.0)compatibility level.
The failure came in step 2) when trying to backup the last database which runs in 2000 compatibility level.
This job runs every hour and it ran successful the next time, and continues to do so.
Whatis the cause?
Free Windows Admin Tool Kit Click here and download it now
May 22nd, 2008 9:59am
I'm delaing with the same issue. I imported maintenance plans from a separate SQL server 2005 into SQL server 2008. When I look at the maintanance plans, I do have each task updated toactually use the new server but I still get the error message but here's the kicker, If, when you're editing the maintenance plan, you can look at the toolbar and see the "Manage Connection" option at the top, you will see what the "Local Server Connection" is set to, in my case, it's still set to my old SQL servers name but as all know, you can't edit it. So here's how I tried to fix on SQL server 2008...Open SQL server Management Studio, select "Integration Services" from the dropdown and open.In my case, for my maintenance plans, I navigated to Stored Packages > MSDB > Maintenance Plans and right clicked on my first package and selected "Run Package".I selected the "Connection Managers", found the "Local serverconnection" and checked it off and edited the connection string but unfortunately, that only, works for that run.Tried the upgrade package route with "Upgrade connection strings to use new provider names" and still no luck.The only way I can see at getting at the connection is by exporting the package into a file format and using Visual Studio Business Intelligence tool to edit the file and update the "Local server connection" value, save the file and import.I've done it but still need to test...if anyone knows of better way, add to the thread.HTHDave
As followup, it does work by editing in Visual Studio BI but you had to reset DB selections for tasks...toss up for just recreating each one but in my case, need to try to keep jobs in sync since dealing with mirrored DB and need jobs to match except for connections...Dave
September 24th, 2009 7:28am
Dave,I had a similar issue and resolved it by performing the following steps using SS Management Studio.1. Connected to a remoteSS Integration Server and imported the maintenance plans from the SQL Server instance I used as the source.2. Using the same remote SSIS, I exported the maintenance plan to the target SQL Server instance.3. After connecting to the target instance, I modified the maintenance plan and added a new connection (via "Manage Connections" button)4. For each task under the subplan, I copied the backup folder text, selected the new connection and databases, and pasted the backup folder text (overlaying what was there). Finally, saved the maintenance plan.NOTE: We do not use the default backup folder location. For cleanup tasks, the folders do not change to the default but you still must change the connection.5. Under the SQL Server Agent > Jobs list of the target instance, I found the corresponding job for the maintenance plan. Opened the properties, edited each step of the job; checked the new connection under the "data sources" tab and saved the modification.Once completed, I was able to execute the maintenance plan and the backup files were created on the file system of the target instance.
Free Windows Admin Tool Kit Click here and download it now
December 3rd, 2009 2:22am
Check the connection on the data sources tab in the Job Step Properties and make sure under connection string is shows the correct instance. I have had this error in the past and was caused when I changed the name of an instance. If the connection
string is wrong, you will need to recreate the Maintenance Plan.
February 18th, 2011 8:58pm