Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Best Method to Verify Database Availability 5

Status
Not open for further replies.
UncleCake

UncleCake

Technical User
Feb 4, 2002
355
US
I am building a wireless bar code scanner application that communicates with SQL Server very frequently. The problem that I am running into is that the scanner is sometimes going out of range of the receiver. I would like to set up something that will test to see if it was in range by seeing if the database is accessible (unless there is a better way).

The two ideas that I have are before each select, update, or delete have a sub that would try to open the database or something that would let me know if it had access to it. The seconded one is to do some type of error catching in each select, update, or delete that would let me know. Either way I don’t know how to do it.

Are either of these a better way or can anyone suggest a better method? This is my first application in VB.net, so I am not an expert. I could post some of my code for connection if needed.

-Uncle Cake
 
Sort by date Sort by votes
There are numerous time outs.

For example, a connection may have a 5 minute time out, but a query can have a 10 second time out, and the server may have a 60 second time out.

The timeout you specify in the connection string is the time out that the connection should use (Overriding the server's default connection time out). The SQLConnection object's .TimeOut property should effect the object's time out, which in this case is the important one since you are looking for a case where the server is not available.

The problem is these time outs get all muddled up in the mix, so even though the connection string has a time out of 5 seconds, it takes 15 seconds to time out. That's why I would suggest also setting the connection object's .TimeOut property to 5.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
[monkey]I believe in killer coding ninja monkeys.[monkey]
 
Upvote 0 Downvote
Here's another idea... you could use the SQLDMO object to scan for all available SQL Servers that you can see. If your server isn't in the list, don't try and connect... if it is in the list, try your test function. At that point, you shouldn't have a long wait for a response from the function to return with a true.

You'll need to create a reference to SQLDMO.DLL.. then...

Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application

oSQLApp = New SQLDMO.Application
oNames = oSQLApp.ListAvailableSQLServers()

For i = 1 To oNames.Count
If oNames.Item(i) = "ServerImLookingFor" Then
CheckLoginToSeeIfCanConnect
End if
Next i

--
James
 
Upvote 0 Downvote
the only problem with scanning via sqldmo is that if the user doesn't have service pack 3a it won't see it.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting
 
Upvote 0 Downvote
Rick,

Thanks for the explanation. I just didn't see anything for the object's time out, which is why I did it in the connection string. I have looked, and I will look some more, but I haven't found anything yet for the object's time out property.

If you know what it is, here is my code:

Code: 
    Private Function testConnection() As Boolean
        Dim MyConnection As System.Data.SQLClient.SqlConnection

        MyConnection = New System.Data.SQLClient.SqlConnection("server=xx; initial catalog=xx;uid=xx;pwd=xx")
        Try
            MyConnection.Open()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
 
Upvote 0 Downvote
adam0101
I'm not upset, I just think making silly comments is unprofessional and purile. Pinging is a perfectly reasonable and decent suggestion in attempting to ascertain availability of a remote server, prefixing it with comments like Now that you've all found a solution that works.... is simply a poor attempt at one up-manship, (OK, that's probably not a real word), in regard to the suggested methodological approach. It's no better than saying 'Ner, ner, ner ner ner. I know better than you do...' which to me is the realm of school yards, children and immature adolescents not professional IT forums.
As I said, it's not that big a deal to me (I just don't particularly like people being condescending) but some people will RF comments like that and it'd be a shame as ca8msm has a lot to offer people in terms of knowledge.

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!
 
Upvote 0 Downvote
I was simply saying that now a solution has been found I'd like to offer an alternative (rather than jump in with one whilst another was being discussed).

I think you've mis-interpreted what was written.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
Actually Rhys I think he was just trying to offer another solution after an acceptable(albeit not perfect) solution had been found. To me it came off as him respecting the solution that was found and offering another possibility. Also, if you want to get into the delicacies of professionalism, the proper action would have been to quietly RF the post, not to publicly chastize him over a difference of interpretations.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
[monkey]I believe in killer coding ninja monkeys.[monkey]
 
Upvote 0 Downvote
James,

Just to at least try it, I tried to add the dll reference and it popped up a message box of "A reference to 'C:\Program Files\Microsoft SQL Server\80\Binn\SQLDMO.dll' could not be added. This is not a .NET assembly.

 
Upvote 0 Downvote
I'm a VB6 kinda guy, but I'm learning vb.net. I've learned so much from this forum just by using the search functionality. You guys are awesome.

I would create a shared folder on the server. Then, put a file in that folder.

In vb6, I would use the File System Object to "read" the file. If you cannot get to the server, FSO would timeout VERY quickly. If you can read the file (actually using fso's FileExists function), then your connection to the server is established and you can "probably" connect to the database.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
UncleCake,

Responding to your earlier question of how to ping, I found this example that you could test:


Be aware though that Chrissie said he has tried it and that it was no quicker but if you wanted to test it then that's up to you. He also pointed out about checking if you have a valid wifi connection which may be worth looking into.


--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
James,

Ahh, that is why I didn't see it. Because this is using a scanner and Win Media 2003 (Smart Device Application) I don't have access to the COM objects.
 
Upvote 0 Downvote
Ah, so maybe that was the way adam0101 read it too. I'd say it's important to be reasonably careful about what you/we (trying not to personalise this at you :) ) say though. There's a lot of people from a lot of places all of whom read things a lot of ways here, and I've seen comments that I couldn't see anything in for love nor money RF'd and I do think thats a shame as when you make a comment, if you've taken it the wrong way round you can be corrected (as I have been) where as if you RF someone you always assume you were right.

Anyway, (as we're WAY off point anyway), you see to me it's better to make a suggestion and try to present an alternative earlier rather than later to avoid all that hassley code re-writing should you decide method 2 is more appropriate than method 1.

Hopefully nothing was taken paticularly personally as you read my later comments?

Rhys

"Vampireware /n/,a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

"I see dead pixels!
 
Upvote 0 Downvote
Sorry, I wasn't aware of Win Media 2003's limitation. I don't suppose you could create an Assembly Wrapper that you could then reference in your Win Media app??

--
James
 
Upvote 0 Downvote
Ca8msn,

I ran across that page also, but I didn't understand it. Maybe I will look at it more.

Everyone Else,

I think that I will put this thread to rest. I am very satisfied with the help I received, as always. You guys help me out so much every time. I will still watch this tread to see if there is any life changing information.

Thanks again, I really appreciate all of your help!!
-Uncle Cake
 
Upvote 0 Downvote
Actually, I quite like the solution made by gmmastros.

Rhys, no it wasn't taken personally - although I do think in this situation it would have been wrong of me to jump in until the other solution had been discussed as it was a good idea (which I thought would be used in the end anyway) and I didn't want to throw it off track.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
Actually, I quite like the solution made by gmmastros.
Click to expand...
That is assuming the database server is on the same server as the file system.

--------------------------------------------------------------------------------------------------------------------------------------------

Need help finding an answer?

Try the search facility ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
1K
strongm
strongm
Swi
  • Locked
  • Question
Printer.Print to PDF 1
Replies
16
Views
921
Swi
Swi
sal21
  • Question
Based the link above how to simulate click event
Replies
4
Views
287
2009luca
2009luca
sal21
  • Locked
  • Question
I can't find the routine to download the image file anymore
Replies
1
Views
262
strongm
strongm
2009luca
  • Question
possible to use xml2 or Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1"), instead code in message
Replies
5
Views
407
2009luca
2009luca

Part and Inventory Search

Sponsor

Back
Top