HELP! Why is SqlException being caught in this code if it can't connect to SQL Server?

  • Thread starter Thread starter Marcus
  • Start date Start date
M

Marcus

I have a function that simply returns TRUE if it can connect to a
particular Sql Server 2005 express, or FALSE if it cannot. I am getting
some strange error codes returned when the computer that sql server
resides on is not reachable. The error is different depending on the
connection string that I use. If I use the following connection string:

"server=192.1.1.1; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 10"

then the error number I get for the sqlexception caught is 53. If I add
'tcp:' in front of the IP address in the connection string like this:

"server=tcp:192.1.1.1; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 10"

then the error number I get for the sqlexception is 10060. If I add an
instance to the server in the connection string like this:

"server=192.1.1.1\SQLEXPRESS; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 10"

then I get the error number -1 for the SqlException.

I cannot find any reference to any of these error codes. Why is this
even being caught as a SqlException if it cannot even connect to the
Sql Server in the first place? Aren't the error numbers for
SqlException supposed to come from the master..sys.messages table???

Also, the timeout value seems to have no effect for the first two
connection strings above, with it taking usually around 30 seconds for
the exception to be caught.

I am confused...!

Here is the function I am using:

Public Function TestConnection() As Boolean
Dim myConnStr as string = "server=192.1.1.1; Initial
Catalog=master; uid=The_User; password=The_Password; Connect Timeout =
10"
Dim mySqlConn As new SqlConnection(myConnStr )

Try
mConn.Open()
Return True

Catch ex As SqlException
Debug.writeline(ex.tostring)
Debug.writeline("---------------- The error code was "
ex.number.tostring)

Return False

Catch ex As Exception
Return False
End Try
End Function


Thanks for any enlightenment.

Cheers,
Marcus
 
The SQLException is coming from the SQLClient library. The
SQLClient.Connection object has an error if it cannot find or connect to the
server -therefore having to retreive error messages from sysmessages would
not be viable. SQLException is both the client side (ADO) errors and tthe
Server side (SQL) errors. You should be able to find all of the connection
related messages in the appropriate dll file -perhaps using ISDASM or some
other 'viewing' tool.

My 'guess' is the first two connection strings are probably taking longer to
time out since a server name is not supplied, and the SQLClient is having to
broadcast a request for a server to respond at that address, and then having
to wait for a response before it even attempts to connect. The third one is
able to time out after the set 10 seconds since it's name is known and it
cannot connect in that time.
 
Hmmm, Might be a silly question on my part... but, you didn't mention it in
your post...

Can you actually connect to the DB through SQL Management Studio Express?

Have you confirmed you have TCP/IP native client protocols
enabled/configured properly?

If the above are true, then is your firewall causing the problem, turn it
off and have a go...

If still no luck... then you start digging on the ADO to SQL connection...

Cheers, Sy
 
Marcus,

You are showing the error text as well, can you give that to us, now you
have more insight than we and ask us to enlighten you?

Cor
 
Here is the Sqlexception information.

Thanks,
Marcus


A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
System.Data.SqlClient.SqlException: 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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean&
useFailoverPartner, Boolean& failoverDemandDone, String host, String
failoverPartner, String protocol, SqlInternalConnectionTds connHandler,
Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:line 80
 
I suspect that you don't want an answer given this to us without telling on
what connectionstring these errors are given.

No problem.

Cor
 
Thanks, Arnie. That helps me understand SqlException a little better.
How would I find all those connection-related messages and error codes?
I'm not familiar with ISDASM, but will look into it.

Some background:
The application I am building allows the user to enter in an IP address
and an instance of a sql server to connect to (there could be many
different sql servers on the network). I want to give the user feedback
about what they enter, i.e. whether the machine is not reachable at
all, whether the machine is reachable but there is no sql server
present, etc. If the connection fails to the server, I want to be able
to read the error code and determine the nature of the failure. What I
was hoping to do was if the connection failed to sql server I would
then ping the server and see if it exists at all . The problem I am
facing now is it is taking too long to timeout even when the timeout
value in the connection string it set to a small value (in the scenario
where the user enters an ip address for sql server express but no
instance value).

