 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / October 2004Inconsistent 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 > When searching for this street using this parameter: > 'L''Avenue De La Reine Elizabeth' [quoted text clipped - 13 lines] > > 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. > Warren, > Could you post the full output of the following SQL script as it is helpful [quoted text clipped - 36 lines] > > > > Warren 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) > Warren, > Could you post the full output of the following SQL script as it is helpful [quoted text clipped - 36 lines] > > > > Warren 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 > Sorry John - here is the relevenat bit of the SQL Statement - @searchStreet > is the parameter in question... [quoted text clipped - 51 lines] > > > > > > Warren |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.