Timeout Error

Question

Timeout Error

Marco Nannini

Newbie

Points: 3
More actions
August 17, 2006 at 2:17 am

#91244

Hi,
I have written a stored procedure as follows
BEGIN
WHILE "Some condition"
EXECUTE dbo.PROC1
END
Now, Proc1 is quite heavy computationally and tipically will take about 15-20 seconds to perform at every loop. If i manually run Proc1 time after time it works fine with no error. However, if i run it as in the procedure above after the second time round i get a timeout error. How can i avoid it? I want it to run for however long it takes.

Thanks,

Marco Nannini.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Marco

To help you out, we'll need to see the code in the stored procedure, and probably also table DDL and sample data. Then we may even be able to help you avoid using a loop to do this at all.

John

Marco Nannini Newbie Points: 3 More actions · Answer 2

The procedure is long and complex and it calls lots of udfs, if i were to post all the elements it would be the longest post on the forum... I dont think the problem lies with the procedure itself...

The procedure works fine, it is just long, you can run it once, twice, n times, no error. But if i run it in a loop I get a timeout error after about 20 seconds (hardly a long time)... Since the procedure doesnt rollback i can go and check where it stopped and there is nothing strange, it just timesout.

I have found no reference so far to this error but i'm sure i could replicate it as follows:

While some condition

Begin

Exec dbo.proc1

end

then write a silly proc1, something like

Begin

Wait 10 seconds

End

I'm sure that you could run the silly proc1 time after time and it would just work but in a loop it would timeout.... Imagine the example above, how would you change settings so that it doesnt timeout for at least 10 minutes (as opposed to 20seconds)?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Have you tested your assumption that the silly proc1 would time out if run in a loop?

Marco Nannini Newbie Points: 3 More actions · Answer 4

How can i change the commandtimeout option on the server? I know how to do it while establishing a connection but not within visual web developer... any takers?