Web Report Server Search Fails with 40 or more in the Batch Report Server User Access Table

From Support

If you have a question or seek clarification, please call Technical Support.

Problem:

When a WinSpool Web Report Server user has more than 40 entries in the "User Access" table for the Batch Report Server, the Web Report Server search fails.

Note: This problem is not a limit to the number of reports in the system. It's a limit placed upon the number of filter criteria that each user can have set up for them.

Solution:

We have identified this to be a limitation in the Microsoft Access Jet database search engine. Currently the following resolutions are available:

1.) Limit the number of specific report filters for a single user to 40 entries or less.

2.) Set up all reports relevent to a single user or user groups in individual or group output queues. This would only require a single User Access entry for each user. The entry or entries would point to the selected output queue(s) the user has access to.

The Microsoft knowledgebase article listed below provides more technical detail:

MS Knowledgebase

NF: Limit on AND's in SQL Select Statement

Article ID: Q92690

There is an undocumented limit on the number of ANDs you can use in a SQL Select statement. The limit is 40 for the Where clause and 40 for the Having clause. Microsoft Access version 1.x returns the following error message when you exceed this limit:

  Out of Memory

Microsoft Access version 2.0 returns the following error message when you exceed this limit:

  Query is too complex

PRB: "Out of Memory" or "Query Too Complex" Running Report

Article ID: Q103429

Symptoms

You receive an "Out of Memory" or "Query too Complex" error message when you run your report.

Cause

Reports create temporary queries for each section of the report, including the report header, page header, group header, detail section, group footer, page footer, and report footer.

All of the temporary queries for each report are combined into a segmented virtual table (SVT). The final output must be compiled within a 64K segment limit. When this limit is reached, you may see either the "Query too Complex," or the "Out of Memory" error message.

Similar 64k limits are used to compile and store all of the expressions from page to page when the report is being processed or to store the unbound controls or label information. If any of these segments exceed the limit, controls on the report may start displaying the "#Name?" error message.

Resolution

The error messages stated above are related to the complexity of the underlying query or the report itself. The following items suggest several ways to reduce the complexity of your queries or report:

- Shorten table names, column names, and control names. Reducing a
  30-character name to the minimum length may help.

- Reduce the number of text fields on the report.

- Avoid extra overhead by removing any fields in the underlying query
  that are not used in the final output for the report.

- Reduce expressions in underlying queries. Reducing space used for
  expressions in the select list helps to avoid these errors. If

  possible, place the expressions directly in the report.

- Move complex expressions to a user-defined function that does all 
  the evaluation.

- Avoid stacked query objects, such as situations in which Query1 is
  used to pull data from Table2, where Query2 filters the data.
  Pulling information together in one query is preferable to having
  multiple queries each doing portions of the task.

- Avoid basing main reports and their subreports on the same queries.
  Look for stacked query objects and for tables that are unnecessary
  to the subset.

- Use subreports to break up a complex report into several less
  complicated reports.

- If the report is based on a query, build a temporary table from the
  query to base the report on instead of the query. Create a Make 
  Table query that includes all the fields from the original query to

  build the temporary table.

- Shorten SQL statements by using the alias property for the field 
  list.  From the View menu bar, select Properties. Click on any 
  field list and change the alias property to something shorter to 
  shorten the SQL statement.