| Author |
Topic |
|
Tigger
Yak Posting Veteran
85 Posts |
 Posted - 2002-02-25 : 23:56:13
|
Every week I have to download transaction data from an Oracle database onto SQL2000 for reporting.For the last few months the DTS job has worked fine, however this week it has failed due to [Microsoft][ODBC SQLServer Driver]Timeout expired.This was after it had been downloading for 4 hours and had downloaded about 400,000 records.The table has 38 million records and is not indexed on the columns I need to use for the where clause.Being the lowest of the low I have no say on what columns are indexed in the Oracle database  How can I stop the DTS package timing out? |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-26 : 00:11:39
|
| G'day Tigs - I do exactly this every day - I'm only pulling about 250 000 records however.Go to your package - double click on the Microsoft ODBC Driver for Oracle Icon (in your package) - Click the Advanced Tab - and you can set your timeouts.Get back to me if this doesn't solve your issue.- (Because I'm sure it'll soon be my issue!)--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-02-26 : 14:45:40
|
Thanks for the info - I've set the timeouts and the job is now rerunning.I'll let you know how it goes - probably in about 8 hours !!  |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-26 : 17:10:42
|
| 8 hours!!!! Ouch! Is it running over a phone line? Smoke signals?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-02-26 : 18:22:50
|
| No, but its powered by a hamster running around in a wheel !! Sometimes I wonder if it would be quicker to just key the data in - any volunteers ??? |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-26 : 18:30:02
|
| My package takes about 35 minutes (for around 250 000 recs)... and I thought THAT was slow!!Just out of interest, what did you set your timeouts to?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-02-26 : 19:40:56
|
| I set the timeouts to 0.Job has just finished - took 4 1/2 hours. Bit bizarre though cos it downloaded 850,000 records, yet it failed after 4 1/2 hours yesterday having downloaded 400,000! Guess the server was busy yesterday. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-26 : 19:49:27
|
It's all in how much you feed the hamster. Glad all is well. As they say in the UK - "Cheers"--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
Tigger
Yak Posting Veteran
85 Posts |
Posted - 2002-02-26 : 19:58:00
|
quote:
It's all in how much you feed the hamster.
Darn, I knew I forgot to do something before I went home last night  Maybe the error message should have been:[Microsoft][Hamster SQL Server Driver] Hamster expired Edited by - tigger on 02/26/2002 20:02:14 |
|
|
|
|
Timeout expired downloading from Oracle