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.
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Bharat Guru

8/22/2022 - Mon
lcohan

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

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 Guru

ASKER
Then how can I prevent to run the stored procedure for time out
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
lcohan

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.
Bharat Guru

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
Arana (G.P.)

Maybe you can try sending an aproximate  timeout value as a parameter, and evaluate it at some places in your SPB.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Bharat Guru

ASKER
Thanks
Vitor Montalvão

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 Guru

ASKER
Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23