Scheduled DTS failure

[JefB]

I have a DTS Local Package that imports data from an Access database table. DTS is in SQL2000 on an NT Server and the Access database is on a networked PC.

The DTS package works fine if I set it off manually but fails if I set a scheduled execution.

This is the fail message (in Job History):

Executed as user: SQL_SVR_U. ...: Executing... DTSRun OnStart: Delete from Table [XDR2000].[dbo].[mDialService] Step DTSRun OnFinish: Delete from Table [XDR2000].[dbo].[mDialService] Step DTSRun OnStart: Copy Data from mDialService to [XDR2000].[dbo].[mDialService] Step DTSRun OnError: Copy Data from mDialService to [XDR2000].[dbo].[mDialService] Step, Error = -2147467259 (80004005) Error string: The Microsoft Jet database engine cannot open the file 'E:\XDR.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 'E:\XDR.mdb'. It is already opened exclusively by another user, or you need permission to view its data. Error source: Microsoft JET Database . The step failed.

I'm sure I am missing something obvious, but I can't see it!

Any suggestions?

JefB

 

[nigelrivett]

DTS is a client application. When you run it from the designer it uses your profile and runs on your machine. When you schedule it it will (probably) use the sql server service profile and run on the server. Probabably either that profile doesn't have permission on something, something isn't installed on the server or it is trying to access a path relative to your m/c instead of the server.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.