Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've set up a full-text index on my SQL Server DB on a varbinary(max) column. I have a type column specified, which contains the extension of the file, such a ".doc", ".pdf" etc.

However, I have noticed that when any .rtf files are indexed, SQL is including all of the meta information in the file (such as the RTF tag "listoverridecount0").

This is bloating the indexes quite a bit, and also means that searches will match on these tags (i.e. I can search on "listoverridecount0" and get EVERY .rtf returned).

Is there any reason that the iFilter for .rtf would not be removing the RTF Tags?

When I run this:

SELECT * FROM sys.fulltext_document_types WHERE document_type = '.rtf';

I get this:

document_type  .rtf                                     
class_id       C7310720-AC80-11D1-8DF3-00C04FB6EF4F 
path       c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\msfte.dll
version        12.0.6828.0
manufacturer   Microsoft Corporation

Any ideas what the problem is? Anyone seeing the same thing?

UPDATE:

I have logged as an MS Connect bug as I don't seem to be able to find any workaround, so presume it is either a) Error with the RTF iFilter not removing tags, or b) Full Text index issue.

https://connect.microsoft.com/SQLServer/feedback/details/814743/full-text-search-rtf-files-incorrectly-indexed-by-rtf-tags

UPDATE:

SELECT @@VERSION

gives

Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) 
Oct 25 2013 19:04:40 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
share|improve this question

1 Answer 1

I am not sure what version of MSSQL you are using, but does this link help? SQL Filters It looks at the various filters set within the SQL Server, maybe the .rtf filter is not loading properly?

share|improve this answer
    
The output of EXEC sp_help_fulltext_system_components 'filter' is giving me the same output as sys.fulltext_document_type (see output in my question) so the .rtf filter appears to have loaded. –  OffHeGoes Jan 29 at 9:30
    
Sorry - I see you are 2012 R1. It is only my instinct at this moment but I would think that the library is at fault. Should .rtf be stored as image? –  EJW Jan 29 at 15:34
    
Yes, that is a good point, but the table is designed for documents of all types, so it needs to be VARBINARY. It is also in FILESTREAM storage. –  OffHeGoes Jan 30 at 9:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.