FIX: Error message when you try to add more than 84 base catalogs into a virtual catalog in Commerce Server 2007: "Exceeded the Max limit of 84 base catalogs"

Article translations Article translations
Article ID: 954959 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

When you try to add more than 84 base catalogs into a virtual catalog in Microsoft Commerce Server 2007, you may receive the following error message:
Exceeded the Max limit of 84 base catalogs.

CAUSE

This problem occurs because of a limitation in the number of base catalogs in a virtual catalog. Lots of base catalogs in a virtual catalog may potentially affect site performance. Therefore, the limitation was set to avoid this behavior.

RESOLUTION

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support
Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

You must have Commerce Server 2007 installed to apply this hotfix.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

This hotfix does not replace any other hotfixes.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
catalogcreate.sqlNot Applicable1,134,31502-13:10Not Applicable

MORE INFORMATION

Factors that may affect site performance and cause SQL Server errors

If you add too many base catalogs into a virtual catalog, you may affect site performance in the following areas:
  • Web site browsing
  • Virtual catalog rebuilding
  • Staging service
Therefore, you must balance overall site performance and business needs.

The following factors may affect site performance or cause SQL Server errors to occur
  • The number of base catalogs that are included in a virtual catalog
  • The number of base catalog categories and products that are included in the virtual catalog
  • The numbers of categories and the hierarchy structure of these categories and products
  • The numbers of the properties for these categories and products.
  • The numbers of the relationships among categories and products
  • Virtual catalog materialization

    Note Sometimes, virtual catalog materialization is the only option to avoid SQL Server limitations.
You may have to adjust these factors accordingly when site performance decreases.

Issues that may occur

When you include lots of base catalogs in a virtual catalog, you may experience one of the following issues.

You receive error message 4414

If you run SQL Server 2000 Service Pack 3 (SP3) or an earlier service pack of SQL Server 2000, you may receive the following error message:
Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (256) was exceeded.
If you run SQL Server 2000 Service Pack 4 (SP4), you may receive the following error message:
Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
This issue occurs because of a limitation in SQL Server 2000 that only enables a maximum of 256 tables joining for a database view. A Commerce Server virtual catalog is implemented as the database view before the virtual catalog is materialized.

To resolve this issue, use one of the following methods:
  • Materialize the virtual catalog.
  • Migrate to SQL Server 2005, and then install the latest service pack for SQL Server 2005.
  • Adjust the factors that are listed in the "Factors that may affect site performance and cause SQL Server errors" section.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
828269 FIX: A Transact-SQL query that uses views may fail unexpectedly in SQL Server 2000 Service Pack 3

You receive error message 511 or error message 8618

You may receive one of the following error messages in Catalog Manager and site browsing:
Error message 1
Msg 511, Level 16, State 1, Line 1

Cannot create a row of size 8081 which is greater than the allowable maximum of 8060.
Error message 2
Msg 8618, Level 16, State 2, Line 1

Could not get the root products for the catalog. ---> System.Data.SqlClient.SqlException: The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.
To resolve this issue, use one of the following methods:
  • Decrease the number of custom properties that are used for categories and for products that are included into the virtual catalog from base catalogs.
  • Materialize the virtual catalog.
  • Adjust the factors that are listed in the "Factors that may affect site performance and cause SQL Server errors" section.

A time-out occurs when you try to access the virtual catalog

When you try to access the virtual catalog and browse the Web site, a time-out may occur.

This issue occurs because the virtual catalog may include categories and products from lots of base catalogs. When this behavior occurs, access to the virtual catalog may be slow, and you exceed the time-out period that is set in the Web site.

To resolve this issue, increase the time-out value in the Web.config file of the Web site. For example, modify the following code example.
<system.web>
  <httpRuntime executionTimeout="190" maxRequestLength="4096" useFullyQualifiedRedirectUrl="false" 	minFreeThreads="8" minLocalRequestFreeThreads="4"	appRequestQueueLimit="100" />
</system.web>

Properties

Article ID: 954959 - Last Review: August 27, 2008 - Revision: 1.0
APPLIES TO
  • Microsoft Commerce Server 2007 Developer Edition
  • Microsoft Commerce Server 2007 Enterprise Edition
  • Microsoft Commerce Server 2007 Standard Edition
  • Microsoft Commerce Server 2007 Service Pack 1
Keywords: 
kbautohotfix kbexpertiseadvanced kbhotfixserver kbqfe KB954959

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com