nicolle
asked on
ADO connection time out
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 !
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 !
Try set ConnectionTimeout Property of the Connection object:
conn.ConnectionTimeout = 3
conn.ConnectionTimeout = 3
hmm.., sorry as like repeating what jayeshshah mentioned, just ignore my post above. cheers
ASKER
This is what I try:
strConnString = "Driver={SQL Server};" & _
"Server=192.168.203.111;" & _
"Address=192.168.203.111,1 433;" & _
"Network=DBMSSOCN;" & _
"Database=Test;" & _
"Uid=Test;" & _
"Pwd=;"
objSQLConn.ConnectionTimeo ut = 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 ?
strConnString = "Driver={SQL Server};" & _
"Server=192.168.203.111;" & _
"Address=192.168.203.111,1
"Network=DBMSSOCN;" & _
"Database=Test;" & _
"Uid=Test;" & _
"Pwd=;"
objSQLConn.ConnectionTimeo
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 ?
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.
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.
ASKER
_agj_,
Please refer to my previous response. Thanks !
Please refer to my previous response. Thanks !
>>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
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
something like this,
if objSQLConn.State = adstateClosed then
'failure in connect
end if
~ fantasy ~
if objSQLConn.State = adstateClosed then
'failure in connect
end if
~ fantasy ~
>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.
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.
ASKER
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.
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.
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
http://www.sqlwarehouse.com/create_a_ado_connection_to_sql.htm
ignore my post....that was thanks to a small refreshing problm...dint c ur post bfore my posting ;)
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,1 433;" & _
"Network=DBMSSOCN;" & _
"Database=Test;" & _
"Uid=Test;" & _
"Pwd=;"
objSQLConn.Open strConnString, , , ConnectOptionEnum.adAsyncC onnect
Do Until intCount >= 3
If objSQLConn.State = ObjectStateEnum.adStateOpe n 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.
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,1
"Network=DBMSSOCN;" & _
"Database=Test;" & _
"Uid=Test;" & _
"Pwd=;"
objSQLConn.Open strConnString, , , ConnectOptionEnum.adAsyncC
Do Until intCount >= 3
If objSQLConn.State = ObjectStateEnum.adStateOpe
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
K'Regards
Jayesh