Timeout expired

[jag_chat]

I wrote a stored procedure...and executed in Query Analyser.
It took 40min to execute....ofcourse...it is something like archieving database of millions of rows along with distributed queries....I am quite ok with the stored procedure.....(even time taking)...

But, then I executed the same stored procedure in my program using the command object which within less than a min...I am returned with the following error:

TIMEOUT EXPIRED. THE TIMEOUT PERIOD ELAPSED PRIOR TO COMPLETION OF THE OPERATION OR THE SERVER IS NOT RESPONDING.

I think the connection could not sustain till the completion of store procedure.

Can any one help me how to execute through UI.

[VBCrazyCoder]

Set your timeout for your command object to more than the default 30 seconds (I think that is the default). Then you should be all set.

You might also want to make the call to execute the Stored Proc asynchronous, so you program doesn't wait around for it (unless you need a return value or something).

[jag_chat]

the CommandTimeout property is for the time it has to wait before executing the command in dotnet.....right?....I don't think this will help me....but if possible can u give me any sample or doc or link, how to achieve the asynchronous methodology

[Grimfort]

No, its how long to wait before giving up. The default is 30 seconds (i belive) anthing above that, it thinks the database has crashed or something, and just carries on anyway. You may be able to set it to 0. Heres what M$ say:


OleDbCommand.CommandTimeout

Property Value
The time (in seconds) to wait for the command to execute. The default is 30 seconds.


Remarks
A value of 0 indicates no limit, and should be avoided in a CommandTimeout because an attempt to execute a command will wait indefinitely.

[jag_chat]

"The time (in seconds) to wait for the command to execute. The default is 30 seconds."

The above statement is a straight copy and paste from MSDN April 2003 of VS.NET 2003. So, I think you are mistaken.

[Grimfort]

Thats exactly what I just pasted, did you even read my reply ?

Of course its not the time to wait BEFORE starting the statement, why on earth would it do that ? If you dont believe me, then set the timer to 300 and run a small statement. Does it wait 300 seconds before it starts it ? I think not.
If your query should take 30 seconds to run, then setting the value to 60, gives you enough time for it to run, and then fail after 60 seconds to ensure your program wont lock up and wait forever.

In the case of a 40 minute run, set the timeout to about 5000 (83 minutes) and if the query is not finished after that time, then something is wrong. If the time may vary often, then set it to 0 (zero) and hope it works.

To make it work concurrently (ie in the background) then create a new thread, and call the function from the new thread, and close the thread when its completed. There are many examples about.

[jag_chat]

Sorry...I didn't see ur reply exactly..

But I request u do give a better explanation or any ex regarding

"To make it work concurrently (ie in the background) then create a new thread, and call the function from the new thread, and close the thread when its completed. There are many examples about. "