O'Connor, Patricia (OTDA)
Patricia.O'Connor at otda.state.ny.us
Wed Aug 23 13:12:43 CDT 2006
Hi Gary, Thanks!! If I run same query in PL_SQL no problem without touching any timers. Asked our DBA and they checked the ORACLE logon and there were no login limits. So we checked the SQLNET.ORA file and the SQLNET.EXPIRE_TIME=0. I set the registry to 6000. Just out of curiosity what do you have in the TOOLS OPTIONS ADVANCED settings Enable DDe refresh OLE/DDE timeout 300 REfresh interval 60 Number of update retries 2 Odbc refresh interval 1500 Update retry interval 250 Record level locking This is so strange. Will try looking at ms forums maybe something there. Thanks ************************************************** * Patricia O'Connor * Associate Computer Programmer Analyst * OTDA - BDMA * (W) mailto:Patricia.O'Connor at otda.state.ny.us * (w) mailto:aa1160 at nysemail.state.ny.us ************************************************** > -------------------------------------------------------- This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > Sent: Tuesday, August 22, 2006 02:14 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Change ODBC timeout default query property > > Hi Patricia, > > Weird. Maybe I'm just lucky? ;-) > > I''ve got some extract queries that run for a few hours > before returning any results so I'd really be in trouble if > it didn't work as it does for me. > > Perhaps you could test the database login theory if you ran a > similar query directly against the Oracle Database using > another tool such as SQL Navigator, Toad or SQL Plus, taking > the ODBC driver out of the mix. If the query works there > then it isn't default limits on the login causing your > problem. Or if you are on friendly enough terms with your > Oracle DBA you could ask them what they think is going on. > > Good luck figuring it out and be sue and let us know when you do. > > GK > > On 8/22/06, O'Connor, Patricia (OTDA) > <Patricia.O'Connor at otda.state.ny.us> wrote: > > Hi Gary > > I use the same ODBC driver and even tried rebooting - still > get same > > message. Will check into the Oracle Database login to see if they > > have something set up there. > > > > I recommend the oracle odbc over the microsoft because the date > > handling is better. Think it is better for passthru's also. > There was > > something else related also but I can't remember at just > this moment. > > > > Thanks > > > > ************************************************** > > * Patricia O'Connor > > * Associate Computer Programmer Analyst > > * OTDA - BDMA > > * (W) mailto:Patricia.O'Connor at otda.state.ny.us > > * (w) mailto:aa1160 at nysemail.state.ny.us > > ************************************************** > > > > > > > > > -------------------------------------------------------- > > This e-mail, including any attachments, may be > confidential, privileged or otherwise legally protected. It > is intended only for the addressee. If you received this > e-mail in error or from someone who was not authorized to > send it to you, do not disseminate, copy or otherwise use > this e-mail or its attachments. Please notify the sender > immediately by reply e-mail and delete the e-mail from your system. > > > > > > -----Original Message----- > > > > > From: accessd-bounces at databaseadvisors.com > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf > Of Gary Kjos > > > Sent: Tuesday, August 22, 2006 12:07 PM > > > To: Access Developers discussion and problem solving > > > Subject: Re: [AccessD] Change ODBC timeout default query property > > > > > > I am currently using Oracle ODBC driver 9.02.00.54 Yep. > > > unclicking the "Enable Query Timeout" box does the trick > for me and > > > my users. > > > That is the only option we change from default when > setting up the > > > ODBC data source. We use System DSN's for all ODBC definitions. I > > > wonder if your Oracle Database login has a defaulting > timeout level > > > that is biting you.. > > > > > > I have also used the Microsoft ODBC driver with good results. > > > The only issue there was that if the Oracle Database > login password > > > was expired the Microsoft ODBC driver didn't prompt for > entering a > > > new password as the Oracle one did. that may have changed with a > > > newer Microsoft driver though. We used to use a generic login and > > > password for everyone's Access ODBC into Oracle stuff, > but because > > > of Sarbains/Oxley we were required to change to individual user > > > logins that had expiring passwords. So that was when the > Microsoft > > > ODBC driver not prompting for new password became more of > an issue. > > > > > > On 8/22/06, O'Connor, Patricia (OTDA) > > > <Patricia.O'Connor at otda.state.ny.us> wrote: > > > > Gary > > > > > > > > Which driver do you use? And unclick works? > > > > > > > > We are using the ORACLE ODBC driver Ora 9i. But also > > > happens against > > > > FOXPRO. > > > > > > > > I went to the Administrative tools, Data Sources(ODBC) and > > > unclicked > > > > Query Time out. Opened the MDB and tried a pretty simple > > > query without > > > > changing the Query Property Timeout over 60. It still > timed out. > > > > > > > > I get the following message if I don't set the QUERY > PROPERTY ODBC > > > > Timeout from 60 up to at least 500. I routinely set it at 6000. > > > > > > > > ODBC--Call failed. > > > > [Oracle] [ODBC] [ORA] ORA-01013: user requested > cancel of > > > > current operation (#1013) > > > > > > > > I know immediately that I forgot to update the timeout by > > > the message > > > > above. Many others do not understand so I am always > > > checking out their > > > > queries and machines. > > > > > > > > I was wondering whether there was a change to Registry or > > > some hidden > > > > MS change that would fix this. > > > > > > > > Thanks > > > > ************************************************** > > > > * Patricia O'Connor > > > > * Associate Computer Programmer Analyst > > > > * OTDA - BDMA > > > > * (W) mailto:Patricia.O'Connor at otda.state.ny.us > > > > * (w) mailto:aa1160 at nysemail.state.ny.us > > > > ************************************************** > > > > > > > > -----Original Message----- > > > > > > > > > From: accessd-bounces at databaseadvisors.com > > > > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf > > > Of Gary Kjos > > > > > Sent: Tuesday, August 22, 2006 10:11 AM > > > > > To: Access Developers discussion and problem solving > > > > > Subject: Re: [AccessD] Change ODBC timeout default query > > > > > property > > > > > > > > > > Which ODBC driver are you using? With the Oracle ODBC Driver > > > > > for Oracle you can tell it to just NOT timeout by unchecking > > > the Timeout > > > > > checkbox. > > > > > > > > > > On 8/22/06, O'Connor, Patricia (OTDA) > > > > > <Patricia.O'Connor at otda.state.ny.us> wrote: > > > > > > Hi all > > > > > > > > > > > > Is there a way to set the ODBC timeout default in > the query > > > > > > properties from 60 to 6000 permanently? Most of our work > > > > > is against > > > > > > our ORACLE databases, some Foxpro, and SqlServer. For every > > > > > new query > > > > > > we do we have to remember to reset this before running > > > > > otherwise we get a timeout. > > > > > > Does not matter how easy or hard the query is, If > it isn't at > > > > > > least 600 then poof it stops. One person said that it would > > > > > cause their MDB > > > > > > to corrupt but I haven't had that problem. > > > > > > > > > > > > The OPTIONS ADVANCED does not do this. I have > looked almost > > > > > > everywhere but can't find out how to change the default. > > > > > This would > > > > > > be for 2k or 2k3. > > > > > > Any help appreciated > > > > > > > > > > > > Thanks > > > > > > > > > > > > ************************************************** > > > > > > * Patricia O'Connor > > > > > > -------------------------------------------------------- > > > > > -- > > > > > Gary Kjos > > > > > garykjos at gmail.com > > > > > -- > > > Gary Kjos > > > garykjos at gmail.com > > > -- > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > -- > Gary Kjos > garykjos at gmail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >