ODBC timeout

[sunshine9]

Hello,
I have an asp page that uses odbc to connect to a sql svr db. The db is quite large and the page times out due to an ODBC timeout. Can someone tell me how to change the odbc timeout property?

thanks,

 

[cmmrfrds]

Is the ODBC part of an ADO connection? Can you show the connection.

 

[sunshine9]

This is how i'm connecting:

strcon = "Driver={SQL Server};Server=" & ComputerName & ";Uid=" & UserID & ";Pwd=" & Password & ";Database=mydb"
if Session("cn&quot.state = 1 then Session("cn&quot.close
Session("cn&quot.Open strcon
Select Case Session("cn&quot.State
Case adStateOpen
Response.Redirect("search.asp&quot
Case Else
Response.Redirect("error_main.asp&quot
End Select
end if

then, the query is:

set RS = server.CreateObject("adodb.recordset&quot

RS.Open sql, Session("cn&quot, 3, 3, 1


Please note:
i've stepped into this code for bugfix and i have to change the connection to use a DSN provided by the user, and to be db independent.

Any help would be appreciated.
thanks

 

[cmmrfrds]

I am not sure about ODBC time out, I always use the native ADO providers - more efficient, but be aware the ADO connection and command objects also have a timeout setting. Maybe one of these, in your case, the connection is timing out and the error message is not correct.

'-set the connection timeout
strcon.CommandTimeOut = 500 '- this is seconds
instead of the default of 30 seconds

 

[SQLSister]

Timing out often indicates that the query is inefficient. You may need to optimize the query that gets the recordset to reduce the time it spends trying to execute it. Is the table indexed on the field(s) you are searching on?

Here's a link with some good optimization tips:

http://www.swynk.com/sql/achigrik/tipTSQL.asp