Query Timeout Setting Advice

Hi Experts,
How to set the query Timeout to 10 seconds.
I checked it using sp_configure and it shows 600 seconds for remote query timeout.
I changed it using sp_configure 'query timeout',10
The above command executed & displayed changed setting.

But still when i ran a query in QA which takes more than 5 mins. doen't time's out

Why is this happening. Is there any other setting i need to change..
I hope you got what i wanted...?
I need to cancel those queries running from QA to be time out in 10 second taking more than that...
Thanks.

Hi ya,

in QA go to Tools, Options. Click on the Connections tab and set the timeout there to what you want. QA will set its own timeout for each connection, the one in MSSQL itself is just a default timeout

Cheers
Twan

Hi TWAN,
I set the option in QA i.e. query timeout from 0 to 10 seconds...
Still the query is not getting cancelled it runs more than that specified time...
Is it something im missing still???????

Hi Satya,
I ran sp_configure 'show advanced option',1
sp_configure 'query wait',10
reconfigure
exec sp_configure


it shows that query wait=10 & query timeout=10
still the query runs for more than 10 seconds and the query is simple select query.

Hi Guys,
I'm still in no mans land.
The portal application calls DB from front end.
The front end guys needs the query timeout error when it runs for more than 10 seconds.
They dont want to make it from front end.
So how to get it done.
I tried sp_configure as i mentioned earlier but with no fruit.
After reconfigure when i run a query in QA it still runs even after 10 seconds.

Post your comments guys??????????

Hi Satya,<br />Thanx alot,<br />i dint thought of restarting the services as you mentioned and i read in BOL of no need of restarting so i left as is.<br />Anyways i restarted the service and its running with timeout expired message.<br />Thanx alot.<br />I'm really lucky that im in this forum ..........[<img src='/community/emoticons/emotion-1.gif' alt='' />]

If you set the query time-out at the server, ALL remote queries against ALL databases will time-out after 10 seconds, not only queries from a specific front-end application against a specific database.

Hi Adriaan,
That's true,
But is there any way to set the query timeout only for a specific database at server level????

I don't think so, check BOL for Setting Database Options.

In the end, that's why it is available as a client-side setting ...

I checked in BOL about databse options adriaan,
But of no help.
So i prefered better for all rather than for none.
anyhow, I'm still fighting to make it client side setting, lets seeee????

Hi Experts,
Sorry to say i checked that in my QA window and was expiring after restarting because of the option i set in QA to 10 secs as said by adriaan.
So that suggestion really worked for me.
So whenever i change any value in QA option it works in QA window and not by sp_configure.
I tried to set query wait & query timeout but both not expiring the query within span.
What should i try now.
I want query timeout especially in application.

Your question was already answered: define the query time-out setting for the connection from the client side.

Hi Adriaan,
Thats true that i need to do it from client side.
But not possible for me this time.
You gave me the solution to change the QA option of query timeout to 10 seconds which worked but now how to get that same timeout expired message through portal.

Your portal must be running a client application of some kind.

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Your portal must be running a client application of some kind.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Yes Adriaan,<br />But Appl developer needs to add that timeout code in various places so they want configure at one central point i.e. at backend. So needs to accomplish this. But how still untraceble.[<img src='/community/emoticons/emotion-6.gif' alt='' />][xx(]

Any decent appl developer knows how to create reusable code - objects, classes ... Reusable code is the central point they need to look at, not the backend.[<img src='/community/emoticons/emotion-1.gif' alt='' />]