You have a computer that is running Microsoft Commerce Server 2009.
You use the CampaignItemManager.Search method on a Commerce Server site to search the marketing database and to retrieve campaign items.
In the CampaignItemManager.Search method, you use the Contains member that is in the ExplicitComparisonOperator enumeration.
The marketing database contains many campaign items.
In this scenario, search results take a long time to be returned.
Additionally, you experience poor performance when you visit a Commerce Server site that uses the CampaignItemManager.Search method.
Note The Contains member tests whether a property contains the value of a specified argument.
This issue occurs because the Contains member maps to the LIKE operator in the Transact-SQL statement. This behavior causes the poor performance of the SQL query that is generated when the CampaignItemManager.Search method is called.
After you apply the hotfix, a new member is added to the ExplicitComparisonOperator enumeration under the Microsoft.CommerceServer namespace. This new member is the ContainsItem member. The ContainsItem member tests whether a property contains the value of a specified argument. The ContainsItem member maps to the CONTAINS operator in Transact-SQL.
Additionally, a new full-text index is created in the mktg_creative_property_value table of the marketing database. Therefore, when you use the CampaignItemManager.Search method on a Commerce Server site together with a ContainsItem member to construct a search statement, the performance is improved.
The ContainsItem member can be used only on values that are mapped to columns of the full-text index in the database table.
The ContainsItem member supports space that is included in the input string. For example, you create a search clause that resembles the following:
When you use the CampaignItemManager.Search method together with this search clause, the method may return a "Discount Fall" value. However, the method does not return any of the following values:
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 the problem described in this article. 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:
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.
You must have Commerce Server 2009 installed to apply this hotfix.
You do not have to restart the computer after you apply this hotfix. However, you must restart Internet Information Services (IIS) after you apply this hotfix. To restart IIS, at a command prompt, type the following command, and then press ENTER:
Note This option stops and then restarts all IIS services that are running.
Hotfix replacement information
This hotfix does not replace any other hotfixes.
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.
After you extract the hotfix package, you obtain the following files:
To install this hotfix, follow these steps:
Install the CommerceServer2007SP3-KB980075-ENU.exe.
Note This file adds a new member to the ExplicitComparisonOperator enumeration. This new member is the ContainsItem member. Additionally, a full-text index is created in the mktg_creative_property_value table for new marketing databases.
Open the SQL Server Management Studio management tool.
Open the marketing database for each Commerce Server site.
Run the SQL script from the KB980075_patch_mktg_creative_property_value.sql file. This script creates a new full-text index in the mktg_creative_property_value table.
Restart IIS so that the update takes effect. To restart IIS, run the following command:
IMPORTANT To successfully execute the steps, you must have SQL Server Full-Text Search Component installed and the associated Full-Text Service enabled.
After you uninstall this hotfix for Commerce Server 2009, both "Commerce Server 2009" and "Commerce Server 2007" appear on the Start menu. You can safely ignore or delete the "Commerce Server 2007" menu item.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
This hotfix includes two DLL updates. If you want to uninstall this hotfix, you must remove the following two entries by using the Add or Remove Programs item in Control Panel.
Microsoft Commerce Server 2007 Client Tools Service Pack 3 (SP3) - KB980075
Microsoft Commerce Server 2007 Enterprise Edition SP3 - KB980075
For more information about the CampaignItemManager.Search method, visit the following Microsoft TechNet Web site: