0

SQL server is occasionally returning hours to weeks old data using both SSMS and application connections. The problem persists long enough for me to verify the problem in both applications and SSMS as well as run slightly altered queries that return the latest data. This is happening across multiple tables in the database. But seems to be isolated to a few rows at a time. NOTE: There is 1 ROW per itemnumber ie the nonclustered index.

SELECT tableA.itemnumber, tableA.location, tableB.itemtype 
from tableA left join tableB on tableA.itemnumber=tableB.itemnumber 
where tableA.warehouse = @warehouse and tableA.itemnumber in (@item1, @item2, ...)

but if I select from tableA directly or with a different query joined to more tables then both SSMS and applications return the 1 ROW of current data. Also I confirmed this behavior using snapshot isolation, no isolation, and even nolock running under different users.

I'd like to understand how this can happen and possible ways to resolve this issue.

The database is originally from our ERP.
All the tables are heaps (no clustered index).
Running on 2012 SQL Server.
Implements snapshot but not RCSI.
Indexes are supposedly rebuilt each week (but cannot confirm, I have seen the index fragmentation at 90).

The errors occur maybe 10 times / 10,000 a day but it's difficult to track since data constantly changes in these tables throughout the day. For now I'm cross-referencing 2 queries and when they don't match, I throw an error and say to try later.

Nelson
  • 31
  • 5
  • 2
    Can't do much with such vagueness and a lack of any real query - especially given what you did post has no obvious filter based on date or time. The short answer - there is a probable logic flaw in your query. Not sure what "from our ERP" means but it sounds like you should consult with your ERP vendor or support organization. Perhaps you assume rows are ordered in some fashion and that the "first" (or "last") rows reflect "current" information? That is not a valid or safe assumption. – SMor Apr 21 '20 at 18:24
  • When querying the tables individually, there is only 1 row returned and it is the updated data. ERP is managed in-house and the team always first blames any problem on the applications. Which doesnt seem to be the problem since I can recreate the issue in SSMS. Also that is the actual query, it's a simple join selecting minimal data. – Nelson Apr 21 '20 at 18:29
  • I don't see any order by. Maybe this is the issue. Check the answer here: https://stackoverflow.com/questions/20050341/when-no-order-by-is-specified-what-order-does-a-query-choose-for-your-record – Andrew Apr 21 '20 at 19:07
  • There is 1 row in question. I can confirm this by querying for all rows under the nonclustered index (itemnumber). – Nelson Apr 21 '20 at 19:37
  • "There is only one row" does not seem to match the filter condition involving "IN". And yes, i agree with your ERP team - application issues are generally the source of problems like this. The burden on you the app developer is to record sufficient information to be able to diagnose why things are not as they should be. Throwing an error is just a start - that only stops the problem. That will not allow anyone to understand why this is happening. I certainly hope NOLOCK (or equivalent) is not involved - that would explain this. – SMor Apr 21 '20 at 20:07

1 Answers1

0

(I hope that tableA, tableB are not actual table names or I would love to meet the ERP developers just to see what they look like).

But without having the data at hand it's hard to tell. You have the data, so you are in a better position than us to trace the problem. I would take note of the IDs for some of the undesirable rows and see if they come back. They must have been returned by your query for a good reason. In this query you have no filter on date anyway. Do a select * and look carefully.

Could it be that the table is regenerated at regular intervals and you were running queries at the wrong time ?

Perhaps you could add a timestamp column in the problematic table, so that when you spot incorrect records, you will have the insert/update time. And then maybe you will be able to correlate those errors with activity in the ERP (that we know nothing about).

Or maybe there is a bug somewhere, causing the wrong rows to be UPDATEd, so they might come and disappear because their values change all the time. That's why you should isolate a few and monitor them.

But without any insight into your environment, table structure and data I'm afraid all we can do is guess.

Kate
  • 1,809
  • 1
  • 8
  • 7
  • The tables only have a nonclustered index on itemnumber. The affected rows are updated but not deleted/inserted. So there is just 1 row in question, no ID/ date to sort by. I think a timestamp column or changelog is a good idea. However because it is in the ERP group's domain, I have not been able to get them to add this (or updatedby column to see where updates come from). – Nelson Apr 21 '20 at 19:31
  • Also, indeed the tables are actually named table00001, table00002, etc. Please talk to microsoft about their flagship ERP. – Nelson Apr 21 '20 at 20:29