Hi,

We have an Access97 database, that lives on a file server running NT40SP6a,
and not running SQL.

We are trying to build a DTS package on an NT40SP6a/SQL7SP4 server that will
open the Access database and populate a SQL database from it.

Everything works fine when we execute the DTS package directly from
Enterprise Manager, because it runs in the logged-in user's security
context. When we try to schedule the package, it fails with the following
result -

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error Detail Records:
Error: -2147008507 (80074005); Provider Error: 0 (0)
Error string: Unspecified error
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts.hlp
Help context: 1100
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)
Error string: The Microsoft Jet database engine cannot open the file
'\\<server_name>\<share_name>\db.mdb'. It is already opened exclusively
by another user, or you need permission to view its data.
Error. The step failed.

The DTS package can't run under the Local System account, because it runs on
the SQL server, which has a different Local System account than the file
server, so we can't pass through the authentication on the Share. Similarly,
we can't map a drive to the file server and have it work, because the drive
map is associated with the logged-in user, and doesn't carry over to the
Local System account.

Does anyone have any idea how to make the scheduled package run in a domain
user's context without reconfiguring the SQLServerAgent to run in a domain
account?

Any help is much appreciated.

Thanks.

Copy your Mdb file to same drive as MSSQL and it will be OK.
I had the same problem with mdb file in Novell maped drive.
SQL doesn't see other drives

Rufin from Poland.

Copy mdb file to the same drive as MSSQL and everything will be OK.
I had the same problem with mdb file on Novell mapped drive.
MSSQL (server service) does'nt see other (user) drives.

Rufin from poland.

Hi,

We tried that. It works, but doesn't solve the problem. We need to keep
the Access db where it currently resides. The task of moving it would play
havoc with our users.

Thanks anyway.

"Rufin" <anonymous (AT) discussions (DOT) microsoft.com> wrote

So. Try "net use" commands to map drives before DTS execute.
Try to include command "dir drive: > c:\test.txt" before DTS execute. It will help you to understand Does MSSQL (server serivice) see proper drive.

Rufin from poland.