AX Error in Envent Viewer: Upper limit for open cursors has been exceeded

AX Error in Envent Viewer: Upper limit for open cursors has been exceeded

 

You receive an "Upper limit for open cursors has been exceeded" error message in Microsoft Axapta 3.0

View products that this article applies to.

 

 

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;900739

 Microsoft Business Solutions-Axapta 3.0 is now part of Microsoft Dynamics AX 3.0. All references to Microsoft Business Solutions-Axapta and Microsoft Axapta pertain to Microsoft Dynamics AX.

On This Page

	SYMPTOMS
	CAUSE
	RESOLUTION
		Method 1: Reduce the time that idle cursors remain open
			AOS three-tier thin client
			Microsoft Axapta two-tier or three-tier rich client
		Method 2: Increase the maximum limit of open cursors
			AOS three-tier thin client
			Microsoft Axapta two-tier or three-tier rich client
		Method 3: Change the X++ code
			Example 1
			Example 2
			General X++ code tips
	APPLIES TO

SYMPTOMS

When you are working in Microsoft Business Solutions - Axapta 3.0, you may receive the following error message at any time:

Upper limit for open cursors has been exceeded (90) - use parameter openCursors or change the X++ code.

Back to the top

CAUSE

This problem may occur because Microsoft Axapta is using more SQL cursors than the default setting of 90. Several factors can cause this condition. These factors may include custom code, uncommitted transactions, or idle connections.

Back to the top

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.

Back to the top

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.

Back to the top

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.

 

Back to the top

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

posted on 2009-06-29 13:22 Jacky Xu 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/JackyXu1981/archive/2009/06/29/1513133.html