Dynamics CRM Knowledge Base Articles search shows unexpected results for specific keywords
The Dynamics CRM Service module provides Articles entity that serves as very useful knowledge repository for users and empowers them to guide customers in most efficient manner. For background, here is the document which describes the Articles usage.
Search functionality of this Articles feature relies on SQL Server Full-Text Search feature and leverages the contains() function as described in the Full-Text Search feature page. We came across an interesting scenario through a customer report as well as internal testing. Searching for specific keywords does not show expected published articles.
Here is screenshot of such example for keyword “use”, there are published articles with keyword "use" in the grid:
However, search results doesn’t show any of those when search for "use" keyword:
Figured SQL query formulated by CRM for above search and executed directly in SQL Management Studio, no records returned! Looking deeper, this turned out expected behavior from SQL standpoint. SQL Server uses Stopwords to prevent a full-text index from becoming bloated. SQL Server has a mechanism that discards commonly occurring strings (Stopwords) that do not help the search. The TechNet article below describes this feature and how to customize or disable usage of Stopwords.
Configure and Manage Stopwords and Stoplists for Full-Text Search -https://technet.microsoft.com/en-us/library/ms142551(v=sql.110).aspx
While there could be multiple options available for administering SQL Stopwords and Stoplists here is one approach that can help to tweak system Stoplist to meet the requirements. The following are step by step instructions along with screenshots for making it easy to follow.
1. Create a new Full-Text Stoplist from context menu shown in below screenshot
2. Choose to create from System Stoplist (can be an empty one too but that’s not recommended as it bloats Full-Text index size). If empty Stoplist is created then remaining steps to delete stopword should be skipped.
3. Right click on new stop list and get to properties window below, delete Stopword you don’t require. Here I removed “use”
4. Attach this custom stop list to Article entity Full-Text index
ALTER FULLTEXT INDEX ON dbo.documentindex SET STOPLIST = MyStopList;
Note: Above command attaches the custom Stoplist to dbo.documentindex that’s tied to knowledge base Articles. Custom Stoplist should be attached to the Full-Text index tied to the entity for which default Stoplist is drawing unexpected results. For example, below is the command to attach custom Stoplist to Account entity Full-Text Index:
ALTER FULLTEXT INDEX ON dbo.Accountbase SET STOPLIST = MyStopList;
5. Rebuild CRMFullTextCatalog from context menu option
6. You can see the difference in output of below queries showing ‘use’ keyword present in system Stoplist but not in our custom Stoplist.
select * from sys.fulltext_system_stopwords
where language_id = 1033 and stopword='use'
select * from sys.fulltext_stopwords MyStopList
where language_id = 1033 and stopword='use'
Now searching by “use” keyword in CRM should yield expected results.
At the time of writing this custom Stoplist is possible only with CRM On-Premise environment but not with CRM Online.
Hope this helps! Cheers!