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