How to handle time out inside the stored procedure

Bharat Guru
Bharat Guru used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
In my opinion you can't really "return a timeout" from a SPA "if execution of procedure "SPA" is more then 2 min..."
lcohanDatabase Analyst

Commented:
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?

Author

Commented:
Then how can I prevent to run the stored procedure for time out
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

lcohanDatabase Analyst

Commented:
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.

Author

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.
Database Analyst
Commented:
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.

Author

Commented:
Thanks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial