?
Solved

EXEC in stored procedure need to set timeout

Posted on 2005-03-09
4
Medium Priority
?
783 Views
Last Modified: 2012-08-13
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
Comment
Question by:FDzjuba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13502455
0
 
LVL 7

Author Comment

by:FDzjuba
ID: 13507559
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
 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 13508075
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
 
LVL 7

Author Comment

by:FDzjuba
ID: 13508211
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question