Scheduled DTS Job to import From a Work Station Access Db

[Liam1]

Hello,

I have got a DTS job that extracts from a table held in an Access databse held on the D:\ drive of my work terminal. This DTS Job is held on a separate SQL 2000 Server over a LAN.
When I execute this DTS Job manually, it is fine - it finds MY work station version of the Access Database using the ODBC connection & named instance in the task manager wizard.

However, when I try to schedule this using the SQL Sever, it fails and gives the error msg saying it cannot find the database.
It looks like it is trying to find an Access Database on the SQL Server itself, and not on my workstation.

1. What is the difference, does executing something manually actually give my workstation some sort of control?
2. How can I get it to read my workstation - I.P. address?

I do not want to use the local Windows NT scheduler if I can help it.

Thanks in advance,

Liam1

 

[jhall01]

Executing a DTS Package in the Designer actually executes it on your local machine. Your machine because a bridge between the Server and the mdb so to speak.

You will need to create a share drive to the mdb and when scheduling it set the path to \\yourmachine\yourpath\your.mdb instead of D:\yourpath\your.mdb.

Then when designing you will need to switch back to the local address instead of the share.

In addition to the path, if you are using a system DSN you will need to create a system DSN on the server for the mdb that is identical to the one on your client machine. This will alleviate the path problem since the path defined in the DSN on the server will be using the network path and the DSN on the client machine will use the local path.

Jon