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.Connectio n
Set conn = New ADODB.Connectio n
conn.Connection Timeout = 1
conn.Connection String =
"Provider=SQLOL EDB;Server=MySe rver;Database=M yDatabase;UID=M yUserID;PWD=MyP a
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 ConnectionTimeo ut property determined the connection
timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

[debashish]

"tperovic" <to*********@ya hoo.com> wrote in message news:<Nr******* **********@news read3.news.atl. earthlink.net>. ..

Hi,

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

Dim conn As ADODB.Connectio n
Set conn = New ADODB.Connectio n
conn.Connection Timeout = 1
conn.Connection String =
"Provider=SQLOL EDB;Server=MySe rver;Database=M yDatabase;UID=M yUserID;PWD=MyP a
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 ConnectionTimeo ut property determined the connection
timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

Hi Tony
As per my knowledge about ConnectionTimeO ut, 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.goo gle.com...

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

news:<Nr******* **********@news read3.news.atl. earthlink.net>. ..

Hi,

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

Dim conn As ADODB.Connectio n
Set conn = New ADODB.Connectio n
conn.Connection Timeout = 1
conn.Connection String =
"Provider=SQLOL EDB;Server=MySe rver;Database=M yDatabase;UID=M yUserID;PWD=MyP a 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 ConnectionTimeo ut property determined the connection timeout? Where is the seconds coming from?

Please advise,
Tony Perovic
Compumation, Inc.

Hi Tony
As per my knowledge about ConnectionTimeO ut, 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 ConnectionTimeo ut.
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 ConnectToDataba se()
Dim lngStartTime as Long
Dim conn As New ADODB.Connectio n
Set conn = New ADODB.Connectio n
conn.Connection Timeout = 10
conn.Connection String =
"Provider=SQLOL EDB;Server=MySe rver;Database=M yDatabase;UID=M yUserID;PWD=MyP a
ssword", adAsyncConnect
conn.Open
lngStartTime = GetTickCount()

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

If Not conn.State = adStateOpen Then
If conn.State = adStateConnecti ng 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