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..... ;)
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!
Amir
· 4 years ago
Thanks for this page saved me lots of time. I was at it for 5 hours.
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?
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.
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)
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.
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,
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.
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
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
Hari
· 4 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?
Erik
· 4 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!
Pot Belly
· 4 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.
ro
· 4 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?
ro
· 4 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 :-)
Sneha
· 3 years ago
It Really worked for me thanx a lot
sandro
· 3 years ago
TAHNK YOU !!!!!!!!!!
BIGSJK
· 2 years ago
Excellent...hit the nail right on teh head. Thanks!!!
Anonymous
· 2 years ago
hEY!..tHANKS A LOT. I BANGED MY HEAD FOR DAYS FOR THIS TO MAKE WORK. thx a lot dude
Anonymous
· 2 years ago
Thanks man, you rock.
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.
All Comments
Good point about renaming the page - will do. I remember vividly how annoyed and then happy I was when I first discovered this issue!
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?
cmd2.CommandTimeout = 300 (which would be 5 minutes)
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,
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
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
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?
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!
Clean, Simple solution, obviously an issue people are coming across, I just hope they find this as well.
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?
That was the key. Thanks!!!! You are awesome! Now if you could only teach Microsoft how to do documentation :-)
Thanks!!!