[Microsoft][ODBC SQL Server Driver]Timeout expired

[earme]

Hello all,

This is the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E31)
[Microsoft][ODBC SQL Server Driver]Timeout expired

I'm receiving the timeout error because of the following call:

mySQL = "UPDATE CUSTFILE SET Ref = 1 " & strWhere
connection.CommandTimeout = 300 '5 minutes
connection.Execute mySQL

strWhere is the Where statement. The where statement has multiple statements like this:

NewCustNo = 'xxxx00000'

Over 3,000 statements like that (NewCustNo = 'xxxx0000' or NewCustNo='xxxx0001' etc), which is why it's timing out.
Is there a way to make this run without timing out?
If I break up the statement into separate peices on the same page will that solve it, or will I have to break it up into seperate peices on separate pages? Or is something else causing it entirly?

Thank you,
Earme

 

[JeremyG]

I would recommend changing the values in the recordset, and then doing a batch update. If I'm not mistaken, this would send the update data to the server in one large block rather than 3000 small ones.

If I'm wrong here someone please correct me

 

[earme]

I thought my Update did moved everything in one large block? It's one call because the where statement has every record that needs to be updated.
At any rate, I tried your suggestion, but it didn't speed things up any. So, I went back to the way I was doing it and increased the timeout of the script and in SQL.
It takes a bit (there's roughly 3,730 records being updated), but it does it eventually.

Earme

 

[Repec]

You may be able to set the cursor to the server instead of the client. This would cause the update to run on the server.

objConn.CursorLocation



Eric Repec
eric@ericrepec.com

http://www.ericrepec.com

If its not broke try it make it faster.....

 

[JeremyG]

Try this this will move all of the comparing cycles off of the server's SQL query and onto the web server.


<Use code here to load your &quot;NewCustNo&quot; values into an array called myArray>
Set rsSQL = Server.createobject(&quot;ADODB.Recordset&quot

strSQL = &quot;Select * from CUSTFILE&quot;
rsSQL.open strSQL, connection, 1, 2

Do until rsSQL.EOF
For i = 0 to ubound(myArray)
If rsSQL(&quot;NewCustNo) = MyArray(i) then
rsSQL(&quot;ref&quot = 1
End if
Next
rsSQL.MoveNext
Loop

rsSQL.update



(I might have a mistake in there, but i'm at work, no time to check....follow the logic though and you should get a solution for yourself)