Problem with extracting large amount of data from Oracle through ADODB
I am currently using ADODB.connection to establish a link to Oracle and ADODB.recordset to store the returned data, then use Range.copyfromRecordset() to populate the result into a worksheet.
However, whenever my query return more than 3,000 rows (with around 75 columns each). I have a strange problem, everything works fine when I run the query the first time, I could see results in the worksheet. However, if I try to run this query the 2nd time, recordset.open does not work anymore. I constantly get timeout errors (no matter how long I set the timeout limit to). If I restart the PC, the I could get it working once only again.
After each query run, I did connection.close, recordset.close and set connection and recordset to nothing. I am suspecting there are some memory leak somewhere. Does anyone know a better way to clean an ADODB connection and recordset?
Many thanks!
hi Humphrey ! Long time no
hi Humphrey !
Long time no see..
One Q - are you closing your recordset before running a 2nd time ?
tks
Long time no see
Hi Nick,
It has probably been like 3 yrs since we last chatted? ... I am still following all the nice photos and funny comments you make on your facebook so still feeling like we have regular dialogue.
I ran rs.close and rs.activeconnection = nothing followed by rs = nothing. Pretty sure these commands got run as I put a break point in front of them.
The strange thing is even restarting Excel doesn't help, had to restart the PC before I could run the query again.
Thanks again for the kind help!
Stabbing in the dark here,
Stabbing in the dark here, but try these:
1. Comment out the line that copies from recordset, and see if it runs twice.
2. Have a word with the DB owner to see if there's anything they are doing on their side that is preventing this from working
3. Is it the same problem on all computers... network settings might affect it
Nick
Found the problem
The SQL was too long (500 lines long), converted it into a storeproc that creates a tmp table, the SQL from ODBC becomes select * from tmp, and everything works well from that point. Of course had to design some tmp table housekeeping mechanism with that. Thanks anyways!