How is SqlException Number assigned

Question

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) ......

Answer 1

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.

Answer 2

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.