full text searching BLOBS

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!On Mar 12, 11:25=A0am, markman...@.gmail.com wrote:
> Hi all,
> I have an issue with full text indexing of someBLOBdata (image data
> type). We recently moved aSQL2000 database to a new server withSQL2005inst=alled. 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 aBLOB(image data type) you have to select a second column that
> tellsSQLwhat filetype is being stored. My column for this is called
> ATTACHMENT_CONTENTTYPE.SQLcompares 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 thesqldocumentation 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 withSQL2000. 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 getSQL2005to recognize the way my
> column is describing the data type?
> Thank you!
Also, I have no errors in the log files that I can see. Thanks all.

Full Text Search breaks after disc replaced

Hello,
(I also posted to the WSS newsgroup but remembered
somebody suggesting that this type of question might be
better answered in the sqlserver.fulltext newsgroup).
We recently had to replace our discs with larger ones. So
we ghosted the original disc containing the WSS dbase,
configured new discs, then reimaged back. Everything
working fine, apart from the full-text search - searching
sites returns no results. This has happened before when
removing, replacing the discs. Strangely enough, when we
examined the services console, the MS Search Service had
reverted to an earlier domain logon (which is the domain
login with database rights - we changed this quite a while
back to the local system logon, which was recommended, and
search had been working correctly ever since). We changed
this logon once again back to local system and then
clicked on the enable full text search on the WSS
interface. The gatherer returned the errors, saying
it 'failed to filter 10052 documents' - 0 urls could not
be reached or were denied access. When we looked at the
full text catalogs in MSSQL Enterprise UI, they were only
1MB in size, but the actual full text files/catalogs on
the C drive were around 12Mb. I'm beginning to think
that some of those documents had been indexed at some
point by the domain logon, not the local system logon, and
now local system can't reach those documents? I am
thinking about opening Enterprise Manager, and 'rebuilding
the index' (this says it will delete the existing one and
build a new one), with the MS Search Service now switched
back to local system logon. Would this be the best course
of action
for me, would anything get lost, or does this simply look
at the existing dbases and rebuild from those?
Thanks,
Jeff
Can you try to change the SQL Server service account through EM as this KB
details?
http://support.microsoft.com/default...b;en-us;277549
This is applicable for SQL 2000 as well.
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:13bb001c4130f$9de78710$a301280a@.phx.gbl...
> Hello,
> (I also posted to the WSS newsgroup but remembered
> somebody suggesting that this type of question might be
> better answered in the sqlserver.fulltext newsgroup).
> We recently had to replace our discs with larger ones. So
> we ghosted the original disc containing the WSS dbase,
> configured new discs, then reimaged back. Everything
> working fine, apart from the full-text search - searching
> sites returns no results. This has happened before when
> removing, replacing the discs. Strangely enough, when we
> examined the services console, the MS Search Service had
> reverted to an earlier domain logon (which is the domain
> login with database rights - we changed this quite a while
> back to the local system logon, which was recommended, and
> search had been working correctly ever since). We changed
> this logon once again back to local system and then
> clicked on the enable full text search on the WSS
> interface. The gatherer returned the errors, saying
> it 'failed to filter 10052 documents' - 0 urls could not
> be reached or were denied access. When we looked at the
> full text catalogs in MSSQL Enterprise UI, they were only
> 1MB in size, but the actual full text files/catalogs on
> the C drive were around 12Mb. I'm beginning to think
> that some of those documents had been indexed at some
> point by the domain logon, not the local system logon, and
> now local system can't reach those documents? I am
> thinking about opening Enterprise Manager, and 'rebuilding
> the index' (this says it will delete the existing one and
> build a new one), with the MS Search Service now switched
> back to local system logon. Would this be the best course
> of action
> for me, would anything get lost, or does this simply look
> at the existing dbases and rebuild from those?
> Thanks,
> Jeff
|||Thanks Hilary, I'm going to try this over this weekend.
Would I need to 'rebuild' catalog after making the change
through the EM?
Jeff
>--Original Message--
>Can you try to change the SQL Server service account
through EM as this KB
>details?
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;277549
>This is applicable for SQL 2000 as well.
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:13bb001c4130f$9de78710$a301280a@.phx.gbl...
So
searching
we
while
and
changed
only
and
and 'rebuilding
and
switched
course
look
>
>.
>
|||You shouldn't have to, but your catalog size indicates that you must.
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:1403f01c4135f$eaa92130$a301280a@.phx.gbl...
> Thanks Hilary, I'm going to try this over this weekend.
> Would I need to 'rebuild' catalog after making the change
> through the EM?
> Jeff
> through EM as this KB
> us;277549
> message
> So
> searching
> we
> while
> and
> changed
> only
> and
> and 'rebuilding
> and
> switched
> course
> look
sql

Full Text Image Type

Hi,
Can anybody tell me from which system tables / catalog views can I get the
Full Text Image Type for a Column in SQL 2005 (Yukon).
TIA
PraWith
SELECT *
FROM INFORMATION_sCHEMA.COLUMNS
WHERE CHARACTER_OCTET_LENGTH > 8000 OR
CHARACTER_OCTET_LENGTH = -1
you will get all image, varbinary(max), text, varchar(max), ntext,
nvarchar(max) and xml data type columns.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:O3EPbsw$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anybody tell me from which system tables / catalog views can I get the
> Full Text Image Type for a Column in SQL 2005 (Yukon).
>
> TIA
> Pra
>
>

Full Text Image Type

