Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    5

    Unanswered: DTS oracle Jobs hang

    hello:
    My situation is
    (1) I upgraded MS SQL 7.0 from original edition to SP 4
    (2) DTS packages which download data from an oracle server work fine both on a remote machine and on the Server
    (3) When these packages are scheduled to run as jobs they do not succeed and do not fail they just keep running e.g for 58 hours over the weekend
    (4) even if I start the job on the server I still get the same behaviour:

    Can anyone help please?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    I have lots of experience with running DTS packages to Oracle, but none in a SQL 7 SP4 environment. These are the things that I would do:

    1. Check your application error log; look for any warnings and/or errors that are related to Oracle.
    2. Open of SQL*Plus on the server; attempt to connect. Look for any error messages or warnings.
    3. Delete and re-initialize the Oracle connection objects in your DTS packages; this can be a pain. Try creating the new connection first and then changing the connection name in the connection objects that are "in the flow".
    4. Save the DTS package as a VB file and open (and execute) the package in VB. I dunno if this is an option with SQL 7.0 SP4; it is with 2000.

    Sorry, that's not very much to go one for right now. I have seen this issue with AS/400 connections (frequently). It boiled down to an application pop-up window which was not visible when the DTS job ran as a job (but which was visible when executed from the DTS pacakge maintenance window).

    Best of luck.

    Regards,

    hmscott

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    5
    Thank you HMScott

    I've investigated these and still could'nt find the problem. Then I noticed that the jobs actually work but are not marked as finished. This seems identical to http://www.dbforums.com/t943042.html except that this SQL 7.0 and that was SQL 2000. I still cant get the jobs to mark themselves as finished. ??

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Out of curiousity, where are you seeing that the jobs are not marked "finished". If you are looking at the jobs through the job monitor (under Management/SQL Server Agent/Jobs), are you remembering to periodically refresh the screen (press F5)?

    Regards,

    hmscott

  5. #5
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    5
    I sure do HM (F5 that is) The jobs normally take a minute but when I cancelled them they had been running for 58 hours plus. I'm begining to wonder if it's something to do with the mail client. This machine was also hit by the Lovebug virus which is mail related I believe.

    I'm no expert but I was wondering if there was anyway I could get a hint as to whats wrong. I looked in sqlagent.out but could see nothing.
    I ran a trace and then started the job. Could'nt see anything obvious but to be honest was a bit lost in there in Trace World.

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Can take help of this KBA to schedule dts as a job.

    And also this Winnet mag article for reference.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  7. #7
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    5
    Thanks to all for their input:

    Current situation
    The jobs are now working.
    I went into the Package properties and disabled the "USE OLE DB service components" and now the jobs work and mark themselves as completed. If I enable this then the Oracle jobs wont mark themselves as completed.
    This weird as the connection still uses the "Oracle provider for OLE DB"

    Nevertheless it is progress

Posting Permissions

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