Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 789
  • Last Modified:

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.
0
FDzjuba
Asked:
FDzjuba
  • 2
  • 2
1 Solution
 
Eugene ZCommented:
0
 
FDzjubaAuthor Commented:
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.
0
 
Eugene ZCommented:
then put the sp in job and

then check jobs status with datediff and check job status -> raiseerror
within ' while begin.. end'

'how to stop a job after certain time'
see @
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21317394.html



0
 
FDzjubaAuthor Commented:
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.DisplayAlerts')
EXEC @hr = sp_OASetProperty @object, @CallMe, 'False';
IF @hr <> 0 GOTO Close_Excel


0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now