SQL0666 - SQL query exceeds specified time limit or storage limit

Question

Periodically, I get this error message while making a call to a DB2 database using the Odbc connection string. I have tried setting the CommandTimeout of the DbCommand object to multiple values, but I still get the following error.

SQL0666 - SQL query exceeds specified time limit or storage limit.

Is there a trick to getting this to stop erroring out. It is very odd because the same query sometimes will work and sometimes will timeout. Any help would be appreciated. Thanks!

Solution 1

I have tried setting the CommandTimeout of the DbCommand object to multiple values

I set the DbCommand.CommandTimeout= 0 and this fixed the timeout error

Solution 2

Kite's answer is the correct, however, I wanted to share my observation/experience after finding this Question and Answer while searching for a fix to this same error message from within a SQL Server Integrated Services (SSIS) project.

Earlier today one of my SSIS Packages started to receive this error on one of it's steps. After a bit of research I found that my package was failing on a DataReader Source object that connects to an iSeries database through ODBC. I'm not sure if this is an ODBC error, or an error within the iSeries/ODBC DB drivers, but the error message was exactly the same.

For me, the really odd thing was that I could browse the data from in a linked table in MS Access which connects through the same ODBC connection and I could also run a MAKE TABLE operation off of the same dataset within Access without any trouble. After searching for the error message, I found this Q & A. This tip also works for SSIS packages as well.

To fix this within SSIS you need to open your package in the Microsoft BIDS designer. Next, open the associated Data Flow Task and then select the DataReader Source object that is experiencing the timeout.

Your DataReader Source object has a property that is also named CommandTimeout. Setting it to 0 (rather than the defaulted 30) should fix the problem. After verifying that the timeout was the issue, I set the timeout to 60 and re-executed the step. The one minute timeout fixed the problem.

It's worth noting that it may be tempting to update your CommandTimeout values on all of your DataReader Source objects to 0. This isn't recommended. Instead, keep the timeouts and increase the limit to a rather generous value. Double them, as I did, or give an even more generous 5-10 minute timeout value.

Timeout properties exist for a reason. You can give your application generous timeouts but if the application doesn't timeout at all, your application may hang on the off-chance that there is a problem from within your Database engine that causes the step to never finish executing! This may be unlikely but isn't impossible.

Be safe and adjust your timeouts appropriately.

Comments

[Admin]

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.