Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. FIX: SQL Server column limit is reached when you enable or disable the Full Text Searchable option in Commerce Server 2009 or in Commerce Server 2009 R2

FIX: SQL Server column limit is reached when you enable or disable the Full Text Searchable option in Commerce Server 2009 or in Commerce Server 2009 R2

View products that this article applies to.

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.

↑ Back to the top

Cause

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

↑ Back to the top

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=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

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 nameFile versionFile sizeDateTimePlatform
Catalogcreate.sqlNot applicable1,157,53616-Jul-201210:22Not applicable
Commerce Server 2009 R2
File nameFile versionFile sizeDateTimePlatform
Catalogcreate.sqlNot applicable1,164,75109-Jul-201219:33Not applicable

↑ Back to the top

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>

↑ Back to the top

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.

↑ Back to the top

Status

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

↑ Back to the top

Keywords: kb, kbautohotfix, kbqfe, kbhotfixserver, kbfix, kbsurveynew, kbexpertiseadvanced, kbseo

↑ Back to the top

Article Info
Article ID : 2729945
Revision : 1
Created on : 1/7/2017
Published on : 9/7/2012
Exists online : False
Views : 353