Nov
17
2011

Get the Office 2010 Filter Pack for search in SQL Server 2012

SQL Server 2012 “Denali” installs the latest Microsoft word breakers and stemmers. However, it does not install the latest filters for Microsoft Office documents. If you want to use Full-Text Search or Semantic Search to index documents created by the most recent versions of Microsoft products, you have to download the latest filters.

Get the latest filters

To get the latest Microsoft filters:

In a moment, SQL Server will be able to search all the document types supported by the filters included in the filter pack:

  • Legacy Office Filter (97-2003; .doc, .ppt, .xls)
  • Metro Office Filter (2007; .docx, .pptx, .xlsx)
  • Zip Filter
  • OneNote filter
  • Visio Filter
  • Publisher Filter
  • Open Document Format Filter

Load the newly-installed filters

I said, “in a moment,” because first you have to tell SQL Server to begin using the newly-installed filters. SQL Server does not automatically use all filters installed on the computer. Run the following Transact-SQL commands:

  1. Load the new filters:
    EXEC sp_fulltext_service 'load_os_resources', 1
  2. Update the list of components maintained by SQL Server:
    EXEC sp_fulltext_service 'update_languages'
    (This step may not be necessary to use the filters. However it is necessary to ensure up-to-date output about the installed filters from the sp_help_fulltext_system_components system stored procedure.)
  3. Restart the filter daemon host processes:
    EXEC sp_fulltext_service 'restart_all_fdhosts'

How to fix a gotcha related to Office 97-2003 documents (.doc, .xls, etc.)

After you install the new filters, SQL Server continues to use the older version of the filter DLL (OFFFILT.DLL) for Microsoft Office 97-2003 documents. This can prevent the proper indexing of documents in certain cases – for example, an Excel spreadsheet embedded inside a Word document is properly indexed by the newer version of the filter, but not by the older version.

To resolve this issue and ensure that SQL Server uses the newly-installed version of OFFFILT.DLL for legacy Office documents, do the following:

  1. Delete this registry key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSearch\Filters\.doc
  2. Load the alternate filters:
    EXEC sp_fulltext_service 'load_os_resources', 1
  3. Restart the filter daemon host processes:
    EXEC sp_fulltext_service 'restart_all_fdhosts'

To confirm that SQL Server is now using the newer version of OFFFILT.DLL in the Common Files\Microsoft Shared\Filters folder, you can run the following query:

select * from  sys.fulltext_document_types where document_type = '.doc'

 

This should return the following information, which confirms that the newly-installed filter DLL is now being used:

“.doc       64F1276A-7A68-4190-882C-5F14B7852019     C:\Program Files\Common Files\Microsoft Shared\Filters\OFFFILT.DLL 2010.1400.4746.1000  Microsoft Corporation”

 

Rebuild full-text indexes after installing new filters

To see the effects of the newly-installed filters, you have to repopulate existing full-text indexes where the indexed columns contain documents that are supported by the new filters.


I hope this information helps you with Full-Text and Semantic Search in SQL Server 2012!

Let me know how I'm doing! Please comment on my blog, or send email to dougbert@dougbert.com.

For technical support, however, I recommend the SQL Server Search forum on MSDN.

-Doug

Comments (1) -

John F

This was really helpful; thanks! Esp the bit about clearing the registry entries for outdated items.