FDzjuba
asked on
EXEC in stored procedure need to set timeout
Hi, all.
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
IF @@ERROR
BEGIN
KILL KILL KILL 'EM ALL
END
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.
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
IF @@ERROR
BEGIN
KILL KILL KILL 'EM ALL
END
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.
ASKER
that is not going to help, this will set a PAUSE before execution the stored procedure. What i need is to execute procedure then raise error if sprocedure has been running for longer than 2 minutes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
great!
that will do, thanx.
for future generation:
Yeah by the way excel was hanging there for ages because it was attempting to display alert message, like "incorrect formula" or "do you want to save a file", to stop this from happening Application.DisplayAlerts = False needs to be set, this will improve the situation. example
SELECT @CallMe=RTRIM('Application
EXEC @hr = sp_OASetProperty @object, @CallMe, 'False';
IF @hr <> 0 GOTO Close_Excel
that will do, thanx.
for future generation:
Yeah by the way excel was hanging there for ages because it was attempting to display alert message, like "incorrect formula" or "do you want to save a file", to stop this from happening Application.DisplayAlerts = False needs to be set, this will improve the situation. example
SELECT @CallMe=RTRIM('Application
EXEC @hr = sp_OASetProperty @object, @CallMe, 'False';
IF @hr <> 0 GOTO Close_Excel
WAITFOR { DELAY 'time' | TIME 'time' }
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_wa-wz_8ipg.asp