Symptoms

Consider the following scenario:

  • You have a Product Catalog that uses approximately 1024 property definitions in Microsoft Commerce Server 2009 or in Microsoft Commerce Server 2009 R2.

  • You enable or disable the Full Text Searchable (FTS) options of the properties multiple times.

In this scenario, the number of columns in the tables or views increases continuously. When the number of columns reaches the 1024-column limit in Microsoft SQL Server, you can no longer add property definitions to the Product Catalog.Note This issue may also occur when the Product Catalog uses a lower number of property definitions. The design of your Catalog Schema affects the overall number of columns in the database tables and views. If a property definition is marked as Full Text Searchable, additional columns are added.

Cause

The issue occurs because the Language Neutral view is not cleaned up correctly.

Resolution

Hotfix information

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. 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, submit a request to 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 website:

http://support.microsoft.com/contactus/?ws=supportNote 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

To apply this hotfix, you must have Commerce Server 2009 or Commerce Server 2009 R2 installed.

Restart requirement

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

Installation instructions

This hotfix package contains the following files:For Commerce Server 2009

  • CommerceServer2007SP3-KB2729945-ENU.exe

  • kb2729945.sql

For Commerce Server 2009 R2

  • CS2009R2RTM-KB2729945-x86.exe

  • kb2729945.sql

To prevent this issue from occurring in new catalog databases, run the .exe file to update the Catalogcreate.sql file for Commerce Server 2009 or for Commerce Server 2009 R2. Additionally, run the kb2729945.sql file against the existing catalog databases.

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.Commerce Server 2009

File name

File version

File size

Date

Time

Platform

Catalogcreate.sql

Not applicable

1,157,536

16-Jul-2012

10:22

Not applicable

Commerce Server 2009 R2

File name

File version

File size

Date

Time

Platform

Catalogcreate.sql

Not applicable

1,164,751

09-Jul-2012

19:33

Not applicable

Workaround

To work around this issue, follow these steps:

  1. In Commerce Server Catalog Manager, click Rebuild All on the Tools menu.

  2. In SQL Server, run the following statement:

    sp_refreshview <View Name>

More Information

The following columns are created in the tables and views of each FTS text property in a virtual catalog.Note Non-FTS properties are not present in these tables and views. Tables

  • [CatalogName]_CatalogProducts

  • [PropertyName] nvarchar(4000)

  • [PropertyName]* bit

Views

  • [CatalogName]_VC_da-DK

  • [PropertyName] nvarchar(4000)

  • [PropertyName]* bit

  • [CatalogName]_VC_LNG_NEUTRAL

  • [PropertyName] nvarchar(4000)

  • [PropertyName]* bit

If you change the property, the property is no longer an FTS text property in the virtual catalog. Additionally, the columns are not removed from the following tables and views: Tables

  • [CatalogName]_CatalogProducts

  • [PropertyName] nvarchar(4000)

Views

  • [CatalogName]_VC_da-DK

  • [PropertyName] nvarchar(4000)

  • [CatalogName]_VC_LNG_NEUTRAL

  • [PropertyName] nvarchar(4000)

  • [PropertyName]* bit

Notes

  • The "[PropertyName]*" column is removed from one table and from one view but remains in the second view.

  • The "[PropertyName]" remains in all tables and in all views.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.