Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Timeout Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 8:36 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:05 AM
Points: 1,474, Visits: 1,652
Hi,

I've just been presented with a potential scenario where I will need to execute a procedure to return a result set, if this query executes in less than a given number of seconds then I will need to re-execute the procedure (different parameters) and so on.

However if the procedure execution lasts longer than the number of given seconds then I will want to abort the procedure execution and just return the result of any previous executions (which I will of stored away).

So my question is, is it possible to say, attempt to execute this procedure, if it takes longer than x number of seconds then raise an error or something. I know we can do this on the server, but is it possible in 2K5 to set this timeout for just one query execution?

Thanks,

Jackal


------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #634732
Posted Monday, January 12, 2009 8:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 42,338, Visits: 35,401
SQL has no concept of timeouts (other that lock timeouts). A timeout is a client application setting that controls how long the client app is willing to wait the server to finish before cancelling the query. If you need to set a timeout, you have to do that in the front end application.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #634751
Posted Monday, January 12, 2009 9:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
It is possible to set a maximum timeout value for remote queries, at the server level, but that won't do what you're looking for. For that, it would need to be set up in the application calling the queries, whether that's a data connection layer or a front-end application.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #634755
Posted Monday, January 12, 2009 9:20 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, July 10, 2014 4:05 AM
Points: 1,474, Visits: 1,652
Thankyou both for the reply.

Your help is much appreciated.

Jackal


------------------------------------------------------------
Check out my blog

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #634775
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse