Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: DTS Job fails when scheduled

    I have a DTS local package that transfer data from the host SQL server to another. If I run the package directly (i.e. Right click - Execute Package) everything works as it should.

    However, if I try and execute this as a Server Agent job it fails, the job history step details shows the following error:

    Executed as user: MYSERVERNAME\SYSTEM. ...: Drop table customer Step DTSRun OnError: Drop table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: Drop table customer Step DTSRun OnStart: Create Table customer Step DTSRun OnError: Create Table customer Step, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server... Process Exit Code 2. The step failed.
    Whilst this is obviously a connection issue, I dont understand how this can work if executed directly and not if executed as an agent job.

    Can anybody shed any light on this?

    If it helps this is the sequence of events my DTS package uses

    1) Connect to remote DB
    2) Drop table customers
    3) Create new remote customers table
    4) Connect to to local DB (hosting the DTS package)
    5) Select * from local customers table
    6) Dump data from local select into remote DB

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    It's a frequently asked question. When you open a DTS package from your workstation, the environment is that of your workstation.

    When you execute a DTS package from a scheduled job, the environment is that of the server.

    If there are differences between the server environment and the environment of your workstation, then the executed package will fail.

    For example, if you have Oracle drivers configured on your PC (but not on the server) you will be able to execute the DTS package from your PC, but not from the server.

    Try using Terminal Services to remote in to the DB Server. Then open the package and execute it from the server desktop. The errors become more apparent at that point.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you allow mixed mode authentication, create an login on your target server for use as a service account, then define credentials for your individual connections in the "Connection Properties" dialog for each specific server connection you intend to use.

    Alternately, you can setup your target server as a linked server and specify which credentials it is to use when operating on the remote server in the "Security -> Linked Servers" node in Enterpise Manager.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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