4

Proof

In the above image what you can see is that I have a table, that when I query a max value of a field from it, I get different results based on a where clause that the rest of the queries seem to rule out as irrelevant.

Back end is MSDE 2000, front end is application written in VB.NET 2008, verification performed using SSMS 2008R2 attached to MSDE instance over VPN.

It is a closed system from application development, however if I could correct whatever is causing this I believe both DB and application would resume operation.

The problem is is causing is when it requests Max([record_index]) + 1 where the [station_id] = 10, the value is coming up as a record that already exists in that table, and the insert is failing because of a unique constraint.

Sabre
  • 2,350
  • 2
  • 18
  • 25
  • 3
    I think this would be a better fit on [dba.se]. Feel free to flag it for migration. – JNK Jun 04 '12 at 20:37
  • Do you know what index is used? Have you tried rebuilding that index? – JNK Jun 04 '12 at 20:41
  • Rebuilding index is already in process now, it is a huge amount of data and not a very big machine. I agree it almost has to be the index. will let you guys know if it works, likely going to be a wait... – Sabre Jun 04 '12 at 20:56

1 Answers1

1

Reindex of the PK index solved the problem and makes the above queries for Max([record_index]) return the same number as Max([record_index]) WHERE... return the same numbers, as they should. So at this point index corruption is the only logical answer. The DB engine is 12 years old, and this is the only time it has ever happened to us, guess I will just have to accept it

Sabre
  • 2,350
  • 2
  • 18
  • 25