May 4, 2012 at 9:32 am
I am having trouble getting my Maintenance Plans to work, the error is:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/04/2012 16:25:38,RDHtest.Subplan_1,Error,0,MELWEBDBS01,RDHtest.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:01:58,0,0,,,,0
05/04/2012 16:25:38,RDHtest.Subplan_1,Error,1,MELWEBDBS01,RDHtest.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 16:25:38 Error: 2012-05-04 16:26:27.86 Code: 0xC00291EC Source: {38253726-DA71-466B-B62D-D1EF3BBE96F1} 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-05-04 16:26:27.86 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-04 16:26:48.91 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-04 16:27:36.45 Code: 0xC0024104 Source: {D090F1F7-498C-411B-9B24-59D06D504B58} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-04 16:27:36.45 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 16:25:38 Finished: 16:27:36 Elapsed: 117.625 seconds. The package execution failed. The step failed.,00:01:58,0,0,,,,0
Any ideas?
May 5, 2012 at 3:40 pm
Anyone??
May 6, 2012 at 6:06 am
robert.hall (5/4/2012)
I am having trouble getting my Maintenance Plans to work, the error is:Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/04/2012 16:25:38,RDHtest.Subplan_1,Error,0,MELWEBDBS01,RDHtest.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:01:58,0,0,,,,0
05/04/2012 16:25:38,RDHtest.Subplan_1,Error,1,MELWEBDBS01,RDHtest.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 16:25:38 Error: 2012-05-04 16:26:27.86 Code: 0xC00291EC Source: {38253726-DA71-466B-B62D-D1EF3BBE96F1} 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-05-04 16:26:27.86 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-04 16:26:48.91 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-04 16:27:36.45 Code: 0xC0024104 Source: {D090F1F7-498C-411B-9B24-59D06D504B58} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-04 16:27:36.45 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 16:25:38 Finished: 16:27:36 Elapsed: 117.625 seconds. The package execution failed. The step failed.,00:01:58,0,0,,,,0
Any ideas?
Please focus on highlighted areas for troubleshooting.
May 6, 2012 at 6:30 am
What should I look at in terms of permissions?
May 6, 2012 at 6:47 am
Please verify if 'platinum' and 'MELWEB\MEL-SV-SQL-WEB' has sufficient permission for step 1 (Subplan_1) in Maintenance job. For Example if it’s a backup job the user should be granted db_backupoperator role and should have OS read / write permission on specific folder where you are creating backups and connect permission on SQL Server.
May 6, 2012 at 7:01 am
Thanks for that, how do I check the sql connect permission?
May 6, 2012 at 7:30 am
select pr.name 'Login', sp.permission_name 'Permission'
from sys.server_permissions sp
inner join sys.server_principals pr
on pr.principal_id = sp.grantee_principal_id
where pr.name = 'test' -- Replace your login names here
select sp.name 'Login' , sr.name 'Role'
from sys.server_role_members srm
inner join sys.server_principals sp
on srm.member_principal_id = sp.principal_id
inner join sys.server_principals sr
on srm.role_principal_id = sr.principal_id
where sp.name = 'test' -- Replace your login names here
select pr.name 'User', dp.permission_name 'Permission'
from sys.database_permissions dp
inner join sys.database_principals pr
on pr.principal_id = dp.grantee_principal_id
where pr.name = 'test' -- Replace your user names here
select dp.name 'User' , dr.name 'Role'
from sys.database_role_members drm
inner join sys.database_principals dp
on drm.member_principal_id = dp.principal_id
inner join sys.database_principals dr
on drm.role_principal_id = dr.principal_id
where dp.name = 'test' -- Replace your user names here
May 9, 2012 at 4:37 am
I was able to check that the user could connect to SQL, but now the job still errors:
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
05/09/2012 11:25:07,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:02:36,0,0,,,,0
05/09/2012 11:25:07,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:25:09 Error: 2012-05-09 11:26:27.11 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-05-09 11:26:27.11 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-09 11:27:21.99 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-09 11:27:43.00 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-09 11:27:43.00 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 11:25:09 Finished: 11:27:43 Elapsed: 153.547 seconds. The package execution failed. The step failed.,00:02:36,0,0,,,,0
05/04/2012 16:55:25,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:01:58,0,0,,,,0
05/04/2012 16:55:25,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 16:55:25 Error: 2012-05-04 16:56:14.77 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-05-04 16:56:14.77 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-04 16:56:35.82 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-04 16:57:23.34 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-04 16:57:23.34 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 16:55:25 Finished: 16:57:23 Elapsed: 117.531 seconds. The package execution failed. The step failed.,00:01:58,0,0,,,,0
05/04/2012 16:11:04,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:01:57,0,0,,,,0
05/04/2012 16:11:04,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 16:11:04 Error: 2012-05-04 16:11:53.08 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-05-04 16:11:53.08 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-04 16:12:14.15 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-04 16:13:01.67 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-04 16:13:01.67 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 16:11:04 Finished: 16:13:01 Elapsed: 117.547 seconds. The package execution failed. The step failed.,00:01:57,0,0,,,,0
05/04/2012 16:10:16,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:00:00,0,0,,,,0
05/04/2012 16:10:16,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account. The step failed.,00:00:00,0,0,,,,0
05/04/2012 15:21:16,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:02:18,0,0,,,,0
05/04/2012 15:21:16,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 15:21:16 Error: 2012-05-04 15:22:25.02 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-05-04 15:22:25.02 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-04 15:22:46.12 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-04 15:23:33.66 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-04 15:23:33.66 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 15:21:16 Finished: 15:23:34 Elapsed: 137.109 seconds. The package execution failed. The step failed.,00:02:18,0,0,,,,0
05/01/2012 09:15:37,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:02:45,0,0,,,,0
05/01/2012 09:15:37,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 09:15:40 Error: 2012-05-01 09:17:12.13 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-05-01 09:17:12.13 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-05-01 09:17:33.40 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-05-01 09:18:20.94 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-05-01 09:18:20.94 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 09:15:40 Finished: 09:18:20 Elapsed: 160.688 seconds. The package execution failed. The step failed.,00:02:44,0,0,,,,0
04/27/2012 12:43:31,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:01:58,0,0,,,,0
04/27/2012 12:43:31,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:43:31 Error: 2012-04-27 12:44:20.49 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-04-27 12:44:20.49 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-04-27 12:44:41.55 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-04-27 12:45:29.07 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-04-27 12:45:29.07 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 12:43:31 Finished: 12:45:29 Elapsed: 117.75 seconds. The package execution failed. The step failed.,00:01:58,0,0,,,,0
04/27/2012 12:18:54,Full Morning Backup Plan 00_30.Subplan_1,Error,0,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,(Job outcome),,The job failed. The Job was invoked by User platinum. The last step to run was step 1 (Subplan_1).,00:02:34,0,0,,,,0
04/27/2012 12:18:54,Full Morning Backup Plan 00_30.Subplan_1,Error,1,MELWEBDBS01,Full Morning Backup Plan 00_30.Subplan_1,Subplan_1,,Executed as user: MELWEB\MEL-SV-SQL-WEB. Microsoft (R) SQL Server Execute Package Utility Version 10.50.2500.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:18:57 Error: 2012-04-27 12:20:16.96 Code: 0xC00291EC Source: {0491F28B-5254-44F2-9BAD-826555F5FEFF} 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-04-27 12:20:16.96 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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-04-27 12:20:40.25 Code: 0xC0024104 Source: Back Up Database (Full) Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2012-04-27 12:21:27.83 Code: 0xC0024104 Source: {7CE565AE-5B51-45C7-A08D-198BC1CA997F} Description: The Execute method on the task returned error code 0x80131904 (A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2012-04-27 12:21:27.83 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> 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: 12:18:57 Finished: 12:21:27 Elapsed: 150.938 seconds. The package execution failed. The step failed.,00:02:34,0,0,,,,0
May 9, 2012 at 4:45 am
have you looked at the error message ?
....configured to allow remote connections. (provider: Named Pipes Provider<c/> error: 40 - Could not open a connection to SQL Server)). The......
check in sql server configuration manager that remote connections are allowed and that named pipes is turned on
MVDBA
May 9, 2012 at 5:50 am
Thanks Mike,
Please excuse my ignorance, I am not very experienced with SQL Server, but have been tasked with setting up some Maintenance Plans.
Also, I have to wait for my sysadmin guy to connect me to the device, plus it is hosted in a remote server somewhere and rdp sessions are slow.
I did see the error, can you advise where to check the instance is configured to allow remote connections, and that Named Pipes are turned on?
Thanks
Robert
May 9, 2012 at 6:59 am
robert.hall (5/9/2012)
Thanks Mike,Please excuse my ignorance, I am not very experienced with SQL Server, but have been tasked with setting up some Maintenance Plans.
Also, I have to wait for my sysadmin guy to connect me to the device, plus it is hosted in a remote server somewhere and rdp sessions are slow.
I did see the error, can you advise where to check the instance is configured to allow remote connections, and that Named Pipes are turned on?
Thanks
Robert
you need to start the SQL server configuration manager from the start menu - it should be self explanatory when you get in there
look for SQL SERVER NETWORK CONNECTIONS and click on PROTOCOLS
but do some reading first and make sure you have secured the server before turning on any protocols and make sure you don't have a weak "sa" password - there are dozens of articles about securing SQL server on the web
MVDBA
May 9, 2012 at 9:05 am
I have noted the following:
Shared Memory
Named Pipes
TCP/IP
were all Enabled - see screenshot attached.
not sure what to check next.
Thanks
Robert
May 9, 2012 at 9:19 am
can you post screenshots of the contents of subplan_1 ?
MVDBA
May 9, 2012 at 9:36 am
Can do, but it will have to be tomorrow now, I don't have a login and have to wait for the sysadmin guy to connect me.
The jobs are a full backup of selected databases in the early AM and late PM.
Thanks
Robert
May 10, 2012 at 3:42 am
USE [msdb]
GO
/****** Object: Job [Full Morning Backup Plan 00_30.Subplan_1] Script Date: 05/10/2012 10:39:55 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 05/10/2012 10:39:55 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Full Morning Backup Plan 00_30.Subplan_1',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'Database Maintenance',
@owner_login_name=N'platinum', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Subplan_1] Script Date: 05/10/2012 10:39:56 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Subplan_1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'SSIS',
@command=N'/Server "$(ESCAPE_NONE(SRVR))" /SQL "Maintenance Plans\Full Morning Backup Plan 00_30" /set "\Package\Subplan_1.Disable;false"',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply