Hi all,

I have an issue with full text indexing of some BLOB data (image data
type). We recently moved a SQL 2000 database to a new server with SQL
2005 installed. Everything thing went very smooth. We have full text
indexing enabled on several columns over several tables. We have a
table for attachments that are stored as BLOBs that is also indexed.

Since the migration, all of the full text searches work as before with
the exception of the binary data. We used to be able to search inside
Word, Excel, and other office documents. Now we cannot.

I believe I have located the source of the problem, but I am not sure
how to resolve. Anyone who has used this feature knows that when you
index a BLOB (image data type) you have to select a second column that
tells SQL what filetype is being stored. My column for this is called
ATTACHMENT_CONTENTTYPE. SQL compares this with a list of iFilters it
has to determine which filter it should use when searching the binary
data for text.

Here is an example of what is contained in my ATTACHMENT_CONTENTTYPE
column:

image/tiff
application/x-zip-compressed
text/richtext
image/bmp
audio/wav
application/msword
video/mpeg
video/avi
image/x-bmp
image/vnd.ms-modi
image/gif
video/mpg
application/octet-stream
application/vnd.ms-excel

All of the sql documentation I am reading is telling me that my column
should contain file extensions like:

.doc
.xls
.ppt

Here is my dilemma. This all worked perfectly with SQL 2000. It is a
big deal for me to change all of the data in this column as well as
the application that populates it. Am I not understanding how this
works, or is there some way I can get SQL 2005 to recognize the way my
column is describing the data type?

Thank you!