Query Execution was Interrupted in ASP

It’s one of the more frustrating errors you can get in ASP coding against a SQL database. Query execution was interrupted, maximum statement execution time exceeded. Usually this comes along with a error ‘80004005’.

What this means in essence is that your query you’re trying to run is taking too long. Your MySQL database has a maximum time limit set, in order to prevent runaway long queries from bogging down your system. Sure, that’s fine, but sometimes you need to run something for a while to get a specific task done.

How do you get around the timeout error?

Your first option is to try a server timeout setting. Something like:

Server.ScriptTimeout=30000

Depending on the type of action you’re trying to do, this could do the trick. But there are going to be specific kinds of queries that still get caught up in a time limit. So next we have:

objCmd.CommandTimeout=10000

This works if you’ve set up a command object a la Server.CreateObject (“ADODB.Command”) – if you do that, you can then set a timeout value for it.

However, what if you’re not doing a command? What if instead you’re doing a recordset type of activity, and it’s the initial selection of the recordset that is timing out?

This is where it gets fun.

Where your code says something like:

SQLText = “select field 1, field 2 from …

add in something like this:

SQLText = “select /*+ MAX_EXECUTION_TIME(60000) */ field 1, field 2 from …

That time is in milliseconds. So this is 60 seconds.

The database realizes that you’ve embedded a special command, interprets it, and then goes on and does normal SQL processing on the rest.

Very cool!