SQL0666 - SQL query exceeds specified period or storage limit

Question

Periodically, I get this error message when I call a DB2 database using the Odbc connection string. I tried setting a few values ​​for the CommandTimeout of the DbCommand object, but I still get the following error.

SQL0666 - SQL query exceeds specified period or storage limit.

Is there a trick to make this stop the error. This is very strange, because the same request will sometimes work, and sometimes a timeout. Any help would be greatly appreciated. Thanks!

Answer 1

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

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

Answer 2

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

Earlier today, one of my SSIS packages began to receive this error at one stage. After a little research, I found that my package failed on the DataReader Source object, which connects to the iSeries database through ODBC. I'm not sure if this is an ODBC error or an error in the iSeries / ODBC DB drivers, but the error message was exactly the same.

It is really strange for me that I could view data from a linked table in MS Access that connects through the same ODBC connection, and I could also start the MAKE TABLE operation from the same dataset in Access without any problem. After searching for the error message, I found this Q and A. This tip also works for SSIS packages.

To fix this in SSIS, you need to open your package in Microsoft BIDS Designer. Then open the associated Data Flow Task , and then select the DataReader Source object that is timed out.

Your DataReader Source object has a property, also called CommandTimeout . Setting this parameter to 0 (and not the default 30 ) should fix the problem. After verifying that the timeout was a problem, I set the timeout to 60 and re-executed the step. The problem is one minute.

It's worth noting that you might be tempted to update your CommandTimeout values ​​for all of your DataReader Source objects to 0 . This is not recommended. Instead, save timeouts and increase the limit to fairly generous values. Connect them the same way I do, 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 does not shut down, your application may freeze out of the possibility that due to an error in your database there is a problem due to which the step never ends! This may be unlikely, but not impossible.

Be safe and set your timeouts accordingly.