none
Full Text problem indexing .eml files in SQL Server 2008 RRS feed

  • Question

  • Hi,

    I have a database that will store a table consisting of email files in a VARBINARY(MAX) data type.  In SQL Server 2005 (9.0.4053) I have had no problems.  However, in SQL Server 2008 the indexing is 'failing' with a number of errors which include:

    Error '0x80040e97' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6860'. Attempt will be made to reindex it.

    Error '0x80040e28' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6422'. Attempt will be made to reindex it.

    Error '0x80043630: The filter daemon process MSFTEFD timed out for an unknown reason. This may indicate a bug in a filter, wordbreaker, or protocol handler.' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6424'. Attempt will be made to reindex it.

    However, if I leave it for a LONG time, some records are being indexed eventually. eg There are 16,900 files in this database table and the catalog rebuild was commenced at 4:30PM and is still running after processing only 1160 of the items and the time is now 10:30AM.

    The test system is as follows:
    Windows Server Standard 2008 SP2 64 bit (6GB RAM)
    SQL Server 2008 SP1 (10.0.2531) 64 bit
    The filter in use for the .eml files is c:\windows\system32\mimefilt.dll (2006.0.6002.18005) which was configured (along with the office filter pack and adobe 9) using:
      exec sys.sp_fulltext_service 'load_os_resources', 1
      exec sys.sp_fulltext_service 'verify_signature', 0
    The full text service is running under Local Service

    In desperation, I found another support item similar to the last 2 errors above (KB963659), and installed the Cumulative Update 6 for SQL Server 2008 SP1 to bring its version to (10.0.2757).  (This is a test server).  However, this has had no effect.  I have also tried increasing the ISM size.  CPU usage and memory use are low.

    I never had this problem with the SQL Server 2005 version of this test database and it currently has over 56000 records in it.

    I have confirmed that other file types have no such problems.

    Thanks in advance of any help.
    Wednesday, March 3, 2010 11:44 PM

All replies

  • I'd try the iFilterShop.com's message filter. http://www.ifiltershop.com/msgfilter.html

    There are other msg iFilters out there.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, March 4, 2010 1:58 PM
  • I already have the IFilterShop msg filter installed, but unfortunately it does not filter .eml files and there does not seem to be any alternatives for filtering .eml files.

    If you are suggesting to convert .eml files to .msg:
    1.  I have tried unsuccessfully to use the IConverterSession interface. 
    2.  Do you have any suggestions as to a good converter. 

    Friday, March 5, 2010 1:01 AM
  • According to one site .eml files are actually formatted the same as .mht.  If you have an mht IFileter can you try renaming one to .mht and then reindexing to see if that works?

    But, in contrast, one report mentioned on Connect indicates a problem with the Microsoft mimefilt.dll reading .eml that is in HTML format.  (Are you .eml files plain text or HTML? Is mimefilt installed on your server?)
    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=368173

    Here is a channel 9 list of IFilters, in case you want to look at any of them.
    http://channel9.msdn.com/wiki/wiki/DesktopSearchIFilters/

    And, although this will not address your immediate issue, you might like to know that there are also some downloadable IFilter packs from Microsoft which can be found here.  (Includes a .msg filter.)
    http://support.microsoft.com/default.aspx?scid=kb;en-us;945934

    RLF
    Friday, March 5, 2010 6:46 PM
  • The default mht filter is of course mimefilt.dll, but I found that SQL Server has an internal filter (msfte.dll) that I managed to configure to work for the .eml extension by adding an entry in HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters\ for {C7310720-AC80-11D1-8DF3-00C04FB6EF4F} which is a copy of that for .mht.

    This works pretty quickly. However, it does not seem to index any included attachment files.

    My files are not HTML - they are plain text.

    There does not seem to be any other MHT filters available.  The citeknet download is only a registry fix to configure mimefilt.dll.
    Monday, March 8, 2010 5:45 AM
  • Have you read this connect item?  Microsoft says it is fixed in the "next major release" (but release of what, I don't know).
    https://connect.microsoft.com/SQLServer/feedback/details/368173/fulltext-service-doesnt-work-with-some-system-ifilters-mimefilt-dll-on-vista?wa=wsignin1.0

    Comments copied:

    I checked path in SQL Server
    ( select [document_type], [path] from sys.fulltext_document_types )
    SQL shows it as "C:\Windows\system32\%systemroot%\system32\mimefilt.dll"
    !!!!

    Workaround is -
    I changed that key on full path "C:\Windows\system32\mimefilt.dll".
    I restarted server and FTS and again loaded the system iFilters.
    All start works fine.


    If you path looks like the problem path above, try the workaround. 

    RLF

    Wednesday, March 10, 2010 3:10 PM
  • Thanks for helping Russell.

    However, I have seen that item already.  I have already tried changing the path, and I have all of the updates I can apply.

    I notice that the last entry simply says "does not work again", but does not elaborate with any description as to what is happening now.

    Sounds like a similar scenario, whereby we want to import a lot of .eml files into a SQL Server database and provide a search facility.

    Funny thing is that it works fine in SQL Server 2005 and we may have to proceed with that and move the database to SQL Server 2008 when it (or mimefilt) is fixed.

    Richard
    Wednesday, March 10, 2010 11:23 PM
  • I have encountered many of the same issues. We have EML files in VARBINARY(MAX) and using SQL 2005 32 bit on Windows 2003 32 bit or 64 bit we are able to jump through all the configuration hoops. The main one being %systemroot% needing to be replaced with c:\windows\.

    However, when we upgrade to SQL 2008 32 bit or 64 bit the indexing will litterally take days to complete. The issue has been raised with Microsoft but has gotten no traction, and I was wondering if anyone else has had indexing issues like this with SQL 2008.

    We have reverted back to SQL 2005 which is a real shame because we could use some of the SQL 2008 Full Text features but it has not been important for Microsoft to properly address this issue.

    One of the key issues seems to be that nearly every EML processed in SQL 2008 generates an apartment threading error, and it would appear that a configuration flag could be set somewhere to overcome this issue. Is there any documentation on configuring iFilters for SQL server crawls, or the crawl engine?

    Thank you for any help.

    Wednesday, June 8, 2011 8:21 PM
  • 8 years later, I am also struggling with this. Have already tried everything to full text index eml files and on all sql server versions after 2008, including sql server 2019. Still no luck, you just cannot index any attachments contained in eml files. Mimefilt.dll does not even index the message body!

    In addition to that, Windows search does index eml files with no issue and does return results even from file attachments when you query it for contents. Crazy! Same dll used by windows search and sql, you get results back from one but not from the other!

    Rjl61,I was wondering if you ever came up with another solution to this, that you would like to share with us?




    • Edited by Lastdim Monday, January 28, 2019 1:13 PM
    Wednesday, January 23, 2019 3:30 PM
  • I have the same problem - did you ever find a solution or workaround?
    Monday, July 29, 2019 7:19 AM