EXEC in stored procedure need to set timeout
Posted on 2005-03-09
The situation is, i have a stored procedure that calls another sp, which will be generating excel report (quite complex), because guys at Microsoft are drunk or on drugs when writing programs, there is a issue when working with excel COM, i hangs SP for unlimited period of time, sitting there and doing nothing, the reason for that is unknown and will never be known. But I don't care about that. What I care about is to make Caller stored procedure to timeout if called stored procedure has been running for longer than 2 minutes or so? Of course before than I will be killing excel and all related processes on the server.
Is there a way to set timeout limit for called out procedure. something like
SET timeout =200000
EXEC ExcelMustDie @bla=@bla, @bla=@bla
KILL KILL KILL 'EM ALL
i can't find a solution in help files or anywhere on the net, but thats just me.
on more thing, the initial SP will be called by a job agent, so is there any way maybe to set timeout on the job instead and then simply go from there?
I cannot alter server's configuration and I cannot use any external client apps to launch SP in the first place.