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:25Xam, 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 withSQL2005installed. 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 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 Setup

I am trying to setup FTS on a table in my database; the column I am
interested in FTS'ing is all image datatypes. The data in the colum is
populated via asp.net; and we store the document type as a mime type e.g.
'application/msword' as it makes it easy to get data back out from the
database.
My table has teh following columns
DocumentID UniqueIdentifier,
DocumentTypeRef int
DocumentSubject varchar(100)
DocumentValue image
DocumentDocType varchar(50)
DocumentDocType sysname -- added to use FTS would rather use vc(50) field
DocumentByteSize int
DocumentFileName varchar(100)
DocumentCreated datetime
MailMergeTemplateID uniqueidentifier
ToDoID uniqueidentifier
DocumentUploadWaiting bit
The table has 50 rows in it; in my test system. mostly word documents. I
have run teh following script to setup fts (and tried the wizard without
success)
use Activities_FTS
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
exec sp_fulltext_column @.tabname = tblDocuments,
@.colname = DocumentValue,
@.action = 'dro',
@.Type_Colname = DocumentDocType2
exec sp_fulltext_table 'tblDocuments', 'activate'
exec sp_fulltext_table 'tblDocuments', 'start_full'
select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
This is based around the steps which worked successfully from the tutorial
on this page
http://msdn.microsoft.com/library/de...extsearch.asp.
When i run the index population check it does run for about 30 seconds; but i
cannot find out why it is not indexing the documents; or is there another
query i can use to test this. (My FTS catalog never grows above 1mb)
If you have any questions please do not hestitate to reply as i am slightly
lost now
Many thanks
Chris
Can you check what your gatherer logs report? I'm not sure if storing them
as a mime type is the best way to go.
To check your gatherer log output do this:
go to c:\program files\common files\system\mssearch\bin and copy gthrlog.vbs
to %windir% then go to your catalog location for your catalog (this should
be c:\program files\microsoft sql server\mssql\ftdata\sqlserver\gatherlogs.
Now you want to id your gatherlog for your catalog. the totally brainless
what to do this is to issue another incremental population for your catalog
and then do a dir /od. Your catalog
gather logs will then appear last in the list. The syntax is
SQLXXXXXYYYYY.?.gthr where the X is your db_id (you can determine this by
doing
this select db_id('database_name') and the YYYYYY is your catalog id, which
you can determine by doing to your database in isqlw and doing this select
id, name from syscatalogs where name ='catalog_name'.
Then with this information what you do is this (from a command prompt)
cscript gthrlog.vbs SQL0017900635.6.gthr
You will get a lot of output, but it will tell you on which row(s) the
indexer had a problem. Then what you have to do is have a look at that row
and see if you can figure out what the problem is with the data in that row
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
>I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
> http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
> but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
> slightly
> lost now
> Many thanks
> Chris
|||Chris,
I'm assuming that you're using SQL Server 2000 as this feature is new with
SQL Server 2000, but what OS platform is it installed on? Could you post the
full output of -- SELECT @.@.version -- as this is very helpful in
troubleshooting SQL FTS issues!
You should use the datatype of sysname (or char(3) or varchar(4)) with your
column "DocumentDocType". Could you also provide details on what values you
have populated in this column? Furthermore, how exactly did you import the
MS Word documents and what is the language of the text stored in the MS Word
documents?
Additionally, the following FREETEXTTABLE query will not return any results
as the "%" (percent) symbols are ignored by the MSSearch engine (depending
upon the OS platform):
select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
[rank] desc
Instead you should use the following:
declare @.searchTerm varchar(1024)
set @.searchTerm = 'some_valid_search_word_here'
select * from freetexttable(tblDocuments, DocumentValue, @.searchTerm) order
by [rank] desc
Finally, you should review your server's Application event log for any
"Microsoft Search" or MssCi source events (warnings, informational and
errors) to determine why the initial FT Indexing is failing as this is the
only place such errors or warnings are written. Have you or anyone else
changed the SQL Server (MSSQLServer) service account &/or password via
Win2K's Component Services vs. changing this in the Enterprise Manager? If
so, then you should also review KB article 277549 (Q277549) PRB: Unable to
Build Full-Text Catalog After You Modify MSSQLServer Logon Account Through
[NT4.0) Control Panel [or Win2K Component Services] at:
http://support.microsoft.com/default...B;EN-US;277549
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:AB36FF24-EE13-4C7A-B8E5-E110588526CC@.microsoft.com...
> I am trying to setup FTS on a table in my database; the column I am
> interested in FTS'ing is all image datatypes. The data in the colum is
> populated via asp.net; and we store the document type as a mime type e.g.
> 'application/msword' as it makes it easy to get data back out from the
> database.
> My table has teh following columns
> DocumentID UniqueIdentifier,
> DocumentTypeRef int
> DocumentSubject varchar(100)
> DocumentValue image
> DocumentDocType varchar(50)
> DocumentDocType sysname -- added to use FTS would rather use vc(50) field
> DocumentByteSize int
> DocumentFileName varchar(100)
> DocumentCreated datetime
> MailMergeTemplateID uniqueidentifier
> ToDoID uniqueidentifier
> DocumentUploadWaiting bit
> The table has 50 rows in it; in my test system. mostly word documents. I
> have run teh following script to setup fts (and tried the wizard without
> success)
> use Activities_FTS
> exec sp_fulltext_database 'enable'
> exec sp_fulltext_catalog 'FTS', 'create', 'D:\\sqldata'
> exec sp_fulltext_table 'tblDocuments', 'create', 'FTS', 'PK_tblDocuments'
> exec sp_fulltext_column @.tabname = tblDocuments,
> @.colname = DocumentValue,
> @.action = 'dro',
> @.Type_Colname = DocumentDocType2
> exec sp_fulltext_table 'tblDocuments', 'activate'
> exec sp_fulltext_table 'tblDocuments', 'start_full'
> --
> select FULLTEXTCATALOGPROPERTY('FTS', 'Populatestatus')
> select * from freetexttable(tblDocuments,DocumentValue , '%%') order by
> [rank] desc
>
> This is based around the steps which worked successfully from the tutorial
> on this page
>
http://msdn.microsoft.com/library/de...extsearch.asp.
> When i run the index population check it does run for about 30 seconds;
but i
> cannot find out why it is not indexing the documents; or is there another
> query i can use to test this. (My FTS catalog never grows above 1mb)
> If you have any questions please do not hestitate to reply as i am
slightly
> lost now
> Many thanks
> Chris
|||OK,
SQL Server details
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
The documents were all imported using request.file object in asp.net; and
were streamed into a stored procuedre. ASP.net can open these files again
(which is why we are storing the mime type.)
The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
the column that i am referencing as Documenttype for sp_fulltext_column
I have changed the contents of the documentdoctype2 column to be both
'word' & 'msword' to be safe and run the recatalog command (Originally these
were mime types 'application/msword' or similar. In both cases this was the
result;
The application log shows the following result : Event ID 2001
"One or more documents stored in image columns with extension 'word' did not
get full-text indexed because loading the filter failed with error '0x1'.
Note: These documents will not be passed to MSSearch for indexing, and
therefore this failure will not be reflected in the end of crawl summary
statistics."
Though strangely it went on to say
"The end of crawl for project <SQLServer SQL0003400005> has been detected.
The Gatherer successfully processed 50 documents totaling 0K. It failed to
filter 0 documents. 0 URLs could not be reached or were denied access."
Finally there is an event for Master merge has completed on the index.
Thanks for your help
|||You're welcome, Chris,
Thanks for the version info as that does help, especially the fact that you
have SQL Server 2000 SP3 on Win2003! You may want to review all of the below
KB articles, and especially 326502 (Q326502) as it provides coding example
on how to use ASP.NET to load MS Word files via:
Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc")
Response.ContentType = "application/msword"
The most likely cause of the Full Population failing is that only valid MS
Word (doc or .doc) files are supported when properly imported into a SQL
Server 2000 table's column defined with the IMAGE (or BLOB) datatype.
Additionally, by populating the column documentdoctype2 column with both
'word' & 'msword' will not succeed as the MSSearch service does not
recognize these as valid file extensions. See SQL Server 2000 BOL title
"Filtering Supported File Types" - specifically, "Microsoft SQL ServerT
2000 includes filters for these file extensions: .doc, .xls, .ppt, .txt, and
..htm".
I grant you that the second informational MSSearch message text was somewhat
mis-leading, but the key error "One or more documents stored in image
columns with extension 'word' did not get full-text indexed because loading
the filter failed with error '0x1'" indicates the FT Population failure
because the wrong value was placed in the documentdoctype2 column.
258038 (Q258038) HOWTO: Access and Modify SQL Server BLOB Data by Using the
ADO Stream Object
http://support.microsoft.com/?kbid=258038
309158 (Q309158) HOW TO: Read and Write BLOB Data by Using ADO.NET with C#
http://support.microsoft.com/default...b;EN-US;309158
308042 (Q308042) HOW TO: Read and Write BLOB Data by Using ADO.NET with
VB.NET
http://support.microsoft.com/default...b;EN-US;308042
326502 (Q326502) HOW TO: Read and Write BLOB Data by Using ADO.NET Through
ASP.NET
http://support.microsoft.com/?id=326502
Regards,
John
"Chris Hoare" <ChrisHoare@.discussions.microsoft.com> wrote in message
news:066C2D40-1BF5-4B26-821E-69F71F39844C@.microsoft.com...
> OK,
> SQL Server details
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
> The documents were all imported using request.file object in asp.net; and
> were streamed into a stored procuedre. ASP.net can open these files again
> (which is why we are storing the mime type.)
> The column I referenced as DocumentDocType2 is typed as 'sysname.' this is
> the column that i am referencing as Documenttype for sp_fulltext_column
> I have changed the contents of the documentdoctype2 column to be both
> 'word' & 'msword' to be safe and run the recatalog command (Originally
these
> were mime types 'application/msword' or similar. In both cases this was
the
> result;
> The application log shows the following result : Event ID 2001
> "One or more documents stored in image columns with extension 'word' did
not
> get full-text indexed because loading the filter failed with error '0x1'.
> Note: These documents will not be passed to MSSearch for indexing, and
> therefore this failure will not be reflected in the end of crawl summary
> statistics."
> Though strangely it went on to say
> "The end of crawl for project <SQLServer SQL0003400005> has been detected.
> The Gatherer successfully processed 50 documents totaling 0K. It failed to
> filter 0 documents. 0 URLs could not be reached or were denied access."
> Finally there is an event for Master merge has completed on the index.
> Thanks for your help
sql

Full text search in image BLOB

We have a ASP.net 2.0 CMS in which the pages are saved in the database as a
BLOB in the table: ASPNET_PersonalizationAllUsers.
Is there a way to search in these BLOB? I have tried to search with FTS but
cannot find the needed iFilter.
Can you help?When creating a full-text index on a varbinary(max) column (or an image
column in SQL 2000) a separate column must be used to store the type of
content (the file extension) - e.g. ".doc" for Word files or ".html" for HTM
L
files.
What exactly is the problem?
Maybe this might also help:
http://milambda.blogspot.com/2006/0...n-or-bybug.html
ML
http://milambda.blogspot.com/sql

Full text search

Hi,
Is full text search only for image column?
Thanks
No, also the (n)(var)char columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is full text search only for image column?
> Thanks
>
|||Thanks Tibor.
What's the benefit for full text search.
If I run a select statement in sql query, Is this using full text serach
automatically?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> No, also the (n)(var)char columns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
|||You need to use special TSQL language elements to use full text indexes, and there in is the power
of FT indexes. Read in Book sOnline about CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239yPeldbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor.
> What's the benefit for full text search.
> If I run a select statement in sql query, Is this using full text serach
> automatically?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>

Full text search

Hi,
Is full text search only for image column?
ThanksNo, also the (n)(var)char columns.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> Hi,
> Is full text search only for image column?
> Thanks
>[/vbcol]|||Thanks Tibor.
What's the benefit for full text search.
If I run a select statement in sql query, Is this using full text serach
automatically?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> No, also the (n)(var)char columns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]|||You need to use special TSQL language elements to use full text indexes, and
there in is the power
of FT indexes. Read in Book sOnline about CONTAINS, CONTAINSTABLE, FREETEXT
and FREETEXTTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239yPeldbFHA.2288@.TK2MSFTNGP14.phx.gbl...

> Thanks Tibor.
> What's the benefit for full text search.
> If I run a select statement in sql query, Is this using full text serach
> automatically?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>

Full text search

Hi,
Is full text search only for image column?
ThanksNo, also the (n)(var)char columns.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Is full text search only for image column?
> Thanks
>|||Thanks Tibor.
What's the benefit for full text search.
If I run a select statement in sql query, Is this using full text serach
automatically?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
> No, also the (n)(var)char columns.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Is full text search only for image column?
> >
> > Thanks
> >
> >|||You need to use special TSQL language elements to use full text indexes, and there in is the power
of FT indexes. Read in Book sOnline about CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239yPeldbFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Thanks Tibor.
> What's the benefit for full text search.
> If I run a select statement in sql query, Is this using full text serach
> automatically?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:#N4KrddbFHA.2968@.TK2MSFTNGP10.phx.gbl...
>> No, also the (n)(var)char columns.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eeVYADdbFHA.3712@.TK2MSFTNGP09.phx.gbl...
>> > Hi,
>> >
>> > Is full text search only for image column?
>> >
>> > Thanks
>> >
>> >
>

Full text indexing question

Hi,

I am using full text indexing to index an image column where I am storing HTML documents. Currently I have an extra column in my table storing the file extension (htm) so the indexer knows which filter to use for every row.

HTML is the only format I'll be indexing, so is there a way for me to set this somewhere else so I don't have to have a column full of htm values - this seems really wasteful, those htm's add up when there are millions of rows...

Thanks!
RobWhy don't you use two separate tables, one for you image data, and one for your HTML pages. You may consider in the last case to store the HTML pages as text or ntext.|||I don't have any "image data" - just html files stored in an image datatype column. My specific data structure isn't really relevent - is there an alternative to the file extension column for full text indexing (because it will always contain the same value)?

Hope this makes it clearer

Rob

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 not return image column results

Hi!
Desperation has lead me to post. Usually I am able to fix problems by just reading through the newsgroups.
I am working on a full text search for an image column. I have an image column (docData) defined - as image (16) and a docExt column defined as char(4). Here are the results of sp_help_fulltext_columns:
dbo1106102981tbl_DocumentdocNameFormal5NULLNULL1033
dbo1106102981tbl_DocumentdocData7docExt61033
The catalog populates (count 6 - small test table) and application log displays no errors. The FTI will return a search on docNameFormal but not on the image column. I loaded the images in the image column using ADO and Textcopy (as outlined in a previous
post by John).
I am using SQL 2000 with SP3a on Windows 2000 professional.
Thank you!
Suz
Suz,
Is your docExt column that is defined as char(4) nullable?
When you store the normal 3 character file extensions (doc, xls, etc.) do
you also store the "." (dot) as well in the char(4) column?
If you alter this column to be a varchar(3) or varchar(4) and then use "doc"
or ".doc" respectively, and then run a Full Population, do the files get FT
Indexed properly (check the Application event log for "Microsoft Search"
errors) and can you successfully return results from a CONTAINS query using
know words in these files?
Thanks,
John
"Suz" <nightfrost@.hotmail.com> wrote in message
news:3310C0C4-5853-4DBB-8505-1C90279838CC@.microsoft.com...
> Hi!
> Desperation has lead me to post. Usually I am able to fix problems by just
reading through the newsgroups.
> I am working on a full text search for an image column. I have an image
column (docData) defined - as image (16) and a docExt column defined as
char(4). Here are the results of sp_help_fulltext_columns:
> dbo 1106102981 tbl_Document docNameFormal 5 NULL NULL 1033
> dbo 1106102981 tbl_Document docData 7 docExt 6 1033
> The catalog populates (count 6 - small test table) and application log
displays no errors. The FTI will return a search on docNameFormal but not on
the image column. I loaded the images in the image column using ADO and
Textcopy (as outlined in a previous post by John).
> I am using SQL 2000 with SP3a on Windows 2000 professional.
> Thank you!
> Suz
|||John,
Thanks so much for your quick reply. The char field was not nullable. I changed the field to varchar and nullable and the Contains query worked!!
Thanks again!
Suz

FTS is accent sensitive?

Hi,
I'm planning to store some romanian documents in SQL image. I understand
that I have to use neutral language.
What I need to know if FTS is accent sensitive: some peoples writes
"acas"(means home), others simply "acasa".
The query "acasa" returns "acasa" and "acas" or only "acasa".
Sorry for my bad english..
Thank you.
Best regards,
Emil Mustea
It is not accent sensitive, or accent aware. searches on "acas" will not
match on row containing "acasa".
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:%239GG17WKEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm planning to store some romanian documents in SQL image. I understand
> that I have to use neutral language.
> What I need to know if FTS is accent sensitive: some peoples writes
> "acas"(means home), others simply "acasa".
> The query "acasa" returns "acasa" and "acas" or only "acasa".
> Sorry for my bad english..
> Thank you.
> Best regards,
> Emil Mustea
>
|||> It is not accent sensitive, or accent aware. searches on "acas" will not
> match on row containing "acasa".
So it's true: searches on "acasa" will return both "acasa" and
"acas".Correct?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uZbXCiYKEHA.3316@.tk2msftngp13.phx.gbl...
> It is not accent sensitive, or accent aware. searches on "acas" will not
> match on row containing "acasa".
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:%239GG17WKEHA.2576@.TK2MSFTNGP12.phx.gbl...
>
|||Emil,
The answer to your question for SQL Server 2000 (RTM to current SP3a) is no.
I've tested this with both an accent sensitive database and an accent
insensitive database collation using the same data and same server
configuration:
select TextCol, VarcharCol, CharCol from FTSAccent
/*
TextCol VarcharCol CharCol
-- -- --
Kahla HalfPipe jam caf cafe
Kahlua HalfPipe jam cafe cafe
Halfpipe Jam? classic-recipes CD-R
Halfpipe Jam classic recipes CD R
*/
-- Accent testing for "accent insensitive" results
select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
non-accented word
-- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
jam" as this database is accent-Insensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
select TextCol from FTSAccent where contains(TextCol,'Kahla') -- accented
word
-- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
jam" as this database is accent-Insensitive
-- Actual Results : 1 row - "Kahla HalfPipe jam"
-- Accent specific testing for "accent sensitive" results...
select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
non-accented word
-- Expected Returns: 1 row - "Kahlua HalfPipe jam" and NOT "Kahla HalfPipe
jam" as this database is accent-sensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
select TextCol from FTSAccent where contains(TextCol,'Kahla') -- accented
word
-- Expected Returns: 1 row - "Kahla HalfPipe jam" and NOT "Kahlua HalfPipe
jam" as this database is accent-sensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
As you can see the actual results differs from the expected results with
both accent sensitive and accent insensitive database collations and for the
accent insensitive database collation, only the accented or non-accented
search word was returned, but not both. This may or may not be fixed in a
future service pack for SQL Server 2000.
Additionally, for SQL Server 2005 (codename Yukon) will support accent
sensitive or insensitive fulltext search via new T-SQL:
CREATE FULLTEXT CATALOG fulltext_catalog_identifier ON FILEGROUP
filegroup_identifier
IN PATH <root path> WITH ACCENT SENSITIVE | INSENSITIVE AS DEFAULT
Regards,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:OP22HkgKEHA.3392@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
not[vbcol=seagreen]
> So it's true: searches on "acasa" will return both "acasa" and
> "acas".Correct?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:uZbXCiYKEHA.3316@.tk2msftngp13.phx.gbl...
not[vbcol=seagreen]
understand
>
|||Thank you for your detailed reply.
In Romanian we have a 2 letters with accents in the bottom of the letter
""(means "sh" in english) ""(it's sounds like zz in Pizza) . These letters
will be considered accented?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uLIxb5hKEHA.3216@.tk2msftngp13.phx.gbl...
> Emil,
> The answer to your question for SQL Server 2000 (RTM to current SP3a) is
no.
> I've tested this with both an accent sensitive database and an accent
> insensitive database collation using the same data and same server
> configuration:
> select TextCol, VarcharCol, CharCol from FTSAccent
> /*
> TextCol VarcharCol CharCol
> -- -- --
--
> --
> Kahla HalfPipe jam caf cafe
> Kahlua HalfPipe jam cafe cafe
> Halfpipe Jam? classic-recipes CD-R
> Halfpipe Jam classic recipes CD R
> */
> -- Accent testing for "accent insensitive" results
> select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
> non-accented word
> -- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
> jam" as this database is accent-Insensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> select TextCol from FTSAccent where contains(TextCol,'Kahla') --
accented
> word
> -- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
> jam" as this database is accent-Insensitive
> -- Actual Results : 1 row - "Kahla HalfPipe jam"
>
> -- Accent specific testing for "accent sensitive" results...
> select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
> non-accented word
> -- Expected Returns: 1 row - "Kahlua HalfPipe jam" and NOT "Kahla
HalfPipe
> jam" as this database is accent-sensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> select TextCol from FTSAccent where contains(TextCol,'Kahla') --
accented
> word
> -- Expected Returns: 1 row - "Kahla HalfPipe jam" and NOT "Kahlua
HalfPipe
> jam" as this database is accent-sensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> As you can see the actual results differs from the expected results with
> both accent sensitive and accent insensitive database collations and for
the
> accent insensitive database collation, only the accented or non-accented
> search word was returned, but not both. This may or may not be fixed in a
> future service pack for SQL Server 2000.
> Additionally, for SQL Server 2005 (codename Yukon) will support accent
> sensitive or insensitive fulltext search via new T-SQL:
> CREATE FULLTEXT CATALOG fulltext_catalog_identifier ON FILEGROUP
> filegroup_identifier
> IN PATH <root path> WITH ACCENT SENSITIVE | INSENSITIVE AS DEFAULT
> Regards,
> John
>
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:OP22HkgKEHA.3392@.TK2MSFTNGP10.phx.gbl...
> not
> not
> understand
>
|||You're welcome, Emil,
Would you be able to provide the ascii code for these letters from the
Romanian code page?
For example for the english accented letter: SELECT ascii('') -- returns:
224
Thanks,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:OO2gNBwKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Thank you for your detailed reply.
> In Romanian we have a 2 letters with accents in the bottom of the letter
> ""(means "sh" in english) ""(it's sounds like zz in Pizza) . These
letters[vbcol=seagreen]
> will be considered accented?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uLIxb5hKEHA.3216@.tk2msftngp13.phx.gbl...
> no.
> -- -- --
> --
> accented
> HalfPipe
> accented
> HalfPipe
> the
a[vbcol=seagreen]
will[vbcol=seagreen]
will[vbcol=seagreen]
writes
>
|||= 186
= 254
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ez5dgUwKEHA.1392@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Emil,
> Would you be able to provide the ascii code for these letters from the
> Romanian code page?
> For example for the english accented letter: SELECT ascii('') --
returns:[vbcol=seagreen]
> 224
> Thanks,
> John
>
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:OO2gNBwKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> letters
is[vbcol=seagreen]
> -- -- --
HalfPipe[vbcol=seagreen]
HalfPipe[vbcol=seagreen]
with[vbcol=seagreen]
for[vbcol=seagreen]
non-accented[vbcol=seagreen]
in
> a
> will
> will
> writes
>
|||Emil,
Yes, I believe that the ascii code values above 127 are "extended character"
and are treated as accented characters.
The best way to confirm this is to put Romanian words that contain these
accented letters in a SQL table defined with one of the following datatypes:
Nvarchar, Nchar, or NText. Then create a FT Index on this column using the
Neutral "Language for Word Breaker" and the confirm that Romanian words that
contain these accented letters, are returned together with similar
non-accented letters using CONTAINS or FREETEXT. "The proof is always in
the pudding" - to paraphrase an old English saying .. <G>
Regards,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:eO7GnM9KEHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> = 186
> = 254
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ez5dgUwKEHA.1392@.TK2MSFTNGP09.phx.gbl...
> returns:
letter[vbcol=seagreen]
SP3a)[vbcol=seagreen]
> is
accent[vbcol=seagreen]
> -- -- --
> HalfPipe
> HalfPipe
> with
> for
> non-accented
> in
accent
>

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