ODBC Queries

[namliam]

ODBC Query's

I have a couple of ODBC query's running from an access DB to an Oracle DB. All is good and fine. However sometimes i have trouble connecting to the Oracle DB.

2 things may happen....:
1) I get the common "ODBC error connection failed"
The SQL is 100% OK, except connection failed or something, i need to restart the DB to clear the memory (and avoid the timeout wait time) to retry.
2) The Access DB Crashes. It doesnt give an error or anything, it just "disappears", again restart and retry.

These 2 can repeat and intermittently show up, Or on try 1 have problem 2, try 2 problem 1 and Vice versa...

Try try try untill it works. Its a pain, is there anybody out there who has experience simmular problems in the past (WinNT, Access 97 - SR2)

Thanx.

 

[Kodo]

try updating your MDAC
http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

it has updated drivers and might solve your problem.
Make sure you back up your system first.

 

[namliam]

Can one determan what version we are currently using? I can imagine this beeing the problem, but a reluctant to suggest it as i do not have the possibility to install (and maybe recover) myself (no rights )

I will have a look at it, i didnt even consider something like this... Something new to think about.... thanx...

Regards

Edit: Did a little searching on my own, found the version#: 2.5..... may be a little out off date Hopefully updating will solve the problem. I will report back when and if the issue is resolved. Meanwhile suggestions are welcome!

 

[Kodo]

sorry it took so long to get back to you.
Glad you found what version you have.. hope it didn't take too long.

For those who are interested on how to do this.
http://support.microsoft.com/default.aspx?kbid=301202

 

[namliam]

Using the version checker tool it seems i am using 2.6... which is still 'slightly' off from the current 2.8....

However Microsoft ODBC for Oracle is using a dll called MSORCL32.DLL, this file is not mentioned in the Component version checker.

Why? This must sound totaly simple to you, but i am new to drivers (allthough i have been using them for ever, i have never had to install/upgrade)

Thanx for your time and effort....

 

[Kodo]

here is a list of ODBC drivers for oracle.
http://otn.oracle.com/software/tech/windows/odbc/index.html

perhaps there is one there you can try for you version.

 

[jfgambit]

In the properties of the queries what is the "ODBC time-out" set to?

 

[namliam]

In the properties of the queries what is the "ODBC time-out" set to?

ODBC timeout is set to (default) 60 secs, but the crash or ODBC error i get when connecting occurs much sooner than that, either will occure within 10 to 20 secs.....

Regards

 

[namliam]

here is a list of ODBC drivers for oracle.
http://otn.oracle.com/software/tech/windows/odbc/index.html

perhaps there is one there you can try for you version.

Thanks for the heads up, when looking thru the list i think i found the proper driver.... But i can i make sure (on my own without going to the the DBA) what Oracle version we are using, is it mentioned in the SQL*Plus window? after "Oracle8i Enterprise Edition Release"...

Question tho: What are Level 2 compatible drivers ? as stated i know nothing about drivers...

Still searching *sigh*

 

[jfgambit]

ODBC timeout is set to (default) 60 secs, but the crash or ODBC error i get when connecting occurs much sooner than that, either will occure within 10 to 20 secs.....

As an FYI...you may want to set this to "0". If there are a number of records and it takes longer than 60 seconds to run any of them once you solve your problem, you are going to continue to get the "ODBC call failed" error. Setting this to "0" allows the queries to run to completion.

HTH

 

[namliam]

Thanks for the FYI, but i am aware of it. All ODBC's should not take more than 2 secs to run from start to end, all are on and over indexes and fully optimized. Any query taking more than a minute is GARBAGE anyway....

 

[namliam]

Same as my nightmares thread, I am still looking for an answer on this one.... Anyone got any bright new ideas maybe?

Regards