DTS Hanging

Hi Guys
I hope someone can help. We have a DTS package that loads data from
Oracle 9.2 into SQL server 7. Most of the time it works fine, but
very occasionally it hangs and stays that way until the server
reboots, (automatically at 3am). There is nothing in the log on
either server that gives any clues as to why it hangs.
I want to fail the job so it can re-try, but I cant seem to set the
command timeout on the Oracle OLEDB connection (the option isn't
there
on the source command properties as it is for other drivers). Can
anyone suggest another way of failing the step? (Even if I create a
vb task that fails after a certain time, it still seems to wait for
any executing tasks to finish). Or any other ideas on another
approach?
My knowledge and access to the Oracle side of things is limited.
ThanksCAn you specify a timeout on the job in sql 7?
If not you could run the dts task from windows scheduler which has a
time out option.|||I am seeing mixed results on this in the newsgroups. What Oracle Client
version do you have installed on your SQL Server? You can take a look at
this article which suggests creating an asynchronous connection and looping
until the timeout is reached, then cancelling the query.
http://support.microsoft.com/support/kb/articles/Q251/2/48.ASP
I am not certain about Oracle 9, but the Oracle 10 OBDC driver (NOT the
microsoft driver) seems to have a query timeout option, although I cannot
see where to set it. An Oracle newgroup will probably be able to give you a
better answer.
Please stop back and let us know what solution you find.
<i.galloway@.vla.defra.gov.uk> wrote in message
news:1191251563.252636.264710@.50g2000hsm.googlegroups.com...
> Hi Guys
> I hope someone can help. We have a DTS package that loads data from
> Oracle 9.2 into SQL server 7. Most of the time it works fine, but
> very occasionally it hangs and stays that way until the server
> reboots, (automatically at 3am). There is nothing in the log on
> either server that gives any clues as to why it hangs.
>
> I want to fail the job so it can re-try, but I cant seem to set the
> command timeout on the Oracle OLEDB connection (the option isn't
> there
> on the source command properties as it is for other drivers). Can
> anyone suggest another way of failing the step? (Even if I create a
> vb task that fails after a certain time, it still seems to wait for
> any executing tasks to finish). Or any other ideas on another
> approach?
>
> My knowledge and access to the Oracle side of things is limited.
>
> Thanks
>|||On 1 Oct, 16:36, Paddy <paddymulla...@.btopenworld.com> wrote:
> CAn you specify a timeout on the job in sql 7?
> If not you could run the dts task from windows scheduler which has a
> time out option.
Thanks for the reply.
You cant specify a timeout on the SQL side. I could maybe run the job
from scheduler, but it does seem a bit perverse...I will try upgrading
the Oracle client first.|||On 1 Oct, 16:37, "Jim Underwood"
<james.underwood_nos...@.fallonclinic.org> wrote:
> I am seeing mixed results on this in the newsgroups. What Oracle Client
> version do you have installed on your SQL Server? You can take a look at
> this article which suggests creating an asynchronous connection and looping
> until the timeout is reached, then cancelling the query.http://support.microsoft.com/support/kb/articles/Q251/2/48.ASP
> I am not certain about Oracle 9, but the Oracle 10 OBDC driver (NOT the
> microsoft driver) seems to have a query timeout option, although I cannot
> see where to set it. An Oracle newgroup will probably be able to give you a
> better answer.
> Please stop back and let us know what solution you find.
> <i.gallo...@.vla.defra.gov.uk> wrote in message
> news:1191251563.252636.264710@.50g2000hsm.googlegroups.com...
>
> > Hi Guys
> > I hope someone can help. We have a DTS package that loads data from
> > Oracle 9.2 into SQL server 7. Most of the time it works fine, but
> > very occasionally it hangs and stays that way until the server
> > reboots, (automatically at 3am). There is nothing in the log on
> > either server that gives any clues as to why it hangs.
> > I want to fail the job so it can re-try, but I cant seem to set the
> > command timeout on the Oracle OLEDB connection (the option isn't
> > there
> > on the source command properties as it is for other drivers). Can
> > anyone suggest another way of failing the step? (Even if I create a
> > vb task that fails after a certain time, it still seems to wait for
> > any executing tasks to finish). Or any other ideas on another
> > approach?
> > My knowledge and access to the Oracle side of things is limited.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for the reply.
I will try upgrading the Oracle client, and let you know.|||No good, I'm afraid. I now have the option to set the command
timeout, but it actually makes no difference.