We help IT Professionals succeed at work.

How to handle time out inside the stored procedure

Bharat Guru
Bharat Guru asked
on
160 Views
Last Modified: 2017-04-19
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

lcohanDatabase Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
lcohanDatabase Analyst
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
Maybe you can try sending an aproximate  timeout value as a parameter, and evaluate it at some places in your SPB.
Database Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions