Timeout a query

Question

Hi,

I wanted to know if we can timeout a sql query.

In the sense, that suppose I have a sql query whose output is useful only if gives the output in 10 minutes ,after which even if it outputs the results its of no use to me.

What I want to do is that if the query takes more than 10 minutes to do the processing then it should just kind of kill itself.

Is there a possible way to do so??

An example will be pretty helpful.

Let me know if my thoughts are not comprehendible..

Answer 1

You can set the CommandTimout property of the Command object to 10 minutes. When the command times out, SQL Server will notice that the connection is dropped and cancel the query.

Answer 2

Here's what it would look like for the SqlCommand.CommandTimeout

   SqlCommand cmd = new SqlCommand();
   cmd.CommandText = "SELECT blah FROM Categories ORDER BY CategoryID";
   cmd.CommandTimeout = 600;  // 10 minutes = 600 seconds
   // Caveat: it you need a timeout value of more than 30 - 60 seconds
   //         perhaps time to look at why it takes so long...