Handling provider-level errors from .Net SqlClient Data Provider

Hi,

After reading this helpful blog entry:

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I think this might be a good place to ask the following question.

I am writing the error handling code for my data access layer for a web application. I am using the Enterprise Library Data Access Application Block. Although this supports generic database connections, I realized that I need to handle errors specific to each database type. Microsoft SQL is the only database type I am using for now, so I am using a try...catch (SqlException e).

In testing my code, I intentionally changed the instance name in web.config to a name that does not exist. I get the very popular error 26 - Error Locating Server/Instance Specified. This is returned as a SqlException, but the SqlError.Number property is set to -1.

Am I getting "-1" because the provider hasn't actually connected to SQL yet, so it doesn't have an actual SQL error number? Can I assume that (SqlError.Number == -1) is always a fatal, provider-level connection exception? Will the provider ever use another SqlError.Number of its own? Or do all numbers besides -1 come from the SQL sysmessages table?. Is there a comprehensive list of what exceptions might be raised by the SqlClient provider, including #26?

The reason for all the questions is that in a web application, I want to prevent the end-user from seeing the "real" exception if it has to do with configuration errors. However, maybe there are other errors that the user should see and handle? It's hard to know without a full list of SqlClient provider errors, along with the SqlError.Number that each error maps to.

Thanks and regards,

Mark

I can only answer 1. SqlError.Number corresponds to the SQL Server message id from sys.messages table on the server, so if it is not -1 it indicates an error message coming from the server.

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks

Waseem

|||

Waseem Basheer - MSFT wrote:

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks Waseem. I'm familiar with sysmessages. For messages coming directly from SQL server, sysmessages is all I need.

I am looking for a comprehensive list of SqlClient messages that are NOT in sysmessages, i.e. the errors that are raised directly by the .NET SqlClient provider. Hopefully this is a much more limited number of messages.

Regards,

Mark

|||

Hi, mcbsys

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

HTH

Ming.

|||

MING LV wrote:

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Good idea, I will try that.

Thanks,

Mark