Unpicking ODBC Error Messages

by Peter Wone - GUI Computing
Image of Line Break

ODBC error messages have never been renowned for their user- (or programmer-) friendliness.

All those square brackets, what does it mean? Why does the rotten thing use two or three VB error codes to cover a multitude of sins, and how can you figure out exactly what happened? Well, as they say, RTFM. The source of all my best information is the ODBC SDK (Microsoft Press)..

The first trap to be aware of is that a single error in VB may report multiple errors at the SQL Server, and your epic message may actually be several error messages. This take the form:

[vendor][ODBC-component-identifier][data-source-identifier] data-source-supplied-text

for example :

[Microsoft][Microsoft SQL Server ODBC Driver][Sybase SQL Server]Unable to allocate socket (#1605)

There is another form:

[vendor][ODBC-component-identifier] component-supplied-text

but you won’t (or shouldn't... not always the same thing) see it unless your configuration is screwy.

Both Sybase SQL Server and MS SQL Server supply their own internal error codes at the end of their error strings, bounded by parentheses and preceded by a hash. This makes them pretty easy to pick out of the string.

You need to watch for the possibility of multiple errors reports in the same string. This is easy enough to check for — multiple occurrences of "(#" - but the fact remains you need to check. Where do you split ’em? Find your "(#" and scan forwards for a left square bracket. When you find it, you’ve found the starting character of the next error string.

It’s a good idea to rip all the garbage out of the message before you dump an unhandled exception on the user. In the case of the exception used to illustrate syntax above, you’d be better off telling the user "Network failure: SQL Server cannot allocate socket".

This message provides information at two levels. The user is likely to understand a statement that the network has failed him, and when he reports the error to the network staff, they too glean something useful — the failure has to do with SQL Server’s inability to allocate a socket. Here’s a good rule of thumb I use: pretend ignorance and ask a user what your message means. If they get it wrong, the message is unclear.

Written by: Peter Wone
August 1995

Image of arrow to previous article Image of arrow to next article