Determine whether SQL Server exists

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?

Hello _DD,

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

If you want a relatively short timeout, you can use ADOX. It is a COM
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" <_DD@nospam.com> wrote in message
news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?

"_DD" <_DD@nospam.com> wrote in message
news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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.

On Sun, 17 Sep 2006 16:17:16 +0200, "Willy Denoyette [MVP]"
<willy.denoyette@telenet.be> wrote:

>
>"_DD" <_DD@nospam.com> wrote in message
>news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?

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.

_DD,

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" <_DD@nospam.com> schreef in bericht
news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?

_DD.

Your answer here -
http://blah.winsmarts.com//2006-9-D...ver_exists.aspx

- Sahil Malik
http://www.winsmarts.com


"_DD" <_DD@nospam.com> wrote in message
news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?

Hello Sahil Malik [MVP C#],

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-D...SQL_Server_exis
S> ts.aspx
S>
S> - Sahil Malik
S> http://www.winsmarts.com
S> "_DD" <_DD@nospam.com> wrote in message
S> news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?
>>
---
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

Ok, in response to this and the other thread that wanted to test to see if
SQL Server is available try the code from my new blog entry:
http://betav.com/blog/billva/2006/0...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" <_DD@nospam.com> wrote in message
news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?

Okay you are going to explain a bit more to me.

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" <nemtsev@msn.com> wrote in message
news:1799a79b3ad4788c8a8d1555db8cb@msnews.microsoft.com...
> Hello Sahil Malik [MVP C#],
>
> 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-D...SQL_Server_exis
> S> ts.aspx
> S> S> - Sahil Malik
> S> http://www.winsmarts.com
> S> "_DD" <_DD@nospam.com> wrote in message
> S> news:15qpg29v5l2joh2lafv6clfa6irh7su6sj@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?
>>>
> ---
> 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
>
>