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.
Best Answer
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.