How to catch properly an SqlException: A transport-level error has occurred

Question

I am getting an SqlException in the logs of .NET 3.5 app, I am looking for the corresponding number (value of the property SqlException.Number).

System.Data.SqlClient.SqlException: A transport-level error has occurred 
when receiving results from the server. (provider: TCP Provider, error: 0 
- The specified network name is no longer available.) at
System.Data.SqlClient.SqlConnection.OnError

I am also getting error such as:

System.Data.SqlClient.SqlException: A network-related or instance-specific error 
occurred while establishing a connection to SQL Server. The server was not found 
or was not accessible. Verify that the instance name is correct and that SQL Server
is configured to allow remote connections.

Does anyone know how to properly catch those exceptions? (you can also post your answer here)

Tews

Answer 1

That particular error does not come from sys.messages in SQL Server - as it's a problem with the connection. So I don't have the number to hand.

TBH, the best thing you should do is to ensure the Number is written out to the log with the exception - that way, you'll always have the number and message side by side in the logs. May not answer your current question, but it will be the best going forwards IMHO. Not only SqlException.Number but SqlException.ErrorCode too - in instances like this I think .ErrorCode may be the one you actually need (not sure without checking).

Supersession

For me, these are the exception's property values: Number=233, ErrorCode=0x80131904, Class=20, State=0. The message is the same (“A transport-level error has occurred […]”). – Herbert

Answer 2

You will not find an associated SQL Error number for this one - simply put, the error occurs because there was a problem with connecting to the server.

As the code cannot connect to SQL Server, it will not have a SQL error to report.

From the MSDN article about SQLException.Number:

This number corresponds to an entry in the master.dbo.sysmessages table.

For exceptions that do not have an associated SQL Error number, you may be able to use the windows HRESULT error code that will be in the ErrorCode property of the exception object.

Eidson

What is the "safe" way to catch this error then? I am developing an app on Windows Azure, and this sort of error should be caught by the retry policy. – Tews

You will have to parse the error message for something like A transport-level error has occurred when receiving results from the server and decide accordingly. – Eidson

Hum, that's really ugly. In particular, it does not even work on my machine who happen to throw a localized (French) error message... – Tews

I think it's the .ErrorCode property you'd be interested in, in this case - that should give a way to identify the specific error without parsing the text – Supersession

Answer 3

Folks, please examine actual exception stack before blindly claiming that SqlException doesn't have .Number just besaus some MSDN page has a boilerplate text. .ErrorCode usually gets one single generic value (set by private CTOR). For this patricular error you can see exception stack here:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/cb61ec85-c0d4-4a26-90e0-8c98cd28332f

and if you follow it you'll see that the code that's preparing is fills in everything but it's a bit convoluted to trace actuall error number in reflector.

So, whoever sees things next pleace post actual error number (or numbers). These errors tend to be quite rare and transient and it would be good to have numbers since at the time a server sees connection hiccup it's too late to go chasing their numbers.

Pyaemia