Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



Posted on 2006-06-09
Medium Priority
Last Modified: 2010-08-05
I have a Ms-Access 2002 SP3 client and Oracle DB 9i in Solaris Unix. In the code when I check the StillExecuting flag for the recordset bound to the query I get the above error (63535 or 3146). The thing is that this happens occasinally and there are other times where the query will complete its execution cycle in the Oracle server perfectly! I use ODBC direct to create and send the queries. Any suggestions on what might be the problem. The ODBC driver I use is the Oracle ODBC V.

If Not rstODBC.StillExecuting Then

Any suggestions? Is something wrong with the ODBC driver / Network connection?
Question by:skapodis
LVL 13

Expert Comment

by:John Mc Hale
ID: 16868850
When you open the recordset, do you need to specify whether to use the Client side or Server side Cursor. I dunno, but maybe this might have something to do with it??

Expert Comment

ID: 16869447
I think StillExecuting does not work well with other than microsoft databases.. check the documentation

Author Comment

ID: 16869820
This is how I initiate the query to the Oracle Server through ODBC:

Set dbs = CurrentDb()
Set odbcWorkspace = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append odbcWorkspace
Set OracleConnection = odbcWorkspace.OpenConnection("OracleDB", dbDriverNoPrompt, False, "ODBC;DSN=OracleDB;UID=BGW;PWD=THULE;")
Set qdfSearch = OracleConnection.CreateQueryDef("")          
qdfSearch.ODBCTimeout = 0
qdfSearch.SQL = SqlString
Set rstODBC = qdfSearch.OpenRecordset(dbOpenDynaset, dbRunAsync) <--- I open a Dynaset recordset type which is similar to an ODBC keyset cursor. If you do not specify the dbOpenDynaset the recordset is opened as a Forward-only type which corresponds to an ODBC forward-only cursor.

Also the above objects (queryDefs, Connections etc) are declared as Global in an Ms-Access module object so I can reference them anywhere in the code. Of course I take care not to reinitialise them once the query is executing in Oracle Server.

I did not find any relevant info on a StillExecuting problem apart from the fact that it had a problem with the True/False value which is fixed in VB.6 libraries.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 16900254
I have updated the code to a more simple version with a modification which seems to have solved the problem (need to do more testing though):

Set OracleConnection = odbcWorkspace.OpenConnection("OracleDB", dbDriverNoPrompt, False, "ODBC;DSN=OracleDB;UID=BGW;PWD=THULE;")
OracleConnection.QueryTimeout = 0
Set rstODBC = OracleConnection.OpenRecordset(GlobalStringSql, dbOpenDynaset, dbRunAsync, dbReadOnly)
rstODBC.Connection.QueryTimeout = 0

Pay attention to the OpenRecordset command. I have added the dbReadOnly flag for the dbOpenDynaset recordset type. Since the error seemed to occur only for queries that had to search through vast ammount of data (searches for more that 10million records) I realised that the dbOpenDynaset recordset may by default make locks on the records matching the SQL criteria possibly returning an error due to these locks which to be honest I do not know if they remain active as long as the recordset is alive. Adding the dbReadOnly does not allow any locks to be placed possibly solving the problem. Can anyone comment/confirm on that?  

Author Comment

ID: 16910355
Nope still get the error 63535! When I get the error I can see that the session in Oracle is not gone. Howeverthe search does not make any more reads meaning that it has stopped. Are ther any hidden timeouts in Access for connection, recorset objects? Do I need to retreive the matched data regularly from Oracle? Does Oracle stops the query execution due to certain profile settings?

Author Comment

ID: 16912329
Just it case it help someone figure out a solution... The error does not seem to appear if the main query is brake up by the Oracle SQL Optimiser into smaller queries running in parallel which of course speeds up the search time! If this is done the results once the query ends are fetched normally back to MS-Access. I will always get the error if the query is not optimized (possibly due to heavy use of the Oracle DB).

Author Comment

ID: 16933746
Finally after a trace in the ODBC data source I found out that the actual Oracle error is ORA-0155 which of course refers to the rollback segments. After increasing the rollback segment from 300MB to 2GB the error appears rarely.

Accepted Solution

CetusMOD earned 0 total points
ID: 17258198
PAQed with points refunded (500)

Community Support Moderator

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question