full text searching

Ok

Im trying to get full text searching to work with parameterised stored procedures.

basicly the proc is

SELECT IndustrySector.title, BiblioHeadings.BiblioHeading, ReferenceTypes.ReferenceType, Bibliographies.Authors, Bibliographies.PublishDate, Bibliographies.BiblioTitle,

Publishers.Publisher, Bibliographies.Journal, Bibliographies.Issue, Bibliographies.BiblioKeyWords, Bibliographies.BiblioAnnotation, Bibliographies.Note,

Bibliographies.BiblioURL, IndustrySector.id

FROM Bibliographies INNER JOIN

BiblioHeadings ON Bibliographies.BiblioHeadingID = BiblioHeadings.BiblioHeadingID INNER JOIN

Publishers ON Bibliographies.PublisherID = Publishers.PublisherID INNER JOIN

ReferenceTypes ON Bibliographies.Reference = ReferenceTypes.ReferenceTypeID INNER JOIN

IndustrySector ON Bibliographies.SectorID = IndustrySector.id

WHERE CONTAINS(BiblioAnnotation, @.SearchFor_txt )

this works as long as only one word is in the @.SearchFor_txt parameter.

How to I get it to work with more than one word?

can I put the parameters in quote marks such as '" @.SearchFor_txt "' ? (I know this dosnt work with parameters but I need to contain the string)

any know how to resolve this issue please

jim

Books on line says

CONTAINS can search for:

A word or phrase.

The prefix of a word or phrase.

A word near another word.

A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).

A word that is a synonym of another word using thesaurus (for example, the word metal can have synonyms such as aluminum and steel).

Full Text Search with a Parameter

I have a Full Text query that works fine when structured as follows:

SELECT First_Name, Middle_Name, Last_Name, Hire_Date, City, Department, Phone_Ext, title, Fax, Secretary_Name, Attorney_Name, Secy_Ext, Home_Phone, Desk_Location, Law_School, Undergraduate_School, Languages, E_mail, CMS_ID, WEB_ID, Notary

FROM dbo.PhotoDir

WHERE CONTAINS (*, 'Jones')

ORDER BY Last_Name, First_Name

However, I would like to replace "Jones" with a parameter, @.LName for example. However, I can't figure out the syntax. I tried the following:

WHERE CONTAINS (*, @.LName)

but received the following error:

The @.LName SQL construct or statement is not supported.

Is there a way to put a parameter in this type of query?

Thanks in advance.

Hi There,

Have a look at this article, mnight give you a clue on what you doing there.

http://technet.microsoft.com/en-us/library/ms187787.aspx

Hope it helps!

|||

Hi lwhalen618,

See this example in sql2005 online book:

USE AdventureWorks;GODECLARE @.SearchWord nvarchar(30)SET @.SearchWord = N'Performance'SELECT Description FROM Production.ProductDescription WHERE CONTAINS(Description, @.SearchWord);GO

See, it's totally fine to use parameter within full-text-indexing. Your code seems okay to me. Please check if you have declared @.LName in your code.

Hope my suggestione helps

|||

I was able to get my code to work using a stored procedure. The query builder within VS2005 would not let me use the additional code before the Select statement.

Thanks for your help.

Full Text Search vs LIKE Search

Are there any big differences between the two search techniques? It seems like they are both very similar.
SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%'
SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')The like query will miss all indexes, but the fulltext query will use some sort of search-page-like index that the FullText engine has created.

Also, the FullText index has to be manually (or via a job) updated to collect all of the new additions. The like query will simply pick them up.|||I didn't try full text with mssql but yes... they are very diferent.
Like may seems to be fast some times, depending on the database, amount of records, etc, etc..
but compare full text index has the purpose to make very fast searchs. That's why the index it's so big.
Read about indexes of full text, and you will see that the size it's huge. Like has nothing to do in comparison to full text.

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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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 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...[vbcol=seagreen]
> 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:
sql

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?
>> >
>> >
>> >

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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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 a
n
>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:
[vbcol=seagreen]
> 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 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...[vbcol=seagreen]
> 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:
>

