SQL error: no SQL cursors remaining

Asked By James Reinheart 200 points N/A Posted on - 06/17/2011

Hi,

I am a developer on Centura 2.1 and the users have issues when they are trying to connect to an Oracle database. The users are getting this error saying:

 

SQL Error:

 

No SQL cursor remaining.

 

Halt application?

 

Actually this application was made for them to open lots of cursors at the same time for performance reasons. The users can open exactly 99 cursors at the same time, then after that this error will occur. I launched the same program again and then I was able to open 99 cursors before getting the error and not being allowed to open another anymore. Is this just a limit per running a program or application or it needs re-engineering the applications?

 

Does anyone know what this is and how to correct this issue? Your assistance would be appreciated.

• Status: Open
• Question Views: 5918
• Answer Count: 1
• Vote Up 20 Vote Down

• Answer Accepted: No
• Question Category: MySQL Server

Answered By glory mathew 0 points N/A #125589

SQL error: no SQL cursors remaining

 

Hi James!

 

You have not specified your network speed, the applications in visual that are slow, manufacturing window, AP entry and the possible matching with all other applications.

 

Network bandwidth and client processing could be the major issue. First tell the visual where the ini file is. If your term server and your DB server is 100 MB then this is very much a potential bottleneck. 

 

Depending on the LAN topology and the component used whether hub or switch, the server should switch approximately a bandwidth of 1 GB. Is your database server multi-tasking? This causes somebody accessing information in a screen to have no impact on performance. Users will experience record locks and get timeout errors.

 

Therefore try to remove all improper coded codes and statements since they cause performance degradation. Set your SQL server to auto-shrink. Configure the disk configuration to many physical arrays and the OS on the server configured to use the extra RAM. 

 

Appropriately set boot.ini according to your version of SQL Server. Set the semaphore values to zero in order to prevent database locking and utilize performance. N is valid and the semaphore settings apply to both SQL Server and Oracle. Set the visual.INI into the following:

 

[Semaphore]

Engineering=N

WorkOrder=N

Inventory=N

Labor=N

Mrp=N

Receivables=N

Payables=N

Ledger=N

PurchaseOrder=N

CustomerOrder=N

Scheduling=N

Costing=N

Estimating=N

NextNumber=Y

EventDetect=N

DemandSupplyLink=N

InterBranchTransfer=N

Revaluation=N

Consolidation=N

 

The Semaphore section is necessary for SQL Server & Oracle sites. Therefore it should be in Visual.ini’s. If it doesn’t work then your error is a 16 bit old limit. However in newer version, the limit is set to 250 cursors. You have a handle leak when you obtain the open cursor. If you don’t, then re-engineer the whole system. The main problem is tracing the source of the error. 

 

These errors can be caused by various client inputs e. g. disconnecting a handle through adding a return false to quit a function. In this case replace all SqlConnect/SqlDisconnect with a wrapper function. e. g. SqlHandler.Connect (hSql). In the wrapper, store functions in places like window name and object name, where connection happens. Let the function write a trace when an error happens. Look which places connected handles and doesn’t disconnect them.

 

E. g. Function Connect Parameter

SQL Handle: p_hSql

Boolean: bOK

Set bOK = SqlConnect( p_hSql)

If NOT bOK

Return FALSE

 

sWindowName =SalGetItemNameX(hWndForm)

sItemName =SalGetItemNameX(hWndItem)

AddToList(p_hSql, sWindowName,sItemName)

Return bOK

 

If it doesn’t work please try checking enterprise manager for extra tables in your database. Or check if the code used is;

 

$options = array( "Scrollable"=>SQLSRV_CURSOR_STATIC);

$SQL = "exec dbs_webgetnextcount 'expense'";

sqlsrv_query($db,$sql,null,$options); 

Then avoid cursor for setting. 

$SQL = "exec dbs_webgetnextcount 'expense'";

sqlsrv_query($db,$sql);

 

By adjusting the selecting statement sqlsrv_num_rows() to give the correct number. Hope this helps.

 

Glory

About glory mathew

Questions
0

Answers
18

Best Answers
7

• Vote Up 0 Vote Down

• Posted on - 06/18/2011
• Question Category: MySQL Server