Error scheduling a DTS Package, file in use

[flaviooooo]

Hi,

I have created a DTS package that empties a table, and then gets some data from an Access-database.

When I run the DTS package, it runs without a problem. When I schedule it through the SQL Server Agent however, it fails. When I check the log of the DTS Package, I get this error:

Step Error Source: Microsoft JET Database Engine
Step Error Description:Could not use '\\UNC\database.mdb'; file already in use.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003045

I have allready altered the SQL Server Agent so it's started by the system administrator account, so it shouldn't be a permission-problem...

The strangest thing is that it works fine manually...
What can be the problem here?

Thanks in advance

 

[dwight1]

I had this similar problem. The reason it executes manually is because you have the right on sql and the folder mdb file is located. Ensure that the SQL DTS has permission to access the folder where the mdb file is located. Yes, you have to give rights to SQL to access any folder/drive.

This could be one of the reasons. Let some Pro's take it from here.

Dwight

 

[flaviooooo]

What do you mean with this:
"you have to give rights to SQL to access any folder/drive" ?

I have set the Service Startup Account in SQL Server Agent to the System Administrator (who has full rights on all folders)

Is there any other permission that I'm overlooking here?

 

[flaviooooo]

Does anyone alse have an other pointers on this? We are still experiencing this problem :-(

 

[unclerico]

i see that you are using a UNC path so I have a couple of questions:
1) Do you have SQL Server service account running as Local System/Network Service or as a domain user??
2) Are you 100% sure that the permissions are set correctly on the target directory

Also, Have you tried moving the database to the SQL box and initiating a test job to see if it works correctly??