In Service Manager, SQL Server Full-Text Search is used to search knowledge article stored in DB. The data is either in .rtf format or .txt format. In this blog post, we only focus on .RTF iFilter issue. For English knowledge article, we are currently using .RTF iFilter provided out of the box by SQL Server. Due to encoding issues, this iFilter may prevent you searching knowledge article which are not written in English.
The current workaround for this issue is to use window .RTF iFilter for non-English knowledge article.
On the computer hosting Service Manager database, you need the following steps:
1. Import following registry files. This is only for default sql instance and rtffilt.dll is default installed on system32 directory, You need to replace them with correct value. Copy and paste the following content and save it as “rft.reg”. Notes: replace correct sql instance id for non-default instance and rtffilt.dll is installed under system32 directory.
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters\.rtf]
@="{e2403e98-663b-4df6-b234-687789db8560}"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\CLSID\{e2403e98-663b-4df6-b234-687789db8560}]
@="c:\\windows\\system32\\rtffilt.dll"
"ThreadingModel"="Both"
2. Double click “rft.reg” and add the keys and values to registry.
3. Open SQL Server Management Studio, In the Object Explorer pane, expand Databases and then click ServiceManager . Click New Query , copy and paste the following commands into center pane, and then click Execute
exec sp_fulltext_service 'verify_signature', 0
go
exec sp_fulltext_service 'update_languages'
go
exec sp_fulltext_service 'restart_all_fdhosts'
go
See Messages “Command(s) completed successfully.”
3. To verify if .RTF iFilter has been replaced correctly, click New Query, copy and paste the following command in to center pane and then click Execute
select * from sys.fulltext_document_types where document_type = '.rtf'
You should see following result:
Notes: I have windows installed on d: drive. In your case, it could be c:\windows\system32\rtffilt.dll.
4. Create a non-English knowledge article in Service Manager console. In this example, I am using Simplified Chinese.
Notes: system language and display language should be same as the language you are using in knowledge article.
5. Search with some words from Internal Content in knowledge search window, you will see a search result.
Optionally, you can verify if new .RTF iFilter works as expected on Service Manager database server. After a non-English knowledge article is created, you can go to the computer hosting the Service Manager database, open SQL Server Management Studio, In the Object Explorer pane, expand Databases and then click ServiceManager . Click New Query , execute the following sql query
select DB_ID()
You will get db id. for example, the database ID is 7
And then execute another sql query with actual database id. For example, 7
select * from sys.dm_fts_index_keywords(7, object_id('FTSearchableBlobStorage'))
In the Results tab, you can verify if the words have been broken correctly.
If you have previously created knowledge article or you want to re-index the non-changed knowledge article, you can update all the document extension by set it back to .rtf. This will update all documents and trigger re-index if you have auto change tracking.
update dbo.FTSearchableBlobStorage set extension = extension where extension = 'RTF'