ADO.NET SQLAdapter Timeout Error
OK, here’s a really annoying trap for ADO.NET users that I thought I’d document, just in case someone is searching for “why the hell is my damned ADO.NET fill function timing out when I’ve set all the timeout values correctly!?” on Google.
Scenario: you have a big nasty SQL query or stored procedure that you are using to fill a DataSet. Something like this:
DataSet resultSet = new DataSet(); String mySQL = "Some big nasty SQL query"; SqlDataAdapter myAdapter = new SqlDataAdapter(mySQL, myConnString); myAdapter.Fill(resultSet);
Pretty simple really. You’ve set your connection timeout correctly in the connection string, and yet you keep getting messages like:
The timeout period elapsed prior to completion of the operation or the server is not responding.
You bang your head against the wall for a while, before realising that when you instansiate a SqlDataAdapter, that first parameter (String mySQL), is actually the SQL string required to create the DataAdapter’s SelectCommand property. Aha! There’s your hint: SelectCommand is a SqlCommand object.
SqlCommand objects have a property called CommandTimeout. Soooo, how about some little ditty like:
myAdapter.SelectCommand.CommandTimeout = int.MaxValue;
NB: I’m kidding with that int.MaxValue business. Use something sane like 60 seconds. You shouldn’t be copy-pasting this snippet anyway – as with any web coding solution, learn why and how it works before using it.
Popularity: 12% [?]
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…..
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!
Thanks for this page saved me lots of time. I was at it for 5 hours.
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?
Oooops, forgot to wrote that It do not stop at the very first record, works fine for sometime and then throws timeout error.
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)
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.
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,
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.
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
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
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
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?
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?
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!
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!
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.
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?
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
It Really worked for me thanx a lot
TAHNK YOU !!!!!!!!!!
Excellent…hit the nail right on teh head.
Thanks!!!
hEY!..tHANKS A LOT. I BANGED MY HEAD FOR DAYS FOR THIS TO MAKE WORK. thx a lot dude
Thanks man, you rock.
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.
Thanks a lot, you saved me and my work
You are a life saver! Thanks!!!