ThinkHighlight for SQL Server Online Help

Installation and Setup

Since ThinkHighlight is a CLR assembly, the first thing you will want to do is make sure CLR is enabled on the desired SQL Server instance (see Enabling CLR Integration). The manager application you are about to install and use will tell you whether CLR is enabled or not.

After installing the MSI package, launch the manager application. Enter server name and authentication to connect to the instance. In order to install the assembly, select the designated database from the databases select box.

From the Setup menu select "Install on current database", this will first install required security prerequisites on the master database: alter trustworthy to ON; create the assembly; create an asymmetric key from the assembly; drop the assembly; create a login from the asymmetric key; add the login to the serveradmin fixed server role; grant unsafe assembly to the login; and finally alter the trustworthy back to OFF. The next step will create the assembly and contained functions on the selected database.

In order to use the assembly, you will first have to activate a license (per database instance). From the Setup menu select License Activation, fill-in the form and select the license type.

Hello World

DECLARE @query nvarchar(50) = '"Microsoft"'
DECLARE @context varbinary(8000) = dbo.HitHighlightContext('doc', 'content', @query, -1)
SELECT TOP 10 doc_id, dbo.HitHighlight(@context, 'top-fragment', 200, doc_id)
FROM doc
WHERE CONTAINS(content, @query)

HitHighlightContext function

The HitHighlightContext function prepares a binary block representing FTS index scheme and query data to be used in the actual highlighting phase.

Syntax

HitHighlightContext(table_name, column_name, querystring, language_id)

Arguments

  • table_name nvarchar(128) - FTS indexed table or indexed-view name.
  • column_name nvarchar(128) - FTS indexed column name.
  • querystring nvarchar(max) - the user query used in the WHERE clause.
  • language_id int - querystring language LCID (-1 for the FTS column language).

Return Types

The function returns a varbinary(max) holding underlying FTS scheme and query data.

HitHighlight function

The HitHighlight function generates the HTML snippet per document.

Syntax

HitHighlight(context, strategy, max_length, doc_id)

Arguments

  • context varbinary(max) - the prepared context for the current highlighting.
  • strategy nvarchar(20) - 'baseline', 'complete' or 'top-fragment'.
  • max_length int - maximum snippet length in characters.
  • doc_id sql_variant - FTS unique-key value identifying the row.

Return Types

The function returns an nvarchar(max) HTML snippet.

Remarks

The HitHighlight function supports both text and binary columns, for binary columns the matching IFilter will be used in conjunction with the index type column. IFilters not implementing the IPersistStream interface are not supported.

HitHighlightEx function

Same as the HitHighlight function except it returns xml, which for binary columns also includes available document properties.

DocumentText function

The DocumentText function returns text extracted from a specific binary document.

Syntax

  • context varbinary(max) - the prepared context for the current operation.
  • doc_id sql_variant - FTS unique-key value identifing the row.

Return Types

The function returns a table of zero or one row: table (text nvarchar(max)).

Remarks

For memory allocation reasons, care should be taken when using on very large documents.

DocumentProperties function

The DocumentProperties function returns available document properties for a specific binary document.

Syntax

  • context varbinary(max) - the prepared context for the current operation.
  • doc_id sql_variant - FTS unique-key value identifying the row.

Return Types

The function returns a table of zero or more rows for properties in the specified document: table (propset uniqueidentifier, propid int, name nvarchar(64), value nvarchar(max)) ORDER (propset, propid).

Troubleshooting

If you get the error "The locale identifier (LCID) 8192 is not supported by SQL Server." when calling one of the above functions, there might be an issue with the LCID of the user account running the SQL Server instance. You can try the following steps to resolve:

  • Launch SQL Server Configuration Manager, check which user account is running the instance service by examining the "Log On As" column.
  • Go to RegEdit -> HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Windows NT -> CurrentVersion -> ProfileList, you will see a list of keys representing SID strings.
  • Iterate the SID list and find the one where the value "ProfileImagePath" contains the name of the user account.
  • Go to HKEY_USERS -> [SID of found account] -> Control Panel -> International, and change "Locale" and "LocaleName" to the desired region (e.g. Locale 00000409, LocaleName en-US).
  • Restart the instance service from the configuration manager.

Note: editing the registry can damage your system, you must take proper precautions.