August 30, 2011 at 11:33 am
Hi,
I have created a DTS packages that makes a connection between SQL Server 2000 and Access 97 db and imports data from Access 97. This DTS Package was scheduled using Jobs. This has been working from past 2 years. Suddenly in the last one month, when this package executed through the Job is failing, but when the package ran manually it is working fine.
Any ideas why it is happening?
The error from the job is below.
Executed as user: IRI_CORP\AcxiomDBA. ...n OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file '\\xfp1\public\pub1\RIS$\PROD\RIS_APPS\vantive.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 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8) Error string: The Microsoft Jet database engine cannot open the file '\\xfp1\public\pub1\RIS$\PROD\RIS_APPS\vantive.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 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Pls help as we have to run manually everyday the process.
Thanks,
August 30, 2011 at 12:47 pm
It looks like someone or some other process has the Access db (.mdb) already opened. Is there a .ldb (lock file) in the same directory? If nothing has the db open, you can remove the lock, or reboot the server (which will do the same thing).
HTH,
Rob
August 30, 2011 at 3:27 pm
I have checked it, but there is no lock file found and as you said I have already restarted the machine where this mdb file is located.
September 11, 2011 at 5:00 pm
Hi
I hope you have found a solution by now but if you haven't, my pick here is that there is a security issue involved. Possibly the owner of the job has changed and has no permissions to that folder/file/mdb.
For SQL jobs, I normally use the SQL Server Agent admin as the job owner and make sure that this user has access to any folders accessed by any of the jobs.
I hope this provides you with some ideas to explore.
Cheers
September 12, 2011 at 5:11 am
I agree with ProofOfLife - this has to be a security issue. However presuming that you haven't recently changed the account your SQL Agent is running under, it seems most likely to have been caused by a recent change in the file system rights for the location the access database is stored in. This is why it works when you run it interactively because in that case, the job is running with the privileges of the person running it rather than with those of the SQL agent.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply