Determine whether SQL Server exists
_DD
validity of a SQL connection string. I'd prefer to keep the timeout
low. What is the conventional way of doing this?
Michael Nemtsev
Just make connection to DB to check whether it valid or not
_> I'd like to include a 'Test Connection' button in an app, for testing
_> validity of a SQL connection string. I'd prefer to keep the timeout
_> low. What is the conventional way of doing this
_>
---
WBR,
Michael Nemtsev :: blog: http://spaces.live.com/laflour
"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche
Cowboy (Gregory A. Beamer)
library that allows you to determine if a server exists, if a database
exists, the objects in a database, etc. The other option I can think of off
hand is to simply connect and allow it to fail.
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
*************************************************
Think outside of the box!
*************************************************
"_DD" <_D...@nospam.com> wrote in message
news:15qpg29v5l2joh2la...@4ax.com...
![Willy Denoyette [MVP]'s profile photo](http://lh3.googleusercontent.com/a-/ALV-UjXfXiAXGhHYcXf-iOUYGXHMIHmtAw4w01ItpjRcuAnrUu-vVQ=s40-c)
Willy Denoyette [MVP]
| validity of a SQL connection string. I'd prefer to keep the timeout
| low. What is the conventional way of doing this?
Set the timeout value ("Connect Timeout") in the connection string to the
desired value and issue a SqlConnect.Open. This is the only way to test
whether a Connection string is valid AND the server is accepting the
connection.
Willy.
_DD
Willy (and others),
I don't think the connection string's "Connect Timeout" affects the
'hard fail' timeout when the connection string points at a server that
does not exist...does it?
I was initially just trying to connect and getting a go/no go. But I
want to do a couple things that seem out of scope for that approach:
I'd like to differentiate between 'server does not exist' and
'database does not exist'. And I'd like to eliminate the long delay
incurred when the server can't be found.
Cor Ligthert [MVP]
Do as in my idea Michael suggest,
Use an SQLClient Connection and put that in a catch, if it fails then there
is no connection. Although in my idea it has not much sense, to do this
appart from your normal ADONET datalayer parts.
SQLConnection con = new SQLConnection();
Try
{con.open;}
Catch
{MessageBox.Show("There is no or malfunction connection");}
If this is not in a form class than you have beside the reference to
SQLClient to set a reference to the System.Windows.Forms for the MessageBox.
Typed in this message so watch typos.
I hope this helps,
Cor
"_DD" <_D...@nospam.com> schreef in bericht
news:15qpg29v5l2joh2la...@4ax.com...
Sahil Malik [MVP C#]
Your answer here -
http://blah.winsmarts.com//2006-9-Determine_whether_a_SQL_Server_exists.aspx
- Sahil Malik
http://www.winsmarts.com
"_DD" <_D...@nospam.com> wrote in message
news:15qpg29v5l2joh2la...@4ax.com...
Michael Nemtsev
Yep, but this check only existence of SQL Server not its availability.
For example, other DBs like Oracle check you connection by requesting your
credentials, but not checking existence of this db instance
S> _DD.
S>
S> Your answer here -
S> http://blah.winsmarts.com//2006-9-Determine_whether_a_SQL_Server_exis
S> ts.aspx
S>
S> - Sahil Malik
S> http://www.winsmarts.com
S> "_DD" <_D...@nospam.com> wrote in message
S> news:15qpg29v5l2joh2la...@4ax.com...
S>
>> I'd like to include a 'Test Connection' button in an app, for testing
>> validity of a SQL connection string. I'd prefer to keep the timeout
>> low. What is the conventional way of doing this?
>>
William (Bill) Vaughn
SQL Server is available try the code from my new blog entry:
http://betav.com/blog/billva/2006/09/question_of_the_day_enumeratin.html
This includes part of an example from my new book that lists all of the SQL
Servers and permits you to determine the state of the service. In other
words you can tell if the SQL Server is running. Does this validate a
ConnectionString? Nope, but it can tell you if the ConnectionString
validation has failed because the service was not there or not running. Even
if you do check to see if you can open a connection, you'll get a pooled
connection that's already connected after the first attempt... not
particularly useful if the service is actually dead... I drone on and on
about this in the book.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"_DD" <_D...@nospam.com> wrote in message
news:15qpg29v5l2joh2la...@4ax.com...
Sahil Malik [MVP C#]
So once I check for the existence - why can't I just try and connect to it
and verify it's availability?
SM
"Michael Nemtsev" <nem...@msn.com> wrote in message
news:1799a79b3ad478...@msnews.microsoft.com...
Willy Denoyette [MVP]
| On Sun, 17 Sep 2006 16:17:16 +0200, "Willy Denoyette [MVP]"
| <willy.d...@telenet.be> wrote:
|
| >
| >"_DD" <_D...@nospam.com> wrote in message
| >news:15qpg29v5l2joh2la...@4ax.com...
| >| I'd like to include a 'Test Connection' button in an app, for testing
| >| validity of a SQL connection string. I'd prefer to keep the timeout
| >| low. What is the conventional way of doing this?
| >
| >Set the timeout value ("Connect Timeout") in the connection string to the
| >desired value and issue a SqlConnect.Open. This is the only way to test
| >whether a Connection string is valid AND the server is accepting the
| >connection.
| >
| >Willy.
|
| Willy (and others),
|
| I don't think the connection string's "Connect Timeout" affects the
| 'hard fail' timeout when the connection string points at a server that
| does not exist...does it?
|
Interface (SNI) layer (unmanaged code) to connect using the protocol as
specified in the connection string or the default protocol, but SNI ignores
the "Connect Timeout" for the initial physical connect (it issues a
synchronous connect) and uses it's own hard coded timeout. The reason for
this is that SQLClient cannot make any progress without a physical
connection, it needs it to authenticate the client in the first place,
without an authenticated connection you won't be able to progress. That
means that this method cannot be used to achieve your goal.
Now, there might be many reasons for a connect failure: the client network
connection may be broken (both at the physically and network stack level),
same for the server side, the netwok itself may be down (think:router
switches etc...), the server (IP or NP host) may be down or non-existing the
SQL instance may be down or non-existing, SQL instance way be in maintenace
mode etc..,
It's just impossible to distinguish between all these type of failures, all
you can do is check the client side of the network stack, you can check if
the network is up and connected to, you could 'ping' the server (host), you
can 'telnet' the sql server instance (telnet hostaname sqlport), but hey
what if one of these fail, what's the cause?
And what if this succeeds, who says that finally your client will use TCP to
connect to the SQL instance? Or what if authentication fails? or the login
server is not available (when using integrated security in an AD domain
realm).
I think you should forget about this, if a connection fails, you will have
to investigate the failure, but don't try to implement this in an end-user
application, it makes no sense, really.
Willy.
Willy Denoyette [MVP]
Ok, did some further investigation, and it looks like SQLClient (V2 of the
framework and SQL2005) effectively considers the "Connect Timeout" value for
the initial connect (+ 1 second). That means that if you set Connect
Timeout=1 in the connectionstring, the connection will timeout after 2
seconds, while a value of 3 will take 4 seconds.
Willy.
William (Bill) Vaughn
I agree... but I'm not convinced that the provider "can't" bubble up more
details on why it couldn't connect. I expect that the generic nature of .NET
providers makes it tough to return more granular details about the failure.
Thanks -- Learn something new every day.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Willy Denoyette [MVP]" <willy.d...@telenet.be> wrote in message
news:OwUk2Iw2...@TK2MSFTNGP03.phx.gbl...
Willy Denoyette [MVP]
"William (Bill) Vaughn" <billvaRe...@nwlink.com> wrote in message
news:%23tmcA7z...@TK2MSFTNGP03.phx.gbl...
| Wow--I'm impressed. (seriously).
| I agree... but I'm not convinced that the provider "can't" bubble up more
| details on why it couldn't connect. I expect that the generic nature of
.NET
| providers makes it tough to return more granular details about the
failure.
| Thanks -- Learn something new every day.
|
| --
|
William, the provider could possibly bubble up some more details, but it can
only bubble up what's coming from the network stack.
To explain what I mean we should take a close look at (ISO) stack as used by
SQL server (or any other DBMS):
Starting from the top - the Application layer - we have the SQLClient
managed API, (SNI and the TDS parser),
below this one (the Session layer) we have the network protocol library
(pipes, sockets, shared memory etc...),
sitting in top of our Network stack (except for the shared memory protocol),
this one depends on the used client session protocol library, but for the
socket protocol we have TCP at the transport layer, IP at the protocol
layer, HDLC the network layer and finally the physical layer (Ethernet LAN,
WAN, etc....)
Now , you see that if you want to bubble up detailed failure notifications,
you need a Network stack in which each layer not only provides in error
recovery but also returns detailed error message when something goes wrong
at the lower layer to the upper layer, and here's where the problem starts.
The network stack does not (should not and in most cases cannot) provide
this status reporting (though ICMP provides some possibilities, it's
un-common to use it from a regular application), it only has provisions for
error recovery at his own layer, and a very simple protocol for error
reporting to it's upper layer. Consider that the physical layer cannot
connect (say cable broken), it will signal it's upper layer that a physical
connection is not possible so the network layer cannot push a connection
HDLC request frame through the network infrastructure. But, it cannot say
what's actually broken at the physical layer, where is the cable broken? at
the client side or at the switch or the router or ...?
The same is true for every layer in the stack, in some cases detailed info
may be available, like irrecoverable transmission errors at the HDLC layer,
but most of the time there is no such info, and more, it makes no sense to
"bubble up" this to the application layer, it's not it's concern, it's up to
the IP layer to start a recovery action (retry) when there is a failure at
the Network layer and it's up to IP to signal the same to it's upper layer
and so on until you reach the application layer, where the only thing that
can be done is report a failure message to the user and/or start a recovery
action, that is, retry the SQLClient Open or give up and let the network and
the DB admin's try to find out the cause of the failure.
Willy.
Willy.
William (Bill) Vaughn
decade... ;) You have a firm grasp on the issues.I see it as a long-term
problem that MS and the provider developers have not been able to fully
address. I've worked with these drivers for awhile and I know it's tough to
be able to bubble up something that's only manifested as a timeout on a NIC
or a "broken pipe" back from the server. When we were (a bit) closer to the
TDS with DBLib we could give developers a bit more information--far more
than the "stuff happened" errors we now see. I also think the exception
string that comes back suggesting that the server is not properly configured
is like telling a driver that he should have been driving slower after
having a bread truck sideswipe him on the freeway.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Willy Denoyette [MVP]" <willy.d...@telenet.be> wrote in message
news:u5awjj02...@TK2MSFTNGP05.phx.gbl...
Willy Denoyette [MVP]
| Yes, this has been detailed to me about a dozen times over the last
| decade... ;) You have a firm grasp on the issues.I see it as a long-term
| problem that MS and the provider developers have not been able to fully
| address.
True, but even if the Network stack could bubble up extended error
information (through error codes f.i), they would not be able to report
details about what's wrong at the physical network layer (infrastructure).
Even in the simple case like a wrong hostname (program error) or instance
name , the network layer has no idea about what's wrong in this case. The
"name lookup" isn't able to resolve the address, but why? Say that the error
message in this case looks like "Name lookup for server "xxxx" failed , make
sure the server name refers to an existing server, check you DNS server or
your etc\hosts file. Check also you network infrastructure, like routers and
switches and cables."., honestly, this is not what I call more helpfull as
we get now :-)
Another thing you must consider is performance, the network stack (the
winsock library and NDIS) is not a place to waste too many cycles, I really
don't think that 3COM and MS are willing to change the NSDIS specifications,
nor will anyone ever change the WinSock2 specifications,let alone change the
implementation, in order to include extended error reporting.
I have since long accepted this inconvenience, just like I accepted that
TCP/IP is far from perfect, I'm sure you also have done so ;-).
Willy.
Cor Ligthert [MVP]
You have forgotten the power cables on more places.
To be serious, what has this for sense. In my opinion is it only needed to
see if the data on the DataBase server is reachable. For that Michael's
solution as first stated is in my opinion more than enough,
Cor
"Willy Denoyette [MVP]" <willy.d...@telenet.be> schreef in bericht
news:e32q9c22...@TK2MSFTNGP03.phx.gbl...
Willy Denoyette [MVP]
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:%23jnvjN6...@TK2MSFTNGP06.phx.gbl...
| Willy and Bill,
|
| You have forgotten the power cables on more places.
|
| To be serious, what has this for sense. In my opinion is it only needed to
| see if the data on the DataBase server is reachable. For that Michael's
| solution as first stated is in my opinion more than enough,
|
| Cor
|
Which was exactly my answer, all I did was answering both of OP's questions:
Q1 ... testing validity of a SQL connection string ...
A. use SqlConnect.Open
Q2 ... to keep the timeout low...
A. set the connetionstring "Connect Timeout" parameter to a lower value.
The rest was just a technical chat with Bill.
Note that I don't necessarily agree with the OP to add a "test connection"
in an end-user's application (end-users should not test connections).
Willy.
William (Bill) Vaughn
there is dialtone. The second you hang up the proverbial beer truck can hit
the phone pole outside and cut you off. I suggest robust exception handlers
to deal with the stuff that happens when connecting. This is why the
"Getting Connected" chapter in my new book is 70 or so pages long (IIRC).
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Willy Denoyette [MVP]" <willy.d...@telenet.be> wrote in message
news:up8$NV82GH...@TK2MSFTNGP06.phx.gbl...
Cor Ligthert [MVP]
What is the trouble to help that the beer is not going over the road and
help to unload that beer truck?
Although Willy will probably write that if it is about beer, you have to be
sure it is good beer first before you are doing work that has no sense.
(He is from Belgian you know, and there is no country in the world with more
beer experts).
:-)
Cor
"William (Bill) Vaughn" <billvaRe...@nwlink.com> schreef in bericht
news:urqEYtA3...@TK2MSFTNGP04.phx.gbl...
Willy Denoyette [MVP]
| Using the "test connection" approach is like picking up the phone to see
if
| there is dialtone. The second you hang up the proverbial beer truck can
hit
| the phone pole outside and cut you off. I suggest robust exception
handlers
| to deal with the stuff that happens when connecting. This is why the
| "Getting Connected" chapter in my new book is 70 or so pages long (IIRC).
|
| --
| ____________________________________
Oh no please, not a beer truck :-(
Any idea when the book will hit the streets?
Willy.
William (Bill) Vaughn
blimp... ;)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Willy Denoyette [MVP]" <willy.d...@telenet.be> wrote in message
news:%23RUKitB...@TK2MSFTNGP04.phx.gbl...
Cor Ligthert [MVP]
A pity that the transport cost from that book with so much pages with
information is probably to expensive for us in the Benelux to buy.
Cor
"William (Bill) Vaughn" <billvaRe...@nwlink.com> schreef in bericht
news:OAh827N3...@TK2MSFTNGP06.phx.gbl...
Willy Denoyette [MVP]
should I say: like an "Hollander" a native inhabitant of Holland), however,
keep in mind that not all Benelux inhabitants are Dutchman :-)
Willy.
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:u4w8jbT3...@TK2MSFTNGP05.phx.gbl...
William (Bill) Vaughn
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
news:u4w8jbT3...@TK2MSFTNGP05.phx.gbl...
_DD
<willy.d...@telenet.be> wrote:
>
>"Cor Ligthert [MVP]" <notmyfi...@planet.nl> wrote in message
>news:%23jnvjN6...@TK2MSFTNGP06.phx.gbl...
>. In my opinion is it only needed to
>| see if the data on the DataBase server is reachable. For that Michael's
>| solution as first stated is in my opinion more than enough,
>Which was exactly my answer, all I did was answering both of OP's questions:
>
>Q1 ... testing validity of a SQL connection string ...
>A. use SqlConnect.Open
>Q2 ... to keep the timeout low...
>A. set the connetionstring "Connect Timeout" parameter to a lower value.
>
>The rest was just a technical chat with Bill.
>
>Note that I don't necessarily agree with the OP to add a "test connection"
>in an end-user's application (end-users should not test connections).
Sorry about the late reply. Posting problems.
I'm not sure that the "Test Connection" will be available to final end
users, but there are enough potential problems that having the "Test
Button" is important during early phases.
There are two things that I was trying to address:
The timout is unnecessarily long. The program stalls out while the
connect times out.
There is insufficient info returned to analyze the problem.
While the release version may end up just going dead for a while (as
it appears to the user), I would like to avoid that, and provide more
coherent information on how to correct the failure.
I liked the "slow down" error message when getting hit by the bread
truck, Bill. <g> That was actually a good analogy.