Timing out In Access

[MarkatLMFJ]

Howdy All,

I have an access application that I'm inheriting from the previous dude who was here which uses 3 or 4 tables in my pervasive database. The access queries time out every 3 minutes or so before selecting the entire data set.

We are using Pervasive 8.6 on the server and the clients.

1) how to increase the time out?
2) if I move from using access to say MS-SQL server and run the query as a data-pump task would it time out there (not tried yet)

---MAL

 

[mirtheil]

I believe there may be a Query Timeout parameter in the Access Query (or linked table) properties.
Another option might be the following:
(from the Pervasive KB)
ID: psql3973
How to make Pervasive.SQL V8 engine ignore SQL_QUERY_TIMEOUT API

Problem Description:
Queries timeout due to support for the SQL_QUERY_TIMEOUT API in Pervasive.SQL V 8.
Applications working with Pervasive.SQL 2000 no longer work with Pervasive.SQL V8. Query Timeout error is due to support for SQL_QUERY_TIMEOUT API in V8.

ERROR [HYT00] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine
Interface]S1T00Timeout expired.
Problem Description:
ERROR [HYT00] [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine
Interface]S1T00Timeout expired
How to make Pervasive.SQL V8 engine ignore SQL_QUERY_TIMEOUT API

Problem Environment:
Pervasive.SQL V8
SQL_QUERY_TIMEOUT
ADO
Defect: 44655

Cause of this problem:

Prior to Pervasive.SQL V8, query timeouts were not checked by the ODBC engine. If a query's processing time was too long, there was no way to stop the processing. ODBC standards provide a way to specify query timeout. If it is specified, the engine will stop processing any query that is taking more than the timeout value. The timeout is specified as a statement option.

The support for this ODBC feature started in Pervasive.SQL V8. Those who do not want any timeout in the application, do not need to do anything to their applications and run as they used to.

The problem happens with ADO applications with no query timeout setting. ADO has this feature to set the timeout by default to 30 seconds. So, if a user application did not set any timeout it was implicitly set to 30 seconds. This did not cause a problem before since it was ignored.


Solution:

The right solution to the problem is to rewrite the applications and set explicit timeouts for ADO applications (with "0" for no timeout or the correct timeout).

The another solution is to change the environment to make the Pervasive.SQL V8 engine to ignore the timeout setting. This is a "Datasource Name" setting, the setting is specific to a client DSN . Connections to different DSNs but to the same database or engine DSN may have different behavior.

The DSN entry is:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<dsn name>\
2. Under this entry, create a new string value named: QueryTimeOutAllowed
3. Set the value to NO to ignore any query timeouts. Set the value to YES to timeout.

If set to "YES", the engine enforces query timeouts for all connections to that DSN. If the setting is set to "NO", the engine ignores any query timeouts for connections to the given DSN.
The default behavior is as if the setting was set to "YES".

The fix itself includes: start passing the new setting from the client application to SRDE, if the setting was passed to the engine during the connection, any attempts to set query timeouts for the given connections are ignored. Client and server components of SRDE needs to be updated to make the fix work.
To get fixes for the ADO problem install the ODBC Client HotFix and the SRDE Hotfix which can be download from: Click here

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[NEmacGuy]

I have never used v8.6, having stated that, what is the access application doing Reporting only or updating data. If reporting only change to query's RecordsetType to snapshot. You could also check the odbc timeout setting.