continue with next iteration if foreach loop takes too long

Question

I have a for each loop that pulls queries out of a table with queries. The loop then executes each query and writes the results to a table. This works like a charm. I even implemented error-handling for when a query could not be executed (if it for example does have syntax-errors).

But sometimes executing a query will take forever, for example a query with a cross join without proper predicament.

Now I would like to be able to set a max duration on the execution of a query. So that a query will be stopped after an x amount of minutes (if it did not finish by then). The loop should then continue with the next query. In other words, an iteration should never take longer than X minutes, after that it should continue with the next iteration.

Any ideas, suggestions?

Answer 1

You CANT set a timeout for the query on the server.

Either you create a client app where you can set a timeout for the sql command

 SqlCommand command = new SqlCommand(queryString, connection);
         // Setting command timeout to 1 second
         command.CommandTimeout = 1;
         try {
            command.ExecuteNonQuery();
         }

Or try this External Tool to monitor query and kill those process when time run out. As mentioned on dba.stackexchange