Re: Ignored words problem

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Hilary Cotter (hilaryk_at_att.net)
Date: 07/17/04 

Date: Fri, 16 Jul 2004 21:05:10 -0400

I am questioning the accuracy of this statement of yours: "sp_MShelpcolumns
returns the column's collation and not the "Language for Word Breaker" value

sp_MShelpcolumns does return the LCID in the final column of tables you are
FTI'ing.

xp_MSfulltext while undocumented does return a list of languages and LCIDs.
These correspond to the word breakers used in SQL FTS.

It comes in very handy when MDSN is not installed or available. 

-- 
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <JohnKane@discussions.microsoft.com> wrote in message
news:1428D74F-4B54-4B9F-BFF4-A201190A14C6@microsoft.com...
> Hilary,
> Actually, I never said that you incorrect, just that sp_MShelpcolumns
"...ultimately may not provide the correct answer as sp_MShelpcolumns
returns the column's collation and not the "Language for Word Breaker"
value, or at least I believe so... " and that you were recommended
un-documented system stored procedures when the perfectly documented
sp_help_fulltext_columns would of been sufficient to get information from
Nick in order to answer his question... Sorry, if I was misleading...
>
> Respectively,
> John
>
>
> "Hilary Cotter" wrote:
>
> > you're incorrect John, sp_MShelpcolumns returns info on the word breaker
not
> > the collation.
> >
> > repro below
> >
> >
> >
> > if exists (select * from dbo.sysobjects where id =
> > object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> > drop table [dbo].[Table1]
> > GO
> >
> > CREATE TABLE [dbo].[Table1] (
> >  [pk] [int] NOT NULL ,
> >  [lang1] [char] (10) COLLATE Albanian_BIN NULL ,
> >  [lang2] [char] (10) COLLATE Chinese_PRC_Stroke_BIN NULL ,
> >  [lang3] [char] (10) COLLATE French_BIN NULL ,
> >  [lang4] [char] (10) COLLATE Greek_BIN NULL ,
> >  [lang5] [char] (10) COLLATE Slovenian_BIN NULL ,
> >  [lang6] [char] (10) COLLATE Ukrainian_BIN NULL ,
> >  [lang7] [char] (10) COLLATE Romanian_BIN NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[Table1] ADD
> >  CONSTRAINT [PK_Table1] PRIMARY KEY  CLUSTERED
> >  (
> >   [pk]
> >  )  ON [PRIMARY]
> > GO
> >
> > if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
> > exec sp_fulltext_database N'enable'
> >
> > GO
> >
> > if not exists (select * from dbo.sysfulltextcatalogs where name =
N'test')
> > exec sp_fulltext_catalog N'test', N'create'
> >
> > GO
> >
> > exec sp_fulltext_table N'[dbo].[Table1]', N'create', N'test',
N'PK_Table1'
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang1', N'add', 2052
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang2', N'add', 1028
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang3', N'add', 1043
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang4', N'add', 2057
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang5', N'add', 1033
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang6', N'add', 1036
> > GO
> >
> > exec sp_fulltext_column N'[dbo].[Table1]', N'lang7', N'add', 1031
> > GO
> >
> > exec sp_fulltext_table N'[dbo].[Table1]', N'activate'
> > GO
> >
> >
> >
> > -- 
> > Hilary Cotter
> > Looking for a book on SQL Server replication?
> > http://www.nwsu.com/0974973602.html
> >
> >
> > "John Kane" <jt-kane@comcast.net> wrote in message
> > news:uOC%232A0aEHA.972@TK2MSFTNGP12.phx.gbl...
> > > You're welcome, Nick,
> > > Hilary, your examples of how to provide the answer to Nick's question
"How
> > > so I tell if I'm using noise.enu or noise.eng?", not only use
> > un-documented
> > > procedures (sp_MShelpcolumns and master..xp_MSfulltext are not
documented
> > in
> > > the BOL), but ultimately may not provide the correct answer as
> > > sp_MShelpcolumns returns the column's collation and not the "Language
for
> > > Word Breaker" value, or at least I believe so...
> > >
> > > Nick, I'd recommend that you run the following documented stored
procedure
> > > in your FT-enable database, for example using the pubs database:
> > >
> > > use pubs
> > > go
> > > sp_help_fulltext_columns
> > > /* -- returns, edited to display only need info:
> > > TABLE_OWNER       TABLE_NAME    FULLTEXT_COLUMN_NAME
FULLTEXT_LANGUAGE
> >
> ----------------- ------------- ----------------------- ----------------- 
> > > dbo               authors       au_lname                1033
> > > */
> > >
> > > The last column "FULLTEXT_LANGUAGE" for the above table 'authors' and
> > column
> > > name 'au_lname' is '1033' or US_English "Language used for the
full-text
> > > search of the column". The last part is from the BOL for
> > > sp_help_fulltext_columns. If this value was '2057' this would indicate
> > > UK_English for the "Language for Word Breaker" for this column and
that
> > you
> > > would need to edit the noise.eng (UK_English) file.
> > >
> > > Regards,
> > > John
> > >
> > >
> > >
> > > "Hilary Cotter" <hilaryk@att.net> wrote in message
> > > news:ehukVQzaEHA.1652@TK2MSFTNGP09.phx.gbl...
> > > > its in the master database on SQL 2000
> > > >
> > > > -- 
> > > > Hilary Cotter
> > > > Looking for a book on SQL Server replication?
> > > > http://www.nwsu.com/0974973602.html
> > > >
> > > >
> > > > "Nick Gilbert" <news@nickgilbert.com> wrote in message
> > > > news:%239CfrzxaEHA.3804@TK2MSFTNGP10.phx.gbl...
> > > > > > xp_MSfulltext which gives of language word breakers installed
and
> > > their
> > > > > > localeid's
> > > > >
> > > > > I don't seem to have that sp... :/
> > > > >
> > > > > Nick...
> > > >
> > > >
> > >
> > >
> >
> >
> >

Relevant Pages

  • Re: Ignored words problem
    ... sp_MShelpcolumns returns info on the word breaker not ... the collation. ... "John Kane" wrote in message ... > "Hilary Cotter" wrote in message ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Features of a normal index comapred to a Full text index
    ... But to answer your question that depends on the word breaker. ... It is at query time when the search arguements might will be stemmed (when ... queries you won't get stemming (although there are word breaker specific ... > "Hilary Cotter" wrote in message ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Problem with UNICODE and Index Server
    ... > according the language rules of the word breaker. ... of characters for a ceratin language and region. ... Expecting ANSI chars just because the regional setting is English ...
    (microsoft.public.inetserver.indexserver)
  • Re: "CONTAINS" ignores "fahrenheit"?
    ... >> "Language for Word Breaker" on your FT-enable column and then run a Full ... >> remove single numbers from noise.dat (Neutral noise word file) prior to ...
    (microsoft.public.sqlserver.fulltext)
  • Re: LCID indexed columns
    ... Thanks Hilary, the language for word breaker was empty, I selected English ... Looking for a SQL Server replication book? ... full-text indexed columns. ...
    (microsoft.public.sqlserver.fulltext)