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:
-
In Commerce Server Catalog Manager, click Rebuild All on the Tools menu.
-
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.
