Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Full-Text Search / January 2006

Tip: Looking for answers? Try searching our database.

Query returning inconsistent results - please help

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Griff - 25 Jan 2006 09:31 GMT
I perform a query against a database and it returns one set of results.  I
perform this query on an "identical" database and it returns a subtly
different set of results.

In detail.

SERVER A

I have a table that has a field 'myField' that is of datatype TEXT.

This table has a full-text index on it using the English (United Kingdom)
word breaker.  The catalog is both built and fully populated.

One of the rows contains the data
'Z42/Z43/Z45/Z51/Z52/Z53/Z54/X63/X70/X73/X80/X83/X84/X85/X125/A10'

I run the following SQL query:
   select * from myDB..myTable where contains(myField,'x73')
and the result set does NOT return this row.

I back this database up and restore it on Server B

I ensure that the same full-text index exists (same word-breaker language).
I build the catalog and fully populate it.  Number of records and size are
the same as on server A.

I perform the same SQL query and this time it DOES return the row in
question.

DETAILS ABOUT THE SERVERS (I can't believe that the following would make a
difference, but hey, perhaps it does...?):

SERVER A - the one the query does not return the row
Windows 2000 sp3
SQL Server Enterprise Edition, sp3a, server collation = Latin1_General_CI_AS

SERVER B
Windows 2003
SQL Server Standard Edition, sp3a, server collation = Latin1_General_CI_AS

The same collation (Latin1_General_CI_AS) exists at the field level...

Running out of ideas....

Thanks for reading this

Griff
Griff - 25 Jan 2006 09:48 GMT
Ah

Think I may have solved it (but I'm not certain so if anyone could confirm
or deny the following that would be extremely helpful)

I think that the Win2003 computer has a different operating system DLL that
defines noise words and this is used in the full-text cataloguing exercise.
Hence the difference...I think.

Griff
Daniel Crichton - 25 Jan 2006 11:58 GMT
Griff wrote  on Wed, 25 Jan 2006 09:48:15 -0000:

> Ah
>
[quoted text clipped - 4 lines]
> that defines noise words and this is used in the full-text cataloguing
> exercise. Hence the difference...I think.

Windows 2003 does indeed have a different word breaker than Windows 2000,
searching this group will turn up many posts about this. Have you tried
using the Neutral word breaker?

Dan
Hilary Cotter - 26 Jan 2006 02:39 GMT
can you verify that the noise word lists are the same? If you made a change
to the noise word lists, did you rebuild the catalog?

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

>I perform a query against a database and it returns one set of results.  I
>perform this query on an "identical" database and it returns a subtly
[quoted text clipped - 44 lines]
>
> Griff
Griff - 26 Jan 2006 09:10 GMT
Hilary

I understand that the DLL that defines the noise words defines the
word-break words (which is what is affecting my search).  I believe that
these are compiled into the DLL so there's no way of changing them...if
there is a way I'd like to know how so that I could manually update my
Win2000 box so that it exhibited the same (desired) results as the Win2003
box

Griff

> can you verify that the noise word lists are the same? If you made a
> change to the noise word lists, did you rebuild the catalog?
ML - 26 Jan 2006 09:33 GMT
Noise words are listed in the noise file. It's a text file and can be edited.

Read more here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_
15_74oj.asp


Also note this: if you need to delete individual words from the noise list,
leave empty rows (where the word used to be).

ML

---
http://milambda.blogspot.com/
Hilary Cotter - 27 Jan 2006 02:56 GMT
Regrettably there is no way to do this as the word breakers are OS specific.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hilary
>
[quoted text clipped - 9 lines]
>> can you verify that the noise word lists are the same? If you made a
>> change to the noise word lists, did you rebuild the catalog?
Jim Underwood - 27 Jan 2006 17:45 GMT
Is one database case sensitive and the other isn't?

> I perform a query against a database and it returns one set of results.  I
> perform this query on an "identical" database and it returns a subtly
[quoted text clipped - 43 lines]
>
> Griff
Hilary Cotter - 27 Jan 2006 19:36 GMT
SQL FTS is not sensitive to case.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Is one database case sensitive and the other isn't?
>
[quoted text clipped - 51 lines]
>>
>> Griff
Jim Underwood - 27 Jan 2006 20:26 GMT
Thanks Hilary, I'll have to make a note of that.  Checking out the FAQ you
linked to will provide me a little more background on it as well.

> SQL FTS is not sensitive to case.
 
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.