How to set Timeout for a specific sql query?

Question

I want to set Timeout value for a specific sql query which will execute inside a stored procedure. Is it possible to set the timeout value for a particular query?

why you are trying to use in sql instead of using in .net or java — Mani
– Mani, Commented Aug 18, 2016 at 11:22
SQL Server won't stop running your query, you should set the timeout in the client from where you perform the connection. If it's SSMS you can use sp_configure — BytesOfMetal
– BytesOfMetal, Commented Aug 18, 2016 at 11:25
I'm using .Net in front end and there is an option from SqlCommand to set the timeout like command.CommandTimeout = 10000. Are you referring this to me? @Mani — Gopal Biswas
– Gopal Biswas, Commented Aug 18, 2016 at 11:56
Thanks for your suggestion. Actually I'm looking for the same option for a stored procedure in SQL Server. @ Mani — Gopal Biswas
– Gopal Biswas, Commented Aug 18, 2016 at 12:08

Dan Guzman · Accepted Answer · 2016-08-18 12:40:58Z

1

It is the client API rather than SQL Server that enforces query timeouts (RPC or batch). Consequently, you can't set a client command timeout at a more granular level when a stored procedure contains multiple statements. You'll need to split the proc such that the desired query is executed separately by the client, and specify a different timeout for that command.

The specifics of how to set the timeout vary depending on the client API. In the case of .NET, it is the SqlCommand.CommandTimeout property.

edited Aug 18, 2016 at 12:40

answered Aug 18, 2016 at 11:39

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gopal Biswas Over a year ago

Suppose the proc contains only one query then what is the way to specify timeout for that proc? Is it from front end like .Net/ java... SqlCommand? @Dan Guzman

Dan Guzman Over a year ago

The CommandTimeout property of .Net SqlCommand will control the timeout. From Java, I think it depends on the driver (e.g. supports setQueryTimeout() method.

Collectives™ on Stack Overflow

How to set Timeout for a specific sql query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related