Like clause, 2501 records and a major headache...

[Don Whiting]

Situation:

BaseTable contains 2501 records that all look like this:
FieldX FieldY
-----------------------
HDA.txt 12
HDA.txt 12
HDA.txt 12
...

(both fields are text)



I have 2 queries:

A - select * from BaseTable
where (FieldX like "HD*.txt");

B - select * from A
where FieldY = "12";

Query B returns zero records.

If I take a record out of the base table, I get 2500
records.
If I add a record to the base table, I get 2502 records.
If there are 2501 records, I get zero. Nada. Zilch.

I've search the MS knowledge base and see nowhere that
mentions this as a bug. I've searched the Internet and
nobody seems to have come across this problem. This is
such an incredibly simple thing I can't believe we were
the first to notice this error.

As it happens, this caused a major production problem and
my boss has asked me to ensure that this never takes place
again. But we have this sort of thing through our system.

 

[Chris]

Try repairing the database. It sounds like an index has
been screwed.