August 31, 2004 at 7:14 am
I have a thread which attempts to connect to a SQL server database using ADO connection object. If the connection is not available then after sometime it tries to establish the connection again.
The problem I am facing is that after sometime the ADO connection object starts to return "timeout expired" error even without waiting for the timeout time. The connection is not made even when the target server is started. Once in a several attempts, the connection is made.
I have tried reusing or creating a new connection object before every connection attempt, but the problem remains the same.
Thanks,
Krishnan
September 1, 2004 at 6:41 am
Remember that the ADO connection object has a timeout property that is completely separate from the SQL Server timeout property. Both need to be set appropriately.
September 1, 2004 at 9:08 am
How are you controlling the attempted retries?
If you are using an ErrorHandler in your connection proc, it might be jumping right back to the Connection.Open! I have seen this several times in VB when the original developer used the Resume statement as notated in the sniplet below. A better way is to Sleep the process for a period of time (NOT the same as DoEvents!!!) prior to retry. Also I recommend doing retries with a 'clean' object (ie destroy the connection that failed) as in the ErrorHandler in the sniplet below.
Lastly, You mention that this is occurring in a 'thread'.
Any chance that thread is getting inadvertantly reactivated by the original caller?!?
That would yield the same results, NO?
'************* VB Code sniplet ************************
Option Explicit
'These declarations would go in a BAS file in VB
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public gconADO As ADODB.Connection
'This is the connection procedure
Public Sub ConnectionGet()
On Error GoTo ErrorHandler
TryAgain:
Set gconADO = New ADODB.Connection
'Sets Connection Timeout to 15 seconds, usually enough if server on LAN
gconADO.ConnectionTimeout = 15
'Sets Command Timeout to 30 seconds for long messy queries
gconADO.CommandTimeout = 30
'There are LOTS of variations on Connection Strings, see ADO documentation for best fit for you purposes
gconADO.ConnectionString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;User Id=YourUserID;Password=YourPassword;"
'Open the Connection
gconADO.Open
ErrorHandler
MsgBox "An Error has occurred making ADO Connection" & vbCr & _
"Error Number: " & Err.Number & vbCr & _
"Error Description: " & Err.Source & vbCr & _
"Error Source: " & Err.Source
'If this Resume were NOT commented out
' Would cause statement that generated error to occurr again immediately
'Resume
'Let's destroy the connection, wait five seconds and try again instead
Set gconADO = Nothing
Sleep 5000 'Causes a 5 Second Wait prior to attempting again
Resume TryAgain
End Sub
'************** End VB Code Sniplet
Hope this helps
Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply