RESOLUTION To resolve this problem, use one or more of the following methods: Method 1: Reduce the time that idle cursors remain open Method 2: Increase the maximum limit of open cursors Method 3: Change the X++ code Note: Before you follow these steps, test these steps in a testing environment, and back up your data.
Method 1: Reduce the time that idle cursors remain open The open cursors may exceed the limit because idle cursors are not closed. The Timeout connection after being idle setting in the Microsoft Axapta Client Configuration Utility or in the Axapta Object Server (AOS) settings can reduce the time that idle cursors remain open. You can use this method to decrease the seconds before disconnecting idle cursors setting from the default of 60 seconds to less than 30 seconds. AOS three-tier thin client:
1. Click Start, click Control Panel, and then click Navision-Axapta Object Server.
2. In the Axapta Server Manager window, click Settings, and then click the Database tab.
3. In the Connection category, click Timeout connection after being idle.
4. In the seconds before disconnecting idle cursors box, enter the time limit that you want. You can make this less than 30 seconds. Microsoft Axapta two-tier or three-tier rich client
1. Open the Axapta Configuration Utility.
2. On the Database tab, click Timeout connection after being idle under the Connection category.
3. In the seconds before disconnecting idle cursors box, enter the time limit that you want.If you still receive the same error message, you can try increasing the maximum limit of open cursors. Method 2: Increase the maximum limit of open cursors By default, the maximum number of open cursors that are allowed is 90. By increasing this setting, the amount of memory that is used will also increase. Therefore, performance may decrease. Increasing the open cursors can mask an underlying code problem. This problem is discussed in Method 3. Note You can start with 400 maximum open cursors. Then, decrease this amount if you experience performance problems. AOS three-tier thin client
1. Click Start, click Control Panel, and then click Navision-Axapta Object Server.
2. In the Axapta Server Manager window, click Settings, and then click the Database tab.
3. Under the Settings category, click Max open cursors, and then enter the number that you want.
Microsoft Axapta two-tier or three-tier rich client: 1. Open the Axapta Configuration Utility. 2. On the Database tab, click Max open cursors under the Settings category, and then enter the number that you want.
Method 3: Change the X++ code The following code uses nested select statements to generate four cursors. While select a { select b where b.f = a.f select c where c.f = b.f while select d where d.f=c.f ...this to 1 ...A smart join can reduce these four cursors to one to save lots of server trips.
The following sample code can also generate lots of cursors because the cursor reuse does not work in the loop. for (ordrCnt = 1;ordrCnt <= TPCOrder.O_OL_CNT;ordrCnt++) { TPCOrderLine.OL_I_ID = tI.getRandomItem(); select firstOnly I_Price, I_Name, I_data from TPCItem // This cannot be reused. where TPCItem.I_ID == TPCOrderLine.OL_I_ID; .. }
If the loop is run through 10 times, the program uses 10 cursors for the internal lookup. To resolve this problem, you can change the code to one of the following two examples.
Example 1 for (ordrCnt = 1;ordrCnt <= TPCOrder.O_OL_CNT;ordrCnt++) { TPCOrderLine.OL_I_ID = tI.getRandomItem(); select firstOnly I_Price, I_Name, I_data from TPCItem // This cannot be reused. where TPCItem.I_ID == TPCOrderLine.OL_I_ID; ... myVariable = TPCItem.myField // Force/Enable CursorReUse TPCItem = NULL; } In Example 1, the select statement is run by using the cursor, a variable is set, and the table buffer is set to NULL to release the cursor for reuse. When the table buffer is set in this manner, the cursor is released for reuse after the statement has been run. The advantage of writing the code in this manner is that the cursor is released after all data has been processed. Therefore, the cursor should be empty when the cursor is released. Example 2 By setting TPCItem = NULL, you force the cursor to be reused. for (ordrCnt = 1;ordrCnt <= TPCOrder.O_OL_CNT;ordrCnt++) { TPCOrderLine.OL_I_ID = tI.getRandomItem(); // Force/Enable CursorReUse for next loop TPCItem = NULL; select firstOnly I_Price, I_Name, I_data from TPCItem // This cannot be reused. where TPCItem.I_ID == TPCOrderLine.OL_I_ID; }
In Example 2, the TCPItem table buffer is set to NULL to release the cursor for reuse before the select statement is run. Then, the cursor is consumed by the select statement and is released on the next iteration of the loop. General X++ code tips If the problem is still not resolved after you review the code examples that are mentioned earlier, examine your code and follow these general tips for working with cursors: Use table joins instead of while select statements.
If you have customizations that query tables in Microsoft Axapta by using X++ code, join the tables in the queries to reduce the need for cursors. Balance the TTSCOMMIT statements.
In nested transactions, when a transaction has been committed, the cursor is released. Make sure that there is a balance between the TTSBEGIN statements and the TTSCOMMIT statements. By using the TTSLEVEL statement, you can receive a count of the current TTSBEGIN statements that are issued to match the TTSCOMMIT statements. Clear the data buffer
Add the following line of code after the TTSCOMMIT statement to clear the buffer. DataBuffer = null
-- Bill 'Luther' Thompson Microsoft Online Support Engineer - MBS Dynamics AX Developer Support
