Query result set too large, (124,87). Result of (SQL Fetch) is over the maximum result size specified for the application server.
One of the common errors encountered while running a PSQuery is:
“Query result set too large, (124,87). Result of (SQL Fetch) is over the maximum result size specified for the application server.“
As the error message suggests, this error has been caused due to huge result size, which the application server is unable to render because of the configuration specified while creating the particular instance. It is common and a good practice to allow only queries that are expected to run in a short time and those with relatively small sets of output must be run online. Queries, which take long to run or result in a huge output are best scheduled to run via process scheduler for performance reasons and to avoid application server performance degradation issues.
Error message also suggests the resolution – increase the maximum size for the application server.
Go to your Application server configuration file, psappsrv.cfg and you will have to look out for “Max Fetch Size” (mentioned in PSQRYSRV and/or PSAPPSRV services depending on the configuration). Max Fetch Size should be reset in PSQRYSRV and/or PSAPPSRV depending on the configuration under below conditions:.
1) Query runs in PSQRYSRV service:
a. When the application server is configured to boot the PSQRYSRV dedicated Query services and
b. All previously saved queries executed via Query Manger or Query Viewer will be handled by PSQRYSRV service.
2) Query runs in PSAPPSRV service:
a. If the application server is not configured to boot PSQRYSRV service as specified above then, the PSAPPSRV service is used.
b. Queries executed from the “Run” tab of Query Manager (in Edit mode) will only be handled by the PSAPPSRV service irrespective of both the settings specified positive for PSQRYSRV service. PSQRYSRV services configuration is recommended in order to eliminate the load of running saved queries off from the PSAPPSRV services which is also used to manage user sessions and transactions.
Increase the Max Fetch Size for PSQRYSRV and/or PSAPPSRV and run the query again to resolve the issue.