Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help.
"Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>
|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help."Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-bina
ry.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> the
> Sub
> return
> have
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'
criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyw
ord,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004sql

Full Text Table and multiple tables

I have a four join database - category as the one and the Subcategory as the
many and so on
Category
SubCategory
Announcement
SubAnnouncement
I have full text indexes on descriptive columns in the Announcement and Sub
Announcement. I would like to use a containstable or freetexttable to return
all rows in either the Announcement or SubAnnouncement that contain a
keyword but pull out the corresponding rows the subcategory and category
table. I can do the join and I can do the FreeTextTable on one table. I have
drawn a blank on putting the two together. Can anyone help."Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> I have a four join database - category as the one and the Subcategory as
the
> many and so on
> Category
> SubCategory
> Announcement
> SubAnnouncement
> I have full text indexes on descriptive columns in the Announcement and
Sub
> Announcement. I would like to use a containstable or freetexttable to
return
> all rows in either the Announcement or SubAnnouncement that contain a
> keyword but pull out the corresponding rows the subcategory and category
> table. I can do the join and I can do the FreeTextTable on one table. I
have
> drawn a blank on putting the two together. Can anyone help.
Not sure what you're after, but CONTAINSTABLE returns a KEY column that
references the primary key of the table indexed.
SELECT *
FROM
Category AS c
JOIN Announcement AS a
ON c.catID = a.catID
JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
ON a.announceID = d.KEY
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does. I would have thought the join in the first table would bring in
the Category information for that row.
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> > I have a four join database - category as the one and the Subcategory as
> the
> > many and so on
> >
> > Category
> > SubCategory
> > Announcement
> > SubAnnouncement
> >
> > I have full text indexes on descriptive columns in the Announcement and
> Sub
> > Announcement. I would like to use a containstable or freetexttable to
> return
> > all rows in either the Announcement or SubAnnouncement that contain a
> > keyword but pull out the corresponding rows the subcategory and category
> > table. I can do the join and I can do the FreeTextTable on one table. I
> have
> > drawn a blank on putting the two together. Can anyone help.
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004
>|||Unfortunately the join returns no rows e.g.
SELECT *
FROM
tblSubCategory AS s
JOIN tblAnnouncements AS a
ON s.[ID] = a.[ID]
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This returns no rows
SELECT *
FROM
tblAnnouncements AS a
JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
ON a.[ID] = d.[KEY]
This does.
I would have thought the join in the first query would bring in the
Category information for that row. Regards, Chris. Also any idea how
to use the contains table on tblAnnouncements and/or its subtable(
there is a subtable called tblAnnouncementData)
"Bob Simms" <bob_simms@.somewhere.com> wrote in message news:<9VZcc.35498$Id.15366@.news-binary.blueyonder.co.uk>...
> "Chris Kennedy" <nospam@.nospam.co.uk> wrote in message
> news:%23qwsWoNHEHA.3952@.TK2MSFTNGP10.phx.gbl...
> > I have a four join database - category as the one and the Subcategory as
> the
> > many and so on
> >
> > Category
> > SubCategory
> > Announcement
> > SubAnnouncement
> >
> > I have full text indexes on descriptive columns in the Announcement and
> Sub
> > Announcement. I would like to use a containstable or freetexttable to
> return
> > all rows in either the Announcement or SubAnnouncement that contain a
> > keyword but pull out the corresponding rows the subcategory and category
> > table. I can do the join and I can do the FreeTextTable on one table. I
> have
> > drawn a blank on putting the two together. Can anyone help.
> Not sure what you're after, but CONTAINSTABLE returns a KEY column that
> references the primary key of the table indexed.
> SELECT *
> FROM
> Category AS c
> JOIN Announcement AS a
> ON c.catID = a.catID
> JOIN CONTAINSTABLE(Announcement,description,'criterea') AS d
> ON a.announceID = d.KEY
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||"Chris Kennedy" <chrisknospam@.cybase.co.uk> wrote in message
news:uQ2c1sTHEHA.3656@.tk2msftngp13.phx.gbl...
> Unfortunately the join returns no rows e.g.
> SELECT *
> FROM
> tblSubCategory AS s
> JOIN tblAnnouncements AS a
> ON s.[ID] = a.[ID]
> JOIN CONTAINSTABLE(tblAnnouncements,orderkeyword,'boy') AS d
> ON a.[ID] = d.[KEY]
You are joining s and a on the same name column. If they are both the
primary key of the respective tables then you are going to get unpredictable
results. You want the s.[ID] column to equal the foreign key in table a
e.g. s.[id] = a.catid
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Full text Substring search not working

I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.

If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;

But it works if i do
select * from testtable where contains(description,'"Exting*"') ;

The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.SQL Server doesn't support leading wildcards. Therefore your first query will be searching on "xting*" explaining why no results are returned.

I'm not sure why MS does this. Perhaps request it for SQL2008!!

HTH.

|||Doign a short talk with a fellow MVP SimonS, this is due to the Wordbreakers which are language specified. They can only chop the words according to known word breaking settings. So it depends whether a compound word can be broken into multiple ones. Searching within the word is that case not possible, searching for the words within a compound is possible if the wordbreaker can identity the appropiate individual words.

Jens K. Suessmeyer

http://www.sqlserver2005.de

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 Question.

Does anyone know if full text search has a problem with numbers?

I have a text field in a table that has been full text indexed. Some of the data in some of the fields start with numbers.
For Example
"123p45 the bla bla bla"
When I run a standard SELECT statement on LIKE '%p4%' it returns the record.
If I run a full text query using CONTAINS(fieldname,'"*p4*"') it returns nothing.
After much head banging I was able to get the CONTAINS to work when I used *123p4*
I figured since there are alpha characters after the p4 the * works however since there are no preceeding alpha characters before the p4 it fails.
I would like to be able to do a "full text" search for the "p4" and get the record above.

Any feedback would be appreciated..

Hello,

You can take a look here

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.databasejournal.com/features/mssql/article.php/1438211

http://www.sql-server-performance.com/full_text_search.asp

|||

These articles explain how to set up a full text index. I already have it set up.
I just want to know if there is any reason why when I search for the p4 listed above
using a CONTAINS select it returns nothing.

Full text search performance problem

Hi,
We are using Full Text Search on SQL 2000. The search performance is
fine.
Our problem is that when a record is added to the table, the average
time it takes before we can find it with a CONTAINS is 20 seconds.
This is with only one user connected.
The tables has only 200000 rows. The field is a VARCHAR of less than
200 characters.
I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
fast disks. If the exact spec is important I can find out.
Thanks for your help.
Sylvain
Sylvain,
Are you using "Change Tracking" with "Update Index in Background" and
concerned that once a record (row) is added that it takes 20 seconds before
a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
the record (row) where search_word exists?
If not, is your concern more about the total time it takes to return a
results using the above FTS query? If so, you may want to use CONTAINSTABLE
and the Top_N_Rank parameter, for example:
use Northwind
go
SELECT e.EmployeeID, e.LastName, ct.[RANK]
FROM Employees AS e
JOIN CONTAINSTABLE(Employees, Notes, 'French',10) AS ct ON e.EmployeeID
= ct.[KEY]
ORDER BY ct.[RANK] DESC
Note, the value 10 in the above query as this represents the Top N (10) by
Rank value. Using this parameter on containstable or freetexttable can
improve the FTS query performance, but you should experiment with the value
as if you set it too low, you can miss some results, see KB article 240833
(Q240833) "FIX: Full-Text Search Performance Improved via Support for TOP"
at
http://support.microsoft.com//defaul...b;EN-US;240833 for more
info on this.
You should also review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well.
Regards,
John
"Sylvain Hamel" <redhotsly@.hotmail.com> wrote in message
news:4716a77b.0408311303.531320fa@.posting.google.c om...
> Hi,
> We are using Full Text Search on SQL 2000. The search performance is
> fine.
> Our problem is that when a record is added to the table, the average
> time it takes before we can find it with a CONTAINS is 20 seconds.
> This is with only one user connected.
> The tables has only 200000 rows. The field is a VARCHAR of less than
> 200 characters.
> I don't know the exact spec of the server but its a 2CPU, 4GB RAM with
> fast disks. If the exact spec is important I can find out.
> Thanks for your help.
> Sylvain
|||John,

> Are you using "Change Tracking" with "Update Index in Background" and
Yes.

> concerned that once a record (row) is added that it takes 20 seconds before
> a SELECT * from FT-enable_table where CONTAINS(*,'search_word') will return
> the record (row) where search_word exists?
This is exactly my problem. Within the first 20 seconds, the SELECT return
zero rows. After, it works fine and it's fast.
Regards,
Sylvain
|||Hi Sylvain,
I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
on this issue along with a repro (see the attached SQL script file) sometime
ago and have pinged him since with no reply <sigh>... While I'm sure he's
busy, I had hoped to get a better response on this issue from Microsoft...
Basically, SQL Server 2000 should push modified rows to the un-documented
system table sysfulltextnotify and then to the MSSearch controlled FT
Catalog for updating. I've been told that this push should happen within 1
second, but can lag depending upon other activity on the server as well as
within SQL Server, but 20 seconds is much longer than my repro
demonstrates...
The attached SQL script file demonstrates that even with inserting one row
can take up to 5 to 7 seconds with no other activity on the server and no
other activity within SQL Server. I had hoped to get this addressed and
hopefully filed as a bug, but that did not happen and may not happen as the
time to post the updated row to the MSSearch FT Catalog has never been
documented and so no guaranteed posting time was ever documented...
If you want I can pursue this again with the FTS Dev Lead, if this is
affecting your production server's FT Indexing and FT Search performance and
therefore leading to dissatisfaction for your users. Let me know if you want
me to pursue this.
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...[vbcol=seagreen]
> John,
>
> Yes.
before[vbcol=seagreen]
return
> This is exactly my problem. Within the first 20 seconds, the SELECT return
> zero rows. After, it works fine and it's fast.
> Regards,
> Sylvain
>
begin 666 FTS_with_Change_Tracking_pooling.sql
M#0HM+0E&:6QE;F%M93H)1E137W=I=&A?0VAA;F=E7U1R86-K:6YG7W!O;VQI
M;F<N<W%L#0HM+0E0=7)P;W-E.@.E4;R!T97-T($9422!A;F0@.1E13('=I=&@.@.
M0VAA;F=E('1R86-K:6YG(&%N9"!U<&1A=&4@.:6YD97@.@.:6X@.8F%C:V=R;W5N
M9"!A;F0@.<WES9G5L;'1E>'1N;W1I9GD@.<&]O;&EN9PT*+2T)16YV:7)O;65N
M=#H)57-I;F<@.4U%,(#(P,# @.4U S(&]N(%=I;F1O=W,@.4V5R=F5R(#(P,#,@.
M4E1-#0HM+0E-;V1I9FEE9#H),3$Z,S @.04T@.,R\Q,2\R,# T#0H-"@.T*#0IU
M<V4@.<'5B<PT*9V\-"G-E;&5C="! 0'9E<G-I;VX@.+2T@.36EC<F]S;V9T(%-1
M3"!397)V97(@.(#(P,# @.+2 X+C P+C<V," H4U S*2!A;F0@.."XP,"XU,S0@.
M*%-0,BD-"F=O#0II9B!E>&ES=',@.*'-E;&5C=" J(&9R;VT@.<WES;V)J96-T
M<R!W:&5R92!I9" ](&]B:F5C=%]I9"@.G1E135&%B;&4G*2D-"B @.9')O<"!T
M86)L92!&5%-486)L90T*9V\-"D-214%412!404),12!&5%-486)L92 H#0H@.
M($ME>4-O; EI;G0@.241%3E1)5%D@.*#$L,2D@.3D]4($Y53$P@.#0H@.(" @.0T].
M4U1204E.5"!&5%-486)L95])1%@.@.4%))34%262!+15D@.0TQ54U1%4D5$+ T*
M("!497AT0V]L"71E>'0@.3E5,3"P-"B @.26UA9V5#;VP):6UA9V4@.3E5,3"P-
M"B @.17AT0V]L"6-H87(H,RD@.3E5,3"P@.+2T@.8V%N(&)E('-Y<VYA;64-"B @.
M5&EM95-T86UP0V]L"71I;65S=&%M<"!.54Q,( T**0T*9V\-"@.T*+2T@.5')U
M;F-A=&4@.1&%T82P@.:68@.;F5C97-S87)Y+BX-"BTM(%1254Y#051%(%1!0DQ%
M($944U1A8FQE#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G36%R>2!H860@.
M82!L:71T;&4@.;&%M8B<L(')E<&QI8V%T92@.G,'@.Q)RPQ,# I+" G='AT)RP@.
M3E5,3"D-"DEN<V5R="!&5%-486)L92!V86QU97,@.*"=->2!D;V<@.8F%R:V5D
M(&QO=61L>2<L(')E<&QI8V%T92@.G,'@.W)RPS,# I+" G='AT)RP@.3E5,3"D@.
M#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G5&AE(&9O>"!J=6UP960@.; W9E
M<B!T:&4@.9F5N8V4G+"!R97!L:6-A=&4H)S!X."<L.# P*2P@.)W1X="<L($Y5
M3$PI#0I);G-E<G0@.1E135&%B;&4@.=F%L=65S("@.G22!D;W9E(&EN=&\@.=&AE
M('!O;F0G+"!R97!L:6-A=&4H)S!X,B<L,3 P*2P@.)W1X="<L($Y53$PI#0H-
M"@.T*+2T@.1G5L;"U497AT($EN9&5X:6YG("A&5$DI('-E='5P('5S:6YG(")#
M:&%N9V4@.5')A8VMI;F<B(&%N9" B57!D871E($EN9&5X(&EN($)A8VMG<F]U
M;F0B+BXN#0IE>&5C('-P7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G
M+"=C<F5A=&4G#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L
M)V-R96%T92<L)T944T-A=&%L;V<G+"=&5%-486)L95])1%@.G#0IE>&5C('-P
M7V9U;&QT97AT7V-O;'5M;B G1E135&%B;&4G+"=497AT0V]L)RPG861D)R M
M+2!A<W-U;65S($5N9VQI<V@.@.;&%N9W5A9V4@.=&5X= T*97AE8R!S<%]F=6QL
M=&5X=%]C;VQU;6X@.)T944U1A8FQE)RPG26UA9V5#;VPG+"=A9&0G+" P># T
M,#DL("=%>'1#;VPG(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN
M(&%N9"!C;VYF:7)M('9I82 B36EC<F]S;V9T(%-E87)C:"(@.<V]U<F-E(&5N
M=')I97,@.:6X@.=&AE('-E<G9E<B=S($%P<&QI8V%T:6]N($5V96YT(&QO9RXN
M+@.T*15A%0R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?
M8VAA;F=E7W1R86-K:6YG)PT*+RH@.+2T@.<F5T=7)N<R H;VX@.4U%,(#(P,# @.
M4U S*0T*5V%R;FEN9SH@.5&%B;&4@.)T944U1A8FQE)R!D;V5S(&YO= "!H879E
M('1H92!O<'1I;VX@.)W1E>'0@.:6X@.<F]W)R!E;F%B;&5D(&%N9"!H87,@.9G5L
M;"UT97AT(&EN9&5X960@.8V]L=6UN<R -"G1H870@.87)E(&]F('1Y<&4@.:6UA
M9V4L('1E>'0L(&]R(&YT97AT+B!&=6QL+71E>'0@.8VAA;F=E('1R86-K:6YG
M(&-A;FYO="!T<F%C:R!74DE4151%6%0@.;W(@.55!$051%5$585"!O< &5R871I
M;VYS( T*<&5R9F]R;65D(&]N('1H97-E(&-O;'5M;G,N#0HJ+PT*15A%0R!S
M<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G4W1A<G1?8F%C:V=R;W5N
M9%]U<&1A=&5I;F1E>"<-"F=O#0H-"@.T*+2T@.16YV:7)O;65N=#H@.4U%,(#(P
M,# @.4U R(&]N(%=I;C)+("8@.4U%,(#(P,# @.4U S(&]N(%=I;C(P,#,-"@.T*
M+2T@.4F5P;R!I;G-T<G5C=&EO;G,Z#0HM+2 Q+B!#:&%N9V4@.=&AE(%=!251&
M3U(@.1$5,05D@.)S P.C P.C!8)R!8('9A;'5E+"!S=&%R=&EN9R!A=" Q('-E
M8V]N9"!A;F0@.:6YC<F5M96YT:6YG('5N=&EL('1H92!S96QE8W0@.< F5T=7)N
M<R Q(')O=RX-"BTM(#(N($AI9VAL:6=H="!T:&4@.8F5L;W<@.8V]D92!A;F0@.
M97AE8W5T92X-"@.T*+2T@.26YS97)T(&%D9&ET:6]N86P@.86YD('5N:7%U92!W
M;W)D('=I=&@.@.0U0@.86YD(%5):4(@.96YA8FQE9" M($1E;&5T92P@.26YS97)T
M(&%N9"!I;F-R96%S92!W86ET9F]R(&1E;&%Y('5N=&EL('-E;&5C=" Q(')O
M=PT*1$5,151%($944U1A8FQE('=H97)E(%1E>'1#;VP@.;&EK9 2 G)71E>'0E
M)R M+2!D96QE=&4@.:6YS97)T960@.<F]W+"!I9B!N96-E<W-A<GD-"F=O#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#4G+"!.54Q,+" G9&]C)RP@.3E5,3"D@.#0IG;PT*5T%)5$9/4B!$14Q!62 G
M,# Z,# Z,#4G("TM('=A:70@.9F]R('@.@.<V5C;VYD<R!A;F0@.=&AE;B!R=6X@.
M4U%,($944R!Q=65R>2!T;R!G970@.97-T:6UA=&4@.;V8@.<F5T=7)N('1I;64-
M"G-E;&5C="!497AT0V]L(&9R;VT@.1E135&%B;&4@.=VAE<F4@.8V]N=&%I;G,H
M5&5X=$-O;"PG=&5S="<I#0HO*B M+2!W:71H(%=!251&3U(@.1$5,05D@.<V5T
M('1O(#4@.<V5C;VYD<SH-"B@.Q(')O=RAS*2!A9F9E8W1E9"D-"B@.Q(')O=RAS
M*2!A9F9E8W1E9"D-"@.T*5&5X=$-O;" @.(" @.(" @.(" @.(" @.(" @.(" @.(" @.
M(" @.(" @.(" @.(" @.(" @.(" @.(" @.#0HM+2TM+2TM+2TM+2TM+2TM+2TM+2TM
M+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2TM+2 -"E1E<W0@.5$585"!$871A
M(&9O<B!R;W<@.-0T**#$@.<F]W*',I(&%F9F5C=&5D*0T**B\-"@.T*+2T@.4W5M
M;6%R>3H@.37D@.=&5S=&EN9R!I;F1I8V%T97,@.=&AA="!O;FQY( &%F=&5R(&$@.
M-2!T;R V('-E8V]N9',@.=V%I=&9O<B!D96QA>2!W:6QL('1H92!S96QE8W0@.
M<F5T=7)N(&$@.:&ET(&%N9"!I9B!Y;W4@.:6YC<F5A<V4-"BTM(" @.(" @.(" @.
M('1H92!W86ET9F]R(&1E;&%Y('1O(#<@.<V5C;VYD<RP@.:70@.<W1I;&P@.;6%Y
M(&YO="!C;VYS:7-T96YT;'D@.<F5T=7)N(#$@.<F]W+B!!9&1I=&EO;F%L;'DL
M('-O;65T:6UE(&5V96X@.:68@.>6]U(&1O('5S90T*+2T)(" @.(#4@.<V5C;VYD
M<RP@.86YD(&EF('EO=2!R92UR=6X@.=&AE(&AI9VAL:6=H=&5D( '%U97)Y+"!Y
M;W4@.;6%Y('-T:6QL(&YO="!G970@.82!R971U<FX@.;V8@.,2!R;W<N#0H-"@.T*
M#0H-"B\J("TM($-L96%N('5P.B!5;BUC;VUM96YT('1H92!F;VQL;W=I;F<@.
M8V]D92!T;R!R96UO=F4@.=&AE($94($-A=&%L;V<L('1A8FQE<PT*=7-E(%!U
M8G,-"F=O#0IE>&5C('-P7V9U;&QT97AT7W1A8FQE("=&5%-486)L92<L)V1R
M;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L;V<@.)T944T-A=&%L;V<G+"=D
C<F]P)PT*9')O<"!T86)L92!&5%-486)L90T*9V\-"BHO#0H`
`
end
|||John, we have opened a call with microsoft regarding this issue. I'll let you
know. Thank you for your help.
Sylvain
"John Kane" wrote:

> Hi Sylvain,
> I suspected as much... I've emailed Microsoft (the current SQL FTS Dev Lead)
> on this issue along with a repro (see the attached SQL script file) sometime
> ago and have pinged him since with no reply <sigh>... While I'm sure he's
> busy, I had hoped to get a better response on this issue from Microsoft...
> Basically, SQL Server 2000 should push modified rows to the un-documented
> system table sysfulltextnotify and then to the MSSearch controlled FT
> Catalog for updating. I've been told that this push should happen within 1
> second, but can lag depending upon other activity on the server as well as
> within SQL Server, but 20 seconds is much longer than my repro
> demonstrates...
> The attached SQL script file demonstrates that even with inserting one row
> can take up to 5 to 7 seconds with no other activity on the server and no
> other activity within SQL Server. I had hoped to get this addressed and
> hopefully filed as a bug, but that did not happen and may not happen as the
> time to post the updated row to the MSSearch FT Catalog has never been
> documented and so no guaranteed posting time was ever documented...
> If you want I can pursue this again with the FTS Dev Lead, if this is
> affecting your production server's FT Indexing and FT Search performance and
> therefore leading to dissatisfaction for your users. Let me know if you want
> me to pursue this.
> Thanks,
> John
>
>
> "Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
> news:5D244B5C-C00B-41E4-B343-9F9CA416D441@.microsoft.com...
> before
> return
>
>
|||You're welcome, Sylvain,
Yes, please do let me know what you find out from Microsoft!
Thanks,
John
"Sylvain" <Sylvain@.discussions.microsoft.com> wrote in message
news:8B6465AF-A649-4014-A066-44C146A81375@.microsoft.com...
> John, we have opened a call with microsoft regarding this issue. I'll let
you[vbcol=seagreen]
> know. Thank you for your help.
> Sylvain
> "John Kane" wrote:
Lead)[vbcol=seagreen]
sometime[vbcol=seagreen]
he's[vbcol=seagreen]
Microsoft...[vbcol=seagreen]
un-documented[vbcol=seagreen]
1[vbcol=seagreen]
as[vbcol=seagreen]
row[vbcol=seagreen]
no[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
want[vbcol=seagreen]
and[vbcol=seagreen]
return[vbcol=seagreen]

Full Text Search on Two columns

How do I use FREETEXTTABLE on two columns in a table but rank by only one
column.
For example I have two columns News_Summary,News_Content
In this case the user can specify during search if he wants to search
News_Summary or the News_Content or Both.There also other columns in the
tables.
In any case I want to rank only by News_Content.
What is the easiest way to accomplish this.
This is the Query I am using right now,How do I add one more column to this.
I dont want to use mutlple stored procedures.
SELECT *
FROM news AS FT_TBL INNER JOIN
FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS KEY_TBL
ON FT_TBL.news_id= KEY_TBL.[KEY]
WHERE(
expire_dtm>getdate()
)
"Bruce" wrote:

> How do I use FREETEXTTABLE on two columns in a table but rank by only one
> column.
> For example I have two columns News_Summary,News_Content
> In this case the user can specify during search if he wants to search
> News_Summary or the News_Content or Both.There also other columns in the
> tables.
> In any case I want to rank only by News_Content.
> What is the easiest way to accomplish this.
>
|||Bruce,
You can use the following examples of multiple FREETEXTTABLE for multiple
columns in the same table (Employees). Note the "AND" condition between the
Primary Keys, you can also change to an "OR" condition.
-- multiple columns from one FT enable table using FREETEXTTABLE in the
Northwind database
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID AND
B.[KEY] = e.EmployeeID
use pubs
go
SELECT pub_id, pr_info FROM pub_info
WHERE FREETEXT(pr_info, ' "publish*" ') AND FREETEXT(pub_id, ' "0736*" ')
Yes, you can search as many columns as necessary, but be aware that each FTS
predicate (CONTAINS* or FREETEXT*) is a "round-trip" to the FT Catalog and
depending upon the size of your FT Catalog (based upon the number of rows in
your FT enable table as well as the number of non-noise unique words), your
FTS query performance may be affected.
Regards,
John
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:BD072BA9-6A90-4C72-B61F-35DC691EC044@.microsoft.com...
> This is the Query I am using right now,How do I add one more column to
this.
> I dont want to use mutlple stored procedures.
> SELECT *
> FROM news AS FT_TBL INNER JOIN
> FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS
KEY_TBL[vbcol=seagreen]
> ON FT_TBL.news_id= KEY_TBL.[KEY]
> WHERE(
> expire_dtm>getdate()
> )
> "Bruce" wrote:
one[vbcol=seagreen]
sql

Full text search on TEXT field

I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

YusufBesides,
I insert text in text datatype field and returs true values...

The problem occours when insert only MS Word or MS Excel etc files.

Full Text Search 'Near'

using SQL server 2005 I have a table which has full text search index enabled

when I run a search using the 'Near' option it returns rows which meet the criteria, how ever the criteria are not 'near' each other they are several hundred words apart.

What is the problem

How near is near

thanks in advance

Glyn

Hi,

The NEAR operator is used to find words which are in close proximity to one another. For example, consider the following three rows in a table:

TextValue

==========

The product being used is SQL Server 2005 Enterprise Edition

We are using full text search in SQL Server 2005 Enterprise Edition

We are performing a test with full text search in SQL Server 2005 Enterprise Edition

Now if I have a query of the following type:

select TextValue

from dbo.sample_tbl

where contains (TextValue, 'search NEAR Server')

This will return the 2nd and the 3rd rows as both the words are approximately close to each other. The NEAR operator returns those rows which have both the words approximately close to each other. When several proximity terms are chained, all proximity terms must be near each other.

|||

Thanks but the search I have is not working

I have a text string

'FTS_STRING_SAMPLE1 can control instantiation to suit the system environment. Relying on delayed instantiation minimizes the amount of memory required by the application, although it might trigger many server requests when properties are referenced.

Instance classes, objects that represent real database objects, can exist in three levels of instantiation. These are minimal-instantiated (only the minimal required properties are read in one block), partially instantiated (all the properties that use a relatively large amount of memory are read in one block), and fully instantiated. Un-instantiated and fully instantiated are the traditional states of instantiation. The partially instantiated state increases efficiency because a partially instantiated object does not contain values for the full set of object properties. Partial instantiation is the default state for an object that is not directly referenced. When one of these properties is referenced, a fault is generated that prompts a full instantiation of the object FTS_STRING_SAMPLE2.'

SELECT *
FROM FTS_TEST_TABLE
WHERE CONTAINS(FTS_TEST_TEXT, 'FTS_STRING_SAMPLE1 NEAR FTS_STRING_SAMPLE2');

It returns the row, however we believe it shouldn't

Glyn

Full Text Search Indexing HTML - does the filter expect certain tags to be present as standard?

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @.SearchWord nvarchar(256)
SET @.SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @.SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).

Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):

Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:

Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.

etc.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>

I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?I've further isolated what the problem is and have started a new thread to request more specific help...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1844786&SiteID=17

Full Text Search Index build is running too long

I did a DTS insert from a .CSV file and loaded 10 million records into a
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.

Full text search in SQL Server 2000

I have one question about full text search in SQl
Server 2000/2005.
I have one table which has 7 text columns. I want to
retrieve all rows that have the search word in either
of those 7 columns.
I want to know if there is a way to add weight to
those columns so SQL Server can better rank the result
set. For example, column 1 has weight 1 and column 2
has weight 0.1. So records that have search word in
column 1 will rank higher that those having search
word only in column 2.

Thanks a lot,

Baoxin

Unfortunately, There is no standard way to do this.
You can have to play around with regular expressions..

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 help

Hi I have a full text index on my product table.

When I do a search for Record, it returns all values for Record and Records.

Now If I do a search with a spelling mistake say Recod . it doen't return anything.

How can I get the full text to return my query even if there is a spelling mistake ?

Thanks

My query:

SELECT * From Product
WHERE FREETEXT (description, @.SearchString)

Try using Soundex|||

Hi dear,

You have to use LIKE operator

http://www.devguru.com/technologies/t-sql/7116.asp
http://doc.ddart.net/mssql/sql70/la-lz_2.htm

i hope these links will help you in this regard.

Thank You

Regards,

Muhammad Akhtar Shiekh

|||

Thank you both for your replied.

The thing is you cannot use LIKE or SOUNDEX in a FULL TEXT SEARCH.

|||

Hi Sleb ,

According to your question, If you want to get the full text to return your query even if there's a spelling mistake, you may achieve that through some special algorithm.

1. You may extend your keyword into several combinations.

eg:

bus -> bus array[0]
bsu array[1]
ubs array[2]
usb array[3]
sub array[4]
sbu array[5]

2. Make the select statement

string sqltxt="select * from Product where ";

for(int i=0;i<array.length;i++)
{
if( i==0 )
{
sqltxt+=" description like '% " + array[i] + " %'" ;
}
else
{
sqltxt+=" or description like '% " + array[i] + " %'" ;
}
}

In this way, you could make every combination from the original keyword match the content.

3. Make your intelligent spelling correcting more smart.

By now, you could get every combination from the original keyword, but you are not sure which one is the correct spelling. So we advice that you should build up a data table to record the user's searching behavior. eg:

keyword SubmitTimes
======================
bus 231254
bsu 12
sub 265421
sbu 125

If the user submit the keyword as "bsu", we are easy to know the possible keyword which the user wants is "bus" or "sub" but not "sbu" according to the submit times from other users.

If this does not help you, pls feel free to mark the post as Not Answered and reply. Thanks.

|||

Hi

From what I can see there is 2 methods. Method one is to use software that is available on the web to do spell checks, or you can write your own code to do a spell check. There are many text file dictionaries on the web so you do not have to be concerned about that.

I had a similar problem, but have come to an understanding that if a surfer cannot spell when surfing a website or the web then they should not be using the web. You cannot cater for every possible problem that a user can create.

I use the FREETEXT operator. What I have noticed is that it will find a plural of a word if the search is singular. For example search for "Card" will return card and cards.

I am sure by this example I think that MSSQL can be used to do a spell check. However I am not sure.

I hope this information will help you

Regard

Full text Search doesnt find a specific string

Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.

Im guessing here, but I think it is looking for records having one or more "words" that start with 1 (e.g. 1hs, 1tv, 1rquut). I'm not certain that the process would find just "1").

That's my story and I'm sticking with it.

Regards,

hmscott|||Thanks for your suggestion. Could you do me one more favor and modify the SQL query that I had put up in the previuos post to return the record that I am talking about.sql

Full text search catalog

Hi,
I am using a full-text catalog to perform search inside a 1GB table. I
have created fulltext catalog as usual methods, selected the necessary
fields for the catalog from the table. When I want to make a stress test
over the search fucntion, it deoesnt return any data if I use more then 7
concurrent connections.
I heard that, this was a problem on sql server version 7. But this is a
sql server 2000 version. And from the white paper of full text catalogs, it
says it is able to handle up to 5124 concurrent connections to a fulltext
catalog.
What would be causing this ?
PS: Computer's props: Dual P3 500 Mhz, 1GB Ram, 18 GB SCSI HDD, Win2k
Server, Sql Server 2K Standard Ed. Computer only works for sql server
processes, not other process bothers the computer.
Thanks and Best Regards.
Murtix Van Basten.
Murtix,
When you say a "1GB Table", do you mean 1GB rows or the overall size of this
table? Also, how long are you waiting for the queries to return results and
what is the expected number of results per query? SQL FT Indexing and FT
Search query performance is a function of the total number rows as well as
the expected number of rows returned. Are you using the exact SQL FTS query
in each of the 7 concurrent connection or is each query different? Could you
post an example of your SQL FTS query?
Also, what is the return value for this query: EXEC sp_fulltext_service
'resource_usage'
Regards,
John
"Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in message
news:406a219f_4@.news.athenanews.com...
> Hi,
> I am using a full-text catalog to perform search inside a 1GB table. I
> have created fulltext catalog as usual methods, selected the necessary
> fields for the catalog from the table. When I want to make a stress test
> over the search fucntion, it deoesnt return any data if I use more then 7
> concurrent connections.
> I heard that, this was a problem on sql server version 7. But this is
a
> sql server 2000 version. And from the white paper of full text catalogs,
it
> says it is able to handle up to 5124 concurrent connections to a fulltext
> catalog.
> What would be causing this ?
> PS: Computer's props: Dual P3 500 Mhz, 1GB Ram, 18 GB SCSI HDD, Win2k
> Server, Sql Server 2K Standard Ed. Computer only works for sql server
> processes, not other process bothers the computer.
> Thanks and Best Regards.
> Murtix Van Basten.
>
|||John,
the table has 784.000 rows. Table has 19 columns, and most important
column (BODY) is Long Text type. Only 5 columns are being used in the FTS
indexing. That is one of them.
when I run it as 1 concurrent user, it takes 3-5 seconds to get a
result. I expect not more than 40 results per query. It can be less or none.
I use WAS Tool to run a stress test on the web[age that triggers the query.
And I use same exact parameters for each 7 concurrent connection. When I put
8th one, the return is nothing.
You can see the webpage that triggers the FTS from:
http://www.learnasp.com/search/a.asp...glish&limit=40
The FTS query is:
SET @.statement = 'SELECT TOP ' + @.num_matches + ' ''1000'' As Rank, list_,
messageid_, M.creatstamp_, hdrfrom_, hdrsubject_' + ' FROM messages_ AS M
INNER JOIN lists_ ON M.list_ = lists_.name_ ' + ' WHERE 1=1' + @.add_pred +
@.list_security + @.lang_pred + @.order_by
-- Execute the SELECT statement.
EXEC (@.statement)
messages_ is the table. If you wanna see, I can paste the whole SP.
Regards.
Murtix Van Basten.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uALwGlsFEHA.692@.TK2MSFTNGP09.phx.gbl...
> Murtix,
> When you say a "1GB Table", do you mean 1GB rows or the overall size of
this
> table? Also, how long are you waiting for the queries to return results
and
> what is the expected number of results per query? SQL FT Indexing and FT
> Search query performance is a function of the total number rows as well as
> the expected number of rows returned. Are you using the exact SQL FTS
query
> in each of the 7 concurrent connection or is each query different? Could
you
> post an example of your SQL FTS query?
> Also, what is the return value for this query: EXEC sp_fulltext_service
> 'resource_usage'
> Regards,
> John
>
> "Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in
message
> news:406a219f_4@.news.athenanews.com...
I
7
is
> a
> it
fulltext
>
|||can you paste the entire sp here?
You aren't doing TSQL paging are you?
"Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in message
news:406a6f30_5@.news.athenanews.com...
> John,
> the table has 784.000 rows. Table has 19 columns, and most important
> column (BODY) is Long Text type. Only 5 columns are being used in the FTS
> indexing. That is one of them.
> when I run it as 1 concurrent user, it takes 3-5 seconds to get a
> result. I expect not more than 40 results per query. It can be less or
none.
> I use WAS Tool to run a stress test on the web[age that triggers the
query.
> And I use same exact parameters for each 7 concurrent connection. When I
put
> 8th one, the return is nothing.
> You can see the webpage that triggers the FTS from:
>
http://www.learnasp.com/search/a.asp...glish&limit=40
> The FTS query is:
> SET @.statement = 'SELECT TOP ' + @.num_matches + ' ''1000'' As Rank,
list_,
> messageid_, M.creatstamp_, hdrfrom_, hdrsubject_' + ' FROM messages_ AS M
> INNER JOIN lists_ ON M.list_ = lists_.name_ ' + ' WHERE 1=1' + @.add_pred
+
> @.list_security + @.lang_pred + @.order_by
> -- Execute the SELECT statement.
> EXEC (@.statement)
> messages_ is the table. If you wanna see, I can paste the whole SP.
> Regards.
> Murtix Van Basten.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uALwGlsFEHA.692@.TK2MSFTNGP09.phx.gbl...
> this
> and
as
> query
> you
sp_fulltext_service
> message
table.
> I
test
then
> 7
> is
catalogs,
> fulltext
>
|||Here is the spGeneral:
-- starts here --
CREATE PROCEDURE spGeneral
@.num_matches varchar(4),
@.additional_predicates varchar(5000) = '',
@.order_by_list varchar(500) = '',
@.list_lang varchar(255),
@.lists smallint = 0
AS
BEGIN
DECLARE
@.add_pred varchar(510),
@.lang_pred varchar(255),
@.order_by varchar(510),
@.statement varchar(8000),
@.list_security varchar(100),
@.querystart datetime,
@.queryend datetime
SET NOCOUNT ON
-- The clock is ticking
SET @.querystart = GetDate()
-- List security
IF @.lists = 1
SET @.list_security = ' AND security_ = ''closed'''
ELSE
IF @.lists = 2
SET @.list_security = ' AND security_ = ''private'''
ELSE
IF @.lists = 3
SET @.list_security = ' AND security_ IN (''closed'', ''private'')'
ELSE
IF @.lists = 4
SET @.list_security = ''
ELSE
SET @.list_security = ' AND security_ = ''open'''
-- Check if language specified
IF @.list_lang = ''
SET @.lang_pred = ''
ELSE
BEGIN
IF @.lists = 4
SET @.lang_pred = ' PrimLang_ IN (' + @.list_lang + ')'
ELSE
SET @.lang_pred = ' AND PrimLang_ IN (' + @.list_lang + ')'
END
-- Additional Predicates
IF @.additional_predicates <> ''
BEGIN
SET @.add_pred = ' AND (' + @.additional_predicates + ')'
-- Remove ambiguity
SET @.add_pred = Replace(@.add_pred, "creatstamp_", "M.creatstamp_")
END
ELSE
SET @.add_pred = ''
-- Insert ORDER BY, if needed.
IF @.order_by_list <> ''
BEGIN
SET @.order_by = ' ORDER BY ' + @.order_by_list
-- Remove ambiguity
SET @.order_by = Replace(@.order_by, "creatstamp_", "M.creatstamp_")
END
ELSE
SET @.order_by = ''
SET @.statement = 'SELECT TOP ' + @.num_matches +
' ''1000'' As Rank, list_, messageid_, M.creatstamp_, hdrfrom_,
hdrsubject_' +
' FROM messages_ AS M INNER JOIN lists_ ON M.list_ =
lists_.name_ ' +
' WHERE 1=1' + @.add_pred + @.list_security + @.lang_pred + @.order_by
-- Execute the SELECT statement.
EXEC (@.statement)
-- Time's up!
SET @.queryend = GetDate()
SELECT DateDiff(ms, @.querystart, @.queryend) As ExecTime
SET NOCOUNT OFF
END
GO
-- ends here --
Murtix Van Basten
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ebtBRlyFEHA.3984@.TK2MSFTNGP10.phx.gbl...
> can you paste the entire sp here?
> You aren't doing TSQL paging are you?
> "Murtix Van Basten" <rdagdelenjNOSPAM31@.comcastNOSPAM.net> wrote in
message
> news:406a6f30_5@.news.athenanews.com...
FTS
> none.
> query.
> put
>
http://www.learnasp.com/search/a.asp...glish&limit=40
> list_,
M
@.add_pred
> +
of
results
FT
well
> as
Could
> sp_fulltext_service
> table.
necessary
> test
> then
this
> catalogs,
Win2k
server
>

Full Text Search Availability

If I have a table with a Full Text Search and I do a Full Re-Population
What happens if someone does a Search while it is doing a Full Repopulation?
Is the search delayed until the repopulation is done,
does it only search the rows that have been populated,
does it timeout/error?
something completely different?
My desire is to have 7x24 searching available, even when massive changes
occur to the indexed table each night.
Chuck,
I don't know the answer for sure but you should really ask this in the FTS
news group to have a better chance of a proper answer.
Andrew J. Kelly SQL MVP
"Chuck P" <nospam@.crlf.com> wrote in message
news:DFDB6653-165C-4720-A04E-D1C394AE1C00@.microsoft.com...
> If I have a table with a Full Text Search and I do a Full Re-Population
> What happens if someone does a Search while it is doing a Full
> Repopulation?
> Is the search delayed until the repopulation is done,
> does it only search the rows that have been populated,
> does it timeout/error?
> something completely different?
> My desire is to have 7x24 searching available, even when massive changes
> occur to the indexed table each night.

Full Text Search Availability

If I have a table with a Full Text Search and I do a Full Re-Population
What happens if someone does a Search while it is doing a Full Repopulation?
Is the search delayed until the repopulation is done,
does it only search the rows that have been populated,
does it timeout/error?
something completely different?
My desire is to have 7x24 searching available, even when massive changes
occur to the indexed table each night.Chuck,
I don't know the answer for sure but you should really ask this in the FTS
news group to have a better chance of a proper answer.
Andrew J. Kelly SQL MVP
"Chuck P" <nospam@.crlf.com> wrote in message
news:DFDB6653-165C-4720-A04E-D1C394AE1C00@.microsoft.com...
> If I have a table with a Full Text Search and I do a Full Re-Population
> What happens if someone does a Search while it is doing a Full
> Repopulation?
> Is the search delayed until the repopulation is done,
> does it only search the rows that have been populated,
> does it timeout/error?
> something completely different?
> My desire is to have 7x24 searching available, even when massive changes
> occur to the indexed table each night.

Full Text Search Availability

If I have a table with a Full Text Search and I do a Full Re-Population
What happens if someone does a Search while it is doing a Full Repopulation?
Is the search delayed until the repopulation is done,
does it only search the rows that have been populated,
does it timeout/error?
something completely different?
My desire is to have 7x24 searching available, even when massive changes
occur to the indexed table each night.Chuck,
I don't know the answer for sure but you should really ask this in the FTS
news group to have a better chance of a proper answer.
--
Andrew J. Kelly SQL MVP
"Chuck P" <nospam@.crlf.com> wrote in message
news:DFDB6653-165C-4720-A04E-D1C394AE1C00@.microsoft.com...
> If I have a table with a Full Text Search and I do a Full Re-Population
> What happens if someone does a Search while it is doing a Full
> Repopulation?
> Is the search delayed until the repopulation is done,
> does it only search the rows that have been populated,
> does it timeout/error?
> something completely different?
> My desire is to have 7x24 searching available, even when massive changes
> occur to the indexed table each night.