timeout in sp but not in normal select

[mac866]

hi!

I use
- .net framework 1.1
- sql server 2000 sp 3a
- windows 2000 server
- asp.net/c#
- system.Data.SqlClient
- latest sp's & hotfixes

when I use a stored procedure (result 50 rows, execution time 2 seconds,
about 600,000 rows in the table) in a c# code I'll get a "timeout expired"
SqlException.
...
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "sp_x";
myCommand.CommandTimeout = 0;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
x = myReader["x"].toString(); // <<-- TIMEOUT
}
...


the same sql in a "normal" select statement will work.
...
myCommand.CommandType = CommandType.Text;
myCommand.CommandText = "select x from ...";
myCommand.CommandTimeout = 0;
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
x = myReader["x"].toString(); // <<-- OK
}
...

if I reduce the amount of rows (result about 5 rows, execution time less
than 0.2 seconds, about 10,000 rows in the table) the stored procedure will
work in c#.

but the "large" stored procedure will work with all other softwareproducts
(like ms access, dts wizard, and so on).

thanx for your help
mac

 

[William Ryan]

If you put a breakpoint in the while loop, does it ever hit the
x=myReader["x"] line? If so, then the problem is weird indeed.

0 Should set the timeout to unlimited (or so I thought), but it's kind of
weird what you are reporting... Once the query starts returning results, it
won't timeout even if it took years to complete. Just to test, what happens
if you bump up the timeout to say 120? Or leave it at it's default. If you
are sure the query only takes 2 seconds to run with the 50,000 rows, then
give this a try. I was of the opinion that if you set the timeout to 0 it
will never timeout, ever, so it's often not a good idea to do this b/c you
can hang your database up and give the user a unresponsive app. Anyway, as
soon as you get one row back from the DB, even if 600k were coming back, you
should be alright. Since you are timing out, it would appear that setting
the timeout as 0 either doesn't actually mean unlimited, or for some reason
it's not working.

Try some other number, or leave it at the default setting and see what
happens.

HTH,

Bill

 

[William \(Bill\) Vaughn]

1) Commandtimeout 0 gives you 1 second--it should be infinite, but it's not.
Set it to 15 or 30 and see if this fixes the problem. this is fixed in
Whidbey and some later builds.
2) I'm confused as to why it's failing on the access and not the
exectuereader, but this is when the data is actually moved from the server.
I suspect you're doing it to yourself with the Timeout=0.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________