Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ScottRayner
Starting Member
2 Posts |
 Posted - 2012-03-22 : 19:49:30
|
| Sorry, the subject is difficult to describe in the space allowed for the subjectI have a database containing a table for invoice details, and another for loadout details - cartons loaded out to the invoice.Each time a carton is scanned to the loadout table, a column in the invoice details is incremented to show the number of cartons, and the carton weight is added to the weight on that row of the invoice (very simplified description!)This system has been in place and operating for at least 10 years, and I've been working with it for the past 5 years.Generally, if you count all rows in the loadout table with a given invoice number, the result should match the value stored as an integer in the invoice detail table, as should the weight- most of the time it does (discounting manually modified invoices).On a number of occasions (first using SQL Server 2000, and now twice with SQL Server 2008R2) the value in the invoice details returns exactly 1 less than the count of cartons while the weight remains correct- those with no cartons loaded out to them have -1 in the invoice details table (and weight 0). HOWEVER it isn't ALL invoices which are affected - up to 400,000 of 2,000,000 were affected this time, with no obvious common denominator - both active and closed invoices. Furthermore, each time I run a simple query to compare the count of cartons against the invoice details, I get different results, sometimes more, sometimes less, indicating the values are sometimes -1 and sometimes correct.When I execute a query to update the value to the count of cartons, it updates (thousands) of rows, and then if I run it again, instead of updating 0 rows as you would expect, it updates more - each time it's run (even tho change tracking says nothing was changed between running the update query)What I've tried so far:Stopping/Starting/Rebooting (obviously)Upgraded the DB from SQL2000 to SQL2008R2 DBCC CHECKDB returns no errorsDropped and rebuilt all indexesNo triggers existActivated Change Tracking - no indication that anything external is changing the valuesCreated new table, imported data from old table, deleted old table, renamed new table (argh - how can it possibly still happen after that!?).Almost everything points to a rogue external query subtracting 1 from the invoice details - however change tracking doesnt concur, and the value is never changed by anything other than -1, and even then, the change appears transitory, as sometime it returns to the correct value by itself.All I can assume is that the value is correctly stored in the database, but when it's retrieved, it's sometimes reported correctly, and sometimes 1 less than it should be.Has anyone heard of anything like this before? Any suggestions?Thanks in advance... |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ScottRayner
Starting Member
2 Posts |
Posted - 2012-03-22 : 20:46:25
|
| SELECT lo.lonum, lo.rowID, SUM(lo.nettweight) AS weightscanned, oi.weightshipped, COUNT(lo.cartonnumber) AS quantityscanned, oi.quantityshippedFROM dbo.tblBRcartonlo AS lo RIGHT OUTER JOIN dbo.tblLDorderitem AS oi ON lo.lonum=oi.lonum AND lo.rowID=oi.rowIDWHERE (NOT (lo.lonum IS NULL)) AND (lo.lonum> 0)GROUP BY lo.lonum, lo.rowID, oi.weightshipped, oi.quantityshippedShould return quantityscanned=quantityshipped where weightscanned=weightshipped and it usually does, but on 4 occasions in the past 12 months, quantityshipped is 1 less than quantityscanned, even tho weightscanned=weightshipped. I can run a query to make quantityshipped=quantityscanned and those affected are fixed, but others are then apparently randomly affected. |
 |
|
|
|
|
|
|
|
INT Column value is 1 less than it should be