Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Question Unanswered: SQL Job fails running DTS job

    Hello,
    I have a DTS job that retrieves data from a different server. When I manually run the DTS job, it executes fine, but when I run it from an SQL job it fails regardless of the owner I use to run the SQL job under.
    I ran the same DTS package from another computer which has SQL client installed and it ran fine.
    Does anyone know what could be the reason for failure when running the DTS job from the SQL job from the server?

    Thanks,
    Adrian.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    When you run the job, it runs under your credentials. When the job is run by the SQL Agent, it runs under the credentials of the agent, not yours. My first thought would be that the service login used for the agent does not have the same permissions on the remote server that you have.

    Start there, then let us know how it goes.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Sep 2005
    Posts
    2

    When you run the job, it runs...

    So, the DTS job is only installed on the server. The job on the server runs under server's ID to which I gave full access and it fails.
    On my workstation I have SQL Client and I created an SQL job to run the same DTS job on the server, and it runs fine. The job runs under [MyComputerName]\SYSTEM user ID.
    Why would SYSTEM ID on my PC have more access to the server than the server's ID to a DTS job running on the server?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by adriansc
    So, the DTS job is only installed on the server. The job on the server runs under server's ID to which I gave full access and it fails.
    On my workstation I have SQL Client and I created an SQL job to run the same DTS job on the server, and it runs fine. The job runs under [MyComputerName]\SYSTEM user ID.
    Why would SYSTEM ID on my PC have more access to the server than the server's ID to a DTS job running on the server?
    A couple of things to bear in mind:

    1. When DTS is executed as a job, it will execute in the context of the SQL server's operating system. This means that any "special" drivers you have on your PC are not (necessarily) available (such as Oracle software, AS/400 software or drivers for Excel and other file formats). If your DTS pacakge is accessing one of these foreign data sources, you need to make sure that the appropriate software is installed on the db server.

    2. When scheduled as a job, DTS will execute in the context of the SQL Agent user account. When you manually execute it on your laptop it's running as YOUR account (not the account you configured for the SQL service on your laptop). Check the configuration/privileges of the SQL Agent service account.


    I think your best bet might be to terminal service into the SQL Server, open the DTS package and then manually executing the package from the server. It's best if you can log in as the SQL Agent account (this may require fiddling with local security policy for Log On Locally privilege).

    For these reasons, DTS is not always immediately intuitive. It takes a little finesse to work through some of these issues.

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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