United States

Change

All Microsoft Sites

Microsoft Support

Article ID: 954959 - Last Review: August 27, 2008 - Revision: 1.0

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"

Hotfix Download Available
View and request hotfix downloads

View products that this article applies to.

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.

Back to the top

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.

Back to the top

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 (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 name	File version	File size	Date	Time	Platform
catalogcreate.sql	Not Applicable	1,134,315	02-	13:10	Not Applicable

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/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>

Back to the top