7

SqlException has the property Number.

Then there is this: http://msdn.microsoft.com/en-us/library/cc645603.aspx

and this: http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

And it seems to be one or the other

QUESTION:

How is it decided which?

REASON FOR ASKING:

I need to catch certain SqlExceptions and decide how to deal with them based on the Number property but I don't know which list I should look at when it seems like the system is using messages from both, and I don't know what criteria is used for choosing.

For example:

  • Number 53 - from server error message list (exists on both)
  • Number 10054 - from system error message list (exists on both)
  • Number -1 - from server error message list (exists only on server list)
  • Number 121 - from system error message list (exists on both) ......
Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
Arie
  • 5,251
  • 2
  • 33
  • 54

2 Answers2

14

The theory goes that it's the SQL error number, eg. the server side ERROR_NUMBER(). In other words, the first list.

However there are a number of exceptions reported by SqlClient that occur on the client side, not on the server side. A typical example would be an error like failure to connect to the server, since you did not connect there is no server side error to speak of. For example a bad server name (does not resolve in DNS), in such cases the InnerException will point toward a Win32Exception with NativeErrorCode value of ERROR_BAD_NETPATH. In this case 53, the OS system error code, will be reported as SqlException error number.

Other cases the error reported by the SqlClient is a socket error, like an abrupt disconnect. Again, there is no 'server side' error to speak of, and the SqlException will wrap an InnerException of type SocketException (a subclass of Win32Error) with the NativeErrorCode of one of the well known WSA error numbers, like WSAECONNRESET. In this case the SqlException.ErrorNumber will be 10054, but it's the 10054 from the WSA range, not the 10054 from the SQL Server errors range. I know...

So what are you supposed to do? Make sure you check the InnerException, if it's a Win32Exception then the ErrorNumber is coming from a system error code (OS error). Otherwise it should be a SQL Server error number.

Oh, and then there is -1... I think that is reported by SqlClient itself (eg. some internal state errors) but I'm not sure.

Pang
  • 9,564
  • 146
  • 81
  • 122
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Brilliant answer. It's worth noting that the hex Win32 codes are converted to decimal in the actual exception. For example we're getting exception number 258 with text *The wait operation timed out.* 258 converted to hex is 0x102, and from the [Win32 codes](https://msdn.microsoft.com/en-us/library/cc231199.aspx) it is clear that error code 0x00000102 matches the error text we're seeing. – Sir Crispalot Jan 06 '16 at 14:30
  • Is there some documentation that guarantees this behavior? The msdn article for SqlException.Number seems to be silent on this. – Mike Asdf Mar 01 '16 at 22:16
3

I would look at the documentation for the SqlException.Number property.

This is what it says

This is a wrapper for the Number property of the first SqlError in the Errors property. For more information on SQL Server engine errors, see SQL Server Books Online.

user2864740
  • 60,010
  • 15
  • 145
  • 220
Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
  • Thanks for pointing me in the right direction (this: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlerror.number.aspx ) – Arie Sep 19 '13 at 12:24