| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2006-03-15 : 04:09:17
|
| If you are reading this you are kind, brave, knowladgeable or nosy.I hope it's number three.I know this error has many different causes. I've discounted some but I dont know which of the remainder is the cause.I have a SP that returns 84000 results in 17 minutes when provided with a certain parameter value. It works fine running it in query analyzer and the results are returned after 17 minutes. But if I run it through my .NET app, I get "General network error. Check your network documentation." after a while. I am looping through the results in a datareader, and after looping through some records, this error is generated.Is there a problem with holding a datareader this big open against the server? It can see how it can actually be a network problem because hundreds of other SProcs run fine - even this problem SProc is ok if it is supplied with a param that results in less records being returned. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-03-15 : 05:11:19
|
| The .NET app is probably timing out waiting for the response. Have you adjusted the timeout figures?-------Moo. :) |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-15 : 06:51:03
|
| i changed the timeout to 30 minutes and that didnt fix it. what else could it be do you think? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-15 : 07:21:42
|
don't return 84k rows to the datareader.Go with the flow & have fun! Else fight the flow  |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-15 : 08:18:45
|
| is that really the answer? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2006-03-15 : 08:22:37
|
| I'd have to look in BOL to verify, but I think that looping through a reader keeps the connection open and gets a row at a time..?I'd suggest pulling eveything into a DataSet and closing the connection and then you can manipulate the data client-side all you want. However, that may or may not work for you.-Lamprey |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-03-15 : 08:33:00
|
| yes that's the answer... you're probably processing data faster than you datareader is receiving it.datareader retrieves the data in async operation. so your 84k rows is being retreived while you're processing the already retreived ones. and you program is processing them faster than it's able to receive them.Go with the flow & have fun! Else fight the flow |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-03-15 : 10:20:44
|
| Well, I suppose there is the question as to why you would need to be returning 84K rows. I mean, is someone actually going to scroll through all of those rows? Maybe a subset would be more useful. People only generally look at the first 2-3 pages of search results.-------Moo. :) |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-15 : 12:05:08
|
| thanks guys, If I get this error, I will prompt them to refine their search. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-03-15 : 12:13:51
|
| I had a very similar problem recently. I had a reporting thing that queries the database and returns a datareader with a commandtimeout of 0. It happens with varying sets of data with varying sizes of rows etc. I discovered that this is a .Net Framework bug."When the SQLCommand.CommandTimeout is set to zero, you expect an infinite timeout. However, versions 1.1 and 1.0 of the SqlClient provider incorrectly timeout when a response from SQL Server is broken into two packets. Immediately upon receipt of the second packet, versions 1.1 and 1.0 of the provider incorrectly timeout. The fix that is included in this article fixes this issue so that the command will have an infinite timeout."http://support.microsoft.com/?id=823679I think the solution is to just set a really high CommandTimeout like they suggest. Infinity is a mightly long time :)Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2006-03-16 : 03:40:43
|
| awesome, thanks dude! |
|
|
|
|
General network error