Newbie question, fulltext and SQL Server Express with Advanced Services

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Larry Brown - 13 Dec 2006 10:06 GMT

Hello,

Sorry, my question is quite long...

I'm testing fulltext search on a SQL Server Express 2005 with Advanced
Services. I've never tested fulltext search on any SQL Server before...

My problem is simple: I can't get anything to work... :)

Here is what I have:

A) Database: named IndexingDB, with one table named File_Store. Four columns
in the table:
cleprim (int, not null, identity, primary key): primary key
file_extension (varchar(8), null): contains file extensions
file_name (varchar(50), null): contains the file name
file_data(varbinary(max), null): contains the file data

B) Catalog: ManuCat, is_default=0, default location on the disk. It was
created using scripts.

C) Scripts:

1) Indexes creation

CREATE UNIQUE INDEX ui_ftindex ON dbo.File_Store(cleprim)
CREATE FULLTEXT INDEX ON dbo.File_Store(file_data TYPE COLUMN file_extension
LANGUAGE 0x0) KEY INDEX ui_ftindex ON ManuCat WITH CHANGE_TRACKING AUTO

When I do a select * from sys.fulltext_indexes, I get the correct catalog
id, change_tracking_state=A, has_crawl_completed=1, crawl_type=F, the start
date corresponds to the moment when I created the indexes, and it stops 5
seconds later (the tables contains just 3 or 4 small files, .doc and .txt)

2) Filling the table

INSERT INTO File_Store(file_name, file_extension, file_data) SELECT
'webserver.txt' AS file_name, '.txt' AS file_extension, * FROM
OPENROWSET(BULK N'c:\temp\webserver.txt', SINGLE_BLOB) AS file_data

Then, I tried to select populatestatus, populatecompletionage, itemcount
from fulltextcatalogproperty: I get 0 itemcount, status 0, and a non-zero
value for completion age.

Each time I add a document, txt or doc, the status changes to 9, and then
comes back to zero.

What did I miss? It's not easy to find examplas using SQL Server Express, so
I don't really know which direction I should search into. Any help will be
greatly appreciated!!!

Many thanks in advance

Reply to this Message

Larry Brown - 13 Dec 2006 10:14 GMT

One precision I forgot: here is a piece of the log file I found under
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

2006-12-13 10:13:08.34 spid8s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:13:08.34 spid8s Informational: Full-text retry pass of
Full population completed for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'). Number of retry documents processed: 3. Number of
documents failed: 3.
2006-12-13 10:13:08.34 spid8s Changing the status to MERGE for
full-text catalog "ManuCat" (19) in database "IndexingDB" (5). This is an
informational message only. No user action is required.
2006-12-13 10:13:08.34 spid23s Informational: Full-text Auto population
initialized for table or indexed view '[IndexingDB].[dbo].[File_Store]'
(table or indexed view ID '693577509', database ID '5'). Population
sub-tasks: 1.
2006-12-13 10:49:24.28 spid22s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000004. Attempt will be made to
reindex it.
2006-12-13 10:49:24.28 spid22s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:49:26.28 spid22s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000004. Attempt will be made to
reindex it.
2006-12-13 10:49:26.28 spid22s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:49:28.28 spid22s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000004. Attempt will be made to
reindex it.
2006-12-13 10:49:28.28 spid22s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:49:30.28 spid22s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000004. Attempt will be made to
reindex it.
2006-12-13 10:49:30.28 spid22s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:53:20.28 spid24s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000005. Attempt will be made to
reindex it.
2006-12-13 10:53:20.28 spid24s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:53:22.28 spid12s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000005. Attempt will be made to
reindex it.
2006-12-13 10:53:22.28 spid12s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:53:24.28 spid12s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000005. Attempt will be made to
reindex it.
2006-12-13 10:53:24.28 spid12s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.
2006-12-13 10:53:26.28 spid8s Error '0x80040e09' occurred during
full-text index population for table or indexed view
'[IndexingDB].[dbo].[File_Store]' (table or indexed view ID '693577509',
database ID '5'), full-text key value 0x00000005. Attempt will be made to
reindex it.
2006-12-13 10:53:26.28 spid8s The component 'sqlfth90.dll' reported
error while indexing. Component path 'c:\Program Files\Microsoft SQL
Server\90\COM\sqlfth90.dll'.

> Hello,
>
[quoted text clipped - 50 lines]
>
> Many thanks in advance

Reply to this Message

Larry Brown - 13 Dec 2006 10:28 GMT

Forget it... I'm stupid... The solution was: REBOOT THE SERVER AFTER SQL
SERVER EXPRESS INSTALLATION!

And that's all...

Sorry... :)

> One precision I forgot: here is a piece of the log file I found under
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
[quoted text clipped - 133 lines]
>>
>> Many thanks in advance

Reply to this Message

Hilary Cotter - 15 Dec 2006 20:12 GMT

It looks like you are having problems with the rows where the pk value is 4
and 5. Does the problem occur with other rows as well? If not the problem
probably is in the content stored in these rows. Can you extract them or
obtain the original files and run them through filtdump?

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> One precision I forgot: here is a piece of the log file I found under
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
[quoted text clipped - 133 lines]
>>
>> Many thanks in advance

Reply to this Message