Monday, March 12, 2012

request for sample program for fulltext search

hi...
i'm newbie here... can i have a sample program for fulltext search.
thanx!
VpUser,
Well, could you be more specific? SQL Server 2000 Full-text Search (FTS)
uses pure T-SQL such as CONTAINS or FREETEXT, for example:
select * from pub_info where CONTAINS(*,'books')
or if you want a SQL-DMO sample program, you can checkout SQL 2000 BOL title
"SQL-DMO Examples: Full-text Indexing"
More info would be helpful. What are you looking to do with the sample
program?
Regards,
John
"VpUser" <vpuser@.someone.com> wrote in message
news:OGwpRiEoEHA.2764@.TK2MSFTNGP11.phx.gbl...
> hi...
> i'm newbie here... can i have a sample program for fulltext search.
> thanx!
>
|||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
|||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//defaul...b;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
"VpUser" <vpuser@.someone.com> wrote in message
news:#JunAhFoEHA.3464@.TK2MSFTNGP14.phx.gbl...
> 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
|||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
"VpUser" <vpuser@.someone.com> wrote in message
news:uEs%23$IIoEHA.3968@.TK2MSFTNGP11.phx.gbl...
> 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
"VpUser" wrote:

> 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
>
>
|||MMSqlserver,
Yes. This is a permissions issue related to accessing the file
(E:\far\farextracts\DEGREE.txt) and the account (DOMAIN\account or "Local
System"/LocalSystem) that is use to start the SQL Server (MSSQLServer)
service most likely does not have the correct permission to access the
server and share where this file exists. You should also ensure that the
share (\far\farextracts) has the correct permissions, such as the Everyone
group with Read access.
While not directly related to uploading a file via BULK INSERT, but still
related to the access denied error, you should review the following KB
article "PRB: Unable to Back Up Database to a Network Drive Without
Permissions" at:
http://support.microsoft.com/default...b;EN-US;207187
Regards,
John
"MMSqlserver" <sqlservermn@.discussions.microsoft.com> wrote in message
news:609CDD9A-DA01-4CBE-9170-502990189403@.microsoft.com...
> 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.[vbcol=seagreen]
> "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
> "VpUser" wrote:
using[vbcol=seagreen]

No comments:

Post a Comment