Hi,
Can anybody tell me from which system tables / catalog views can I get the
Full Text Image Type for a Column in SQL 2005 (Yukon).
TIA
Prasad
With
SELECT *
FROM INFORMATION_sCHEMA.COLUMNS
WHERE CHARACTER_OCTET_LENGTH > 8000 OR
CHARACTER_OCTET_LENGTH = -1
you will get all image, varbinary(max), text, varchar(max), ntext,
nvarchar(max) and xml data type columns.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:O3EPbsw$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anybody tell me from which system tables / catalog views can I get the
> Full Text Image Type for a Column in SQL 2005 (Yukon).
>
> TIA
> Prasad
>
>

Full Text Image Type

Hi,
Can anybody tell me from which system tables / catalog views can I get the
Full Text Image Type for a Column in SQL 2005 (Yukon).
TIA
PrasadWith
SELECT *
FROM INFORMATION_sCHEMA.COLUMNS
WHERE CHARACTER_OCTET_LENGTH > 8000 OR
CHARACTER_OCTET_LENGTH = -1
you will get all image, varbinary(max), text, varchar(max), ntext,
nvarchar(max) and xml data type columns.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:O3EPbsw$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anybody tell me from which system tables / catalog views can I get the
> Full Text Image Type for a Column in SQL 2005 (Yukon).
>
> TIA
> Prasad
>
>

Full Text Image Type

Hi,
Can anybody tell me from which system tables / catalog views can I get the
Full Text Image Type for a Column in SQL 2005 (Yukon).
TIA
PrasadWith
SELECT *
FROM INFORMATION_sCHEMA.COLUMNS
WHERE CHARACTER_OCTET_LENGTH > 8000 OR
CHARACTER_OCTET_LENGTH = -1
you will get all image, varbinary(max), text, varchar(max), ntext,
nvarchar(max) and xml data type columns.
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:O3EPbsw$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anybody tell me from which system tables / catalog views can I get the
> Full Text Image Type for a Column in SQL 2005 (Yukon).
>
> TIA
> Prasad
>
>

FTS does not index htm file in image column

Hi,
I have FT indexing set up on an image column, the column is binded to a
document type column and the FT indexing works fine. My problem is that
when a html file has the following utf-8 metatag
<META http-equiv=Content-Type content="text/html; charset=utf-16">
then the FT searches do not work on that particular file at all. Does
anyone know why this should be the case? Is there a way of making FT
index these files?
thanks in advance,
Paul
what is the value of your document type column? Are there any errors in your
gatherer log?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<paul.craigie@.gmail.com> wrote in message
news:1105972135.333652.164980@.f14g2000cwb.googlegr oups.com...
> Hi,
> I have FT indexing set up on an image column, the column is binded to a
> document type column and the FT indexing works fine. My problem is that
> when a html file has the following utf-8 metatag
> <META http-equiv=Content-Type content="text/html; charset=utf-16">
> then the FT searches do not work on that particular file at all. Does
> anyone know why this should be the case? Is there a way of making FT
> index these files?
> thanks in advance,
> Paul
>
|||thanks for prompt response.
the value of the documenttype column for a html file that does not get
returned by FTS is corpUpdate3.htm. For comparison, the documenttype
value for another record which does get returned (i.e. does not have
the utf-8 meta tag) is corpUpdate4.htm . I'm not sure what you mean by
gatherer log, I have checked the event viewer and there's nothing there
from MS Search or msci.
Paul
|||I think you will need to add the MS.Locale language tag for this to work.
Let me try to repro it.
Also, the DocumentType column contents should be htm, html, .htm, or .html
<paul.craigie@.gmail.com> wrote in message
news:1105974655.007897.288270@.z14g2000cwz.googlegr oups.com...
> thanks for prompt response.
> the value of the documenttype column for a html file that does not get
> returned by FTS is corpUpdate3.htm. For comparison, the documenttype
> value for another record which does get returned (i.e. does not have
> the utf-8 meta tag) is corpUpdate4.htm . I'm not sure what you mean by
> gatherer log, I have checked the event viewer and there's nothing there
> from MS Search or msci.
> Paul
>
|||Hi Hilary,
I've worked out where the gatherer log files are. There are lots of
this kind of error in there ..
f28c7c9b1c4fcb0MSSQL75://SQLServer/3805392f/000000638000001c080040e21Multiple-step
OLE DB operation generated errors. Check each OLE DB status value, if
available. No work was done.
does this tell us anything?
Paul
|||It could. Could you possibly post the html file here?
Also is there anything non-standard about your machine? Do you have
Sharepoint, Yukon, Exchange running on this machine? Is this a cluster?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
<paul.craigie@.gmail.com> wrote in message
news:1105979877.237531.111230@.c13g2000cwb.googlegr oups.com...
> Hi Hilary,
> I've worked out where the gatherer log files are. There are lots of
> this kind of error in there ..
> f28c7c9b 1c4fcb0 MSSQL75://SQLServer/3805392f/00000063 8000001c 0 80040e21
Multiple-step
> OLE DB operation generated errors. Check each OLE DB status value, if
> available. No work was done.
> does this tell us anything?
> Paul
>
|||OK,
the first test file looks like this ..
<html>
<head><META http-equiv=Content-Type content="text/html;
charset=utf-8"></head>
<body>
<p>paultest2</p>
</body>
</html>
and the second test file looks like this
<html>
<head></head>
<body>
<p>paultest2</p>
</body>
</html>
when I do a search on 'paultest2', only the second file is returned.
The SQL server is on a dedicated DB server, there are no other apps
running on it. I tried the MS.Locale language tag but it hasn't made
any difference to the chances of a file being returned by FTS.
Paul