Scheduling a DTS

[tman135]

I've got a DTS Package scheduled to run. The DTS goes to another server on the domain to get a 2000 .mdb file to import in SQL 7.0. The SQL Server is the PDC, running NT 4.0.

Cannot get the schedule to run unless the .mdb file is local to SQL Server. The package runs fine if executed from the client machine. I am in the owner of the package, the admin on the sql server and an admin on the server where the .mdb file is coming from.

This is the error code I get

Error:  -2147467259 (80004005); Provider Error:  -534709256 (E020FBF8)

   Error string:  The Microsoft Jet database engine cannot open the file '\\yada\yada\yada\yada\yada\accessfile.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.

   Error source:  Microsoft JET Database Engine

   Help file:  

   Help context:  5003051

I thought this might be an MDAC issue as the access file is 2000 (MDAC 2.5) and the SQL is MDAC 2.1, but then I tried with an older version of access and I get the same problem. IS this something about the permissions? I've read the micorsoft paper on scheduling a dts and have tried their suggestions but still the error.

Any thoughts would be helpful.

 

[foxdev]

It seems like it is a rights problem with SQL Server Agent. This is usually indicated when a package runs fine when run manually (using your Windows login) but fails when run by SQL Server Agent.

Check the rights assignments for the account used by SQL Server Agent. Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[tman135]

Thanks.

I traced permissions from the database through the web site and found some conflicting configurations with the SQLServer Agent account. Seems to be cruising now.