Large update to sql server

[bongmarley]

I am trying to run a page where i make a large update to an sql server database. If I run the update but only update a few records aprox 15 or so it will let me update. However if i try to update a large amount of records 20 or more it
gives me this error

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

here is my update code
con.execute(&quot;Update DaysTaughtAndClaimed set recdate='&quot;&request(&quot;recdate&quot;&getrec(&quot;id&quot)&&quot;',Reason='&quot;&Replace(NewReason(&quot;Description&quot,&quot;'&quot;,&quot;''&quot&&quot;', ReasonCode=&quot;&NewReason(&quot;Code&quot&&quot;, sub1Payrate='&quot;&request(&quot;sub1Payrate&quot;&getrec(&quot;id&quot)&&quot;', sub1percentageofday=&quot;&request(&quot;sub1perecnt&quot;&getrec(&quot;id&quot)&&quot;, PercentageOfDay=&quot;&request(&quot;perecnt&quot;&getrec(&quot;id&quot)&&quot;, PortionOfDay='&quot;&request(&quot;portion&quot;&getrec(&quot;id&quot)&&quot;',ReasonType='&quot;&getRType(&quot;ClaimableorUnclaimable&quot&&quot;/&quot;&getRType(&quot;WithpayorWithoutpay&quot&&quot;' where id=&quot;&getrec(&quot;id&quot&&quot;<br>&quot

 

[Genimuse]

It's surprising that such a short statement would timeout, as 20 fields just isn't that many -- I've had ASP apps with many dozens of fields -- over 100 IIRC -- with no timeout problems.

I notice that you're concatenating the statement from what appears to be at least two other recordsets along with the request object -- perhaps the timeout is related to something on the reading side of the equation. If so then using

getrows

and closing the recordsets before performing the update might solve your problem.

Concatenation can eat quite a bit of server resource: are any of those strings particularly long? If you're concatenating tens of thousands of characters then you might be hanging things in the open recordsets while putting the string together (though it's unlikely).

Are you reading any large-sized fields in general? Large fields should be read last: in Access that would be memo fields, and in SQL Server that would be text, ntext, or image fields. Reading them in the middle of the row can hang things (though I don't recall if it throws a timeout error).

 

[DreXor]

i also see a <br> at the end of the SQL statement?