3

In Microsoft.Data.SqlClient/System.Data.SqlClient libraries, the Sql errors returned with the SqlException have a property called Number.

In many of StackOverflow questions and other discussions over the internet people suggest to use this property to catch specific issues related to the exception (e.g. deadlock, duplicate keys, etc.).

However, based on the official Microsoft documentation this property can be populated both from the server error code (SQL Server's master.dbo.sysmessages table), Win32 error code and can have some other numbers too (e.g. -2 for timeout). Does this mean that the same number can mean different thing based on the fact where the exception is being thrown from?

An example for this is number 10054.

  1. Based on the data on the internet, this is being thrown when there is a connecitivy issue - https://dba.stackexchange.com/questions/329332/unable-connect-to-sql-server-on-ssms-and-got-error-10054
  2. Based on what I see in the SQL Server's master.dbo.sysmessages table, number 10054 corresponds to "The data value for one or more columns overflowed the type used by the provider.".

Another example is 233.

  1. Based on the data on the internet, this also is being thrown when there is a connectivity issue - SQL Server connection arbitrarily returns 233 or 18456 error codes
  2. Based on what I see in the SQL Server's master.dbo.sysmessages table, number 233 corresponds to "The column '%.*ls' in table '%.*ls' cannot be null."

So, I want to understand why everyone on the internet is suggesting to use this number to assume what the exact SQL exception is and how safe it is when the same number can mean different things?

Improve this question
4
  • SQL error numbers can be trusted, but only when carried by SqlException. Otherwise you can get any other error (including texts w/o even a number). 10054 is a socket error (WSAECONNRESET), so a lower-layer level error, relayed by SQL layers (learn.microsoft.com/en-us/troubleshoot/sql/database-engine/…) Commented Oct 14, 2024 at 18:24
  • @SimonMourier What does "carried by SqlException" exactly mean? The OP got 233 from the SqlException's Number property in my second example. Commented Oct 14, 2024 at 18:30
  • 1
    When i peek into the documentation SqlException.Number is just the Number of the first Error in the SqlException.Error my take away is that the number is not garantueed to be unique but in conjunction with a potential Inner Execption you might look at it get closer to being unique. And if you want to be even more concrete you have to dive into the SqlError object in the Errors list. I would still assume just looking at the number is close enough. Commented Oct 14, 2024 at 19:00
  • ... the likelyhood of a collision seems just to slim. Commented Oct 14, 2024 at 19:01

1 Answer 1

Reset to default
2

There are some overlaps, and you have to use the context to figure it out. As a rule of thumb you'll always be looking at sys.messages if the session is healthy, and the error is actually coming from SQL Server.

You'll get numbers related to Winsock error codes if you fail to establish a connection or a connection is suddenly broken.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the clarification. I understand that the chances of collision is quite rare, but out of curiosity - what is the reason SqlException/SqlError does not include any property to distinguish between the source of the error? Based on my tests, the "Source" property is always set to "Framework Microsoft SqlClient Data Provider" regardless of the fact whether the error is coming from the sys.messages table or it's a Win 32/other error. Is it something that is not possible to distinguish in a system level?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.