Full Text Search Query

Hi,
If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '), no
problem.
But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse" ')
returns an error, because the word 'the' is in the black list.
On my website, the users can check a checkbox if they want a search on all
the words they have entered.
Is there a way (something else having my own blacklist and removing by
myself the black words before sending the query to SQL Server...) to avoid
this error ?
If the only way is to use my own blacklist, is there a way to retreive the
SQL Server's one ?
Thanks.Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:

> Hi,
> If i write: SELECT * FROM Table1 WHERE Contains(*, ' "the" OR "horse" '),
> no
> problem.
> But the query SELECT * FROM Table1 WHERE Contains(*, ' "the" AND "horse"
> ') returns an error, because the word 'the' is in the black list.
> On my website, the users can check a checkbox if they want a search on all
> the words they have entered.
> Is there a way (something else having my own blacklist and removing by
> myself the black words before sending the query to SQL Server...) to avoid
> this error ?
> If the only way is to use my own blacklist, is there a way to retreive the
> SQL Server's one ?
You can remove the list of words from the SQL Server noise word file, or
remove the words from your query. I ended up clearing out my noise word
file. It depends on your installation path (and possibly SQL Server version)
as to where your noise word files are, mine are in
\MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
noise word file is noise.enu, but I also edited noise.eng. If you decide to
remove all the noise words from the config, do not empty the file
completely - leave a single line with a space on it. You'll have to
repopulate your catalogs if you change your noise word file so that the
words you have removed are added to the catalogs.
BTW: a more appropriate group for this is
microsoft.public.sqlserver.fulltext :)
Dan|||Steph,
In addition to what Daniel says below... Yes, the full path to the noise
word files (noise.enu) is SQL Server version specific, I usually shorten it
to the files under \FTDATA\SQLServer\Config. Note, that noise.enu = US
English, noise.eng = UK English and noise.dat = Neutral and are related to
the column-specific "Language for Word Breaker" in the FT Indexing wizard.
Also, run a Full Population after modifying the noise word files.
You may also want to review KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defau...kb;EN-US;246800
For other SQL FTS resources see "SQL Server 2000 Full-Text Search Resources
and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.e
ntry
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:um7ny3iLFHA.656@.TK2MSFTNGP14.phx.gbl...
> Steph wrote on Mon, 21 Mar 2005 16:34:21 +0100:
>
'),
all
avoid
the
> You can remove the list of words from the SQL Server noise word file, or
> remove the words from your query. I ended up clearing out my noise word
> file. It depends on your installation path (and possibly SQL Server
version)
> as to where your noise word files are, mine are in
> \MSSQL7\FTDATA\SQLServer\Config. As my server is configured for English my
> noise word file is noise.enu, but I also edited noise.eng. If you decide
to
> remove all the noise words from the config, do not empty the file
> completely - leave a single line with a space on it. You'll have to
> repopulate your catalogs if you change your noise word file so that the
> words you have removed are added to the catalogs.
> BTW: a more appropriate group for this is
> microsoft.public.sqlserver.fulltext :)
> Dan
>

Full text search installed but no languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

