Imagine it's year-end. In fact, imagine tomorrow is the deadline to finish closing the prior year. You've never done this before and are following the support guide of closing procedures for General Ledger. So far, you've made it through right up to the step of clicking Close Year in the Year-End Closing window (Step 11). There're only a few steps left! You click the Close Year button and watch the progress bar begin to fill up. Victory is near! But then out of nowhere, an error stops the whole process. You stare blankly at an annoyingly vague "Query timeout expired" message.
You do what any sane person would do, which is everything to try to fix the error and get the year closed; restart GP, restart the SQL server, clear the temp and activity tables, run Reconcile, run Check Links, disable every 3rd party product, install a new copy of GP, replace the whole workstation, beg for it to just work. Nothing changes, and you can't sleep because all you see when you close your eyes is that error! You cry out in vain, "What is happening?! What do I do?!"
We often see support cases on this error around the busy year-end season. Typically, this happens when closing the year for General Ledger. However, it can happen in other modules as well, and not just during year-end closing. For the sake of this example, let's stick with closing the year for General Ledger.
The "Query timeout expired" message is indicating that GP did not receive a response from a SQL query within the time allotted by GP. By default, GP will wait 300 seconds (5 minutes). That limit is implemented so that GP is not hung up indefinitely when waiting for a response to a SQL query (for example if there is damaged data preventing the action from completing or a SQL query that creates a continuous loop). 300 seconds is normally plenty of time to complete a single query, but in some cases the data being queried can branch out and cause the query to run much longer than expected. This is especially the case during Step 3 of the Year-End Closing routine, which is when currency translation occurs. That process is very data intensive and often requires more than 5 minutes to complete.
The good news is that there is a quick fix we can try. All we need to do is change (or add) a line to the Dex.ini file in the GP data folder.
NOTE: Please make sure that you have complete backup prior to performing any troubleshooting steps.
First, close out of GP and make sure the SQLQueryTimeout line exists in the Dex.ini file of the GP install. Including the SQLQueryTimeout setting in the Dex.ini file will control the period of time GP will wait for a SQL query to execute. When set to 0 it will wait indefinitely. The valid range for this setting is 0 to 9999. If this setting is not present, the default value is 300 seconds.
The Dex.ini is typically located in the following location:
C:\Program Files (x86)\Microsoft Dynamics\GPXXXX\Data\
Update or add the following line to the Dex.ini file and test running the year-end close again.
There are two other timeout settings that could also be useful in dealing with these type of errors.
So if timeout errors are haunting you in other situations, try adding those settings to the Dex.ini file.
I hope that helps explain what these timeout errors can mean and gives a good workaround to get past them, especially during that year-end crunch time!