You're welcome, VpUser,
1. May i know what different between CONTAINS and FREETEXT?
A. CONTAINS and CONTAINSTABLE will return rows that contain the exact word
that you are searching for while FREETEXT or FREETEXTTABLE will return the
exact word and if present, words that "match the meaning and not the exact
wording of the words in the search condition" (from SQL 2000 BOL). So while
in the small table pub_info, the results would be the same for your search
word booktitle, while in larger tables with more diverse text, FREETEXT
would often return more rows than CONTAINS when searching for the same word.

2. The correct sntax for the table pub_info than does not have a column
called "title", would be:
select * from pub_info where CONTAINS(*,'book and title')

When I execute the above query on Win2003, it returns 0 rows and no errors.
However, if I change title in the search condition, to "between" a
US_English noise word:

select * from pub_info where CONTAINS(*,'book and between')

This returns error Msg 7619 ".. A clause of the query contained only
ignored words". This can be avoided by removing "between" from the
US_English noise word file noise.enu that is located under
\FTDATA\SQLServer\Config where you have SQL Server 2000 installed. You can
open and edit this file with notepad.exe, but to save it you will need to
stop the "Microsoft Search" service and then run a Full Population.
Additionally, you can alter the query using quotes or parse the noise word
out via pre-processing before passing it to a SQL Server contains query. I'd
recommend that you review SQL Server 2000 BOL title "Full-text Search
Recommendations" as well as KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//default.aspx?scid=kb;EN-US;246800

3. May i enter noisy word in the search? how do i know the noisy word by
using SQL statement(not from text file).
A. Yes, but you will need to parse the noise word into phrases or remove it
to avoid the error. You can also use BULK INSERT and import the noise word
file (noise.enu text file) into a SQL table and then use that table as a
lookup table to determine if the searcher enters a noise word.

Regards,
John




[quoted text, click to view]

wrap your search phrase in double quotes

select * from pub_info where CONTAINS(*,'"yellow book"')

that's a single quote, followed by a double quote, followed by yellow book
followed by a double quote followed by a single quote



[quoted text, click to view]

John,

Thanks John! It's very great example. I'm using SQL Server 2000 FTS.

1. May i know what different between CONTAINS and FREETEXT?

eg.

select * from pub_info where CONTAINS(title,'booktitle')

and

select * from pub_info where FREETEXT(title,'booktitle')

Its returns same result.

2. When i try this query:

==> select * from pub_info where CONTAINS(title,'book and title')

It's giving me the error. Error abt noisy words...

May i enter noisy word in the search? how do i know the noisy word by using
SQL statement(not from text file).

regards,

VpUser

John,

Thanks your clear explanation. It's very clear and "user friendly", easy for
me to pick up.

Well... i still have one question abt Contains.
I try to run this query in SQL server it giving me the error.
Error ==> select * from pub_info where CONTAINS(*,'yellow book')
it return me the error...
If I enter
OK==> select * from pub_info where CONTAINS(*,'yellow and book')
How do i the query if i want to find "yellow book" exact word in the query?


best regards,
VpUser

I am trying to use Bulk insert to populate a table from a text file.it works
fine on localhost but while running on network it gives the following error.

"Could not bulk insert because file 'E:\far\farextracts\DEGREE.txt' could
not be opened. Operating system error code 5(Access is denied.)."
Any Suggestions or solutions.
Thanks

[quoted text, click to view]