Full Text Search in text field for 'JN00001'

Hi
(I posted this on the full text newsgroup but it seems pretty quiet at the
mo and I was hoping someone on this list might know the answer).
I have a full text index on a table on a few columns including a text data
type field, and I'm searching for JN00001 which I know is in the text column
that is indexed by the full text index, however the result is not being
returned. Is this an issue related to noise words, or why would the result
not be being returned?
Thanks for any help
Cheers
MattHi Matt,
Can you post the query please.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
> Hi
> (I posted this on the full text newsgroup but it seems pretty quiet at the
> mo and I was hoping someone on this list might know the answer).
> I have a full text index on a table on a few columns including a text data
> type field, and I'm searching for JN00001 which I know is in the text
> column
> that is indexed by the full text index, however the result is not being
> returned. Is this an issue related to noise words, or why would the result
> not be being returned?
> Thanks for any help
> Cheers
> Matt
>
>|||Hi, thanks, the query is
SELECT rank, doc_id, pub_date, title, synopsis, notes
FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
ON doc.doc_id=Result.[Key]
But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
which has the text column in question with the 'JN00001' data in it and
other data, as follows:
RESEARCH SUMMARY
Title:
Emergency Help Points (EHP) at bus stations
Objective:
Passengers' awareness of EHP's, perceived uses and attitudes towards them
as a personal security aid.
Date:
February 2001
Methodology:
387 passengers were interviewed waiting for buses at Crystal Palace and
Addington Village bus stations between 9AM and 9PM. Interviews achieved are
broadly representative of London Buses users in terms of gender and age.
Key findings
<trimmed text>Three fifths of passengers at Addington station were aware
that there was an EHP at their station compared with only two fifths of
passengers at Crystal Palace station. Some passengers at Addington had
learned of the EHPs in the media
</trimmed text>.
Job number: JN00001
Any ideas?
Cheers
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hi Matt,
> Can you post the query please.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:uW7jVOPeGHA.4900@.TK2MSFTNGP02.phx.gbl...
>|||Does it return when you do just this?
SELECT *
FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
I know this works because I have a process that takes the USENET groups and
puts them into a SQL Server 2000 database that has a full-text catalogue on
it and the following query brings back our posts!
select *
from mb_message_detail
where contains( body, 'JN00001' )
Tony.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
> Hi, thanks, the query is
> SELECT rank, doc_id, pub_date, title, synopsis, notes
> FROM doc JOIN CONTAINSTABLE(doc, *, 'JN00001') AS Result
> ON doc.doc_id=Result.[Key]
> But when I replace 'JN00001' with 'EHPs' then record 538 gets returned,
> which has the text column in question with the 'JN00001' data in it and
> other data, as follows:
> RESEARCH SUMMARY
> Title:
> Emergency Help Points (EHP) at bus stations
> Objective:
> Passengers' awareness of EHP's, perceived uses and attitudes towards them
> as a personal security aid.
> Date:
> February 2001
> Methodology:
> 387 passengers were interviewed waiting for buses at Crystal Palace and
> Addington Village bus stations between 9AM and 9PM. Interviews achieved
> are broadly representative of London Buses users in terms of gender and
> age.
>
> Key findings
> <trimmed text>Three fifths of passengers at Addington station were aware
> that there was an EHP at their station compared with only two fifths of
> passengers at Crystal Palace station. Some passengers at Addington had
> learned of the EHPs in the media
> </trimmed text>.
> Job number: JN00001
>
> Any ideas?
> Cheers
> Matt
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:ODYnsZPeGHA.3952@.TK2MSFTNGP04.phx.gbl...
>|||No...
Thanks
Matt
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
> Does it return when you do just this?
> SELECT *
> FROM CONTAINSTABLE(doc, *, 'JN00001') AS Result
> I know this works because I have a process that takes the USENET groups
> and puts them into a SQL Server 2000 database that has a full-text
> catalogue on it and the following query brings back our posts!
> select *
> from mb_message_detail
> where contains( body, 'JN00001' )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a
> SQL Server Consultant
> http://sqlserverfaq.com - free video tutorials
>
> "Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
> news:%239QaFuPeGHA.380@.TK2MSFTNGP04.phx.gbl...
>|||Hi Matt,
Do a full rebuild on the catalogue, the row can't be indexed for some
reason, the noise file won't effect that being indexed.
Can you script out the CREATE TABLE and full-text catalogue creation for me.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Matt Jensen" <replytonewsgroups@.microsoft.com> wrote in message
news:eQyl6pQeGHA.2188@.TK2MSFTNGP04.phx.gbl...
> No...
> Thanks
> Matt
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:uG$1CXQeGHA.3932@.TK2MSFTNGP05.phx.gbl...
>