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:
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:
And on the new server it looked like this:
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.