What does "A severe error occurred on the current command. The results, if any, should be discarded." SQL Azure error mean?

Question

Once in a while my code encounters

System.Data.SqlClient.SqlException
The service has encountered an error processing your request. Please try again.
Error code 40540. A severe error occurred on the current command.
The results, if any, should be discarded.
Class 20
Number 40197

that happens very rarely, usually goes away in a minute or two and I can't reliably reproduce it. Sometimes Error code can be a number other than 40540.

I've Googled a bit and looks like it's usually triggered by bugs in SQL Server and is reproducible.

I have two options - retry the query or treat it as fatal and break hard. I'd prefer to have better understanding what the problem actually is and whether I'm safe retrying the query.

Do I retry the query when this error occurs?

Answer 1

A quick google search gave me this.

The service has encountered an error processing your request. Please try again. Error code %d. You will receive this error, when the service is down due to software or hardware upgrades, hardware failures, or any other failover problems. Reconnecting to your SQL Database server will automatically connect you to a healthy copy of your database. You may see error codes 40143 and 40166 embedded within the message of error 40540. The error codes 40143 and 40166 provide additional information about the kind of failover that occurred. Do not modify your application to catch error codes 40143 and 40166. Your application should catch 40540 and try reconnecting to SQL Database until the resources are available and your connection is established again.

You can visit here for more information. It says Your application should catch 40540 and try reconnecting to SQL Database until the resources are available and your connection is established again.

Though on a personal note I would suggest not to use Sql azure as it throttles easily and you can't control the resources allocated to you. What I did was to install sql server on azure vm and use that in my application.

Hope this helps you.

Answer 2

I encountered similar error: And I followed the answer in http://social.msdn.microsoft.com/Forums/en-US/bbe589f8-e0eb-402e-b374-dbc74a089afc/severe-error-in-current-command-during-datareaderread which resolved the issue..

Avoid looping of reader; load data in a DataTable

DataTable table = new DataTable();
table.Load(reader);
reader.Close();