We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


ODBC--Call failed. AS400/Access query

Medium Priority
Last Modified: 2013-12-06
I have a user that attempts to run a query in Access from linked AS400 tables and receives the following error. ODBC--Call failed. I ran the same query from my computer and it worked fine. At her computer, I ran some of the querys that make up the first query and found that one of the querys brings back the following error message; ODBC--Call failed. [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit. (#-666). If I attempt to open the table that this particular query uses, it opens fine without any errors. Any ideas? I tried to be a succint as possible. Please let me know if there are any questions. I have also attached a screenshot of the error message.
Watch Question

VP Technology / Senior Consultant
Well, it is probably not a surprise that you get this message when a query runs for too long or produces a large amount of intermediate results.

It would be helpful if you posted the failing query.  
Also, determine how many records are being selected by this particular query.

For example, if the query says:

select a,b,c from table1 where a> 100 and b =3
Run a summary query to get a record count:

select count(*) from table 1 where a>100 and b=3
There is a Query Timeout parameter that you can set either on the ODBC DSN that the query references:

  • Open ODBC Data Source Administrator on the workstation that is experiencing the error
  • In the Administrator window, select the DSN and click Configure
  • Go to Performance tab inside the iSeries Access for Windows window that appears
  • Click Advanced under the Performance options.
  • Uncheck "Allow Query Timeout"
  • Apply and OK out.
Or in Access itself (open the query in design mode and select View --> Query Properties.  Increasing the timeout gradually may resolve the issue.

Note that the instructions for both of these operations may vary a bit depending on your OS and Access version.  If you need version-specific instructions, post the relevant info here.

If the issue is intermediate size, post backl and we can take a look at that issue.

- Gary Patterson

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Gary PattersonVP Technology / Senior Consultant

Here's more information on SQL errors (you'll have to search down for SQL0666), and various remedies.  Note that these options and remedies change a bit from release to release, so make sure you use the correct Information Center.  I picked V5R4 for this example:


- Gary Patterson


As soon as I removed the "Allow Query Timeout" selection, the query ran wtihout any errors.

I had this same problem about two weeks ago.  I am not entirely sure which of the actions I took resolved it, but I think it was related to his Windows domain user ID rights on the network.  Because he did not have the capability of deleting the .ldb file from the location on the network where the database resided.  I however did have the proper rights.  When I ran it from my machine on the network it was fine, but from his machine logged on as him, it would error out.  I would try temporarily changing the network rights for this one user and see if it makes a difference.  The other two things I did for him was to run Windows update and to update his iSeries Access for Windows client.  In my case, the error message may have had a different error number, but it seems odd that it would not time out for you but it would for your user.  These messages are often not very well formed and can be misleading.
Gary PattersonVP Technology / Senior Consultant

I posted the wrong link above for the detailed error messages:


The generic message "ODBC Call Failed" is not very meaningful, and is returned by Access as a catch-all whenever anything goes wrong on an ODBC call.  That's why it isn't very useful in diagnosing problems - lots and lots of underlying problems can cause this generic message.

If the generic "ODBC call failed" mesage is in response to a lower-level message from the database server (as it is in this case), it can be a very good indicator of the problem (as it was in this case).

The trick is to use the detailed error message (SQL0666 in this case) that is actually generated by the database server (AS/400 DB2 in this case - which in my experience generates better-than-average error information.)  If you look at the link above, and search for SQL0666, you'll find a pretty detailed description of the problem and resolution right in the IBM documentation:

SQL0666    Message Text:

SQL query exceeds specified time limit or storage limit.  

Cause Text:  

A database query was about to be started whose estimated run time of &1 exceeds the specified limit of &2 or whose estimated temporary storage usage of &3 exceeds the specified limit of &4. The query time limit and temporary storage limit are specified on the CHGQRYA CL command.  

Recovery Text:  

The following changes to the query could reduce the estimated elapsed time or estimated amount of temporary storage usage.

  • Change the query so that fewer records are returned by adding more restrictive record selection specifications.
  • Change the record selection of the query so that an existing access path can be used to process the records more quickly.
  • Create an access path with keys that match the record selection of the query using the SQL CREATE INDEX statement.
  • Change the ordering specification so neither a temporary access path needs to be built nor a sort needs to be performed.
  • Change the grouping field specification to match the left-most key fields of an existing access path. This allows an existing access path to be used.
  • Specify the OPTIMIZE FOR NN ROWS clause where NN represents the actual number of records which are to be retrieved before the query is ended. This clause will cause the query optimizer to assume that the query will not be run to completion and consequently reduce the estimates.
  • Change the output type of your query to produce the results on the display.
  • Specify a new value for the query time limit with the QRYTIMLMT parameter of the CHGQRYA CL command.
  • Specify a new value for the query temporary storage limit with the QRYSTGLMT parameter of the CHGQRYA CL command.
  • Examine the query debug messages contained in the job log for performance information and suggestions. These messages will precede this message in the job log and will provide additional information and suggestions.
  SQLCODE or SQLCODEs:  -666  


I forgot to mention some things that I had done before posting the question. The query builds a report using other queries. I tested those individual queries on the users machine and found that I received an error message on one particular query. From my machine, I changed the all of the query definitions to include a smaller record set. However, she would still get the same message. After you posted your fix above, I looked at the configuration on my machine and found that the "Allow Query Timeout" was not selected on my computer. This would explain why it worked on mine, and not hers.
Gary PattersonVP Technology / Senior Consultant

Thanks for the update.  The configuration difference sure explains the difference in behavior.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.