By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,212 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jul 19 '06 #1
Share this Question
Share on Google+
14 Replies


P: n/a
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.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Marcus" <ho**********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
>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

Jul 19 '06 #2

P: n/a
Sy
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" <ho**********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
>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

Jul 19 '06 #3

P: n/a
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googlegr oups.com...
>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

Jul 19 '06 #4

P: n/a
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googlegr oups.com...
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
Jul 19 '06 #5

P: n/a
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
>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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googleg roups.com...
>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

Jul 19 '06 #6

P: n/a
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
Arnie Rowland wrote:
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.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Marcus" <ho**********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
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
Jul 19 '06 #7

P: n/a
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94

Thanks,
Marcus

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googlegr oups.com...
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
Jul 19 '06 #8

P: n/a
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
Sy wrote:
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" <ho**********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...
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
Jul 19 '06 #9

P: n/a
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@s13g2000cwa.googlegro ups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94

Thanks,
Marcus

Cor Ligthert [MVP] wrote:
>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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googleg roups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googleg roups.com...
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


Jul 20 '06 #10

P: n/a
Sy
Sorry just read this... try looking here...
http://msdn.microsoft.com/sql/expres...lexcustapp.asp

Might get you going down the right track...

Cheers, Sy

"Marcus" <ho**********@hotmail.comwrote in message
news:11*********************@75g2000cwc.googlegrou ps.com...
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
Sy wrote:
>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" <ho**********@hotmail.comwrote in message
news:11**********************@s13g2000cwa.googleg roups.com...
>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

Jul 20 '06 #11

P: n/a
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

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@s13g2000cwa.googlegro ups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94

Thanks,
Marcus

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googlegr oups.com...
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
Jul 20 '06 #12

P: n/a
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
"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@b28g2000cwb.googlegro ups.com...
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

Cor Ligthert [MVP] wrote:
>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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@s13g2000cwa.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94

Thanks,
Marcus

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googleg roups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object
providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions
options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googleg roups.com...
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

Jul 20 '06 #13

P: n/a
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.
Cor Ligthert [MVP] wrote:
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
"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@b28g2000cwb.googlegro ups.com...
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

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@s13g2000cwa.googlegro ups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94

Thanks,
Marcus

Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@m73g2000cwd.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object
providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions
options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 80


Cor Ligthert [MVP] wrote:
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

"Marcus" <ho**********@hotmail.comschreef in bericht
news:11**********************@s13g2000cwa.googlegr oups.com...
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

Jul 20 '06 #14

P: n/a
hi marcus

see whether this additional checking of networkconnecvity will help you
catch the reason (reasonable???!!!) before sql catches it?

refer new features of .net 2.0 (networkavailability event of application
object)
in vb.net VS2005 IDE provides but in case of c# u have to use delegate and
add your event handler.

http://msdn.microsoft.com/coding4fun...2/default.aspx

my 2 cents

cheers.
sahridhayan

"Marcus" wrote:
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.
Cor Ligthert [MVP] wrote:
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
"Marcus" <ho**********@hotmail.comschreef in bericht
news:11*********************@b28g2000cwb.googlegro ups.com...
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
>
>
>
Cor Ligthert [MVP] wrote:
>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
>>
>"Marcus" <ho**********@hotmail.comschreef in bericht
>news:11*********************@s13g2000cwa.googlegr oups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at
System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CS_SqlServerNET.DALC_SQLDB.TestConnection() in
C:\CountersoftNET\CS_SqlServerNET\DALC_SqlDb.vb:li ne 94
>
Thanks,
Marcus
>
>
>
Cor Ligthert [MVP] wrote:
>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
>>
>"Marcus" <ho**********@hotmail.comschreef in bericht
>news:11**********************@m73g2000cwd.googleg roups.com...
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.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(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.Ope nLoginEnlist(SqlConnection
Jul 28 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.