April 5, 2006 at 2:15 am
Problem:
Created a DTS package to get information from a MSAccess Database. We used an ODBC to get access, and configured it to use the sysem.mdw (Security file). When we run the DTS package manual (meaning by right click/execute) the package runs and gets the data. No security issues.
We then scheduled the the DTS to run once a day. Now when executing this job we always get the same error.
The user sqlserveragent is a domain administrator who has all the rights to the drive/folder/file where the security and msaccess database are located.
We have tried everything, but cannot find the solution for this problem.
We know it can work because we have done this before, but (unfortunately) we did not documented it the last time.
Is there someone who can help us..
***************** start of error message ****************
Executed as user: Mydomain\sqlserveragent.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005);
Provider Error: 1901 (76D)
Error string:
Error source:
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete. Process Exit Code 1. The step failed.
***************** end of error message ****************
Version info
MSSQL server version 2000 / SP4
Windows standard server 2003 / SP1
MSAccess version 97
Windows standard server 2000 / SP4
April 6, 2006 at 11:52 am
When you create a DTS and then schedule it from the DTS interface, it is executed in the resulting job step by referencing the GUID of the DTS. Each new version of the DTS (after any save) creates a new GUID, but the job is not updated. The job is still executing the DTS version tied to the original GUID. You need to replace the GUID with the new GUID, or the name of the DTS in the job step.
I like to run DTS using TSQL in the job step and master..xp_cmdshell 'DTSRun' with the name of the DTS.
Terry
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply