24 comments

• 

  Jason Gurgal

  4 years ago

  This is the BEST PAGE ever, you described my situation to a tee, I have been banging my head for 3 hours!!!! My only suggestion is to rename your page, I overlooked your site a few times, maybe SqlAdapter timeout errors..................anyways you rock, thank you so so so much..........stupid msdn..............I didn't mean that..... ;)
  reply
• 

  Ben

  4 years ago

  Glad I could help :)
  Good point about renaming the page - will do. I remember vividly how annoyed and then happy I was when I first discovered this issue!
  reply
• 

  Amir

  4 years ago

  Thanks for this page saved me lots of time. I was at it for 5 hours.
  reply
• 

  Sameers (theAngrycodeR)

  4 years ago

  Ok, I am also receiving this error. But I am not using Select like statement instead using Stored procedures to UPDATE database. Its a small routine which picks up data from one table (of one database) and insert that to second table (of second database). Here is the little snippet of the code.
  
  Dim con2 As New SqlConnection(ConnectionStringHere)
  con2.Open()
  Dim cmd2 As New SqlCommand("StoredProcName, con2)
  cmd2.Parameters.Add("@oneparam", paramvalue)
  cmd2.CommandType = CommandType.StoredProcedure
  cmd2.ExecuteNonQuery()
  ''' and BANG.
  cmd2.dispose()
  con2.close
  
  
  The source table contains 40K+ records where I enumirate and for each record, after testing, I add that to second table using stored proc above.
  
  Any idea?
  reply
• 

  Sameers (theAngrycodeR)

  4 years ago

  Oooops, forgot to wrote that It do not stop at the very first record, works fine for sometime and then throws timeout error.
  reply
• 

  ben

  4 years ago

  You should be able to just set the CommandTimeout on your cmd2 object I think. Something like
  
  cmd2.CommandTimeout = 300 (which would be 5 minutes)
  reply
• 

  Sameers (theAngrycodeR)

  4 years ago

  Well, I alrady have tried that but no Luck. BTW, the query (in stored procedure) is not that long enough. Just a simple update statement which update only one row in the database. Before this, I was using direct query to update that and at that moment I was also receiving this error. Thought update to stored procs to faster the work but error remained.
  reply
• 

  Sameers (theAngrycodeR)

  4 years ago

  Well, I have this execution within a loop of recordset (datareader) fetched from another table.
  
  Dim mReader as SQLDataReader = mCmd.ExecuteReader () 'This statement returns 28K+ records
  
  While mReader.Read
  'Some code to validate data
  'Then insertion to a new table using stored procedure.
  'Updating of a column in the current data row. The code I written above
  End While
  
  I just tested this again by fetching fewer data (only 50 rows instead of that 28K+) and it worked fine. So the problem was due to heavy datareader (am I correct)?
  
  Now any suggestion how to avoid this error while having such heavy datareader?
  
  Thanks,
  reply
• 

  ben

  4 years ago

  That looks like something you should think about doing within one stored procedure. Is the validation code you refere to ( "'Some code to validate data") code that has to be on the client side? If not, that sort of data manipulation should really be pushed back to the SQL server. It would be hugely faster to do the work on the SQL server, rather than pull the 28k rows onto the client, then do 28k insertions and updates.
  reply
• 

  Sameers (theAngrycodeR)

  4 years ago

  Well, the validation code is nothing more than validating the EMail address only. But I just got that working by changing SQLDataReader to DataTable. It worked fine but only one problem that it consumes 100% CPU + alot of memory. Which is bad for server.
  
  I just remembered, that was not in my mind ago, that the SQLDataReader mentioned above belongs to the same table which is being updated by the stored procedure where I receive timeout error. Means, the datareader fetches rows from Promotions table and the stored procedure where timeout occrures also updates the same tabele. Actually I was fetching rows from Promotions table and then validation if that contains good email address then adds that to another table and mark that Promotion table's row to exported (column bExported=1).
  
  IS that the real problem? Seems to be so, since when I changed to DataTable it worked fine even more than these rows (28K).
  
  Thanks for your help,
  Sameers
  reply
• 

  samir shah

  4 years ago

  Hey Sameers,
  Did you try setting the Adapters property to ContinueOnError??
  its like
  adapter.ContinueUpdateOnError
  
  may be you are getting error at some place in one of the records and
  thats where you are stuck !! ??
  
  let me know
  reply
• 

  Hari

  3 years ago

  Hi,
  
  I tried your idea, and what the page does now is wait for a whopping 5 minutes and then give the same error, instead of giving it earlier. But when the stored procedure is run on its own, it takes less than two min. to do so.
  
  Any clue why it takes much longer in the application?
  reply
• 

  Erik

  3 years ago

  Hi,
  
  I have the same problem, but none of the suggestions here helped.
  
  I've set the CommandTimeout property of my command to various values (60,90,120,300), and every time with my monster query, the page waits exactly the number of seconds of CommandTimeout and then gives me the error!
  
  Interestingly, in SQL Query Analyzer, the query takes 30 seconds!
  reply
• 

  Pot Belly

  3 years ago

  just gotta give you credit for this item, I was on the verge of giving up having read a multitude of mind boggling listings when I found this.
  Clean, Simple solution, obviously an issue people are coming across, I just hope they find this as well.
  reply
• 

  ro

  3 years ago

  I've set the command timeout, the connection timeout, th server.script timeout, and the iis configuration timeout and
  my application is timing out before the set timeouts. It is timing out on the dataadapter.fill statement. It used to run fine until
  the vendor realized they were missing tons of data in the database and fixed that problem. The query averages 2 minutes
  in query analyzer. Any ideas?
  reply
• 

  ro

  3 years ago

  I went back and looked at your suggestion more closely. I had set my command timeout but I had not set dataadapter.selectstatement.commandtimeout.
  That was the key. Thanks!!!! You are awesome! Now if you could only teach Microsoft how to do documentation :-)
  reply
• 

  Sneha

  2 years ago

  It Really worked for me thanx a lot
  reply
• 

  sandro

  2 years ago

  TAHNK YOU !!!!!!!!!!
  reply
• 

  BIGSJK

  2 years ago

  Excellent...hit the nail right on teh head.
  Thanks!!!
  reply
• 

  Anonymous

  2 years ago

  hEY!..tHANKS A LOT. I BANGED MY HEAD FOR DAYS FOR THIS TO MAKE WORK. thx a lot dude
  reply
• 

  Anonymous

  2 years ago

  Thanks man, you rock.
  reply
• 

  Karl

  2 years ago

  Great stuff, needed one line, spent months rewriting queries to cut down on time and all I needed was the timeout set. Such is programming. Thanks.
  reply
• 

  Goudarz

  1 year ago

  Thanks a lot, you saved me and my work :)
  reply
• 

  Anon

  8 months ago

  You are a life saver! Thanks!!!
  reply