Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Nairobi Kenya (East Africa)

    Red face 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?

  2. #2
    Join Date
    Aug 2002

    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

    Good luck!


  3. #3
    Join Date
    Sep 2002
    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.


  4. #4
    Join Date
    Sep 2002
    Dallas, TX, USA
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts