Oracle Database Error ORA-01013 in Excel VBA After Migrating Application to New Server

I was testing an old Excel application that I had moved from one server to another and kept getting this error when running it on the new server:

VBA run-time error ORA-01013

The error ORA-01013 (“user requested cancel of current operation”) is commonly a timeout issue. What could be causing a query to timeout on one server, but not another?

In debugging the code, I found that no CommandTimeout property had been set on the Connection object.

Dim cnnRawData As ADODB.Connection
'...
Set cnnRawData = New ADODB.Connection
cnnRawData.ConnectionString = glbConnectString
cnnRawData.Open
cnnRawData.Execute glbSetCurrentSchema

If it is not explicitly set, this property will default to 30 seconds. However, this did not explain why the query did not timeout on the old server.

The connection to the Oracle Database was being made via ODBC, so I opened the 32-bit ODBC Administrator to see if the connections had been set up differently. This is where I realized what the problem was.

On the old server, the connection configuration looked like this:

ODBC config on old server

And on the new server it looked like this:

ODBC config on new server

See the difference? (Aside from the Description field, that is!)

It’s the “Enable Query Timeout” checkbox in the Application tab. By default, when creating a new connection, it is checked. I unchecked the box and saved the connection, and the error went away.

This is not necessarily the best way to do it, however. I might want some queries to timeout, and not others. In this case the solution would be to set the CommandTimeout property for this particular VBA macro to 0:

Dim cnnRawData As ADODB.Connection
'...
Set cnnRawData = New ADODB.Connection
cnnRawData.ConnectionString = glbConnectString
cnnRawData.CommandTimeout = 0
cnnRawData.Open
cnnRawData.Execute glbSetCurrentSchema

This would prevent timeouts on this particular connection, but not any others that might be made using the same ODBC connection.

Leave a Reply