Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired (Unrelated to Connection or ASP script Timeout Properties)

Currently facing a very strange problem. What ever i do to the database or the record, the sql server always timeout at a particular point. if i change the content of the particular record, the problem still arise. Not sure what is the problem. May it be related to database? is the data corrupted? currently my suspicions is this. Need desperate help. But for your info my script does a batch update on all the records, so it opens quite a lot of connection. Didnt want to convert the whole thing to stored procedure because of the logic complexity. Have turned part of logic into stored procedure, but results still timeout at the same place. Need help. URGENT
alcatraz_437Asked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
Agree with ShogunWade--when you receive the error, run SP_WHO in query analyzer and see if there is any blocking.  Sounds like one or your previous transactions is blocking....
0
 
Dishan FernandoSoftware Engineer / DBACommented:
When the timeout occers , whats the error it apper?? on web page. If the Query take long time to execute, you must increase to db timeout time in iis or what ever.
0
 
alcatraz_437Author Commented:
dishanf
   As i have stated in the question, that this has nothing to do with sql server time out or the iis timeout, cause it hits at a particular part of the database only. Have tried increasing connection timeout and iis timeout both have failed.. ! :)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dishan FernandoSoftware Engineer / DBACommented:
Sorry . I dont know asp much... leave it to other experts
0
 
ShogunWadeCommented:
you say it times out at a particular point.... can you tell us what it is actually attempting to do at that particular point, otherwise we are a bit in the dark here.
0
 
alcatraz_437Author Commented:
it times out at a normal update command. There is no problem with this update command, since it is already executed a few thousand times before it was executed at the particular point. On top of that, i have tried running the sql update command in the query analyser and it works just fine.
0
 
ShogunWadeConnect With a Mentor Commented:
Is there an open transaction on the table causing an indefinate lock ?
0
 
alcatraz_437Author Commented:
Thanx, found out that while doing a select statement on a table, i update on the same table simultaneously, so there is a point where the select statement locks a record the update statement wishes to update. So to rectify this, place the results that need to be updated at a temp table, then once the select statement is finish, run thru the temp table to update the necessary records.

Thanx a million.
0
 
arbertCommented:
You can also add the WITH (NOLOCK) after the table name on your select statement to not take a lock.  There IS the possibility you will read dirty data, but it's and option....
0
 
alcatraz_437Author Commented:
Thanx a lot arbert!!
0
All Courses

From novice to tech pro — start learning today.