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