Full Text ContainsTable does not return desired results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aartinawani@hotmail.com - 20 Jun 2005 19:04 GMT

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)

Reply to this Message

Hilary Cotter - 21 Jun 2005 03:38 GMT

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'

Signature

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

> Hi -
>
[quoted text clipped - 28 lines]
> 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
> Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Reply to this Message

Aarti. - 21 Jun 2005 14:20 GMT

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.

> 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?
[quoted text clipped - 38 lines]
> > 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise
> > Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Reply to this Message