Large update problem

[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

Why would this occur. I can even response.writethe update statement and all the correct values are there.

 

[reevesmjr]

What do you have your command timeout set to?

 

[reevesmjr]

Try con.commandtimeout = 0

 

[SQLSister]

The above solution of course will not fix your problem merely mask it.

Your problem is you are fundamentally wrong in how you are designing your system. If you have problems with efficiency then you need to change the methods of how you do things, not simply allow more time. 20 is very few records in SQL terms. If you are having a timeout problem already, then your whole approach to the problem is probably wrong. What will happen if you update 1,000,000 records? Probably you'll just lock up your system since it won't time out and the users will come for your head.

Try putting this update in a stored procedure using a set-based statement not row based processing trough a loop or cursor and then it will run more efficiently.