ADS 12: error 7216 SQL Statement Limit Exceeded

Symptom

Error 7216 SQL Statement Limit Exceeded

Environment

SAP Advantage Database Server 12.x

Cause

The configured SQL statement limit was exceeded. Beginning with v12 of Advantage, the default number of SQL statements that a connection can obtain at a given time is 50.

Resolution

• It is possible to increase or disable the limit for a given connection by executing the SQL statement sp_SetStatementLimit. Note that this must be executed on an existing statement handle if the error has already been encountered.
• The limit for the server itself can be changed by setting the SQL_STATEMENT_LIMIT configuration parameter. This controls the maximum number of SQL statement that a single connection can keep open at one time (by default its value is set to 50). To completely remove the limitation for all connections on the server, this value can be set to zero. To modify the limit for a specific connection an application calls sp_SetStatementLimit.
• On Linux, a configuration file, ads.conf, is installed with Advantage daemon to assist in defining configuration parameters. The parameters can be changed by editing the file ads.conf with a standard text editor. The configuration file is used when loading the Advantage daemon. If the Advantage daemon is already running, you must kill and then restart the daemon on the file server to use the newly changed settings. By default, the configuration file is stored in /usr/local/advantage/ads.conf
• On Windows, browse to HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesAdvantageConfiguration, add a DWORD key named SQL_STATEMENT_LIMIT and set it to the required value (e.g. 0 for disabling). After changing a configuration parameter, you need to restart ADS server.