Prerequisites
If you are using Microsoft SQL Server 2000, you need to use Microsoft SQL Server 2000 Standard or Enterprise Edition - Full-text search is not supported with MSDE or in the Developer Edition. Full-text search must be installed on your SQL Server.
How to set up full-text search
1. Run Microsoft SQL Server Enterprise Manager console.
2. Locate your Kentico CMS database, right-click it and choose New -> New Full-Text Catalog:
3. New Full-Text Catalog Properties dialog appears. Enter the name of the new catalog - e.g. KenticoCMS. Click OK to save.
4. Right-click the CMS_Attachment table in the Kentico CMS database and choose Full-Text Index Table -> Define Full-Text Indexing on a Table...
5. The SQL Server Full-Text Indexing Wizard opens. Click Next.
6. Choose the primary key PK_CMS_Attachment_... in the Unique index field.
7. In the next step, check the AttachmentBinary column, choose the appropriate language that will be used for indexing and choose AttachmentExtension column as Document type column. Click Next.
Please note: if you choose English language, French (Italian, etc.) documents will be indexed too, but without native support (noise words, stop words, etc.) which can lead to inappropriate search results in some cases.
8. In the next step, choose the catalog you created in step 3. Click Next.
9. Now you can define a new indexing schedule. This is important, since all documents must be indexed before they can be searched. The index catalog must be updated after new files are added. You can do that manually or (preferably) you can schedule automatic indexing (incremental indexing is usually sufficient).
10. Click Next and click Finish. Now you have created a new catalog. However, it's not indexed yet. You need to start the first full indexing manually. Right-click the CMS_Attachment table, choose Full-Text Index Table -> Start Full Population.
11. Wait a few seconds and then refresh the Full-Text Catalogs listing. Check that the Last Population Date has been updated.
12. Sign in as administrator and go to Site Manager -> Development -> Document types -> Root and edit the searchattachments query. Uncomment the following query:
SELECT view_cms_tree_joined.*, view_cms_tree_joined.NodeName AS SearchResultName FROM cms_attachment INNER JOIN view_cms_tree_joined on view_cms_tree_joined.DocumentID = cms_attachment.AttachmentDocumentID WHERE NodeAliasPath LIKE @NodeAliasPath AND ([AttachmentName] Like N'%'+ @Expression + N'%' OR FREETEXT(attachmentbinary, @expression)) AND @SiteName = SiteName AND (@DocumentCulture = DocumentCulture OR @DefaultCulture = DocumentCulture ) |