SQL Server Connection Timeout

[tperovic]

Hi,

I have an application that uses ADO & VBA to connect to SQL Server 2000
periodically like this:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 1
conn.ConnectionString =
"Provider=SQLOLEDB;Server=MyServer;Database=MyData base;UID=MyUserID;PWD=MyPa
ssword"
conn.Open

When the server is unavailable, the Open waits 60 seconds before timing out.
During this time, the application is locked up because the VBA is not
reentrant. Doesn't the ConnectionTimeout property determined the connection
timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

[debashish]

"tperovic" <to*********@yahoo.com> wrote in message news:<Nr*****************@newsread3.news.atl.earth link.net>...

Hi,

I have an application that uses ADO & VBA to connect to SQL Server 2000
periodically like this:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 1
conn.ConnectionString =
"Provider=SQLOLEDB;Server=MyServer;Database=MyData base;UID=MyUserID;PWD=MyPa
ssword"
conn.Open

When the server is unavailable, the Open waits 60 seconds before timing out.
During this time, the application is locked up because the VBA is not
reentrant. Doesn't the ConnectionTimeout property determined the connection
timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

Hi Tony
As per my knowledge about ConnectionTimeOut, if u set it to zero
then the connection attempt waits indefinitely. Does this rule also
employ to lower values of TimeOut is a question which i am not sure
about.

With Regards
Debashish

[tperovic]

I've tried values of 1,5,10 and 15 and the result is always the same - it
takes sixty seconds to timeout.

"debashish" <de****************@rediffmail.com> wrote in message
news:bb**************************@posting.google.c om...

"tperovic" <to*********@yahoo.com> wrote in message

news:<Nr*****************@newsread3.news.atl.earth link.net>...

Hi,

I have an application that uses ADO & VBA to connect to SQL Server 2000
periodically like this:

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 1
conn.ConnectionString =
"Provider=SQLOLEDB;Server=MyServer;Database=MyData base;UID=MyUserID;PWD=MyPa ssword"
conn.Open

When the server is unavailable, the Open waits 60 seconds before timing out. During this time, the application is locked up because the VBA is not
reentrant. Doesn't the ConnectionTimeout property determined the connection timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

Hi Tony
As per my knowledge about ConnectionTimeOut, if u set it to zero
then the connection attempt waits indefinitely. Does this rule also
employ to lower values of TimeOut is a question which i am not sure
about.

With Regards
Debashish

[debashish]

Hello Tony
From what i know, this is a problem when you connect to oracle
since the MS Oracle ODBC driver and MS OLE DB Provider for Oracle dont
support setting ConnectionTimeout.
This occurs due to a limitation in the Oracle Call Interface(OCI)
and applies to Oracle ODBC driver as well as the Oracle OLEDB
provider. This happens as a result of design specifications.
If one still wants to check the connection status(for Oracle or SQL
server), the following code may work:
You must use the option - adAsyncConnect to open the connection.

'*********************Module declarations*******************

Private Declare Function GetTickCount Lib "Kernel32" () As Long

Public sub ConnectToDatabase()
Dim lngStartTime as Long
Dim conn As New ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionTimeout = 10
conn.ConnectionString =
"Provider=SQLOLEDB;Server=MyServer;Database=MyData base;UID=MyUserID;PWD=MyPa
ssword", adAsyncConnect
conn.Open
lngStartTime = GetTickCount()

Do While ((GetTickCount() - lngStartTime) < conn.ConnectionTimeOut
* 1000)
And (Not conn.State = adStateOpen)
Loop

If Not conn.State = adStateOpen Then
If conn.State = adStateConnecting Then
conn.Cancel
End If
Else
MsgBox "Connection Successfull!"
conn.close
End If

End Sub

'*****Module ends*****

Hope this works for SQL server also! Do let me know if this works.

With Regards
Debashish