Unpicking ODBC Error Messages
by Peter Wone - GUI Computing
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:
for example :
There is another form:
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.
