Troubleshooting Errors When Enabling / Disabling Document Content Search

This solution outlines how to deal with errors with document content search.

Action:
Enable Document Search

Error:
Warning: Table or indexed view 'RevisionAttachmentItem' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns.

Solution:
This is expected behaviour and indicates that document content search has been enabled correctly.


************************************

Action:
Enable Document Search

Error:
Msg 7642, Level 16, State 2, Line 1
A full-text catalog named 'DocumentBodyCatalog' already exists in this database. Use a different name.

Solution:
This indicates that document content search has already been enabled, and that the catalog has been created. If a document search is not giving the expected results be aware that it may some time for a database with a large number of documents to have it's index built. If this error is being displayed and document content search is not working then run the script below to disable document content search:

ALTER FULLTEXT INDEX ON RevisionAttachmentItem DISABLE;
ALTER FULLTEXT INDEX ON RevisionAttachmentItem DROP (FileData);
DROP FULLTEXT INDEX ON RevisionAttachmentItem;
DROP FULLTEXT CATALOG DocumentBodyCatalog;
EXEC sp_fulltext_database 'disable';

Once this has been run, re-enable document content search.

************************************

Action:
Disable Document Search

Error:
Msg 7658, Level 16, State 2, Line 1
Table or indexed view 'RevisionAttachmentItem' does not have a full-text index or user does not have permission to perform this action.

Solution:
This indicates that the document content search has not been fully enabled. To ensure all components are disabled correctly run each line of the disable script individually. It is lightly that one or more of the lines will report an error - this is expected behaviour.

************************************

Action:
Enable Document Search

Error:
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'tblDocumentBodyCatalog' because it is not full-text indexed.

Solution:
Run the following SQL script against the database to ensre that full text search has been enabled:

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

If this returns a 0 - then add the 'SQL Full Text Search' feature to the SQL instance. Article below explains how to add a feature to an existing instance:

http://www.techrepublic.com/blog/data-center/adding-sql-full-text-search-to-an-existing-sql-server/

If the above statement returns a 1, run the statement below - replacing QPulse5 with the name of the database:

SELECT DatabaseProperty ('QPulse5','IsFulltextEnabled' )

If this returns 0 then full test search is not enabled for that database. To enable right-click on the database, click 'Files' then ensure 'use full text indexing' is checked.

************************************

Action:
Searching for documents

Error:
Search not returning in content results for PDF's / Office 2007 / Office 2010

Solution:

By default, SQL Server supports Full-Text searching of the document file types shown further below.

ASP, ASPX, CSS, HTM, HTML, HHT, HTW, HTX, ODC, STM, DOC, DOT, POT, PPS, PPT, XLB, XLC, XLS, XLT, TXT, ASM, BAT, C, CPP, CXX, CMD, DEF, DIC, H, HPP, XML, RTF, EML

Filter packs below must be installed. Note that the instance must be restared for these to take effect.

Office 2007/10: http://support.microsoft.com/kb/945934
PDF: http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611


************************************

How to test In Document Search Is Working:

In the query below, replace keyword with a search keyword that you know is contained within an embedded document attachment of a file type that's supported by a filter pack. The document should be returned in the query results.
 
SELECT
D.Number, R.Title, R.RevisionReference AS 'Revision', RA.DisplayFileName AS 'Attachment', R.AttachmentName AS 'Description', RA.ModifiedDate AS 'Modified Date'
FROM
RevisionAttachmentItem RA INNER JOIN Revision R
ON RA.RevisionID = R.ID
INNER JOIN Document D
ON D.ID = R.DocumentID
WHERE
CONTAINS (RA.FileData, 'keyword') AND RA.IsEmbedded = 1 ORDER BY
Number, Revision;