Search code examples
sql-serverexceptiontimeoutado

ADO/SQL Server: What is the error code for "timeout expired"?

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:

  • CPU usage
  • I/O reads/writes
  • network bandwidth

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.

Update 3

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.

Update Three

Found it. Answer applied as answer.

Solution

  • 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 also
    • HYT01 Connection timeout expired

    So you can use HYT00 for "Timeout expired".