Inconsistent Search Results

When searching for this street using this parameter:
'L''Avenue De La Reine Elizabeth'
I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
Second',)
but When I try searching for the whole thing, it won't return any rows, nor
will it for
'L''Avenue De La Reine Elizabeth The'.
Is there a word limit of some sort on full text search?
Can anyone throw some light on this?
Thanks In advance
Warren
Warren,
Could you post the full output of the following SQL script as it is helpful
info in troubleshooting SQL FTS issues?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
Reine Elizabeth The Second'? Could you post the exact SQL query that you
were using?
Thanks,
John
"WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> When searching for this street using this parameter:
> 'L''Avenue De La Reine Elizabeth'
> I get a result ( The record contains 'L''Avenue De La Reine Elizabeth The
> Second',)
> but When I try searching for the whole thing, it won't return any rows,
nor
> will it for
> 'L''Avenue De La Reine Elizabeth The'.
> Is there a word limit of some sort on full text search?
> Can anyone throw some light on this?
> Thanks In advance
> Warren
>
|||Thanks for replying, John - here is the stuff you asked for, though I can't
send you the SQL query itself, though it isd interesting to Note that if I
restrict the search to 5 words, it will search & find the record(s). If any
more than that, it does not bother searching!
------
us_english
(1 row(s) affected)
-----------
-
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: )
(1 row(s) affected)
name minimum maximum config_value
run_value
-- -- -- --
default full-text language 0 2147483647 1033 1033
ftcatid NAME
PATH
STATUS NUMBER_FULLTEXT_TABLES
------
-----------
- -- --
5 Address Data
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 4
6 Cat_Desc
C:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA
0 0
(2 row(s) affected)
TABLE_OWNER
TABLE_NAME
FULLTEXT_KEY_INDEX_NAME
FULLTEXT_KEY_COLID FULLTEXT_INDEX_ACTIVE FULLTEXT_CATALOG_NAME
------
------
------
-- --
------
dbo
building
PK_building
1
1 Address Data
dbo
DeliveryPoint
PK_DeliveryPoint
1
1 Address Data
dbo
parish
PK_parish
1
1 Address Data
dbo
road
PK_road
1
1 Address Data
(4 row(s) affected)
TABLE_OWNER
TABLE_ID TABLE_NAME
FULLTEXT_COLUMN_NAME
FULLTEXT_COLID FULLTEXT_BLOBTP_COLNAME
FULLTEXT_BLOBTP_COLID FULLTEXT_LANGUAGE
------
------
------
------
-- --
dbo
373576369 building
name
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Business
2 NULL
NULL 1033
dbo
741577680 DeliveryPoint
SubElementDesc
3 NULL
NULL 1033
dbo
741577680 DeliveryPoint
LocationOnStreet
5 NULL
NULL 1033
dbo
741577680 DeliveryPoint
NameOfTerrace
6 NULL
NULL 1033
dbo
741577680 DeliveryPoint
Locality
8 NULL
NULL 1033
dbo
741577680 DeliveryPoint
PostCode
10 NULL
NULL 1033
dbo
533576939 parish
name
2 NULL
NULL 1033
dbo
709577566 road
name
2 NULL
NULL 1033
(9 row(s) affected)
Name
Owner
Type Created_datetime
------
------
road
dbo
user table 2004-05-06
10:14:12.887
Column_name
Type
Computed Length Prec
Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
------
------
-- -- -- --
-- --
------
id
int
no 4 10
0 no (n/a)
(n/a) NULL
name
varchar
no 80
no no
no SQL_Latin1_General_CP1_CI_AS
parishid
int
no 4 10
0 no (n/a)
(n/a) NULL
usrn
float
no 8 53
NULL yes (n/a)
(n/a) NULL
root
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
SortOrder
varchar
no 80
yes no
no SQL_Latin1_General_CP1_CI_AS
Identity
Seed
Increment Not For Replication
------
--- --
id
1
1 0
RowGuidCol
------
No rowguidcol column defined.
Data_located_on_filegroup
------
PRIMARY
index_name
index_description
index_keys
------
---------
-----------
-
PK_road
clustered, unique,
primary key located on PRIMARY
id
constraint_type
constraint_name
delete_action
update_action status_enabled status_for_replication constraint_keys
-------
------
-- -- -- --
-----------
-
FOREIGN KEY
FK_road_parish
No Action No Action
Enabled Is_For_Replication parishid
REFERENCES APMS.dbo.parish (id)
PRIMARY KEY (clustered)
PK_road
(n/a) (n/a) (n/a)
(n/a) id
Table is referenced by foreign key
-----------
-
APMS.dbo.DeliveryPoint: FK_DeliveryPoint_road
APMS.dbo.parentRoad: FK_parentRoad_road
APMS.dbo.parentRoad: FK_parentRoad_road1
APMS.dbo.roadAlias: FK_roadAlias_road
No views with schema binding reference this table.
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Sorry John - here is the relevenat bit of the SQL Statement - @.searchStreet
is the parameter in question...
where (
contains (d.*, @.searchtext1)
or d.business like @.Business+'%'
or contains( b.*, @.searchtext1)
or b.name like @.Business+'%'
or contains( r1.*, @.searchtext1)
or r1.name like @.Business+'%'
or contains( r2.*, @.searchtext1)
or d.NameOfTerrace like @.Business+'%'
or d.postcode like @.Business+'%'
or d.locality like @.searchtext2+'%')
and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
"John Kane" wrote:

