Configuring SQL search for attachment files

You can use the SQL search to find results in the content of page attachment files uploaded into the database.

The SQL attachment search uses the standard Microsoft SQL Server full-text search engine. The search is available for all supported versions of SQL Server:

• SQL Server 2008
• SQL Server 2008 R2
• SQL Server 2012

Use one of the following guides to configure your Kentico database for SQL search of attachment files:

• Configuring SQL search for attachment files
• Configuring SQL search for attachment files

Manually configuring full-text search on MSSQL Server

Use the following steps to configure your Kentico database for full-text search in file attachments:

1. Start Microsoft SQL Server Management Studio.

   • If you cannot use SQL Server Management Studio on your database server, you can configure the full-text search through a script instead.

2. Locate your Kentico database.

3. Unfold the Storage sub-folder, right-click Full Text Catalogs and click New Full-Text Catalog.

   

4. Type a Full-text catalog name and click OK.
   

5. Right-click the new full-text catalog and choose Properties.

6. In the Full-Text Catalog Properties dialog, click the Tables/Views tab.

7. Assign the CMS_Attachment table to the catalog.

   1. Check the box next to the AttachmentBinary column
   2. Set the Language for Word Breaker to English or another value
   3. Set the Data Type Column to AttachmentExtension

   

8. Click OK.

You can now combine the SQL attachment search with smart search results or enable attachments for the SQL search.

Enabling full-text search on MSSQL Server - Script

If you cannot use SQL Server Management Studio to configure the full-text search, run the following script against your Kentico database:

-- Allows IFilter library loading
exec sp_fulltext_service 'verify_signature', 0
exec sp_fulltext_service 'load_os_resources', 1

-- Creates the Full Text Catalog 
exec sp_fulltext_catalog 'KenticoCMSCatalog','create'

-- Adds the CMS_Attachment table to the catalog
exec sp_fulltext_table 'CMS_Attachment','create','KenticoCMSCatalog','PK_CMS_Attachment'

-- Sets the data column of the CMS_Attachment table in the catalog
exec sp_fulltext_column 'CMS_Attachment','AttachmentBinary','add',NULL,'AttachmentExtension'

-- Populates the catalog
exec sp_fulltext_table 'CMS_Attachment','start_full'

You can now combine the SQL attachment search with smart search results or enable attachments for the SQL search.

Combining the SQL attachment search with the Smart search

Once you have the SQL server set up, you can configure your smart search result web parts to run SQL searches through the content of page attachments.

Enable SQL attachment searching through the properties of the Smart search dialog with results or Smart search results web part:

When users perform a search and the system finds a match in the attachment of a page, the given page is added to the search results. The attachment results are always interlaced with the other results provided by the specified smart search indexes. This behavior is by design and cannot be modified.

The attachment search is performed by the SQL server, so it is not affected by the settings and restrictions of the used search indexes. To limit the attachment search scope, enter an appropriate value into the WHERE condition property of the used web part. For example, if you have a search results web part using a page index that is limited to the /News/% section of your website, you need to add the following WHERE condition to ensure that the attachment search is also restricted to these pages: NodeAliasPath LIKE ‘/News/%’

Enabling attachment search for the SQL search

Perform the following steps if you wish to search attachments using the SQL search:

1. Open the Page types application.

2. Edit the Root page type.

3. Select the Queries tab.

4. Edit the searchattachments query and uncomment the following part of the code:

   Copy

   
   
   
    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 (##WHERE##) AND
    (([AttachmentName] Like N'%'+ @Expression + N'%') OR ([AttachmentTitle] Like N'%'+ @Expression + N'%') OR ([AttachmentDescription] Like N'%'+ @Expression + N'%')) OR (FREETEXT(AttachmentBinary, @expression)) 
    ORDER BY ##ORDERBY##
   
   
    

The SQL search automatically includes the results from the attachment search.