Hi,

I have a table ADSFULL containing ADVERTISMENTS with a FTI index on 2 fields
FADS_TITLE and FADS_TEXT (see script at the end).

--
The following query returns 94 rows.

SELECT fads_title, fads_text
FROM VWADS
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL WHERE (CONTAINS(*,
'"twingo"') ) ) )

(To be clear VWADS is a view, joining the table ADS and ADSFULL, having a 1
on 1 relation via the fields
ADS.ADS_ADID and ADSFULL.FADS_ADID)

--

I see there are some rows containing the word (colour) beige.
So the following query returns 2 rows.  That's fine.

SELECT ads_adid, fads_id, fads_adid, fads_title, fads_text
FROM VWADS
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL WHERE (CONTAINS(*,
'"twingo"') ) ) )
AND fads_text like '%beige%'

45592 45234 45592 Renault Twingo initilal cuir beige et jantes initiale,
bleu métal superbe voiture a saisir
11947 11700 11947 Renault Twingo Initiale, ... 2001, full options, toit
panoramique, cuir beige, airco, couleur champagne, jantes, installation cd,
80.000 km, carnet, ct ok, coup de coeur assuré

--

But when I try to achieve that with my FTI using the following query,
SQL Server returns NOT TWO BUT ZERO ROWS

SELECT ads_adid, fads_id, fads_adid, fads_title, fads_text
FROM VWADS
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL WHERE (CONTAINS(*,
'"twingo" AND "beige"') ) ) )

--

I've already rebuild the FTC, but that does not solve this mistery.  Is
there a logical explanation that I don't see?

Any help really appreciated.

PS:
I've read timestamp isn't really convertible to datetime, but can the
timestamp tell me when the FTI for the two rows above was updated in order
be sure the words beige where not added after the rows where indexed?
Can I get a list of all words that have been indexed?
Can I see for each word how many times they appear in the table?
and ...
Is there a command that tells me which noise word file my FTI is using?
I've got 2 FT indexed databases with same tablestructure, one storing dutch
and one storing french content,
but I guess they both use the same (english) noise word file?

Thanks a lot,
Kind regards,
Peter Van Wilrijk.

--
The script
--

CREATE TABLE [dbo].[ADSFULL] (
[FADS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[FADS_ADID] [int] NOT NULL ,
[FADS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[FADS_TEXT] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[FADS_MEMO] [nvarchar] (1500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[FADS_FTISTAMP] [timestamp] NOT NULL ,
[FADS_HTMLTEXT] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ADSFULL] WITH NOCHECK ADD
CONSTRAINT [PK_ADSFULL] PRIMARY KEY  CLUSTERED
(
 [FADS_ID]
)  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'FTADSFR')
exec sp_fulltext_catalog N'FTADSFR', N'create'
GO
exec sp_fulltext_table N'[dbo].[ADSFULL]', N'create', N'FTADSFR',
N'PK_ADSFULL'
GO
exec sp_fulltext_column N'[dbo].[ADSFULL]', N'FADS_TITLE', N'add', 1033
GO
exec sp_fulltext_column N'[dbo].[ADSFULL]', N'FADS_TEXT', N'add', 1033
GO
exec sp_fulltext_table N'[dbo].[ADSFULL]', N'activate'
GO
ALTER TABLE [dbo].[ADSFULL] WITH NOCHECK ADD
CONSTRAINT [DF_ADSFULL_FADS_ADID] DEFAULT (0) FOR [FADS_ADID],
CONSTRAINT [DF_ADSFULL_FADS_TITLE] DEFAULT ('') FOR [FADS_TITLE],
CONSTRAINT [DF_ADSFULL_FADS_TEXT] DEFAULT ('') FOR [FADS_TEXT],
CONSTRAINT [DF_ADSFULL_FADS_MEMO] DEFAULT ('') FOR [FADS_MEMO],
CONSTRAINT [DF_ADSFULL_FADS_HTMLTEXT] DEFAULT ('') FOR [FADS_HTMLTEXT]
GO
CREATE  INDEX [IX_FOREIGNADID] ON [dbo].[ADSFULL]([FADS_ADID]) ON [PRIMARY]
GO