MS SQL Server DTS - Problems using DTS to build a table just for FT indexing

Author

Problems using DTS to build a table just for FT indexing

Brad Baker

2005-05-26, 8:24 pm

Description: We are using a DTS to build a table just for FT indexing.
Basically we gather all of the words from the fields we'd like to search,
and put them into a single table (moosejaw_tblFullTex
t). Then we're using
FormsOf (Inflecitonal...) to get results as the SQL below shows.

For one of our customers, we need to be able to differentiate between words
like "person", "2-person", and "4-person". Right now the SQL below returns
ALL results for "person". From what I've read, I believe it is considering
the dash "-" to be a word, and since "2" is a noise word, it effectively
only searches for "person". I read that this was fixed in Windows Server
2003, in the langwrbk.dll, but it does not appear so.

Does anyone have any suggestions or ideas?

SQL:
Select uvw.pf_id, ft.* From uvw_cat_prod_backord
er uvw, moosejaw_tblFullText

ft Where uvw.pf_id=ft.pf_id AND Contains(ft.Description,' FORMSOF
(INFLECTIONAL, "2-person") ')

@@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.2 (Build 3790: Service Pack 1)

Thanks,
Brad

Peter Yang [MSFT]

2005-05-27, 3:23 am

Hello Brad,

This behavior is as designed. Please refer to the following article:

200043 PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/?id=200043

A custom word breaker that does not break on hyphens is the only solution
from a
purely Indexing Service standpoint. The following links are for your
reference:

Word Breaker and Stemmer Sample
<<http://msdn.microsoft.com/library/e...enario_3e91.asp
?frame=true>>

Constructing Language Resource Components
<<http://msdn.microsoft.com/library/e...enario_9i0j.asp
?frame=true>>

Implementing a Word Breaker
<<http://msdn.microsoft.com/library/e...enario_54bp.asp
?frame=true>>

Property Names
<<http://msdn.microsoft.com/library/e...h_4er7.asp?fram
e=true>>

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Brad Baker" <brad@nospam.nospam>
| Subject: Problems using DTS to build a table just for FT indexing
| Date: Thu, 26 May 2005 15:17:38 -0400
| Lines: 28
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <ORTzVeiYFHA.2768@tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.dts
| NNTP-Posting-Host: cpe-66-66-2-236.rochester.res.rr.com 66.66.2.236
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.dts:12225
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| Description: We are using a DTS to build a table just for FT indexing.
| Basically we gather all of the words from the fields we'd like to search,
| and put them into a single table (moosejaw_tblFullTex
t). Then we're
using
| FormsOf (Inflecitonal...) to get results as the SQL below shows.
|
| For one of our customers, we need to be able to differentiate between
words
| like "person", "2-person", and "4-person". Right now the SQL below
returns
| ALL results for "person". From what I've read, I believe it is
considering
| the dash "-" to be a word, and since "2" is a noise word, it effectively
| only searches for "person". I read that this was fixed in Windows Server
| 2003, in the langwrbk.dll, but it does not appear so.
|
| Does anyone have any suggestions or ideas?
|
| SQL:
| Select uvw.pf_id, ft.* From uvw_cat_prod_backord
er uvw,
moosejaw_tblFullText

| ft Where uvw.pf_id=ft.pf_id AND Contains(ft.Description,' FORMSOF
| (INFLECTIONAL, "2-person") ')
|
| @@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.2 (Build 3790: Service Pack 1)
|
| Thanks,
| Brad
|
|
|

Sponsored Links