# Miscellaneous > General Database Discussions >  SQL Connectivity Test?

## groggrog

Hi -

I'm having a problem with a VB6 program I'm writing that posts data to a SQL server.  The problem occurs when the SQL server is unavailable.  When this happens it causes the program to hang for at least 30 seconds before releasing.  The hang happens at the code that connects to the SQL server, because the server isn't there.  I've added code found on this site to test the connectivity prior to making my actual data connection, but this causes the same hang.  Is there any way to verify connectivity to an SQL server without actually trying to make the connection?  I'm really trying to eliminate the hang that occurs when the server is unavailable.

Thanks

----------


## rmiao

Tried with odbcping?

----------


## groggrog

Nope, never heard of that, but will now look it up.

Thanks very much!

----------


## groggrog

Unfortunately I don't have odbcping.  Also I'm wondering if there's a programmatic way to do this rather than shelling out to dos.

Thanks

----------


## MAK

try this...

http://www.databasejournal.com/scrip...le.php/3494931

----------


## groggrog

That's definitely what I'm looking for, so thanks.  I still think I'm out of luck though, because even with this script and with ODBCPing, if the server isn't available there's a pause of about 30 seconds.  That's what I'm trying to avoid.  I'm rolling out this app to all 50,000 computers here, and they all post to a single SQL server.  If that server goes down then 50,000 users are gonna have a 30 second pause when they log in.  Not a big deal to me, but typical non-technical users will pick up the phone and call the help desk to complain, and that's what I'm trying to avoid.  I guess it's not possible, but I'd love something that just checks connectivity and responds very quickly - it's either live or it's not live.

Thanks again for all your help, but I think I'm S.O.L. on this one.

Greg.

----------


## Ant

This may sound a bit simplistic, but would it not be simpler to check the sql server's availability by another method?  For example if there's a shared folder on the sql server, you could check for visibility of that?

----------


## rmiao

But that doesn't mean sql service is running.

----------


## groggrog

SQL Service availability is what I really need to know, but I can't have every client taking so long to determine that.  It's weird that once I get connected to SQL it's the fastest process in the world, but knowing whether or not I CAN connect is the big challenge.  I don't expect the service to be unavailable often, but the minute it is we're gonna have a customer support problem.  Whatever ODBCPING is doing to figure out availability is what I need to be able to do from VB.  I know it's possible, because ODBCPING is doing it, but I have no idea how to make it happen.

I thought this would be a pretty common task, but it looks like it's really not.  I do greatly appreciate all of your help though!

Greg.

----------


## ddiabetes

You're saying that the biggest proplem with this 30 second wait will be the users complaining to the help desk. You're also saying that you don't expect this issue to happen very often, and when it does, there won't be any mission critical consequenses. 
I'm new to SQL, but it seems to me that the easiest fix would be to display a "Please Wait" screen at this point. If the connectivity is as fast as you expect, no one will see the screen until you get that rare 30 second pause.

----------


## groggrog

The application has no front end - users don't even know it's running.  That's why the 30 second hang is a problem, because their machine suddenly pauses for about 30 seconds and they have no idea why.  That generates calls and with so many users, a panic.

Greg

----------


## russellb

why is the server unavailable so often?  that doesn't sound good.

if you are doing this in vb, have an asynchronous process poll the service periodically and set a flag that can be read by the app.

----------


## BulletSponge

Let me guess, the test takes forever only when it can't connect to the server right?
The reason it's taking so long is because your connection timeout is set to the default 30 seconds.


Reduce the connection timeout to 5 or 10 seconds.

----------


## Erhardt

You may wish to try the following connection error trapping/handling code.  It works with a web (ASP "classic" vbscript) page and Oracle (but can easily be adapted to MSSQL by changing the provider string to SQLOLEDB, etc.)

' Partial MSSQL conn string syntax
e.g.; "Provider=SQLOLEDB; Data Source=svrSource; Initial Catalog=myDBName;"

<%
'*************************************************  *******************************************
'	Global Routines
'*************************************************  *******************************************
Sub ConnectDB(oConn_Local)

	On Error Resume Next

	'Establish Connection to Oracle database.
	Set oConn_Local = Server.CreateObject("ADODB.Connection")

	oConn_Local.Open "Provider=MSDAORA;Password=myPWD;User ID=myUID;Data Source=myDS;Persist Security Info=True;"

	' If connection error occurs, send user to "Under Construction" page
	'
	If Err.Number <> 0 Then

		' Send mail to admin.
		Dim objCDO
		Set objCDO = Server.CreateObject("CDONTS.NewMail") 
		objCDO.TO = "test@acme.com"
		'objCDO.CC = "test@acme.com"

	             objCDO.FROM = "server@acme.com"
		objCDO.SUBJECT = "Possible connection problem with myApp - SOURCE: myServer"
		objCDO.BODY = "SOURCE: myServer.  Possible db/network connection problem with the myApp.  Please check and repair if necessary."

		objCDO.MailFormat = 1	' CdoMailFormatText
		objCDO.BodyFormat = 1	' CdoBodyFormatText

		objCDO.Send
		Set objCDO = Nothing

		Response.Redirect("http://www.someothersite/under_construction.htm")

	End If

	On Error GoTo 0

End Sub

Sub DisconnectDB(oConn_Local)
	'Close and Destroy connection object.
	If IsObject(oConn_Local) Then
		If oConn_Local.State = adStateOpen Then
			oConn_Local.Close
		End If

		Set oConn_Local = Nothing
	End If
End Sub
%>

----------

