3

I've got a recently installed sql 2005 database server (an upgrade from 2000 on the same machine), I'm trying to get maintenance plans set up to reindex etc but they are constantly failing. This is the message I get, I presume some permission for some user somewhere is wrong but not really sure where I should be looking.

Executed as user: 194709-DB2\SYSTEM. ... 9.00.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:45:34 Error: 2009-07-20 12:46:05.42
Code: 0xC00291EC Source: {BDA7B017-98D2-41A4-864E-49A5A9B39855} 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-07-20 12:46:05.42 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-07-20 12:46:05.44 Code: 0xC0024104
Source: Reorganize Index Task
De... The package execution fa... The step failed.

2
  • When you installed sql 2005, did it create a new instance or did it use the same default instance name?
    – jinsungy
    Jul 20, 2009 at 16:14
  • I didn't actually do the installation, but it's running under an instance name of MSSQL2005
    – Robin
    Jul 21, 2009 at 9:29

4 Answers 4

3

Creating the jobs through sa from my machine connecting to the remote SQL Server instance doesn't work. However if I RDP onto the machine and use my Windows credentials to set up the jobs up it works.

I didn't try it, but connecting to the sql server instance through Management Studio, using windows credentials would probably have worked as well.

1

Check the connection within the package to make sure it is connecting to the correct server. I've had a different scenario (instance fail-over) produce the same error message - i.e. on the packages running on the secondary server were trying to run against the original server name.

1
  • This is our problem, but how do I fix it?
    – kralco626
    Jul 21, 2015 at 17:18
0

Another suggestion, open up your maintenance plan and select the connection from "Manage Connections..." (on the toolbar) and make sure you have given the correct credentials.

-1

It will failed even you configured it properly through the agent account itself. Sometimes in production scenarios many other jobs are also running which acquire the same connection on the same server. Due to this both jobs collide and will fail sometimes...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .