Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-02-08 : 15:38:02
|
Jonathan writes "Please help...
i am trying to run a stored procedure which takes about 5 minutes to complete. The problem is that i am running this procedure through an ado connection from VB and it times out at 30 seconds.
Is there any alternative way of doing this? it is inpractical to tell the end user to run this stored procedure within query analyser everytime the data is being checked.
tried asynchronous connection, no luck.
here is the code:
Dim startTime As Long
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Conn.Open "uid=;pwd=;Trusted_Connection=yes;driver={SQL Server};server=;database=;dsn=", adAsyncConnect
Dim timeout As Long
timeout = 60 'Number of seconds to wait before timing out
startTime = GetTickCount()
'Wait until timeout seconds have passed, or the connection is open
While ((GetTickCount() - startTime) < timeout * 1000) And (Not Conn.State = adStateOpen)
Wend
If Not Conn.State = adStateOpen Then
MsgBox "Timeout occurred"
If Conn.State = adStateConnecting Then
Conn.Cancel
End If
Else
MsgBox "Connection is open!"
'conn.Close
End If
startime = 0
Set cmdchange = New ADODB.Command
cmdchange.ActiveConnection = Conn
cmdchange.CommandText = "UPDATE [trigger] Set start = 'xyy'"
startime = Timer
'executing the command, displaying a message while it is executing
cmdchange.Execute , , adAsyncExecute
Do Until cmdchange.State <> adStateExecuting{FALLS OVER HERE AT 30 SECONDS}
Label1.Caption = "create_customer_history stored procedure executing..."
Label2.Caption = ">>"
Label2.Refresh
Label2.Caption = ">>>>>>"
Label2.Refresh
Label2.Caption = ">>>>>>>>>>"
Label2.Refresh
Label2.Caption = ">>>>>>>>>>>>>>"
Label2.Refresh
Label2.Caption = ">>>>>>>>>>>>>>>>>>"
Label2.Refresh
Label2.Caption = ">>>>>>>>>>>>>>>>>>>>>>"
Label2.Refresh
Label3.Caption = "Execution time: " & CLng((Timer - startime) / 60) & " minutes"
Label3.Refresh
Label2.Refresh
Loop
MsgBox "Done! Hit refresh all button on Excel toolbar" " |
|
|
|
|
|
increasing ado timeout connection problem