Tuesday, February 21, 2012

SQL query exceeds specified time limit or storage limit

One user was having problems using an Microsoft Access 2003 form after changing her machine.  The person who developed the form has left the company so she has no idea how the form works.  She has to keep going back to her old machine to run the form.  Out of goodwill, I decided to help her to troubleshoot the problems.

The form provides function to pull data from an Excel file and from DB2 as well.  I opened the the form in design mode by holding down the shift key while opening the Access file.  The design mode allowed me to better understand how the form works and where/how data are being pulled.

The first problem was the linked table path for the Excel file was out-dated after the change of machine.  The problem was fixed by updating the path through the Linked Table Manager.

The second problem was due to the limited text field size (255) of Access table.  The data from the Excel file exceeded the size limit so she has to truncate the data to make them fit in.

The last problem happens when the form tries to pull data from DB2.  It throws out the following error.

ODBC – call failed.
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0666 – SQL query exceeds specified time limit or storage limit. (#-666)

SQL query exceeds specified time limit or storage limit

A quick search leads me to http://www-01.ibm.com/support/docview.wss?uid=nas1337ac2bf0df7e4268625697d00793eb5.

The cwbODBCreg tool seems to only set the value of QueryTimeout in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\ODBC.

On her new machine, there are HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\ODBC and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC.

The fix for her is to set the value of QueryTimeout in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC to 0.

No comments: