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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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..."
0
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?
0
Bharat GuruAuthor Commented:
Then how can I prevent to run the stored procedure for time out
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lcohanDatabase AnalystCommented:
For that matter you could use "Configure the remote query timeout Server Configuration Option" https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/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.
0
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
0
aranaCommented:
Maybe you can try sending an aproximate  timeout value as a parameter, and evaluate it at some places in your SPB.
0
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
sp_stop_job  
      [@job_name =] 'job_name'  
    | [@job_id =] job_id  
    | [@originating_server =] 'master_server'  
    | [@server_name =] 'target_server'
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql

if it was running more than 120seconds.
1

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:
Thanks
0
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.
0
Bharat GuruAuthor Commented:
Thanks
0
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
SQL

From novice to tech pro — start learning today.