sql

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search installed but ni languages supported :(

Hi,

I Have the same problem, i execute the T-sql statement select * from sys.fulltext_languages and i get zero rows, i uninstalled and installed again and nothing happend i still have zero when executing that statement, i try another computer and there evrything went fine!.. i do have Microsoft Net framework, and everything but i still can get this to work! help please!!!

Hi Ikeer,

Fulltext Search is not installed by default. You should confirm that you actually selected if from the feature list when you installed it. If you just took the defaults (i.e. clicked Next until you finished) you didn't install it. You'll need to launch maintenance mode from Add/Remove Programs and select Fulltext Search from the feature tree.

Mike

|||

Hi Mike,

Yes, I know i installed the Fullsearch text but still i do not get languages. I follow the same step's in my other computer and it worked in that one, the only difference between the two systems is that i installed the SQL sever 2005 beta in the one that is failing. I realized that and read that it need it to be removed so i removed the SQL server 2005 and then went to a link that i found here and it remove the beta. I double check and the application told me that they were not traces of the Beta products. I though that would work now.. but it did not.. when i reinstalled the SQL server 2005 i had no languages :(. I gave up and worked remotely in my other computer. I would love to know what is preventing me from install the SQL Server because i dont want to reinstall everything.. it's like 20hrs :(. anyway any help would be appriciate.

Thank you.

|||

Hi!

Did you try actualy running a FT query? does it work? try selecting a language at query time different than neutral or English?

If it worked in other computer with Express, it might be because that other computer is using the OS WordBreakers and SQL Server is loading them. It seems that this is not happening in your instance (you dont see the installed ones nor the OS ones). Let me investigate more and come back when I get more answers.

Thanks.

|||

Did this resolve the issue?

Mike

|||

Hi again guys,

We need to be sure that FTS was installed correctly. Please do the following:

-Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSearch\Language

And let me know which folders/languages are you seeing there. If you don't see any, then it is not correctly installed, if you see them, please forward them to me and we will investigate here what is going on.

you can reply me as well to fernlope@.microsoft.com

Thanks!

|||

Hi i went to that key and saw many folders, inside the language there is a ke call default type REG_SZ Data value not set

then there are many folders

chs

cht

deu

eng

enu

esn

fra

ita

jpn

kor

neutral

nld

sve

tha

zh-hk

zh-mo

zh-sg

by the way, ansering if i tried to run the Full text search query, i did not since it wont let me create the full search in the table.

P.S i also had sql server 2000 install but i unisntalled it way before install the SQL server 2005

Thank you for your help

|||

I am having the exact same problem as Ikeer described above. I did not previously have any SQL Server installed on the computer. I do have MS Visual Studio 2005 Professional installed, but did not elect to install SQL server from that setup. Has there been any new info on this? I tried reinstalling just FTS for my instance, but that didn't seem to help.

Full text search can not return result

Hi all,
In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
From http://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Very rarely when you are in the middle of a merge you may get an
inaccurate count like what you are seeing. it lasts for milli-
seconds.
On Jan 16, 4:56 am, ken<nos...@.developmentnow.com> wrote:
> Hi all,
> In my case, I build a full text search query. like "select * from _tt_fields where contains(string,'"development group"')", when I click F5 then execute the sql, it returned the records, but when I click execute the query more than 4 times, it does not return anything!
> Fromhttp://www.developmentnow.com/g/104_0_0_0_0_0/sql-server-fulltext.htm
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com

full text search : Search orders of single input of "multiple word

Sql server express advanced edition
Hi
I am using following query to select data using full text search in a stored
procedure:
=====================
SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
Select *
from tablename1
where contains (*, @.keyword)
===========================
It returns me rows for keyword "Fish Chips" which have values (Fish and
chips) but when i change my search keyword to "Chips Fish" it does not return
any rows.
Can I modify my query so that It returms me all the rows containing Fish or
chips in any order?
i.e. In the input screen for users they input a combination of words like
"chinese mexican". My query should spit all rows which have chinese and
mexican in any order in one of indexed columns.
For above input (Chinese mexican) I should have output like :
Chinese mexican food
chinese and mexican food
mexican and chinese food
mexican chinese clothing
mexican food indian food and chinese food
If I need to parse input and divide it in multiple words before searching,
If someone has a example on how to do this, will be great.
ontario, canada
In other words, I have requirement where I need to do full text search on
all the
search text. For example, for the search text "fish chips" , I should get
the following records having "fish chips" as well as "chips fish".
ontario, canada
"db" wrote:

> Sql server express advanced edition
> Hi
> I am using following query to select data using full text search in a stored
> procedure:
> =====================
> SET @.keyword = '"*' + LOWER(@.keyword) + '*"'
> Select *
> from tablename1
> where contains (*, @.keyword)
> ===========================
> It returns me rows for keyword "Fish Chips" which have values (Fish and
> chips) but when i change my search keyword to "Chips Fish" it does not return
> any rows.
> Can I modify my query so that It returms me all the rows containing Fish or
> chips in any order?
> i.e. In the input screen for users they input a combination of words like
> "chinese mexican". My query should spit all rows which have chinese and
> mexican in any order in one of indexed columns.
> For above input (Chinese mexican) I should have output like :
> Chinese mexican food
> chinese and mexican food
> mexican and chinese food
> mexican chinese clothing
> mexican food indian food and chinese food
> If I need to parse input and divide it in multiple words before searching,
> If someone has a example on how to do this, will be great.
>
> --
> ontario, canada

Full Text Search (2005) - How to determine word offset in CONTAINS query?

Does anyone know if it is possible to determine the relative word offset (the Occ) from a simple-term query such as:-

SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "mountain biking" ');

So, given the text:-

"Maybe it's just because I'm new to mountain biking, but I had a terrible time getting used to these pedals."

I would like the query would return both the text and the word offset of 8. To me, it seems like this would be quite useful as I want to highlight the found text for the user to see. Obviously I can do a post-SELECT scan of the string to find the values but this would seem unnecessary.

If anyone can give me any pointers I'd be very grateful.

Thanks

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93 since this is not a SQL Server Data Mining feature.|||I think I will, but I wasn't sure where to post the question as there isn't an FTS forum (yet!). Thanks for your suggestion.sql

Full Text Problem Help please

DECLARE @.Wrd varchar(50)
SELECT UName
FROM Basic
WHERE CONTAINS(UName, @.Wrd)
Im using the the following query to get the user name from the table if the
user doesn't know it all. Im using SQLEXPRESS 2005 and it says i can't use
full text searching. Is there anything I can do, or can sum1 give me a query
that does the same job on my version of SQL. Thank you.sum1 ?
Look up LIKE in Books Online.
There is no full-text indexing in SQL 2005 Express.
ML|||Eamon,
Please, checkout the "SQL Server 2005 Features Comparison" at
http://www.microsoft.com/sql/2005/p...05features.mspx under
"Manageability", see that "Full Text Search" for "Express" is not check
marked, and therefore Full Text Search (FTS) is not supported in the SQL
Server 2005 Express edition.
I am curious, if you had a "full-text search" feature that was functional as
a 3rd party add-on to SQL 2005 Express would you find it useful? What
features are you looking for?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"eamon" <eamon@.discussions.microsoft.com> wrote in message
news:BCF75263-FB77-419E-9A09-55AD0E8FB363@.microsoft.com...
> DECLARE @.Wrd varchar(50)
> SELECT UName
> FROM Basic
> WHERE CONTAINS(UName, @.Wrd)
> Im using the the following query to get the user name from the table if
> the
> user doesn't know it all. Im using SQLEXPRESS 2005 and it says i can't use
> full text searching. Is there anything I can do, or can sum1 give me a
> query
> that does the same job on my version of SQL. Thank you.|||Well, I would be interested in a third party FTS tool. :) Have you seen
sqlTurbo?
ML|||ML,
Of course, its listed on my blog as SQL Turbo from Imceda. However, that's
not was what I was referring to, but more a low-cost T-SQL based FTS tool
for SQL Server 2000 MSDE or SQL Server 2005 Express. What features are you
looking for?
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:9BA03825-F792-4638-A78A-9F293E55EEF5@.microsoft.com...
> Well, I would be interested in a third party FTS tool. :) Have you seen
> sqlTurbo?
>
> MLsql

