SqlConnection.Open() Which ErrorCodes

[Henrik Skak Pedersen]

Hi,

I am trying to discover which SQLException.ErrorCode SqlConnection.Open()
can throw. I have until now discovered these:

// 53 : {"An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server)"}
// 4060 : {"Cannot open database \"XXX\" requested by the login. The login
failed.\r\nLogin failed for user 'xx'."}
// 18456 : {"Login failed for user 'xx'."}
// 6 (SQL Server not found, or access is denied.)
// 17 (SQL Server not found, or access is denied.)
// 19 (General network error. Check your network documentation.)
// 18487 : Password expired
// 18488 : Password expired

Am I missing any?

Best regards
Henrik Skak Pedersen.

 

[WenYuan Wang [MSFT]]

Hi Skak,

According to your description, you application want to connect SQL server
2005 . However, the code statement SQLConnection.Open() throw an exception
that "An error has occurred while establishing a connection to the
server.". If I misunderstand anything here, please kindly correct me.

It seems like your SQL Server doesn't allow remote connection. Thus, your
application cannot connect this server from remote machine.

Please let me know which version of SQL server your machine is running on.
As far as I know, Networking protocols are disabled by default in SQL
Server Express.

To enable SQL Server 2005 Express/ Developer Edition to accept remote
connections,I would like to suggest you check the following document:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
[How to configure SQL Server 2005 to allow remote connections]

1) Enable remote connections for SQL Server 2005
2) Enable the SQL Server Browser service
3) Create exceptions in Windows Firewall

Hope this helps. Please let me know if this method works for you and I'm
glad to assist you.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Benny Skjold Tordrup]

Hi

I believe Henrik is asking for the *possible* errors that *may* occur when
trying to connect to a SQL Server. He is not asking for help about a
specific error.

Best regards,

Benny

 

[WenYuan Wang [MSFT]]

Hello Henrik,
Thanks for Benny's point, .

Oops, I must have misunderstood what you really need.
I think you just want to know if there is anything miss from the code list
which SqlConnection.Open() could cause.

Please wait me one more day, let me check for this.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Henrik Skak Pedersen]

Hi,

Yes exactly. After the SqlConnection.Open() has fired an exception I have to
catch the SqlException and examine the SqlException.Number property to
fugure out what has gone wrong. I am interested in a list of the different
error codes/numbers which the Open() statement can throw.

Cheers
Henrik-.

Hi

I believe Henrik is asking for the *possible* errors that *may* occur when
trying to connect to a SQL Server. He is not asking for help about a
specific error.

Best regards,

Benny

Hi Skak,

According to your description, you application want to connect SQL server
2005 . However, the code statement SQLConnection.Open() throw an
exception
that "An error has occurred while establishing a connection to the
server.". If I misunderstand anything here, please kindly correct me.

It seems like your SQL Server doesn't allow remote connection. Thus, your
application cannot connect this server from remote machine.

Please let me know which version of SQL server your machine is running
on.
As far as I know, Networking protocols are disabled by default in SQL
Server Express.

To enable SQL Server 2005 Express/ Developer Edition to accept remote
connections,I would like to suggest you check the following document:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
[How to configure SQL Server 2005 to allow remote connections]

1) Enable remote connections for SQL Server 2005
2) Enable the SQL Server Browser service
3) Create exceptions in Windows Firewall

Hope this helps. Please let me know if this method works for you and I'm
glad to assist you.
Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.

 

[Henrik Skak Pedersen]

Hi again,

If possible I actually also need the Error Numbers for ExecuteScalar,
ExecuteReader and ExecuteNonQuery.

Best regards
Henrik Skak Pedersen.

 

[WenYuan Wang [MSFT]]

Hi Henrik,
Thanks for your waiting.

I have spend the whole day searching on internet and documents, however,
doesn't found List which included all possible error number caused by
Open()/Execute***(). I also tried with Reflector. This doesn't help...

Sorry, for my curious, would you please kindly let me know why do you want
to get such Error Number list? If you just want to write some
trouble-shooting document for your end user, I think the information in
SQLException.error is enough.

For the entire error messages, I'm afraid the only way is that querying the
sys.messages system table for the message_id and text of SQL Server errors.
See the error messages topic in SQL Books Online. On the client side, see
the documentation in the .NET Framework Help for these classes:
SqlConnection.Open, SqlException, and SqlErrorCollection/SqlError, which
enables you to handle server-side errors. However, there must be a lot of
work for us.

I'm sorry if you feel this isn't really helping...
But, would you please let us know what your objective is? Maybe there is
other way to approach that without the Erro Number List...

Thanks again for your patience
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[WenYuan Wang [MSFT]]

Hi Henrik,

How are you? This is Wen Yuan again.
I just want to check if there is anything we can help with.
Would you please kindly let us know what do you want to do with ErrorCodes?
I'm afraid it is not a good way to collect all possible error numbers from
Open() or Execute() method. Sometime the error message comes from SQL
server. However, there are about 7653 rows in sysmessages table...

Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Henrik Skak Pedersen]

Hi Wen,

Thank you for your reply.

I would just like to show a decent error messsage to the user if something
goes wrong during the connection phase.

Cheers
Henrik.

 

[WenYuan Wang [MSFT]]

Hello Henrik,
Thanks for your reply.

In my opinion, I'd like to provide end users a detailed guide to avoid
possible connect error, rather than teach them how to trouble shoot connect
issue.

You can write some documents (Something looks like Readme or KB) told end
user how to deploy application step by step. If they could follow the
steps, in general, they could avoid all issues.
The document may also involve System Requirements, known issues and FAQ.

On the other side, I don't think the error message from ADO.net or SQL
server is always exact. Sometime, the error thrown by ADO.net is different
from the actual issue. For example: if there is something error with
network or SQL Server doesn't allow remote connection, ADO.net will tell
you it cannot found the remote server. It is really difficult to image all
the possible reason for each error. If they face some special issue, then
let him tell you. After you resolved it, you can add the case into the
document. I think this is a good way to achieve.

Hope this helps
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[William \(Bill\) Vaughn]

I understand completely. We've been trying to get MS to divulge the error
numbers (like we had back in VB6) for some time now. The problem with the
exceptions is that they are often too generic ("Something happened") and the
messages don't really help an end user any more than a fist-full of
documentation. As it is the messages are often less than useful--sometimes
pointing blame at conditions that don't exist and are way beyond the control
of the end user.

I'm sure you understand it's not the end-user that needs to debug the
connection issue--it's your application. None of the messages returned by
the Framework are suitable for end-user consumption. They are the person
with the least chance of fixing the problem. Without an in-depth
understanding of the exceptions and the information they return you're
running blind. In addition, if you write code to test for a specific
exception message, you're pooched when the application has to be ported to
another language (English to French, or English to Texan).

I applaud your efforts to bring sanity to the handling of exceptions. I
might suggest that you look at the Exception Handler Blocks that might
clarify some of this for you (and everyone else).

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[WenYuan Wang [MSFT]]

Hello Henrik

How are you. I just want to check if there is anything we can help with.
Please feel free to update here. We are glad to assist you.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.