Solved

ADO connection time out

Posted on 2003-11-04
17
484 Views
Last Modified: 2013-11-23
How do I create a function to test a db connection in ADO in
3 seconds ? I don't see there is any properties in ADO allows me to do that.

My purpose is doing db failover test. If the primary db fail to connect, then
I will use secondary db. Thus, time is very critical for me, I need the function
to return false, if it cannot connect to primary db in 3 seconds. I will test the failover by turning off SQL server and unplug the cable.

Thanks !
0
Comment
Question by:nicolle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +5
17 Comments
 
LVL 5

Expert Comment

by:jayeshshah
ID: 9684239
set the Connection timeout property of the connection object to 3 seconds.

K'Regards

Jayesh
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 9684287
Try set ConnectionTimeout Property of the Connection object:

conn.ConnectionTimeout = 3
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 9684294
hmm.., sorry as like repeating what jayeshshah mentioned, just ignore my post above. cheers
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nicolle
ID: 9684330
This is what I try:

strConnString = "Driver={SQL Server};" & _
                  "Server=192.168.203.111;" & _
                  "Address=192.168.203.111,1433;" & _
                  "Network=DBMSSOCN;" & _
                  "Database=Test;" & _
                  "Uid=Test;" & _
                  "Pwd=;"

  objSQLConn.ConnectionTimeout = 3
  objSQLConn.Open strConnString

when I unplug the cable or shutdown the server that hosts SQL server, I tried to connect
10 times, only 2 out 10 have timed out in about 3 seconds, 8 times timed out more than 10 seconds. What happen ?
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9684341
u cud write a connect function

with the

Dim db As ADODB.Connection
db.ConnectionTimeout = 3 (not sure if it is sec/millisecs)

after the .connect statement, u cud check for the connection state.

OR else, if it throws an error on not connecting, use the error code.

0
 

Author Comment

by:nicolle
ID: 9684452
_agj_,

Please refer to my previous response. Thanks !
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 9684494
>>I tried to connect
10 times, only 2 out 10 have timed out in about 3 seconds, 8 times timed out more than 10 seconds. What happen ?

Maybe your system is hang when try to establish a connecting, it may not consider as a connecting timeout problem.

And personally think that 3 seconds for establishing a connecting may not enough, you may increase the figure to greater number.

regards
0
 
LVL 5

Expert Comment

by:fantasy1001
ID: 9684498
something like this,
if objSQLConn.State = adstateClosed then
   'failure in connect
end if

~ fantasy ~
0
 
LVL 9

Expert Comment

by:dancebert
ID: 9684511
>What happen ?
William Vaughn discusses this 'feature' of ADO timeout in his ADO book, if you have it.  I have it, but it's at work, otherwise I'd post the explanation.  I'll post it tomorrow if nobody beats me to it

What database are you using?  Pardon my ignorance, but I thought the main point of clustering was to get automatic failover so the programs would continue accessing the database as if nothing had happened.  Perhaps you're trying to do something in the front end that is better done in the back end.

0
 

Author Comment

by:nicolle
ID: 9684513
ryancys,

Actually, I am doing the DB failover testing. If the primary SQL server is down, then I am going to secondary SQL server. Do you have much better idea ? The timing is very critical here.
I have a very fast connection. At most, the connection timeout is 5 seconds.
0
 
LVL 5

Expert Comment

by:jayeshshah
ID: 9684620
check this link i found. it shows a way how to connect to SQL Server. I hope this is helpful to you.

http://www.sqlwarehouse.com/create_a_ado_connection_to_sql.htm
0
 
LVL 7

Expert Comment

by:_agj_
ID: 9685681
ignore my post....that was thanks to a small refreshing problm...dint c ur post bfore my posting ;)
0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 9685720
You will need to wait for the response from the connecting process, i think we cant speed up the response time from the connecting process.
0
 
LVL 9

Assisted Solution

by:dancebert
dancebert earned 150 total points
ID: 9687863
ConnectionTimeout starts when a LAN connection is established to the database server and ends when the data source provider creates a connection.  This means that that the network component is NOT part of the timing.  ADO time-out code is blocked until the network answers - if it ever does answer. Pull the plug and you're dependent on the timout of your NIC card, which is not adjustable.

ConnnectionTimeout also does not account for the time ADO and low-level providers require to connect to the LAN hardware.  
0
 
LVL 2

Accepted Solution

by:
kamlesh_agrawal earned 50 total points
ID: 9689614
Well!!
Are u using VB6.0 application to connect database with ADO

then,
you first use a timer and set the interval to 1 second i.e 60000 milliseconds

and then keep a counter and increase in timer's subroutine

Like

dim countme as integer
dim tryconnection as integer


Private sub form_load
countme = 0
end sub

Private sub urtimer_timer()

countme = countme + 1

if (countme mod 3) = 0 and (countme < 7) then
 select case countme
   case 3:
           server = "primary db"
           uid = ""
           pwd = ""
           ....................
   case 6:
          server = "secondary db"
           uid = ""
           pwd = ""
           ....................
 end select
 'Call the procedure to connect
 Call connectme()
end if

End sub

'Procedure to connect
Private sub connectme()
      'Your code to connect to db
End Sub



Hope it would help u

rgds
Kamlesh A.

0
 

Author Comment

by:nicolle
ID: 9703531
Experts,

I have found the below code somewhere in the WWW:

  Dim objSQLConn As New ADODB.Connection
  Dim objSQLRs As New ADODB.Recordset
  Dim strConnString As String
  Dim blnConnected As Boolean
  Dim intCount As Integer
 
  intCount = 1
 
  strConnString = "Driver={SQL Server};" & _
                  "Server=192.168.203.111;" & _
                  "Address=192.168.203.111,1433;" & _
                  "Network=DBMSSOCN;" & _
                  "Database=Test;" & _
                  "Uid=Test;" & _
                  "Pwd=;"

  objSQLConn.Open strConnString, , , ConnectOptionEnum.adAsyncConnect

  Do Until intCount >= 3
    If objSQLConn.State = ObjectStateEnum.adStateOpen Then
      blnConnected = True
      Exit Do
    End If
   
    Pause 1
   
    intCount = intCount + 1
   
    DoEvents
  Loop
 
  If blnConnected Then
    MsgBox "connected"
  Else
    MsgBox "not connected"
    objSQLConn.Cancel
    objSQLConn.Close
  End If


I think this code really help me out. The only problem is when I closed the program, it is still
remain in the memory for about 10 seconds. I am thinking objSQLconn.Cancel is not working right.
0
 
LVL 5

Assisted Solution

by:RainUK
RainUK earned 50 total points
ID: 9788031
Well I agree with Dancebert regards to the LAN issue.

Rather than attempting to connect, straight away you could try using ICMP ping method (Returns in about 0.5 second). At least this way you know that when you Conn.Open you will at least attempt to connect to the machine, rather than wait for a packet to return on the LAN.

As for using cancel method, you could just nuke the object by using Set objSQLConn = Nothing or the horrible but sometimes useful End statement. Which will nuke your program.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vbModal 12 75
Delphi, TImage, Png 2 57
delphi popmenu non latine charcters 3 49
Count text color with conditional formating in Excel 4 68
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question