full text problem

Sorry my poor english
I am using SQL SERVER 2005 FT-enable database,
my qruestion is
some query like :
select * from test where contains(description ,'二次金改')
return 343 records--ok
try again same query
return 343 records--ok

but try 4 times later the same query,
return 0 record --stranger
and continue try is alway return 0 recode

Next day try the same query ,
return return 343 records--ok
but same situation appear again-try 4 times later the same query
return 0 record

Hi,

Which database collation are you using? Are you using a Thesaurus, and which noise word file are you using. Sorry for the questions to your questions but it helps find the problem.

Can you recreate the problem with a simple test script that you could post?

Best regards

Trevor Dwyer

|||

Thank you for replay,

database collation: Chinese_Taiwan_Stroke_CI_AS

noise word :noiseCHS.txt

I am not using aThesaurus

USE [icdb02]
GO
/****** Object: Table [dbo].[picture] Script Date: 11/05/2006 14:27:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[picture](
[FileID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
[Photographer] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Place] [char](12) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PaperID] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[PictureDate] [smalldatetime] NULL,
[Description] [varchar](255) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Available] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[TimeLimit] [smalldatetime] NULL,
[DeleteDate] [smalldatetime] NULL,
[Processed] [char](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[OperatorID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[InputDate] [smalldatetime] NULL,
[InputID] [char](15) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_picture_rowguid] DEFAULT (newid()),
[picstamp] [timestamp] NULL,
CONSTRAINT [PK_picture] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Full Text option install

I have been trying to figure out why my full text menu option is not
working and it seems it is not installed.
I did get a 0 when I did the:
SELECT fulltextserviceproperty('IsFulltextInstalled')
Why wouldn't it be installed? Is it a special option? How do I install it.
Here is the results from select @.@.version
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
Thanks,
Tom.It is not selected by default in the setup program. Run setup again and
select the ft index service.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:404A22E4.9090902@.deltanet.com...
> I have been trying to figure out why my full text menu option is not
> working and it seems it is not installed.
> I did get a 0 when I did the:
> SELECT fulltextserviceproperty('IsFulltextInstalled')
> Why wouldn't it be installed? Is it a special option? How do I install
it.
> Here is the results from select @.@.version
> Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Desktop Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> Thanks,
> Tom.
>|||Tibor Karaszi wrote:
> It is not selected by default in the setup program. Run setup again and
> select the ft index service.
>
Does this have to be run from the CD, or can it be run off the harddisk
with what is already there?
Thanks,
Tom.
>|||Thomas Scheiderich wrote:
> Tibor Karaszi wrote:
>> It is not selected by default in the setup program. Run setup again and
>> select the ft index service.
> Does this have to be run from the CD, or can it be run off the harddisk
> with what is already there?
When I try to load from the CD, it will only allow me to load the full
install (either desktop or standard). There doesn't seem to be a way to
install optional items the way 6.5 did.
> Thanks,
> Tom.
>|||I have an instance named FRESH on my machine. Insert he DVD (correct
edition, of course), Autorun, SQL Server 2000 Components, Install Database
Server. And then I get an option to "Upgrade, remove or add components to an
existing installation". The name of this dialog is "Installation Selection
and the other two options are "Create a new instance..." and "Advanced
options".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:404A2753.2090806@.deltanet.com...
> Tibor Karaszi wrote:
> > It is not selected by default in the setup program. Run setup again and
> > select the ft index service.
> >
> Does this have to be run from the CD, or can it be run off the harddisk
> with what is already there?
> Thanks,
> Tom.
> >
>|||Tibor Karaszi wrote:
> I have an instance named FRESH on my machine. Insert he DVD (correct
> edition, of course), Autorun, SQL Server 2000 Components, Install Database
> Server. And then I get an option to "Upgrade, remove or add components to an
> existing installation". The name of this dialog is "Installation Selection
> and the other two options are "Create a new instance..." and "Advanced
> options".
>
You're right. When I ran the install further, it allows me to install
other options.
But the only other 2 options not chose are development tools and code
samples. Everything else is checked.
So, why isn't full text search not showing? Could it be because on this
machine I only have the desktop version running?
Tom.
>|||Hmm, according to Books Online for SQK2K, FT is supported for Personal
Edition (except for Windows 98). Is there a "Microsoft Search" service on
your machine?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Thomas Scheiderich" <tfs@.deltanet.com> wrote in message
news:404AAACA.5090205@.deltanet.com...
> Tibor Karaszi wrote:
> > I have an instance named FRESH on my machine. Insert he DVD (correct
> > edition, of course), Autorun, SQL Server 2000 Components, Install
Database
> > Server. And then I get an option to "Upgrade, remove or add components
to an
> > existing installation". The name of this dialog is "Installation
Selection
> > and the other two options are "Create a new instance..." and "Advanced
> > options".
> >
> You're right. When I ran the install further, it allows me to install
> other options.
> But the only other 2 options not chose are development tools and code
> samples. Everything else is checked.
> So, why isn't full text search not showing? Could it be because on this
> machine I only have the desktop version running?
> Tom.
> >
>|||Tibor Karaszi wrote:
> Hmm, according to Books Online for SQK2K, FT is supported for Personal
> Edition (except for Windows 98). Is there a "Microsoft Search" service on
> your machine?
>
No. There is an indexing service, but that appears to be for something else.
I also have SQL server 7 loaded on my MS2K server as a standard edition
and it doesn't work there, either.
Tom

Full text indexing parentheses and special characters

select @.@.version
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all other special characters while generating the index? These characters aren't in the noise.eng file and I can't seem to figure out how to index them.
The reason we need these characters indexed is because of searches like "4 (1 1/2)" where only records with the exact phrase should be returned. Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we need to eliminate them.
Naresh
Naresh,
First of all, thanks for providing your SQL Server and OS platform version
info as the latter is especially important for this issue.
Unfortunately, the means to control the use of punctuation (all the special
characters) is not accessible, nor controllable via any registry key/value,
dbcc or trace flag as this is a big issue for many SQL FTS customers that is
not addressed in SQL Server 2000 with the MSSearch service.
There is some improvement via the OS-supplied wordbreaker in Win2003
(langwrbk.dll) over the Win2K supplied wordbreaker (infosoft.dll), however,
neither OS provides any means to control how the MSSearch service handles
these use of punctuation in the FT indexing process. Note, this may or may
not change in the next release of SQL Server 2005 (codename Yukon) that will
ship with its own wordbreaker (langwrbk.dll).
You may also want to experiment with using FREETEXT vs. CONTAINS on Win2K
and Win2003 to get the best results possible in your environment.
Regards,
John
PS: If I had my way this and the noise word file would be at the table level
and not at the OS level, but then that's just wishful thinking after many
years of having to answer this question... :-)
"Naresh Rajpal" <RajpalN@.sullcrom.com> wrote in message
news:97E163D6-1CB6-4947-B9AA-096FC28FC631@.microsoft.com...
> select @.@.version
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> How you tell the indexer (SQL 2K with WIN2K) to include () , /, - and all
other special characters while generating the index? These characters
aren't in the noise.eng file and I can't seem to figure out how to index
them.
> The reason we need these characters indexed is because of searches like "4
(1 1/2)" where only records with the exact phrase should be returned.
Currently records with 4 1/2 and 4 1 1/2 are being returned as well and we
need to eliminate them.
> Naresh
>
>

full text indexing

full text indexing

Hi,

In SQL Server 2005, if I set full text indexing enables in column MyDesc and

1. use "Select * from MyTable where MyDesc LIKE '%abc%'" would this be using full text indexing? Or have to use Contains to get it be in use?

2. Once I create the full-text index, should I be setting it to populate periodically? Isn't it populating itself?

1. Your first query will not need to use your full text index, instead it will likely just use your PK_index. You can click the "Include Actual Execution Plan" to see how the query is performed. In a query that uses contains, you will see a Remote Scan task - this is because full-text indexing is not internal to MSSQL, but uses a windows service.

2. Yes populating your full-text indexes every now and again is recommended, because any changes tounderlying data columns are not immediately reflected in the full-textindex (due to the external structure for storing full-text indexes). So, to keep your full-text indexes up to date, you need to populate or a crawl them periodically.

Hope this helps,

Tones.

|||

TonyMilne:

2. Yes populating your full-text indexes every now and again is recommended, because any changes tounderlying data columns are not immediately reflected in the full-textindex (due to the external structure for storing full-text indexes). So, to keep your full-text indexes up to date, you need to populate or a crawl them periodically.

You can ask SQL server to track the changes automatically and update the indexes. Use below 2 queries to configure automatic indexing.

EXECsp_fulltext_table <table name>,'Start_change_tracking'EXECsp_fulltext_table <table name>,'Start_background_updateindex'

The action 'Start_change_tracking' asks SQL Server to start and incremental population of full text index.

The action 'Start_background_updateindex' asks SQL Server to propagate the changes as and when they occur.

So, each of your Insert, Update and Delete query on the table will automate prompt the incremental population and the indexes will be automatically updated.

Full text index query plans

I have a table with a full-text index on a given column. If I run the
following:
declare @.s varchar(100)
select @.s = 'fast'
select * from tblHannahRES where contains(RES_SER_TI, @.s)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
on my SQL2000/SP4 box I get substantially different access times, which
is unlike what I would get with a normal index i.e. performing the SQL
below (where the index is on ResID) yields identical plans and access
times:
declare @.i int
select @.i = 1463440
select * from tblHannahRES where ResID = @.i
select * from tblHannahRES where ResID = 1463440
With the full-text indices I get the plans shown below and the second
query is consistenly 6X faster than the first.
One thing I notice is that the remote scan itself costs 0.36 vs 0.06
though both generate the same number of rows. the second thing to
notice is that the first case incurs an extra sorting step (which costs
about 0.02 points). I'm not sure why this should be the case. Can
anyone elucidate on the issue here?
TIA - e
select * from tblHannahRES where contains(RES_SER_TI, @.s)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
| |--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)
select * from tblHannahRES where contains(RES_SER_TI, 'fast')
|--Nested Loops(Inner Join, OUTER
REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
|--Remote Scan(OBJECT:(CONTAINS))
|--Clustered Index
S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
FORWARD)thoughts anyone?
ekkis wrote:
> I have a table with a full-text index on a given column. If I run the
> following:
> declare @.s varchar(100)
> select @.s = 'fast'
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> on my SQL2000/SP4 box I get substantially different access times, which
> is unlike what I would get with a normal index i.e. performing the SQL
> below (where the index is on ResID) yields identical plans and access
> times:
> declare @.i int
> select @.i = 1463440
> select * from tblHannahRES where ResID = @.i
> select * from tblHannahRES where ResID = 1463440
> With the full-text indices I get the plans shown below and the second
> query is consistenly 6X faster than the first.
> One thing I notice is that the remote scan itself costs 0.36 vs 0.06
> though both generate the same number of rows. the second thing to
> notice is that the first case incurs an extra sorting step (which costs
> about 0.02 points). I'm not sure why this should be the case. Can
> anyone elucidate on the issue here?
> TIA - e
> select * from tblHannahRES where contains(RES_SER_TI, @.s)
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Sort(ORDER BY:([FULLTEXT:tblHannahRES].[KEY] ASC))
> | |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)
> select * from tblHannahRES where contains(RES_SER_TI, 'fast')
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([FULLTEXT:tblHannahRES].[KEY]) WITH PREFETCH)
> |--Remote Scan(OBJECT:(CONTAINS))
> |--Clustered Index
> S(OBJECT:([Merlin].[dbo].[tblHannahRES].[PK_tblHannahRES]),
> SEEK:([tblHannahRES].[ResID]=[FULLTEXT:tblHannahRES].[KEY]) ORDERED
> FORWARD)