Full-Text Search

It is possible to use quick search using Full-Text Search in SD Console and SD WebApp. You need to have the Full-Text and Semantics Extractions for Search, an optional part of the SQL Database Engine, installed to use this function. This component can be added during initial installation of the MS SQL Server or can be installed later using the SQL Server installation package for any edition.

Full-Text Search Enabled Check

Installation and settings can be checked using the SQL command:

IF (1 != ISNULL(fulltextserviceproperty('IsFulltextInstalled'),0)) PRINT N'FTS not installed.' ELSE IF (1 != ISNULL((SELECT 1 FROM [dbo].[sysfulltextcatalogs] WHERE name = 'ftCatalogAlvao'),0)) PRINT N'FTS not enabled.' ELSE PRINT N'FTS is installed and enabled.'

If "FTS not installed" is returned, you need to install Full-Text and Semantics Extractions for Search and then set it up.

If "FTS not enabled" is returned, you need to set it up.

If "FTS installed and enabled" is returned, you do not need to set anything up.

Full-Text Search Enabling

If Full-Text Search is not enabled by using the ALVAO database, you need to run the SQL script to enable it. This script sets the database to use database language for word inflection during search. If you want to change this language, in the following script:

DECLARE @languageLCID int; SELECT TOP 1 @languageLCID = lcid from master..syslanguages where langid = @@DEFAULT_LANGID; DECLARE @CreateFulltext nvarchar(MAX); SET @CreateFulltext = N'CREATE FULLTEXT CATALOG ftCatalogAlvao WITH ACCENT_SENSITIVITY = OFF AS DEFAULT; CREATE FULLTEXT INDEX ON tHdTicket (sHdTicket LANGUAGE '+CAST(@languageLCID AS nvarchar)+N', mHdTicketNotice LANGUAGE '+CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tHdTicket_iHdTicketId WITH STOPLIST OFF; CREATE FULLTEXT INDEX ON tAct (sAct LANGUAGE '+CAST(@languageLCID AS nvarchar)+N', mActNotice LANGUAGE '+CAST(@languageLCID AS nvarchar)+N', sActFrom LANGUAGE ' +CAST(@languageLCID AS nvarchar)+N', sActTo LANGUAGE '+CAST(@languageLCID AS nvarchar)+N', sActToEmail LANGUAGE '+CAST(@languageLCID AS nvarchar)+N', sActFromEmail LANGUAGE ' +CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tAct_iActId WITH STOPLIST OFF; CREATE FULLTEXT INDEX ON tDocument (sDocument LANGUAGE '+CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tDocument_iDocumentId WITH STOPLIST OFF; CREATE FULLTEXT INDEX ON tHdTicketApproval (ApprovalNotice LANGUAGE '+CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tHdTicketApproval WITH STOPLIST OFF; CREATE FULLTEXT INDEX ON tArticle (HtmlArticleBinary TYPE COLUMN HtmlArticleBinaryExt, sArticle, mArticleAnnotation, mArticle LANGUAGE '+CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tArticle_iArticleId; CREATE FULLTEXT INDEX ON tHdTicketApprovalItem (mHdTicketApprovalItemNotes LANGUAGE '+CAST(@languageLCID AS nvarchar)+N') KEY INDEX PK_tHdTicketApprovalItem WITH STOPLIST OFF;'; EXECUTE sp_executesql @CreateFulltext;

replace the second line with the following:

SET @languageLCID = 1029

Replace the number 1029 with the number of national environment which language you want to use.

Then run the modified script. If you want to use the database language, do not modify anything and use the original script.

If you want to change Full-Text Search language after its enabling, you need first to disable Full-Text Search and then enable it again for another language.

Full-Text Search Disabling

To disable Full-Text Search catalog execute the following SQL script:

DROP FULLTEXT INDEX ON tHdTicket; DROP FULLTEXT INDEX ON tAct; DROP FULLTEXT INDEX ON tDocument; IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] WHERE t.name= 'tArticle') DROP FULLTEXT INDEX ON tArticle; IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] WHERE t.name= 'tHdTicketApproval') DROP FULLTEXT INDEX ON tHdTicketApproval; IF EXISTS (SELECT * FROM sys.tables t INNER JOIN sys.fulltext_indexes fi ON t.[object_id] = fi.[object_id] WHERE t.name= 'tHdTicketApprovalItem') DROP FULLTEXT INDEX ON tHdTicketApprovalItem; DROP FULLTEXT CATALOG ftCatalogAlvao;

These commands remove the catalog and all the Full-Text Search indexes. After removal you can no longer use quick search.

Did not find what you were looking for? Ask our technical support team.