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.