Calling stored procedure timeout problem..??

[bgseib]

Ok - I am calling a stored procedure that runs anywhere from 30 seconds to 6 minutes depending on what parameter the user enters. My problem is that when ever it runs for anything more than 35 seconds I get a runtime error for timeout expired. Here is my code:
Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim rs As ADODB.Recordset
cnn.ConnectionTimeout = 4000
cnn.ConnectionString = "driver={SQL Server};" & _
"server=*&*^%*;uid=sa;pwd=&$%*%&;database=recall"
cnn.Open

Set rs = cnn.Execute("sp_recall_data_filter")

Set rs = cnn.Execute("Select count(*) from patient_waiting")

If rs(0) > 0 Then
MsgBox rs(0) & " rows were selected by your parameters"
Else
MsgBox "Sorry. No data extracted."
End If
' Close the connection.
cnn.Close
End Sub

As far as I know this is correct - but please let me know if there is a timout setting I can change or something..

Thanks,
Brian

 

[Travis]

Have you tried setting the cnn.ConnectionTimeOut=0 'Indefinate

 

[bgseib]

Unluckily I have tried that. It is really funny - I have created a new form in a new project and called the same sp and it does the exact same. Everytime it errors out between 30a nd 35 seconds. Is there some global setting I am missing somewhere? I appreciate the help...

BS

 

[Travis]

Do you have a TimeOut setting on the SQL Server?

 

[bgseib]

I don't think so. I can run the sp on the sql box through query analyzer and it runs for about 4 minutes with no parameters just fine (transfering about 6 million records), but when I run it through the access front end where it is calling the sp through ado it times out on me... ???

Thanks for any additional info,
Brian

 

[Travis]

Look to see if the ODBC Driver you are using has a timeout property?

 

[bgseib]

I am actually not using an odbc driver. I created a new form in a new access database - and just put a button on the form with the code I pasted earlier. So I the only connection I am using is the adodb.connection string... any thought?? I have also swapped the code out for other things and tried short running sp's, and they all work fine - it only has problems when it takes more than 30 seconds to complete the execution...

Thanks,
Brian

 

[The_Doc_Man]

Since you aren't using ODBC, you must be using a different layer of TCP/IP or whatever else you are using. Check the timeout settings on your network driver. A time on the order of 30 seconds could also be a physical device timeout for the point-to-point or presentation layers.

 

[bgseib]

Anyone??? I am using ado - so as far as I understand ado - once I open a connection - it will stay open until I close that connection???

Any other thoughts??