Link to home
Start Free TrialLog in
Avatar of Bharat Guru
Bharat Guru

asked on

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.
Avatar of lcohan
lcohan
Flag of Canada image

In my opinion you can't really "return a timeout" from a SPA "if execution of procedure "SPA" is more then 2 min..."
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?
Avatar of Bharat Guru
Bharat Guru

ASKER

Then how can I prevent to run the stored procedure for time out
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks
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.
Thanks