can't create full-text index on binary column

[1DMF]

Hi,

I have MS SQL2008 R2 and am trying to implement full-text search on a PDF BLOB.

I have followed this thread :

http://social.msdn.microsoft.com/fo...948-af12-ffcbe9f95aa1/store-pdf-in-sql-server

And so far have the Adobe iFilter installed.

However, when I try to create a full-text index with...

CREATE FULLTEXT INDEX ON Compliance_Updates
 ( 
  FileDesc
     Language 1033,
  FileData
	TYPE COLUMN FileData
     Language 1033 
 ) 
  KEY INDEX PK_Compliance_Updates
      ON Compliance_Updates_Catalog; 
GO

I get this error

Column 'FileData' cannot be used as full-text type column for image column. It must be a character-based column with a size less or equal than 260 characters.

FileData is a VARBINARY(MAX) column and stores BLOBs of PDF's which is the whole point isn't it?

What am I doing wrong?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

It's ok I was being a numpty, didn't realise I had to have a column that stored the file extension..

CREATE FULLTEXT INDEX ON Compliance_Updates
 ( 
  FileDesc
     Language 1033,
  FileData
	TYPE COLUMN [b]FileDataType[/b]
 ) 
  KEY INDEX PK_Compliance_Updates
      ON FT_Compliance_Updates; 
GO

So FileDataType is a char(4) column with default '.pdf' , then the full-text index created fine!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[1DMF]

ok, the only problem I have now is the search doesn't work?

nothing is returned when I know the word I'm searching for exists in the PDF's?

Does anyone know if this actually works?

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[yelworcm]

Fulltext search is pretty finicky in some places. What is the query you are using?

 

[1DMF]

here is the query..

SELECT [FileID],[FileDesc],[PubDate]
FROM Compliance_Updates
INNER JOIN 
CONTAINSTABLE ([Compliance_Updates],([FileDesc], [FileData]), 'ISABOUT( FORMSOF (INFLECTIONAL, Mortgage) WEIGHT(0.9))',language 'English') AS res
ON res.[key]=[FileID]

I know the PDF's have the word Mortgage in them, but I get zero results?

All help greatly appreciated.

1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music