Full text query on VarBinary column is not returning rows

Question

Hello All,
SQL Server2005 is installed in Windows 2003 Server(64-Bit) and updated with Sql Server SP2 and SP3. I installed FilterPack and registered with SQL server as per the steps given in the following link
http://support.microsoft.com/kb/945934

I'm trying to do Fulltext search on varBinary column (contains visio documents).
When setting up the full-text index, I specified the type column(.vsd), in addition to picking the binary column.
then I run the following full-text query.
select * from visDocuments where freetext([Document],'Dell')
But its not returning the rows. I'm sure one of the row is having the word Dell.
Please guide me to solve this problem. Thanks in advance.

Note: I followed the above steps in Windows XP(32-bit) machine, it worked fine. it returned rows.

Answer 1

what is your unique key count for this table, what version of visio are you running and are there are errors in your full text log.

Look in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG for the log files and they will look like this: SQLFT0001500005.LOG

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html 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

Answer 2

Thanks for your reply.

Unique key count is 0. and I'm using both VISIO 2003 and VISIO 2007.

In sys.fulltext_document_types table, I found the following entry for .vsd file type

'.vsd'      'FAEA5B46-761B-400E-B53E-E805A97A543E'           'C:\Program Files\Common Files\Microsoft Shared\Visio Shared\VISFILT.DLL'          '11.0.3216.0'           'Microsoft Corporation'

There are several log files in FTDATA folder. Below, I listed three log files
Please guide me to solve this problem.

In log file(SQLFT0001100005.LOG), I found the following info repeatedly

2009-05-28 14:07:24.75 spid17s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x00000008. Attempt will be made to reindex it.
2009-05-28 14:07:24.75 spid17s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.

In one of the file, I found the following
2009-05-28 14:07:22.73 spid17s     Informational: Full-text Full population initialized for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'). Population sub-tasks: 1.
2009-05-28 14:07:23.75 spid17s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x0000000A. Attempt will be made to reindex it.

In one of the LOG file,

2009-05-31 05:50:49.29 spid22s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2009-05-31 05:50:49.29 spid22s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x0000000B. Attempt will be made to reindex it.
2009-05-31 05:50:49.29 spid22s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2009-05-31 05:50:49.29 spid22s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x0000000C. Attempt will be made to reindex it.
2009-05-31 05:50:49.29 spid22s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2009-05-31 05:50:49.29 spid22s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x0000000D. Attempt will be made to reindex it.
2009-05-31 05:50:49.29 spid22s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2009-05-31 05:50:49.29 spid22s     Error '0x80004005' occurred during full-text index population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'), full-text key value 0x0000000E. Attempt will be made to reindex it.
2009-05-31 05:50:49.29 spid22s     The component 'sqlfth90.dll' reported error while indexing. Component path 'c:\Program Files\Microsoft SQL Server\90\COM\sqlfth90.dll'.
2009-05-31 05:50:49.29 spid22s     Informational: Full-text retry pass of Full population completed for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'). Number of retry documents processed: 10. Number of documents failed: 10.
2009-05-31 05:50:49.29 spid22s     Changing the status to MERGE for full-text catalog "gsmVisDocCatalog28-05" (5) in database "GSM2.0" (11). This is an informational message only. No user action is required.
2009-05-31 05:50:49.64 spid22s     Informational: Full-text Auto population initialized for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'). Population sub-tasks: 1.
2009-05-31 05:56:29.48 spid57      Informational: Full-text Auto population for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11') was cancelled by  user.
2009-05-31 05:56:29.48 spid57      Informational: Full-text Auto population completed for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'). Number of documents processed: 0. Number of documents failed: 0. Number of documents need retry: 0.
2009-05-31 05:56:29.48 spid57      Informational: Full-text retry pass of Auto population completed for table or indexed view '[GSM2.0].[dbo].[gsmVisioDocuments]' (table or indexed view ID '517576882', database ID '11'). Number of retry documents processed: 0. Number of documents failed: 0.

Answer 3

Hello

check that you have update your full text index (mode auto or other mode in the creation statement of the full text index)

if it is ok
you can try to use the function CONTAINS
check also if you have the good language which allows the iFilter to filters word

see these links :
http://msdn.microsoft.com/fr-fr/library/ms142547(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms142531(SQL.90).aspx

read these docs you must found the answer of your question

thanks

Youcef

---

developpeur C#

Answer 4

Hi,

Fulltext index mode is auto and tried with both CONTAINS and FREETEXT. :(
The msdn links which you specified contains basic information and it didn't have much information on varbinary column.
Please let me know the links which have more detail information on varbinary column (fulltext search)

Thanks in advance.

Answer 5

Hi,

Did you find the solution? I'm also newbie and having trouble getting it to work I'm uploaded only .txt content and the error log entry i got in fulltext log is:

2011-12-12 04:39:14.06 spid37s     Informational: Full-text Auto population completed for table or indexed view '[Tweetdb].[dbo].[DocumentIndex]' (table or indexed view ID '1743345275', database ID '6'). Number of documents processed: 0. Number of documents failed: 0. Number of documents that will be retried: 0.

Thanks,

Answer 6

Are you loading data into varbinary(max), nvarchar(max), varchar(max), text, or image data types?  

If you are storing the data in varbinary or image data types, you need a "type column" to describe the type of data in the binary so that the SQL Server can interpret it.  

If the data is stored in char, varchar, nchar, nvarchar, or text, the SQL Server knows that it is text without any further help.

RLF

PS - Actually, this is an old thread. It would be better to repost to a new thread.