CODEWITHSUNDEEP
sqlt-sqlmsde
Sabre
Sabre

Reputation: 2400

Possible index corruption?

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.

Upvotes: 4

Views: 159

Answers (1)

Sabre
Sabre

Reputation: 2400

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

Upvotes: 1

Related Questions