i'm trying to trap a "timeout expired" error from ADO.
When a timeout happens, ADO returns:
Number: 0x80040E31 (DB_E_ABORTLIMITREACHED in oledberr.h)
SQLState: HYT00
NativeError: 0
The NativeError
of zero makes sense, since the timeout is not a function of the database engine (i.e. SQL Server), but of ADO's internal timeout mechanism.
The Number
(i.e. the COM hresult) looks useful, but the definition of DB_E_ABORTLIMITREACHED
in oledberr.h
says:
Execution stopped because a resource limit was reached. No results were returned.
This error could apply to things besides "timeout expired" (some potentially server-side), such as a governor that limits:
and stops a query.
The final useful piece is SQLState
, which is a database-independent error code system. Unfortunately the only reference for SQLState error codes i can find have no mention of HYT00
.
What to do? What do do?
Note: i can't trust
0x80040E31 (DB_E_ABORTLIMITREACHED)
to mean "timeout expired", anymore than i could trust
0x80004005 (E_UNSPECIFIED_ERROR)
to mean "Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim".
My pseudo-question becomes: does anyone have documentation on what the SQLState "HYT000
" means?
And my real question still remains: How can i specifically trap an ADO timeout expired exception thrown by ADO?
Gotta love the questions where the developer is trying to "do the right thing", but nobody knows how to do the right thing. Also gotta love how googling for DB_E_ABORTLIMITREACHED
and this question is #9, with MSDN nowhere to be found.
From the OLEdb ICommand.Execute reference:
DB_E_ABORTLIMITREACHED
Execution has been aborted because a resource limit has been reached. For example, a query timed out. No results have been returned.
"For example", meaning not an exhaustive list.
Found it. Answer applied as answer.
public class SQLTimeoutException extends SQLTransientException. The subclass of SQLException thrown when the timeout specified by Statement has expired. This exception does not correspond to a standard SQLState.
The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which is a 10-minute wait. Setting this value to 0 disables the time-out.
Using SQL Server Management StudioIn Object Explorer, right-click a server and select Properties. Click the Connections node. Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.
You can safely use HYT00
to mean "Timeout expired". The following comes from Microsoft's SQLSTATEs reference. It mentions the HYT00
SQLSTATE:
The following SQLSTATEs indicate run-time errors or warnings and are good candidates on which to base programming logic. However, there is no guarantee that all drivers return them.
01004 (Data truncated)
01S02 (Option value changed)
HY008 (Operation canceled)
HYC00 (Optional feature not implemented)
HYT00 (Timeout expired)
Which then links to Appendix A: ODBC Error Codes of the ODBC Programmer's Reference, which documents the SQLSTATE
values:
HYT00
Timeout expired, and interestingly alsoHYT01
Connection timeout expiredSo you can use HYT00 for "Timeout expired".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With