Solved

ODBC CALL FAILED 63535

Posted on 2006-06-09
9
892 Views
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.9.00.11.00.

If Not rstODBC.StillExecuting Then

Any suggestions? Is something wrong with the ODBC driver / Network connection?
0
Comment
Question by:skapodis
9 Comments
 
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??
0
 
LVL 9

Expert Comment

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

Author Comment

by:skapodis
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.

0
 

Author Comment

by:skapodis
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?  
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:skapodis
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?
0
 

Author Comment

by:skapodis
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).
0
 

Author Comment

by:skapodis
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.
0
 

Accepted Solution

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

CetusMOD
Community Support Moderator
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mysql vs Oracle 10 119
Folder Replication 4 45
query returning everything 11 66
What does "flush tables;" do exactly? 7 46
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now