Full Text ContainsTable does not return desired results

Hi -
I did the following SQL Query.
set @.sql = @.sql + 'select distinct
s.PrimaryKey,
indx.rank, d.documenttype_id,
d.publish_date, d.Document_ID, d.Document_name, d.Description,
d.create_date
,isnull(f.mimetype,'''') as [mimetype]
,isnull(f.filesize,0) as [filesize]
--,dbo.fn_GetBreadCrumbHtml(d.Document_ID) as [breadcrumb]
,'''' as [breadcrumb]
from SearchItem s Join
containstable(SearchItem, *, ''' + @.p_keywords + ''') '+
'indx on s.SearchItem_Id = indx.[key]
JOIN document d on s.primarykey = d.document_id'
However, when I set the @.p_keywords = "ACLU" or "ability to pay" or
"credibility determination" - I get back relevant results but not every
single document that contains the keywords. I checked the documents
that are not returned and they exist in the full text table & do
contains the exact keywords.
Thanks in advance. -Aarti.
My config:
us_english
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
can you tell me anything about the nature of the data in the table? Is any
of it in the image column? If so what is the document type?
Also what happens if you uncomment the
'indx on s.SearchItem_Id = indx.[key]
and comment the
JOIN document d on s.primarykey = d.document_id'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aartinawani@.hotmail.com> wrote in message
news:1119290688.908083.251200@.g14g2000cwa.googlegr oups.com...
> Hi -
> I did the following SQL Query.
> set @.sql = @.sql + 'select distinct
> s.PrimaryKey,
> indx.rank, d.documenttype_id,
> d.publish_date, d.Document_ID, d.Document_name, d.Description,
> d.create_date
> ,isnull(f.mimetype,'''') as [mimetype]
> ,isnull(f.filesize,0) as [filesize]
> --,dbo.fn_GetBreadCrumbHtml(d.Document_ID) as [breadcrumb]
> ,'''' as [breadcrumb]
> from SearchItem s Join
> containstable(SearchItem, *, ''' + @.p_keywords + ''') '+
> 'indx on s.SearchItem_Id = indx.[key]
> JOIN document d on s.primarykey = d.document_id'
> However, when I set the @.p_keywords = "ACLU" or "ability to pay" or
> "credibility determination" - I get back relevant results but not every
> single document that contains the keywords. I checked the documents
> that are not returned and they exist in the full text table & do
> contains the exact keywords.
> Thanks in advance. -Aarti.
> My config:
> us_english
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000
> 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
|||Thanks for your response. We are indexing two columns one image (document
type = pdf) and one ntext.
This line is not commented - 'indx on s.SearchItem_Id = indx.[key]
It's just a sql string I put together and execute at the end of the stored
proc.
This line - JOIN document d on s.primarykey = d.document_id : cannot be
commented out because the where clause is dependent on the document table. I
sql query I posted was just a sample not the entire query. Please let me know
if you need any more information and thanks again for your response.
"Hilary Cotter" wrote:

> can you tell me anything about the nature of the data in the table? Is any
> of it in the image column? If so what is the document type?
> Also what happens if you uncomment the
> 'indx on s.SearchItem_Id = indx.[key]
> and comment the
> JOIN document d on s.primarykey = d.document_id'
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> <aartinawani@.hotmail.com> wrote in message
> news:1119290688.908083.251200@.g14g2000cwa.googlegr oups.com...
>
>