Header Ads

  • Recent Posts

    Query result set too large, (124,87). Result of (SQL Fetch) is over the maximum result size specified for the application server.

    When running PS-Query, sometimes the following error message appears:
    "Query result set too large, (124,87). Result of (SQL Fetch) is over the maximum result size specified for the application server."

    When running few queries, specifically within large date ranges or in conditions where huge volume of data is expected to be returned this error message might occur.

    Cause and Solution:
    Based on the text of error message, as one would expect, the solution is to increase the maximum size for the application server.

    So where in the application server one could specify this size for fetching maximum rows?

    Application server reconfiguration is required to resolve this issue. The “Max Fetch Size” (for PSQRYSRV and/or PSAPPSRV services depending on the configuration) setting of the application server process currently handling the query which encountered this error must be increased to a higher value. However, as a general practice only queries that are expected to run in a short time and those with relatively small sets of output must be run online. Queries that take longer duration and/or returning huge volume of data must be best scheduled for performance reasons and to avoid application server performance degradation issues.

    I specified that the 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.

    5 comments:

    1. Thank you.

      This wiki provided the solution to our issue.

      Please keep up the great work.
      I refer to your blog often for help with unusual issues.

      ReplyDelete
    2. In our case the query runs fine and returns 85,000+ rows. We only get this message when downloading the results to Excel and this message is in the resulting spreadsheet which has 65,000 rows.

      ReplyDelete
      Replies
      1. Schedule the Query and get the result

        Delete
    3. thanks, the detail provided here was very helpful.

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad