I have set up a full text search to handle multiple columns searching
for chinese
But the result of the search isn't really what i have expected.
I have setup the catalog to have a chiense word break, and the columns
in the tables are all nvachar
when i do something like
select * from dbo.Table_1 where contains(*, '"<chinese
character>"',language 1082)
the search result is really inconsistent, especially with single
characters.I have also checked that these characters are not in the
noise filter file...
the search result is better when the input is more than a single
characters, but still, somtimes it will not return any result at all.
so, I try to use the "like" statement instead of "contains" to do the
search with the same inputs, and 100% of the time, it returns the
correct result.
does anyone have any experience about things like that? coz I guess
this is a more spcific issue with language. Is there any place that
you guys know of, can offer me some help?
thank you in advance.
On Feb 11, 5:33Xam, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out characters to try figure out which characters go together and which ones should be treated as a single "word". It even pulls out radicals.
> However, try this on the thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on 某人為了商X或個人使用.
> Then try 某人為了商X或個人使 (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
icic
oh Thank you, so there is no way to improve that ?
thank you
|||On Feb 11, 5:33Xam, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out characters to try figure out which characters go together and which ones should be treated as a single "word". It even pulls out radicals.
> However, try this on the thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on 某人為了商X或個人使用.
> Then try 某人為了商X或個人使 (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
I hit the send button too quickly for the last post...
coz I tried out a couple of single character that my search couldn't
handle in http://forums.asp.net, and it acutally can return results
there...
i am just wondering if there is any other settings i can try to
improve that ?
thank you
|||You may have better success using the Traditional word breaker.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<admin.onQhk@.gmail.com> wrote in message
news:1171225568.325092.171990@.h3g2000cwc.googlegro ups.com...
On Feb 11, 5:33 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out
> characters to try figure out which characters go together and which ones
> should be treated as a single "word". It even pulls out radicals.
> However, try this on the
> thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on ???.
> Then try ?? (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication
> book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL
> FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in
> messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
icic
oh Thank you, so there is no way to improve that ?
thank you
|||They may be doing a like.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<admin.onQhk@.gmail.com> wrote in message
news:1171226670.382397.154660@.k78g2000cwa.googlegr oups.com...
On Feb 11, 5:33 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> The Chinese word breaker does multiple passes on characters pulling out
> characters to try figure out which characters go together and which ones
> should be treated as a single "word". It even pulls out radicals.
> However, try this on the
> thishttp://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> searching on ???.
> Then try ?? (note I have removed the last character)
> http://search.microsoft.com/results.aspx?q=%E6%9F%90%E4%BA%BA%E7%82%B...
> Nothing found.
> So while it works well in general, there are some inconsistencies.
> --
> Hilary Cotter
> Looking for a SQL Server replication
> book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL
> FTShttp://www.indexserverfaq.com
>
> <admin.on...@.gmail.com> wrote in
> messagenews:1171171251.557135.10240@.h3g2000cwc.goo glegroups.com...
>
>
>
>
>
> - Show quoted text -
I hit the send button too quickly for the last post...
coz I tried out a couple of single character that my search couldn't
handle in http://forums.asp.net, and it acutally can return results
there...
i am just wondering if there is any other settings i can try to
improve that ?
thank you
2012年3月27日星期二
full text search with language other than english(ex chinese, japanese)
I have set up a full text search to handle multiple columns searching for chinese
But the result of the search isn't really what i have expected.
I have setup the catalog to have a chiense word break, and the columns in the tables are all nvachar
when i do something like
select * from dbo.Table_1 where contains(*, '"<chinese character>"',language 1082)
the search result is really inconsistent, especially with single characters.I have also checked that these characters are not in the noise filter file...
the search result is better when the input is more than a single characters, but still, somtimes it will not return any result at all.
so, I try to use the "like" statement instead of "contains" to do the search with the same inputs, and 100% of the time, it returns the correct result.
does anyone have any experience about things like that? coz I guess this is a more spcific issue with language. Is there any place that you guys know of, can offer me some help?
thank you in advance.
I think you need to use the correct Chinese collation instead of just Nvarchar because Nvarchar just tells SQL Server you are not using ASCII but Chinese and Japanese alphabet are more than two thousand characters while Latin is just 26. There are six Chinese and I think three Japanese collations defined in SQL Server, try the link below to choose your collation. I also think you need to make sure your Microsoft search catalog is populated and check the noise word file. Run a search for noise words and Microsoft search catalog in SQL Server BOL(books online). Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms180175.aspx
|||Thank you so much
But after trying lots... of different collation settings, it still yields the same results...
Is there any more think that I can try ?
thank you
|||
Out of cuorcuriosity, I try to search a single chinese character which my search couldn't handle in this asp.net forums. This forum's search acutally returns result ...
so there must be something wrong that I am doing coz I really doubt that they will do anything special in this forum to handle chinese characters specifically......
just wondering if there is anything else that i could try ??
thank you in advance.....
full text search weighting on different columns
Hi,
I've not used FT much, but I've successfully created a CONTAINSTABLE query
on a database that contains names and addresses, using a wildcard for the
field names.
Can anyone please point me in the right direction for info on how to
"weight" one field over another? For example - if someone searches for
"John", the system needs to rank "John Smith" in the "name" column over "10
St John St" in the "address1" column.
Thanks in advance,
Dunc
Duncan,
You must join two or more CONTAINSTABLE from the same table or multiple
tables and then use the appropriate weight for each predicate, for example
using the Northwind table Employees and two FT-enabled columns:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Regards,
John
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:OqE2ZoWWEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I've not used FT much, but I've successfully created a CONTAINSTABLE query
> on a database that contains names and addresses, using a wildcard for the
> field names.
> Can anyone please point me in the right direction for info on how to
> "weight" one field over another? For example - if someone searches for
> "John", the system needs to rank "John Smith" in the "name" column over
"10
> St John St" in the "address1" column.
> Thanks in advance,
> Dunc
>
I've not used FT much, but I've successfully created a CONTAINSTABLE query
on a database that contains names and addresses, using a wildcard for the
field names.
Can anyone please point me in the right direction for info on how to
"weight" one field over another? For example - if someone searches for
"John", the system needs to rank "John Smith" in the "name" column over "10
St John St" in the "address1" column.
Thanks in advance,
Dunc
Duncan,
You must join two or more CONTAINSTABLE from the same table or multiple
tables and then use the appropriate weight for each predicate, for example
using the Northwind table Employees and two FT-enabled columns:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Regards,
John
"Duncan Welch" <dunc@.ntpcl.f9.co.uk> wrote in message
news:OqE2ZoWWEHA.2636@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I've not used FT much, but I've successfully created a CONTAINSTABLE query
> on a database that contains names and addresses, using a wildcard for the
> field names.
> Can anyone please point me in the right direction for info on how to
> "weight" one field over another? For example - if someone searches for
> "John", the system needs to rank "John Smith" in the "name" column over
"10
> St John St" in the "address1" column.
> Thanks in advance,
> Dunc
>
2012年3月26日星期一
full text search on views?
Can we buld full text search on views? If yes, can I build a search catalog on a view that allowed both varchar and text columns?
Hi Abhi,
I've asked help for Tony Ting which is full text expert and here are his comments:
Answer is yes, but you could only build fulltext on indexed view (materialized view) and column are limited to all available in normal case but not on text/ntext/image/xml column. The later constraint is came from the current limitation that index could not be created on a indexed view w/ text/ntext/image/xml, but fulltext column need a primary index to work on.
Hope this helps.
Thanks,
Ana Elisa - MSFT
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
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 Language Specification
I'm using FTS on on two columns (VARCHAR) of my database. The data is exported from a MySQL database to SQL Server. When populating the database the default language was set to English (or maybe neutral?), although 90% of the records are in Dutch. Now, when I change the FTS language specification to Dutch problems occur when querying the database. When I enter typical Dutch noise words in my query like "van" or "van der" the query does not return any results. When I set it back to Neutral the queries do return results, although a drawback is that I can't query for example plural forms of words. Could this be because, when populating the database, the correct language was not set? If so, is there a way to get the Full Text Index working with Dutch in a correct manner?
Thanks in advance for your replies! Would be great to get this working in Dutch!
Rino:
When I enter typical Dutch noise words in my query like "van" or "van der" the query does not return any results
I think ignoring the noise words is the feature of FTS and we can't manipulate it.
|||
Well, It think it is not really about ignoring the noise words. The are ignored by default, am I right?
To clarify things a bit: When I use the query "Van der Vaart" and the language for the FTS column is set to Dutch it returns no results. When I set the language to Neutral it does return results. Basically all it needs to do is find results for the part of the query that says "Vaart".
ps: We are using the excellent FTS Normalizer from E. Bachtal (http://ewbi.blogs.com/develops/2007/05/normalizing_sql.html) Could that possibly cause the issue described above?
|||Got it solved...
For some reason if I add the language code in the SQL query it works: "...WHERE CONTAINS(table, @.query, LANGUAGE 1043)..."
2012年3月25日星期日
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.
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..
2012年3月21日星期三
Full Text Query Starts Slow
I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.
The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.
Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.
I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.
I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.
Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.
In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql
The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.
Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.
I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.
I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.
Let me know if I'm missing any pertinent information. Thanks.http://www.sql-server-performance.com/full_text_search.asp & http://www.sql-server-performance.com/tb_search_optimization.asp for your action & information|||I have verified that nothing is being populated at the time I'm running these queries, so the first one should be the problem. We'll be loading 500,000 records per day once in the morning. I have the Catalog set to manual population, so that I re-populate after the load has completed.
In 2005 I didn't think that it had to go out to the MS Search searvice? I thought it was now all contained within SQL Server. In any regards, this is happening exactly the same way regard less of the query or amount of data returned. A call just to ContainsTable() with no other joins that returns 3 or 70.000 rows takes roughly the same amount of time.sql
2012年3月11日星期日
Full text index population is slow
I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
be indexed.
The DB is replicated on another server. Transactional.
On the Publisher the population take the CPU near the 100% for 3 day
and then the work is done.
On the subscriber, that is also the distributor the work don't end.
The CPU of the server is at the 100% (2 XEON 4GB Ram).
In the profiler i see many commands SP_fulltext_getdbdata ma the
sp_fulltext_pending_changes return ever the same numbre of pending
rows or more.
The columns contains italian word.
Someone can help me.
Something is very wrong here. What do the gatherer logs say. You can find
them in c:\Program Files\Microsoft sql server\mssql.X\mssql\log
and they look like SQLFT0018700017.log.X
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<innocenzo.daraio@.gmail.com> wrote in message
news:1176273986.698099.26940@.l77g2000hsb.googlegro ups.com...
>I have 1 milion rows on a SQL server 2005 SP1 with 2 Text columns to
> be indexed.
> The DB is replicated on another server. Transactional.
> On the Publisher the population take the CPU near the 100% for 3 day
> and then the work is done.
> On the subscriber, that is also the distributor the work don't end.
> The CPU of the server is at the 100% (2 XEON 4GB Ram).
> In the profiler i see many commands SP_fulltext_getdbdata ma the
> sp_fulltext_pending_changes return ever the same numbre of pending
> rows or more.
> The columns contains italian word.
> Someone can help me.
>
Full text index not populating correctly
Hi
I have a table with 4 columns which are full-text indexed. I have noticed
that certain words are not being indexed and therefore not returned in
searches.
I have also noticed that the position on the word in the column effects
whether it is indexed.
For example:
I need to search a column called "Subtitle" which contains information on
product model numbers. If i search for "KF18W420GB" on a column that
contains "KF18W420GB stainless steel" then nothing is returned. If i change
the value in the database to "KF18W420GB" and repopulate the index then the
search works. Similarly, if i change the value in the database to "another
word KF18W420GB stainless steel" the search also works.
Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
guessing this is probably the problem and i am attempting to upgrade.
Thanks in advance.
Dan
Dan,
It would depend upon the OS platform wordbreaker, in this case the NT4.0
infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
(contains vs. freetext) that you are using. Could you post the exact
CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
exact text you are expecting to return.
FYI, as you're using NT4.0, you can easily move in the Win2K version of
infosoft.dll as a test. If you want to do this, I have the exact methods on
how to do this.
Regards,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have a table with 4 columns which are full-text indexed. I have noticed
> that certain words are not being indexed and therefore not returned in
> searches.
> I have also noticed that the position on the word in the column effects
> whether it is indexed.
> For example:
> I need to search a column called "Subtitle" which contains information on
> product model numbers. If i search for "KF18W420GB" on a column that
> contains "KF18W420GB stainless steel" then nothing is returned. If i
change
> the value in the database to "KF18W420GB" and repopulate the index then
the
> search works. Similarly, if i change the value in the database to "another
> word KF18W420GB stainless steel" the search also works.
> Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
> guessing this is probably the problem and i am attempting to upgrade.
> Thanks in advance.
> Dan
>
|||Hi
i have tried using CONTAINSTABLE also but the result is the same as the
query below:
SELECT
KEY_TBL.Rank,
FT_TBL.LinkText,
FT_TBL.PageId,
FT_TBL.template
FROM (tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY])
WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
Select @.@.version:
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
Select @.@.language:
us_english
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Dan,
> It would depend upon the OS platform wordbreaker, in this case the NT4.0
> infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
> (contains vs. freetext) that you are using. Could you post the exact
> CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
> exact text you are expecting to return.
> FYI, as you're using NT4.0, you can easily move in the Win2K version of
> infosoft.dll as a test. If you want to do this, I have the exact methods
> on
> how to do this.
> Regards,
> John
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> change
> the
>
|||Dan,
If you re-write the query and remove the where clause (WHERE FT_TBL.Search
!=0)
SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
FROM tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
does it return any &/or expectant results? If not, then as this is SQL 7.0
on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I don't
have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In the
row that contains the above search string 'KF18W420GB' is there any other
characters &/or punctuation that is in contact or touching the search
string?
Thanks,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> i have tried using CONTAINSTABLE also but the result is the same as the
> query below:
> SELECT
> KEY_TBL.Rank,
> FT_TBL.LinkText,
> FT_TBL.PageId,
> FT_TBL.template
> FROM (tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY])
> WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
> Select @.@.version:
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
> NT 4.0 (Build 1381: Service Pack 6)
> Select @.@.language:
> us_english
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
noticed[vbcol=seagreen]
on
>
|||No, that doesn't make a difference. There is no other words/characters other
than those specified.
I will endevour to upgrade the server.
Thanks
Dan
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uIexBlgmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Dan,
> If you re-write the query and remove the where clause (WHERE FT_TBL.Search
> !=0)
> SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
> FROM tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> does it return any &/or expectant results? If not, then as this is SQL 7.0
> on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I
> don't
> have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In
> the
> row that contains the above search string 'KF18W420GB' is there any other
> characters &/or punctuation that is in contact or touching the search
> string?
> Thanks,
> John
>
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...
> the
> noticed
> on
>
I have a table with 4 columns which are full-text indexed. I have noticed
that certain words are not being indexed and therefore not returned in
searches.
I have also noticed that the position on the word in the column effects
whether it is indexed.
For example:
I need to search a column called "Subtitle" which contains information on
product model numbers. If i search for "KF18W420GB" on a column that
contains "KF18W420GB stainless steel" then nothing is returned. If i change
the value in the database to "KF18W420GB" and repopulate the index then the
search works. Similarly, if i change the value in the database to "another
word KF18W420GB stainless steel" the search also works.
Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
guessing this is probably the problem and i am attempting to upgrade.
Thanks in advance.
Dan
Dan,
It would depend upon the OS platform wordbreaker, in this case the NT4.0
infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
(contains vs. freetext) that you are using. Could you post the exact
CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
exact text you are expecting to return.
FYI, as you're using NT4.0, you can easily move in the Win2K version of
infosoft.dll as a test. If you want to do this, I have the exact methods on
how to do this.
Regards,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have a table with 4 columns which are full-text indexed. I have noticed
> that certain words are not being indexed and therefore not returned in
> searches.
> I have also noticed that the position on the word in the column effects
> whether it is indexed.
> For example:
> I need to search a column called "Subtitle" which contains information on
> product model numbers. If i search for "KF18W420GB" on a column that
> contains "KF18W420GB stainless steel" then nothing is returned. If i
change
> the value in the database to "KF18W420GB" and repopulate the index then
the
> search works. Similarly, if i change the value in the database to "another
> word KF18W420GB stainless steel" the search also works.
> Does anyone know why this is? I am using SQL Server 7 on NT 4 - i am
> guessing this is probably the problem and i am attempting to upgrade.
> Thanks in advance.
> Dan
>
|||Hi
i have tried using CONTAINSTABLE also but the result is the same as the
query below:
SELECT
KEY_TBL.Rank,
FT_TBL.LinkText,
FT_TBL.PageId,
FT_TBL.template
FROM (tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY])
WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
Select @.@.version:
Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
NT 4.0 (Build 1381: Service Pack 6)
Select @.@.language:
us_english
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
> Dan,
> It would depend upon the OS platform wordbreaker, in this case the NT4.0
> infosoft.dll. It also depends upon the exact SQL Server 7.0 FTS query
> (contains vs. freetext) that you are using. Could you post the exact
> CONTAINS* or FREETEXT* FTS query you are using as well as a sample of the
> exact text you are expecting to return.
> FYI, as you're using NT4.0, you can easily move in the Win2K version of
> infosoft.dll as a test. If you want to do this, I have the exact methods
> on
> how to do this.
> Regards,
> John
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:O4fDi9amEHA.3372@.TK2MSFTNGP15.phx.gbl...
> change
> the
>
|||Dan,
If you re-write the query and remove the where clause (WHERE FT_TBL.Search
!=0)
SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
FROM tblPageContent AS FT_TBL
INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
ON FT_TBL.PageID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
does it return any &/or expectant results? If not, then as this is SQL 7.0
on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I don't
have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In the
row that contains the above search string 'KF18W420GB' is there any other
characters &/or punctuation that is in contact or touching the search
string?
Thanks,
John
"Dan" <dan@.nospamplan9.co.uk> wrote in message
news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi
> i have tried using CONTAINSTABLE also but the result is the same as the
> query below:
> SELECT
> KEY_TBL.Rank,
> FT_TBL.LinkText,
> FT_TBL.PageId,
> FT_TBL.template
> FROM (tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY])
> WHERE FT_TBL.Search !=0 ORDER BY KEY_TBL.RANK DESC
> Select @.@.version:
> Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86) Apr 9 2002 14:18:16
> Copyright (c) 1988-2002 Microsoft Corporation Standard Edition on Windows
> NT 4.0 (Build 1381: Service Pack 6)
> Select @.@.language:
> us_english
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:e3EU%231bmEHA.3356@.TK2MSFTNGP14.phx.gbl...
the[vbcol=seagreen]
noticed[vbcol=seagreen]
on
>
|||No, that doesn't make a difference. There is no other words/characters other
than those specified.
I will endevour to upgrade the server.
Thanks
Dan
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uIexBlgmEHA.512@.TK2MSFTNGP10.phx.gbl...
> Dan,
> If you re-write the query and remove the where clause (WHERE FT_TBL.Search
> !=0)
> SELECT KEY_TBL.Rank, FT_TBL.LinkText, FT_TBL.PageId, FT_TBL.template
> FROM tblPageContent AS FT_TBL
> INNER JOIN FREETEXTTABLE(tblPageContent,*,'KF18W420GB') AS KEY_TBL
> ON FT_TBL.PageID = KEY_TBL.[KEY]
> ORDER BY KEY_TBL.RANK DESC
> does it return any &/or expectant results? If not, then as this is SQL 7.0
> on NT4.0, this may be a NT4.0 wordbreaking issue, and unfortunately, I
> don't
> have an NT4.0 installation to test the NT4.0 version of infosoft.dll. In
> the
> row that contains the above search string 'KF18W420GB' is there any other
> characters &/or punctuation that is in contact or touching the search
> string?
> Thanks,
> John
>
>
> "Dan" <dan@.nospamplan9.co.uk> wrote in message
> news:eqzVCZemEHA.2616@.tk2msftngp13.phx.gbl...
> the
> noticed
> on
>
2012年3月9日星期五
Full Text in SQL 2005 vs SQL Express
Does full text work the same in both of these? From what I've read, it
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
Tom
Hello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
Tom
Hello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Full Text in SQL 2005 vs SQL Express
Does full text work the same in both of these? From what I've read, it
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
TomHello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
TomHello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Full Text in SQL 2005 vs SQL Express
Does full text work the same in both of these? From what I've read, it
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
TomHello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
appears SQl Express only searches in text columns...
I have a client who needs to search in a a column which contains imported
Word docs, etc. (varbinary(max)). Can this be done in the Express version or
not?
Thanks,
TomHello Tom,
According to this MSDN article, SQL Express did not support to full-text
query the varbinary(max) column. But the SQL Express with Advanced Services
support.
http://msdn2.microsoft.com/en-us/library/ms143761.aspx
You may try to use that Edition.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
2012年3月7日星期三
Full Text "And Not" Fails
I have set up a simple "ProdTest1" table with 3 columns: ID (int) which is an Identity column and indexed, Name (nvarchar 45), and Description (nvarchar 2000). I have then entered a single row with the following data:
ID: 1
Name: Purple Black Blue
Description: Black Blue
I have then set up a Full-Text index based on the Name and Description columns.
When I execute the following query that row is returned in the query results:
select * from ProdTest1 where contains (*,'Black and not purple')
I would not expect any returned rows. Am I missing something?
I am using Microsoft SQL Server 2000 - 8.00.2039 (Enterprise Edition)
Thanks
Adatacorp,Yes... You need to use double quotes for your words &/or phrases, for example:
"black" and NOT "purple" within the single quotes of the CONTAINS clause.
From BOL title "CONTAINS":
AND | AND NOT | OR - Specifies a logical operation between two contains search conditions. When <contains_search_condition> contains parenthesized groups, these parenthesized groups are evaluated first. After evaluating parenthesized groups, these rules apply when using these logical operators with contains search conditions:
NOT is applied before AND.
NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed.
NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ).
AND is applied before OR.
Boolean operators of the same type (AND, OR) are associative and can therefore be applied in any order.
More specificly, try the following examples:
SELECT p.pub_id, p.pr_info, c.[rank]
from pub_info AS p,
containstable(pub_info, *, '"books" and NOT "publisher"') as c
where c.[KEY] = p.pub_id
order by c.[rank]
select pub_id, pr_info from pub_info
where contains(*,'"moon"') and NOT contains(*,'"Binnet"')
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||John,
Including double-quotes in the SQL does not solve the problem. Both of the following two SQL statements incorrectly return my example row:
select * from ProdTest1 where contains (*,'black and not purple')
select * from ProdTest1 where contains (*,'"black" and not "purple"')|||
How about this version? Does it return the results you are expecting?
select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')
Additional, do you have more than one column FT-enabled? There are well known issues with searching "accross" columns when used with CONTAINS or CONTAINSTABLE, see KB article 294809 "FIX: Full-Text Search Queries with CONTAINS Clause Search Across Columns" at http://support.microsoft.com/default.aspx?scid=kb;en-us;294809 for more details.
Finally, the OS platform that you have SQL Server installed and depending upon the text in your FT-enabled column, can be an issue as well. Does your FT-enabled column contain any punctuation characters in contact with your search words? Could you post the full output of SELECT @.@.version ?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
John,
You have answered the question.
I was expecting the full-text query to be applied across the concatenation of all the columns. But instead (per the KB article you reference), the query
select * from ProdTest1 where contains (*,'black and not purple')
is actually executed as...
select * from ProdTest1 where contains (Name,'black and not purple') OR contains (Description,'black and not purple')
Thus the Full-Text query correctly returns my row because the data in the Description column (Black Blue) meets the query requirement.
Indeed your suggested query:
select * from ProdTest1
where contains(*,'"black"') and NOT contains(*,'"purple"')
does return the results I desire.
However for my desired behavior in all searches I think I will need to create yet another column and in a trigger concatenate all the query column data into that added column.
I wish Microsoft had an option to simply do the Full-Text searches on the catenation of all the columns as I desire.
Thanks for your help.
|||You're welcome, Bob,I suspected so, as you were using the "*" (astrick) that indicates to search all FT-enabled column and behavior the you are seeing is the default behavior for SQL Server 2000.
However, for SQL Server 2005 (codename Yukon) supports a new syntax of multiple column syntax, for example:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(( FTSCol2, FTSCol3) , ' "john" AND "kane" ')
The above query is functionally equivalent to the following SQL 2000 FTS query:
SELECT FTSPkey, FTSCol2, FTSCol3
FROM FTS_Table
WHERE CONTAINS(FTSCol2, '"john" AND "kane"') OR
CONTAINS(FTSCol3, '"john" AND "kane"')
Specifically, an OR between columns is supported in a single statement, but
not an AND between columns and to the best of my knowledge NOT is not supported within this new syntax. I'll confirm this as well.
As for doing FT Searches on concatenated columns, I suspect you'll have to wait for the next version of SQL Server (post-Yukon) as IBM's DB2 Text Information Extender can do this somewhat via a UDF. 
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
2012年2月19日星期日
FTS Newbie - Help Please!
Hi!
I've got a table SupplierItem with full text catalogue set up on it
and I need to search two columns of it, Description2 and
SupplierItemCode, for any combination of terms entered in any order
with wildcards
(ie if user puts in FOLDER, it finds FOLDERS as well) and also search
for two columns non-FTS for
possible entries as well. I've played around with wildcards, CONTAINS
but it still doesn't work properly!!
Please help...
Here's the example:
SELECT
SupplierProduct.SupplierProductId,
SupplierProduct.SupplierProduct,
CurrentItem.SupplierItemId,
CurrentItem.SupplierItemCode,
CurrentItem.Description2,
CurrentItem.BuyUnit,
CurrentItem.PricingUnit,
CurrentItem.OCCost,
CurrentItem.OCCost1,
CurrentItem.OCCost2,
CurrentItem.OCCost3,
CurrentItem.ConnCost,
CurrentItem.ConnCost1,
CurrentItem.ItemStatusId,
ItemStatus.ItemStatusName,
ItemStatus.ItemStatusImage,
ItemStatus.ItemStatusColour,
Supplier.SourceCode,
CurrentItem.UnitId,
CatalogueItem.Item
FROMSupplierItem CurrentItem
INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
SupplierProduct.SupplierProductId)
INNER JOIN Supplier ON (Supplier.SupplierId =
SupplierProduct.SupplierId)
INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
ItemStatus.ItemStatusId)
INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
CurrentItem.BaseProductId)
LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
BaseProduct.BaseProductId)
AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> dbo.Date(year(getdate()), 12, 31) ))
WHERE(IsApproved = 1 -- item must be approved
AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
archived
)
AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
@.SearchTerm))
OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
@.SearchTerm))
OR @.SearchTerm = CatalogueItem.Item)
OR (@.SearchTerm = BaseProduct.BaseItem)
)
AND CurrentItem.OCCost > 0
ORDER BY
CurrentItem.SupplierItemId
What do you mean by it doesn't work? Are you able to find anything? Are
there any error messages?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"masterslave" <touchdowntokyo@.gmail.com> wrote in message
news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
> Hi!
> I've got a table SupplierItem with full text catalogue set up on it
> and I need to search two columns of it, Description2 and
> SupplierItemCode, for any combination of terms entered in any order
> with wildcards
> (ie if user puts in FOLDER, it finds FOLDERS as well) and also search
> for two columns non-FTS for
> possible entries as well. I've played around with wildcards, CONTAINS
> but it still doesn't work properly!!
> Please help...
> Here's the example:
> SELECT
> SupplierProduct.SupplierProductId,
> SupplierProduct.SupplierProduct,
> CurrentItem.SupplierItemId,
> CurrentItem.SupplierItemCode,
> CurrentItem.Description2,
> CurrentItem.BuyUnit,
> CurrentItem.PricingUnit,
> CurrentItem.OCCost,
> CurrentItem.OCCost1,
> CurrentItem.OCCost2,
> CurrentItem.OCCost3,
> CurrentItem.ConnCost,
> CurrentItem.ConnCost1,
> CurrentItem.ItemStatusId,
> ItemStatus.ItemStatusName,
> ItemStatus.ItemStatusImage,
> ItemStatus.ItemStatusColour,
> Supplier.SourceCode,
> CurrentItem.UnitId,
> CatalogueItem.Item
> FROM SupplierItem CurrentItem
> INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
> SupplierProduct.SupplierProductId)
> INNER JOIN Supplier ON (Supplier.SupplierId =
> SupplierProduct.SupplierId)
> INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
> ItemStatus.ItemStatusId)
> INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
> CurrentItem.BaseProductId)
> LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
> BaseProduct.BaseProductId)
> AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
> AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> WHERE (IsApproved = 1 -- item must be approved
> AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
> archived
> )
> AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
> @.SearchTerm))
> OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
> @.SearchTerm))
> OR @.SearchTerm = CatalogueItem.Item)
> OR (@.SearchTerm = BaseProduct.BaseItem)
> )
> AND CurrentItem.OCCost > 0
> ORDER BY
> CurrentItem.SupplierItemId
>
|||Sorry for being really vague... The problem is I'm getting error
messages like this seemingly randomly:
Execution of a full-text operation failed. A clause of the query
contained only ignored words.
I've got a search log but if I try to replicate the search terms I
don't get the error... I've emptied the
words file in SQL Server, but it's still happening!
And the other issue is that it's very slow. The database has about
half a million records, but the query
takes at least five seconds to run...
Any help very much appreciated!!
On Nov 2, 3:05 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> What do you mean by it doesn't work? Are you able to find anything? Are
> there any error messages?
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"masterslave" <touchdownto...@.gmail.com> wrote in message
> news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
>
>
>
> - Show quoted text -
I've got a table SupplierItem with full text catalogue set up on it
and I need to search two columns of it, Description2 and
SupplierItemCode, for any combination of terms entered in any order
with wildcards
(ie if user puts in FOLDER, it finds FOLDERS as well) and also search
for two columns non-FTS for
possible entries as well. I've played around with wildcards, CONTAINS
but it still doesn't work properly!!
Please help...
Here's the example:
SELECT
SupplierProduct.SupplierProductId,
SupplierProduct.SupplierProduct,
CurrentItem.SupplierItemId,
CurrentItem.SupplierItemCode,
CurrentItem.Description2,
CurrentItem.BuyUnit,
CurrentItem.PricingUnit,
CurrentItem.OCCost,
CurrentItem.OCCost1,
CurrentItem.OCCost2,
CurrentItem.OCCost3,
CurrentItem.ConnCost,
CurrentItem.ConnCost1,
CurrentItem.ItemStatusId,
ItemStatus.ItemStatusName,
ItemStatus.ItemStatusImage,
ItemStatus.ItemStatusColour,
Supplier.SourceCode,
CurrentItem.UnitId,
CatalogueItem.Item
FROMSupplierItem CurrentItem
INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
SupplierProduct.SupplierProductId)
INNER JOIN Supplier ON (Supplier.SupplierId =
SupplierProduct.SupplierId)
INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
ItemStatus.ItemStatusId)
INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
CurrentItem.BaseProductId)
LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
BaseProduct.BaseProductId)
AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> dbo.Date(year(getdate()), 12, 31) ))
WHERE(IsApproved = 1 -- item must be approved
AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
archived
)
AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
@.SearchTerm))
OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
@.SearchTerm))
OR @.SearchTerm = CatalogueItem.Item)
OR (@.SearchTerm = BaseProduct.BaseItem)
)
AND CurrentItem.OCCost > 0
ORDER BY
CurrentItem.SupplierItemId
What do you mean by it doesn't work? Are you able to find anything? Are
there any error messages?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"masterslave" <touchdowntokyo@.gmail.com> wrote in message
news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
> Hi!
> I've got a table SupplierItem with full text catalogue set up on it
> and I need to search two columns of it, Description2 and
> SupplierItemCode, for any combination of terms entered in any order
> with wildcards
> (ie if user puts in FOLDER, it finds FOLDERS as well) and also search
> for two columns non-FTS for
> possible entries as well. I've played around with wildcards, CONTAINS
> but it still doesn't work properly!!
> Please help...
> Here's the example:
> SELECT
> SupplierProduct.SupplierProductId,
> SupplierProduct.SupplierProduct,
> CurrentItem.SupplierItemId,
> CurrentItem.SupplierItemCode,
> CurrentItem.Description2,
> CurrentItem.BuyUnit,
> CurrentItem.PricingUnit,
> CurrentItem.OCCost,
> CurrentItem.OCCost1,
> CurrentItem.OCCost2,
> CurrentItem.OCCost3,
> CurrentItem.ConnCost,
> CurrentItem.ConnCost1,
> CurrentItem.ItemStatusId,
> ItemStatus.ItemStatusName,
> ItemStatus.ItemStatusImage,
> ItemStatus.ItemStatusColour,
> Supplier.SourceCode,
> CurrentItem.UnitId,
> CatalogueItem.Item
> FROM SupplierItem CurrentItem
> INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
> SupplierProduct.SupplierProductId)
> INNER JOIN Supplier ON (Supplier.SupplierId =
> SupplierProduct.SupplierId)
> INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
> ItemStatus.ItemStatusId)
> INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
> CurrentItem.BaseProductId)
> LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
> BaseProduct.BaseProductId)
> AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
> AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> WHERE (IsApproved = 1 -- item must be approved
> AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
> archived
> )
> AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
> @.SearchTerm))
> OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
> @.SearchTerm))
> OR @.SearchTerm = CatalogueItem.Item)
> OR (@.SearchTerm = BaseProduct.BaseItem)
> )
> AND CurrentItem.OCCost > 0
> ORDER BY
> CurrentItem.SupplierItemId
>
|||Sorry for being really vague... The problem is I'm getting error
messages like this seemingly randomly:
Execution of a full-text operation failed. A clause of the query
contained only ignored words.
I've got a search log but if I try to replicate the search terms I
don't get the error... I've emptied the
words file in SQL Server, but it's still happening!
And the other issue is that it's very slow. The database has about
half a million records, but the query
takes at least five seconds to run...
Any help very much appreciated!!
On Nov 2, 3:05 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> What do you mean by it doesn't work? Are you able to find anything? Are
> there any error messages?
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"masterslave" <touchdownto...@.gmail.com> wrote in message
> news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
>
>
>
> - Show quoted text -
FTS Multiple tables and columns. Different spin. Help!
Been reading thru all the posts and KB's on dealing with searching
across multiple columns. Most examples indicate you know what you're
looking for in each column.
Here's my big problem:
A: I have 12 tables, each with multiple columns that need to be
searched.
B: User will enter a search condition in any or all the boxes below
1 - "All these words"
2 - "Exact phrase"
3 - "Any of these words"
Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
want a match if "house" and "cat" are in different tables.
In a nutshell, I need to take the 3 inputs, and search across all the
tables, and get ranked results.
The only answer I see, which seems to be kludge, is to create another
table with one column that concats all the indexable columns for all
the tables.
Doing a monster union with multiple contains/free/? on each select
seems really ugly and would kill performance.
Am I missing the boat, or does anyone have a better solution?
All comments are REALLY appreciated, as I just pulled out the last hair
on my head!
Thanks.
My approach would be to consolidate all of your data into a single table. I
realize it doesn't help much.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"woody" <shellymatthys@.yahoo.com> wrote in message
news:1116568453.936941.176620@.g47g2000cwa.googlegr oups.com...
> Been reading thru all the posts and KB's on dealing with searching
> across multiple columns. Most examples indicate you know what you're
> looking for in each column.
> Here's my big problem:
> A: I have 12 tables, each with multiple columns that need to be
> searched.
> B: User will enter a search condition in any or all the boxes below
> 1 - "All these words"
> 2 - "Exact phrase"
> 3 - "Any of these words"
> Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
> want a match if "house" and "cat" are in different tables.
> In a nutshell, I need to take the 3 inputs, and search across all the
> tables, and get ranked results.
> The only answer I see, which seems to be kludge, is to create another
> table with one column that concats all the indexable columns for all
> the tables.
> Doing a monster union with multiple contains/free/? on each select
> seems really ugly and would kill performance.
> Am I missing the boat, or does anyone have a better solution?
> All comments are REALLY appreciated, as I just pulled out the last hair
> on my head!
> Thanks.
>
across multiple columns. Most examples indicate you know what you're
looking for in each column.
Here's my big problem:
A: I have 12 tables, each with multiple columns that need to be
searched.
B: User will enter a search condition in any or all the boxes below
1 - "All these words"
2 - "Exact phrase"
3 - "Any of these words"
Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
want a match if "house" and "cat" are in different tables.
In a nutshell, I need to take the 3 inputs, and search across all the
tables, and get ranked results.
The only answer I see, which seems to be kludge, is to create another
table with one column that concats all the indexable columns for all
the tables.
Doing a monster union with multiple contains/free/? on each select
seems really ugly and would kill performance.
Am I missing the boat, or does anyone have a better solution?
All comments are REALLY appreciated, as I just pulled out the last hair
on my head!
Thanks.
My approach would be to consolidate all of your data into a single table. I
realize it doesn't help much.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"woody" <shellymatthys@.yahoo.com> wrote in message
news:1116568453.936941.176620@.g47g2000cwa.googlegr oups.com...
> Been reading thru all the posts and KB's on dealing with searching
> across multiple columns. Most examples indicate you know what you're
> looking for in each column.
> Here's my big problem:
> A: I have 12 tables, each with multiple columns that need to be
> searched.
> B: User will enter a search condition in any or all the boxes below
> 1 - "All these words"
> 2 - "Exact phrase"
> 3 - "Any of these words"
> Unlike the SP3 "fix", if they enter "house cat" in "all these words", I
> want a match if "house" and "cat" are in different tables.
> In a nutshell, I need to take the 3 inputs, and search across all the
> tables, and get ranked results.
> The only answer I see, which seems to be kludge, is to create another
> table with one column that concats all the indexable columns for all
> the tables.
> Doing a monster union with multiple contains/free/? on each select
> seems really ugly and would kill performance.
> Am I missing the boat, or does anyone have a better solution?
> All comments are REALLY appreciated, as I just pulled out the last hair
> on my head!
> Thanks.
>
FTS indexable table column
Hi, how can I programatically identify if table columns are eligible to include in a FTS catalog index?
I can do this:
SELECT COLUMNPROPERTY (object_id('Person.Address') , 'AddressID', 'IsIndexable' )
but what I'd like to do is:
SELECT COLUMNPROPERTY (object_id('Person.Address') , 'AddressID', 'IsFullTextIndexable' )
but that property doesn't exist. The best I got sofar is to compare data types with what the documentation says, something like this:
...
if (tc.Text == "char" ||
tc.Text == "varchar" ||
tc.Text == "nvarchar" ||
tc.Text == "varbinary(max)" ||
tc.Text == "text" ||
tc.Text == "image")
...
but I'd like something less hard-coded like a column property. In fact, in another place in the Books Online there's a document that syas that you can also use nchar, ntext, xml, and varbinary without the (max) in an index; so, which data types are really supported then?
Regards, and TIA,
Pieter
Sorry, I just saw there's a more appropiate forum for FTS (SQL Server Database Engine) so I post the same Q there too.
Regards, Pieter
订阅：
博文 (Atom)