Knowledge Search is not retrieving all documents based on keywords

Products

• CA Service Management - Service Desk Manager

Issue/Introduction

Issue:

Few keyword searches are not retrieving all relevant knowledge documents.

Steps:

• Create KBs with words like Dockingstation, Servicekonto, Digtasoft, etc.
• Run pdm_k_reindex and search with the word "Dockingstation", no documents or only 1 document will be retrieved though the search displays the actual count of all documents created.

Environment

Release : 17.3

Cause

Few documents are not getting indexed due to some special characters. It was identified due to the database collation setting, especially in the processing/parsing/sorting of the keywords/tokens. 

Resolution

• We need to update the collation setting on the three columns below as they are responsible for the KD search.

            1. Table: ebr_fulltext, Attributes (full_word, full_word_reverse, short_word).

            2. Table: ebr_fulltext_adm, Attributes (full_word, full_word_reverse, short_word).

• Previously, these columns had the collation 'SQL_Latin1_General_CP1_CS_AS'. With this new collation (SQL_Latin1_General_CP437_BIN), we can distinguish between various special characters as Code Page 437 (CP437) is an extended ASCII character encoding that includes special characters and symbols. With this change, we can search better for documents with many special characters. 
• The search is more clean and refined with this collation. Also, we observed all the other searches also working fine when the search string has all the valid characters which are defined in the parse settings.
• Make sure that all your KB documents have valid characters in them because these are the settings used by the system while indexing a document.
• Please find the attached SQL script to change the column collation of the DB Table columns.  You can execute the SQL script by loading it in the SQL server management studio. Or work with the DBA to perform these actions. SE recommends working with the DBA to perform these actions.
• After executing the SQL script, make sure that the following collation change is observed in both ‘ebr_fulltext’ and ‘ebr_fulltext_adm’ tables. Below is the screenshot for your reference. 
• Execute the command "sp_help ebr_fulltext" to check the collation settings of each column.
• After changing the collation, we need to run pdm_k_reindex.
• We recommend to take a MDB backup before performing any operations on DB table and also try it on your test environment before implementing in your production.