Unanswered: scheduled dts fails but runs when done manually
I'm a newbie to sql2000. i have some jobs that copy stuff from a non microsoft database(titanium) to SQL2000. i use the propriety odbc drivers provided by titanium. when run manually in enterprize manager,it works. but scheduled as a job it fails with the message "System cannot find the specified File" i've read up and tried microsofts suggestions ie ensuring that the sql server agent has rights to the folders used etc. i've also tried puttin in the the dts owner and user passwords. each time it fails with the same error.
any one out there who can help?
Re: scheduled dts fails but runs when done manually
I'm a SQL2K newbie and I've experienced a similar problem.
When you run the DTS pkg manually, it runs on your client machine;
when you run it as a job it runs on the SQL server.
If you can run the DTS pkg manually on the SQL server (from its console), do that. You should get more details on the error. Most likely
something is missing from the PATH environment variable on the SQL
server (and you can compare to what you have for this on your client
Jeff's on the right track. When you develop a DTS package remotely, it remembers the paths based on where you developed it. You would either need to
- develop the package locally (on the server), in which case it would not run interactively from a remote machine, or
- use a unc path, ie. \\servername\sharename\filename. In this case, it should run interactively or scheduled.
I'd also add to check permissions. You need to keep in mind that the job on the server is not going to run as "you" but as the sql server agent, so it maynot have the same authorization to access drives and shares as you do. That's the one that always catches me.