Problem with MS SQL 2005 x64 FTS on WinSr 2008 x64

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Problem with MS SQL 2005 x64 FTS on WinSr 2008 x64

Author

Topic

miljen
Starting Member

6 Posts

Posted - 2010-03-31 : 06:24:12

Full text search does not return results from T-SQL query with CONTAINS, on 64-bit SQL Server 2005 on 64-bit Windows Server 2008, when I execute sp_fulltext_service 'load_os_resources', 1, and after I restart SQL Server services (http://support.microsoft.com/kb/945934).

Server configuration:
- SQL Server 2005 x64 Enterprise Edition SP3 (tested with and without Cumulative Update Package 8) with Croatian_CI_AS collation
- Windows Server 2008 x64 SP2 with Regional Settings set to Croatian
- Hyper-V virtual machine with 2 GB RAM and 1 CPU

Error from MS SQL log:
2010-02-26 10:51:43.04 spid17s Error: 7683, Severity: 16, State: 1.
2010-02-26 10:51:43.04 spid17s Errors were encountered during full-text index population for table or indexed view '[FTS_Test].[dbo].[Dokumenti]', database 'FTS_Test' (table or indexed view ID '2073058421', database ID '5'). Please see full-text crawl logs for details.

Errors from FTS log:
2010-02-26 10:51:39.03 spid17s Informational: Full-text Full population initialized for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Population sub-tasks: 1.
2010-02-26 10:51:40.04 spid17s Error '0x8007007e' occurred during full-text index population for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000007. Attempt will be made to reindex it.
2010-02-26 10:51:40.04 spid17s The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
2010-02-26 10:51:40.04 spid17s Informational: Full-text Full population completed for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Number of documents processed: 1. Number of documents failed: 0. Number of documents need retry: 1.
2010-02-26 10:51:41.04 spid17s Error '0x8007007e' occurred during full-text index population for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000007. Attempt will be made to reindex it.
2010-02-26 10:51:41.04 spid17s The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
2010-02-26 10:51:41.04 spid17s Informational: Full-text retry pass of Full population completed for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Number of retry documents processed: 1. Number of documents failed: 0.
2010-02-26 10:51:42.04 spid17s Error '0x8007007e' occurred during full-text index population for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000007. Attempt will be made to reindex it.
2010-02-26 10:51:42.04 spid17s The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
2010-02-26 10:51:42.04 spid17s Informational: Full-text retry pass of Full population completed for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Number of retry documents processed: 1. Number of documents failed: 0.
2010-02-26 10:51:43.04 spid17s Error '0x8007007e' occurred during full-text index population for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'), full-text key value 0x00000007. Attempt will be made to reindex it.
2010-02-26 10:51:43.04 spid17s The component 'MSFTE.DLL' reported error while indexing. Component path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\MSFTE.DLL'.
2010-02-26 10:51:43.04 spid17s Informational: Full-text retry pass of Full population completed for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Number of retry documents processed: 1. Number of documents failed: 1.
2010-02-26 10:51:43.05 spid17s Changing the status to MERGE for full-text catalog "FTS_Test" (19) in database "FTS_Test" (5). This is an informational message only. No user action is required.
2010-02-26 10:51:43.05 spid17s Informational: Full-text Auto population initialized for table or indexed view '[FTS_Test].[dbo].[Dokumenti]' (table or indexed view ID '2073058421', database ID '5'). Population sub-tasks: 1.

Test DB contains only one DOC document.

We need to set FTS option 'load_os_resources' to 1, so that we could use iFilter components for DOCX (Microsoft Filter Pack 1.0) and PDF types of documents (Adobe PDF iFilter 9 for 64-bit Platforms), so I would appreciate any help.

TIA,
Miljen

matija_lah
Starting Member

1 Post

Posted - 2010-04-06 : 10:16:37

Could it be that the document is damaged?
Does the CONTENT TYPE column contain the correct abbreviation for the given document (".doc", ".docx", ".pdf", etc.)?

ML

http://milambda.blogspot.com

miljen
Starting Member

6 Posts

Posted - 2010-04-06 : 12:06:10

No.
Yes.

It looks like it is a problem with SQL 2005 x64 and Windows Server 2008 x64 compatibility.

If I execute sp_fulltext_service 'load_os_resources', 0, and after that I restart SQL Server services, the same query works OK with the same data (DOC file), but I need to set 'load_os_resources' to 1 so that I could also index DOCX and PDF files.

PS. Same query with the same data and 'load_os_resources' set to 1 works OK on:
- Windows XP x86 + SQL 2005 x86
- Windows XP x86 + SQL 2008 x86
- Windows Server 2008 x64 + SQL 2008 x64

Miljen

laviniuc
Starting Member

4 Posts

Posted - 2011-07-25 : 08:42:03

Hi,

i seem to have the same issue, did you ever find a solution? (same as in your case i have sql2005 64bits running on win2008 r2 (64bits).

if i set load_os_res to 1 (to parse also pdf's / docx's) the parse for docs (in my case - but it's probably all "default handled" parses) goes overboard.

if i set it back to 0, at least the default parsed ones work

thank you,
Laviniu

miljen
Starting Member

6 Posts

Posted - 2011-08-04 : 11:44:16

Hi,

We contacted Microsoft Support about this issue.
After intensive troubleshooting they confirmed that this is a bug, but they don't know whether they will fix this or when it will be fixed.

Luckily just a few of our customers have this problem and the plan is to migrate then to SQL 2008 or 2008 R2 as soon as possible any way.

Miljen

laviniuc
Starting Member

4 Posts

Posted - 2011-08-05 : 12:27:55

thank you for the answer, is there are change you could keep me posted (or give me a link to your support ticket with them) in case ms ever fix the underlying problem?

(unfortunately in my case upgrading to sql 2008 is not an option so i'm stuck with load_os_resources to 0 until i find a workaround or the problem is fixed by ms)

miljen
Starting Member

6 Posts

Posted - 2011-08-06 : 14:48:52

If I get any new information about this problem, I will post it to this forum.

Miljen

laviniuc
Starting Member

4 Posts

Posted - 2011-11-21 : 13:49:31

Hi,

so i finally got to the bottom of it. or so i think, i wasn't able to confirm this on the production environment (but this is how i was able to replicate/fix it on various test virtual machines that we setup to find the solution for this issue).

i think the issue lies on the server regional settings, the ones for the system locale, (region/language, administrative tab). when set to English everything seems to work great.

is there any chance that you might be able to confirm if changing this fixes the issue for you too?

thanks,
Laviniu

p.s. great error handling for the fts engine on ms's part if this is indeed the issue

miljen
Starting Member

6 Posts

Posted - 2011-11-24 : 06:04:51

Hi Laviniu,

I'm sorry, but we have deleted our test VM-s where we have been troubleshooting this problem and we currently have no time to build another test environment, so I can't confirm your findings.

Miljen

laviniuc
Starting Member

4 Posts

Posted - 2011-11-24 : 13:25:24

ok,

no problem, thanks anyway. in the end we bit the bullet and went ahead with the fix on the production environment.

everything turned out exactly how we hoped. the full text search works nicely now so i guess that was the issue, the system localization.

the full text search on sql2005 64 bits over win 2008 r2 (64bits) with the load_os_resources is set to 1 (and verify_signature to 0) - for custom ifilters does not work properly unless the system locale setting is en/us. (for me, the only documents parsed were docs/xls/ppt, even the standard text parser failed with the described error (Error '0x8007007e' occurred during full-text index population for table or indexed)

edit: did not have time to test what happens when the language for the sql installation is the same as the system BUT different than the default en/us (but i suspect this is your situation)

let's hope whoever runs into this finds this forum and doesn't waste the time of his/her life trying to pinpoint it...

Laviniu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources