How to handle time out inside the stored procedure

How to call stored procedure "SPA" from stored procedure "SPB" and if execution of procedure "SPA" is more then 2 min then return as time out.
Bharat GuruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
In my opinion you can't really "return a timeout" from a SPA "if execution of procedure "SPA" is more then 2 min..."
lcohanDatabase AnalystCommented:
you could define some variables inside your SP's and check against some reference variable how log (minutes, seconds) passed since but once a statement is started inside an SP you can't say how long it will take or check how long it was running for inside that same object while this statement is running right?
Bharat GuruAuthor Commented:
Then how can I prevent to run the stored procedure for time out
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

lcohanDatabase AnalystCommented:
For that matter you could use "Configure the remote query timeout Server Configuration Option" however CAUTION!!! that this setting will apply at SERVER LEVEL so any query longer than 2minutes or 120seconds will timeout after that execution time!!!
Default I believe is set to 600seconds or 10minutes and many times that is set by DBA's to 0-unlimmited on production servers where there are SQL Jobs or reports running longer periods of time.
Bharat GuruAuthor Commented:
I basically want to call the stored procedure from a sql agent job but I want to time out it take more then few minutes
Maybe you can try sending an aproximate  timeout value as a parameter, and evaluate it at some places in your SPB.
lcohanDatabase AnalystCommented:
You could create a temp table like #jobs then populate it like

insert into #jobs exec msdb.dbo.sp_help_job;

then select/check from it the job you are interested in and see for how long it was running then "cancel" it using
      [@job_name =] 'job_name'  
    | [@job_id =] job_id  
    | [@originating_server =] 'master_server'  
    | [@server_name =] 'target_server'

if it was running more than 120seconds.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bharat GuruAuthor Commented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
What the SP is trying to do?
If it's a query operation that's running longer that expected then the solution is to tune the query.
Mind that the timeout configuration can only be set from remote connections.
Bharat GuruAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.