Marcus
 
Whoops, sorry about that. Here is the SqlException for the connection
string:

(The other connection strings and errors are below)...

"server=192.1.1.1; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 1": (produces a sqlexceptino
number of 53)

A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
System.Data.SqlClient.SqlException: 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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean&
useFailoverPartner, Boolean& failoverDemandDone, String host, String
failoverPartner, String protocol, SqlInternalConnectionTds connHandler,
Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:line 94



For the connection string "server=tcp:192.1.1.1; Initial
Catalog=master; uid=The_User; password=The_Password; Connect Timeout =
1" -- (produces a sqlexception number of 10060)

A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
System.Data.SqlClient.SqlException: 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: TCP
Provider, error: 0 - A connection attempt failed because the connected
party did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean&
useFailoverPartner, Boolean& failoverDemandDone, String host, String
failoverPartner, String protocol, SqlInternalConnectionTds connHandler,
Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:line 94


For the connection string "server=192.1.1.1\SQLEXPRESS; Initial
Catalog=master; uid=The_User; password=The_Password; Connect Timeout =
1" -- (produces a sqlexception number of -1)

A first chance exception of type 'System.Data.SqlClient.SqlException'
occurred in System.Data.dll
System.Data.SqlClient.SqlException: 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: SQL
Network Interfaces, error: 26 - Error Locating Server/Instance
Specified)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean&
useFailoverPartner, Boolean& failoverDemandDone, String host, String
failoverPartner, String protocol, SqlInternalConnectionTds connHandler,
Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:line 94

Thanks,
Marcus
 
Hi, Sy

I can connect fine to any sql server 2005 instance that exists on the
network with my code or with sql server management studio. The problem
is when there server does not exist. I user of my application may enter
incorrect server information and I need to gracefully handle this.
Having it timeout after 30 seconds is too long (even though I am
setting the timeout to 10 sec).

Cheers,
Marcus
 
Marcus,

All those errors are saying that your SQL server is not set to allow remote
connections, that it than traps other errors as well is in my opinion not
important.

Cor
 
Hi, Cor

The purpose of the TestConnection() function I am writing is to
determine if the user of my application has entered an ip address that
(1) exists at all, and (2) has a Sql Server on it that it can connect
to. My main concern here is when the machine does not exist or is
unreachable, that the timeout is taking far longer than the timeout
specified in the connection string - not good for a user interface. I
suppose my code could first ping the machine and if it fails then don't
try to connect with ado, but the risk there is that some machines may
be blocking ping requests. For the connection string that I tested with
(and posted here) I was using an ip address for a machine that did not
exist (192.1.1.1) hence the comment in the exception about "server may
not be set to allow remote connections".

Marcus
 
Marcus,

Is it really important when a user has entered an IP address where a SQL
server should be if it goes or not?

The program cannot connect to the server, is it your duty to find every
reason for it. There can be a power down of the Server during the connection
and than what. In my opinion do you want to grasp of more than your
responsibility can hold.

In my idea you have only to tell that there is something wrong with the
parameters he has entered eventually you can tell to check the internet
connection.

However just my opinion,

Cor
 
Thanks for your input, Cor. Yes, you are right, perhaps I am putting a
little overkill into user feedabck. But what is really bugging me is
the connection timeout property in the connection string seeming to
have no effect when the machine is unreachable and the sql server ip is
specified but not the instance.

Example:

"server=192.1.1.1\SQLEXPRESS; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 10" will timeout out properly,
but "server=192.1.1.1; Initial Catalog=master; uid=The_User;
password=The_Password; Connect Timeout = 10" will take much longer
(about 30 seconds).

Hmmm...

M.
 
Back
Top