> Warren,
> Could you post the full output of the following SQL script as it is helpful
> info in troubleshooting SQL FTS issues?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help <your_FT-enable_table_name_here>
> go
> Are you using CONTAINS or FREETEXT when you searched for 'L''Avenue De La
> Reine Elizabeth The Second'? Could you post the exact SQL query that you
> were using?
> Thanks,
> John
>
> "WalrusWal" <WalrusWal@.discussions.microsoft.com> wrote in message
> news:D6020947-4BD9-4C0E-A7B2-9B157213E242@.microsoft.com...
> nor
>
>
|||Warren,
Thank you for the info! You're using SQL Server 2000 SP3 on Windows Server
2003 (Win2003) with US_english (1033) both the default language as well as
the "Language for Word Breaker" for your FT-enable varchar columns.
Since you're using Win2003 and it's new wordbreaker dll (LangWrbk.dll), the
string "L''Avenue De La Reine Elizabeth The Second" (without the double
quotes) breaks as follows:
Original text: 'L''Avenue De La Reine Elizabeth The Second'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 0, cwc 9, 'L''Avenue'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 10, cwc 2, 'De'
IWordSink::PutWord: cwcSrcLen 2, cwcSrcPos 13, cwc 2, 'La'
IWordSink::PutWord: cwcSrcLen 5, cwcSrcPos 16, cwc 5, 'Reine'
IWordSink::PutWord: cwcSrcLen 9, cwcSrcPos 22, cwc 9, 'Elizabeth'
IWordSink::PutWord: cwcSrcLen 3, cwcSrcPos 32, cwc 3, 'The'
IWordSink::PutWord: cwcSrcLen 6, cwcSrcPos 36, cwc 6, 'Second'
When you pass the above string into the @.searchstreet variable are you also
passing in double quotes (" ")? This would indicate to SQL Server 2000 that
this is a phrase query. Additionally, if you search only on "Avenue De La
Reine Elizabeth The Second" (with the double quotes) do you get a hit on
this? Note, there is no word limit of any sort on full text search queries,
but there are issues with punctuation characters (such as single quotes that
are escaped) that depend upon the OS-supplied wordbreaker dll.
Thanks,
John
"Wal" <Wal@.discussions.microsoft.com> wrote in message
news:C6D8715A-9C48-4CA5-87B2-3C551A77859D@.microsoft.com...
> Sorry John - here is the relevenat bit of the SQL Statement -
@.searchStreet[vbcol=seagreen]
> is the parameter in question...
> where (
> contains (d.*, @.searchtext1)
> or d.business like @.Business+'%'
> or contains( b.*, @.searchtext1)
> or b.name like @.Business+'%'
> or contains( r1.*, @.searchtext1)
> or r1.name like @.Business+'%'
> or contains( r2.*, @.searchtext1)
> or d.NameOfTerrace like @.Business+'%'
> or d.postcode like @.Business+'%'
> or d.locality like @.searchtext2+'%')
> and contains(r2.* ,@.searchstreet) and contains(p.*,@.searchTown)
> "John Kane" wrote:
helpful[vbcol=seagreen]
La[vbcol=seagreen]
The[vbcol=seagreen]
rows,[vbcol=seagreen]