full text search query

select pID,pcode from MyProduct where freetext(PIndex, 'the')
I have the above full text search query. It generate the following err msg:
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words. "
if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
also generate the same err msg.
I have create a full text search index on product table, catalog, full
population . It will not generate err when i use normal word e.g. 'buy',
'model' etc
I am using MS SQL server 2000 on server 2003. Anything to do with
configuration? Any one can help me with it?'the' a what is termed a noise word so is extracted from the query.
You can get and modify the noise word files the full-text engine uses - do a
search for noise.*, there should be a noise.enu - depends on your locale
though.
Its classed a noise word along with the other symbols because thats not
really what full-text is about, think of all the occurances of 'the' and 'a'
and 'and' for instance - does it really make sense indexing those items?
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> select pID,pcode from MyProduct where freetext(PIndex, 'the')
> I have the above full text search query. It generate the following err
> msg:
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words. "
> if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> also generate the same err msg.
> I have create a full text search index on product table, catalog, full
> population . It will not generate err when i use normal word e.g. 'buy',
> 'model' etc
> I am using MS SQL server 2000 on server 2003. Anything to do with
> configuration? Any one can help me with it?|||the point is that SQL server 2k return as an error/msg. Assume a user
performing a full text search query from coldfusion, user can enter
anything. If user enter the word 'the' and because of SQL server 2k issue an
error/msg, my SQL server service get HANG. I need it manually restart my
service to get my sql server working.
--
xxx
"Tony Rogerson" wrote:
> 'the' a what is termed a noise word so is extracted from the query.
> You can get and modify the noise word files the full-text engine uses - do a
> search for noise.*, there should be a noise.enu - depends on your locale
> though.
> Its classed a noise word along with the other symbols because thats not
> really what full-text is about, think of all the occurances of 'the' and 'a'
> and 'and' for instance - does it really make sense indexing those items?
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "eslim" <eslim@.discussions.microsoft.com> wrote in message
> news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> > select pID,pcode from MyProduct where freetext(PIndex, 'the')
> >
> > I have the above full text search query. It generate the following err
> > msg:
> > "Server: Msg 7619, Level 16, State 1, Line 1
> > Execution of a full-text operation failed. A clause of the query contained
> > only ignored words. "
> >
> > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> > also generate the same err msg.
> >
> > I have create a full text search index on product table, catalog, full
> > population . It will not generate err when i use normal word e.g. 'buy',
> > 'model' etc
> >
> > I am using MS SQL server 2000 on server 2003. Anything to do with
> > configuration? Any one can help me with it?
>
>|||When you implement a search using any search engine you need to validate the
user input, do that using a parser that extracts the noise words from the
users input and then if there is nothing left then give the user a message.
Check this: http://www.aspfaq.com/show.asp?id=2502
Also, you should do error handling in the app to pick up any error - check
for that particular error number and gracefully give the user a help
message.
Getting this message does not hang SQL Server, i've done a lot with ASP,
ASP.NET and Full-Text, its how i do my own search on
http://sqlserverfaq.com, if your SQL Server is hanging you have other more
serious problems - check the SQL errorlog for one, is the server responsive
through Query Analyser?
Is it just the application not behaviouring correctly because its got an
error? What happens if you have another failure, server unavailable - you
need to handle those situations as well.
Tony.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:2D4AE282-AC3D-416C-8405-4444C5ED01E8@.microsoft.com...
> the point is that SQL server 2k return as an error/msg. Assume a user
> performing a full text search query from coldfusion, user can enter
> anything. If user enter the word 'the' and because of SQL server 2k issue
> an
> error/msg, my SQL server service get HANG. I need it manually restart my
> service to get my sql server working.
> --
> xxx
>
> "Tony Rogerson" wrote:
>> 'the' a what is termed a noise word so is extracted from the query.
>> You can get and modify the noise word files the full-text engine uses -
>> do a
>> search for noise.*, there should be a noise.enu - depends on your locale
>> though.
>> Its classed a noise word along with the other symbols because thats not
>> really what full-text is about, think of all the occurances of 'the' and
>> 'a'
>> and 'and' for instance - does it really make sense indexing those items?
>> Tony.
>> --
>> Tony Rogerson
>> SQL Server MVP
>> http://sqlserverfaq.com - free video tutorials
>>
>> "eslim" <eslim@.discussions.microsoft.com> wrote in message
>> news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
>> > select pID,pcode from MyProduct where freetext(PIndex, 'the')
>> >
>> > I have the above full text search query. It generate the following err
>> > msg:
>> > "Server: Msg 7619, Level 16, State 1, Line 1
>> > Execution of a full-text operation failed. A clause of the query
>> > contained
>> > only ignored words. "
>> >
>> > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc,
>> > it
>> > also generate the same err msg.
>> >
>> > I have create a full text search index on product table, catalog, full
>> > population . It will not generate err when i use normal word e.g.
>> > 'buy',
>> > 'model' etc
>> >
>> > I am using MS SQL server 2000 on server 2003. Anything to do with
>> > configuration? Any one can help me with it?
>>|||If you're getting an error message returned from SQL Server then the
service isn't "hanging"...it's just that your application needs the
proper error-handling code to deal with this situation.
On Sat, 26 Nov 2005 05:58:02 -0800, "eslim"
<eslim@.discussions.microsoft.com> wrote:
>the point is that SQL server 2k return as an error/msg. Assume a user
>performing a full text search query from coldfusion, user can enter
>anything. If user enter the word 'the' and because of SQL server 2k issue an
>error/msg, my SQL server service get HANG. I need it manually restart my
>service to get my sql server working.|||Maybe I should rephrase my question to : How to perform full text search for
symbols(&^<.>? etc) in addition to normal word
-- scenario:
I have a user interface done in Coldfusion that access SQL server 2K store
procedure on server 2003 that perform full text search on a field of a table.
I have remove all content of noise.enu as user can use anything as a search
index.
Why is it that SQL server or SQL Analyser still return err/msg when I use
symbol as search index (e.g. {/}/?/>/</./,/@./!/$):
select pID,pcode from MyProduct where contains(PIndex, '@.')
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words." - error msg from SQL Analyser
At minimun SQL server should return no record. Instead it return err/msg,
that hangs my SQL service when run from Coldfusion and i had to manual
restart my service. Beside doing extra job of parsing user input, any easy
solution? Any ideas '
xxx
"eslim" wrote:
> the point is that SQL server 2k return as an error/msg. Assume a user
> performing a full text search query from coldfusion, user can enter
> anything. If user enter the word 'the' and because of SQL server 2k issue an
> error/msg, my SQL server service get HANG. I need it manually restart my
> service to get my sql server working.
> --
> xxx
>
> "Tony Rogerson" wrote:
> > 'the' a what is termed a noise word so is extracted from the query.
> >
> > You can get and modify the noise word files the full-text engine uses - do a
> > search for noise.*, there should be a noise.enu - depends on your locale
> > though.
> >
> > Its classed a noise word along with the other symbols because thats not
> > really what full-text is about, think of all the occurances of 'the' and 'a'
> > and 'and' for instance - does it really make sense indexing those items?
> >
> > Tony.
> >
> > --
> > Tony Rogerson
> > SQL Server MVP
> > http://sqlserverfaq.com - free video tutorials
> >
> >
> > "eslim" <eslim@.discussions.microsoft.com> wrote in message
> > news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
> > > select pID,pcode from MyProduct where freetext(PIndex, 'the')
> > >
> > > I have the above full text search query. It generate the following err
> > > msg:
> > > "Server: Msg 7619, Level 16, State 1, Line 1
> > > Execution of a full-text operation failed. A clause of the query contained
> > > only ignored words. "
> > >
> > > if i replace the word 'the' with symbol such as '-' or '?' or '>', etc, it
> > > also generate the same err msg.
> > >
> > > I have create a full text search index on product table, catalog, full
> > > population . It will not generate err when i use normal word e.g. 'buy',
> > > 'model' etc
> > >
> > > I am using MS SQL server 2000 on server 2003. Anything to do with
> > > configuration? Any one can help me with it?
> >
> >
> >|||The proper way is to put error handing in the coldfusion application to trap
any errors, not just this one.
I think you really ought to look at using LIKE '%@.%' for instance, Full-Text
just isn't for doing what you are trying to do.
If you think about what SQL Server is doing, basically it extracts any noise
stuff from your search term leaving what's left as the argument into the
full-text engine, if there is no argument then no search - thats basically
how it works.
Have you looked at FREETEXT instead of CONTAINS?
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:55E4BE89-6C31-4247-B8F8-DE2AA5F1819A@.microsoft.com...
> Maybe I should rephrase my question to : How to perform full text search
> for
> symbols(&^<.>? etc) in addition to normal word
> -- scenario:
> I have a user interface done in Coldfusion that access SQL server 2K
> store
> procedure on server 2003 that perform full text search on a field of a
> table.
> I have remove all content of noise.enu as user can use anything as a
> search
> index.
> Why is it that SQL server or SQL Analyser still return err/msg when I use
> symbol as search index (e.g. {/}/?/>/</./,/@./!/$):
> select pID,pcode from MyProduct where contains(PIndex, '@.')
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words." - error msg from SQL Analyser
> At minimun SQL server should return no record. Instead it return err/msg,
> that hangs my SQL service when run from Coldfusion and i had to manual
> restart my service. Beside doing extra job of parsing user input, any easy
> solution? Any ideas '
> xxx
>
> "eslim" wrote:
>> the point is that SQL server 2k return as an error/msg. Assume a user
>> performing a full text search query from coldfusion, user can enter
>> anything. If user enter the word 'the' and because of SQL server 2k issue
>> an
>> error/msg, my SQL server service get HANG. I need it manually restart my
>> service to get my sql server working.
>> --
>> xxx
>>
>> "Tony Rogerson" wrote:
>> > 'the' a what is termed a noise word so is extracted from the query.
>> >
>> > You can get and modify the noise word files the full-text engine uses -
>> > do a
>> > search for noise.*, there should be a noise.enu - depends on your
>> > locale
>> > though.
>> >
>> > Its classed a noise word along with the other symbols because thats not
>> > really what full-text is about, think of all the occurances of 'the'
>> > and 'a'
>> > and 'and' for instance - does it really make sense indexing those
>> > items?
>> >
>> > Tony.
>> >
>> > --
>> > Tony Rogerson
>> > SQL Server MVP
>> > http://sqlserverfaq.com - free video tutorials
>> >
>> >
>> > "eslim" <eslim@.discussions.microsoft.com> wrote in message
>> > news:9B7D9011-17C8-478F-9297-C8EF028FFCA7@.microsoft.com...
>> > > select pID,pcode from MyProduct where freetext(PIndex, 'the')
>> > >
>> > > I have the above full text search query. It generate the following
>> > > err
>> > > msg:
>> > > "Server: Msg 7619, Level 16, State 1, Line 1
>> > > Execution of a full-text operation failed. A clause of the query
>> > > contained
>> > > only ignored words. "
>> > >
>> > > if i replace the word 'the' with symbol such as '-' or '?' or '>',
>> > > etc, it
>> > > also generate the same err msg.
>> > >
>> > > I have create a full text search index on product table, catalog,
>> > > full
>> > > population . It will not generate err when i use normal word e.g.
>> > > 'buy',
>> > > 'model' etc
>> > >
>> > > I am using MS SQL server 2000 on server 2003. Anything to do with
>> > > configuration? Any one can help me with it?
>> >
>> >
>> >