October 21, 2012 at 4:27 pm
I am getting the error below when I setup a job using the Maintenance Plan (SQL 2008 SP2). I have tried to create backups, integrity checks, update stats and index reorgs and get the same error every single time. I can setup backup jobs or other jobs manually that run under the sql agent account but not through a maintenance plan. The jobs are setup to run as the sql server agent account which has sys admin privs. The account is a local account on the server and belongs to the Users group. A little background...I received this server that is an image of another server. I noticed that the server name was incorrect when I did a select @@servername or sp_helpserver so I ran the sp_dropserver and sp_addserver to correctly name the server. I have also given the agent account privileges on the necessary directories. I am at a lost on how to fix this error. Also, the above steps worked on another server and jobs created from maintenance plans work fine. It's just this one server that isn't working now. I do not know if this is a permission issue or a SSIS issue.
Any help is greatly appreciated.
Error
Executed as user: xxxxxx\sqlsrvagt. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:18:27 PM Error: 2012-10-19 14:18:28.47 Code: 0xC00291EC Source: {DFAFCDDB-E46A-4610-A902-A2F91DE4D79E} 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: 2012-10-19 14:18:28.48 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: 2012-10-19 14:18:28.51 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2012-10-19 14:18:28.51 Code: 0xC0024104 Source: {9D9F5F95-7345-4A2B-BD70-5D91C8C08CD0} Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2012-10-19 14:18:28.51 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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 DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:18:27 PM Finished: 2:18:28 PM Elapsed: 1.156 seconds. The package execution failed. The step failed.
October 22, 2012 at 2:17 am
Are you trying to run a preexisting maintenance plan, or are you creating one from scratch and are getting this error when trying to save or run it? Also, just to check, is the SSMS you are connecting from the same version and patch level as the instance of SQL Server you are receing the error on? I have commonly seen where different versions or even SP levels of SSMS will generate errors when trying to create and/or modify maintenance plans.
Joie Andrew
"Since 1982"
October 22, 2012 at 5:45 am
sqlnovice2001 (10/21/2012)
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.
its clearly stating that you havent provided proper permissions here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 22, 2012 at 8:59 am
The sql agent account has sysadmin privs. What other privileges does it need?
October 23, 2012 at 6:49 am
I also want to add that I tested running the services under the local system account (just to test, I know this isn't an option) to see if the job would still fail and it did. Same error. So it's not just when creating maintenance plans under the sql agent account but also as local system and local system has sysadmin privs as well.
Executed as user: *******\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.4000.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:40:54 AM Error: 2012-10-23 07:41:00.74 Code: 0xC00291EC Source: {2A18DE07-1322-40AC-AA95-9F41C2FA8ABD} 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: 2012-10-23 07:41:00.74 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: 2012-10-23 07:41:01.25 Code: 0xC0024104 Source: Check Database Integrity Task Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Error: 2012-10-23 07:41:01.33 Code: 0xC0024104 Source: {AB2FE0CE-1CA4-4E16-9FC0-9172D601D902} Description: The Execute method on the task returned error code 0x80131604 (Exception has been thrown by the target of an invocation.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error Warning: 2012-10-23 07:41:01.33 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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 DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:40:54 AM Finished: 7:41:01 AM Elapsed: 6.688 seconds. The package execution failed. The step failed.
October 23, 2012 at 7:05 am
Are you trying to run a preexisting maintenance plan, or are you creating one from scratch and are getting this error when trying to save or run it?
You never answered this question. You stated that you inherited this server as an image of another server. I am curious if the "local server connection" that the error is saying is failing was setup when the maintenance plan was created to connect to an instance name which is no longer valid.
If you create a new maintenance plan does it fail with the same error, or does it succeed?
Joie Andrew
"Since 1982"
October 23, 2012 at 8:13 am
I am creating new maintenance plans. There were no prior jobs or maintenance plans. Ironically, I can create a manual job, for example to cycle the errorlog or a backup. Also, I changed the server name from the old one to the new server name using sp_dropserver and sp_addserver. So as far as I can tell, the local server name is correct. I don't know where else to check to see what server name it could be pulling from.
Also, the version of SSMS is 10.0.4064, same as the SQL Server version.
October 23, 2012 at 8:24 am
Have you checked out the following:
SSIS package does not run when called from a SQL Server Agent job step
Joie Andrew
"Since 1982"
October 23, 2012 at 9:02 am
SSIS is not installed on the server. Not sure if these steps would apply. This is just utilizing maintenance plans. Integration Services isn't required to be installed correct? It's not installed on other servers that just utilize maintenance plans.
October 23, 2012 at 10:19 am
No, SSIS is not required to run maintenance plans, but maintenance plans actions in SSMS are a subset of what can be done in SSIS and a maintenance plan built in SSMS is a SSIS package. I do not think it would not hurt to troubleshoot based on the article.
For more info
Joie Andrew
"Since 1982"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply