SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 INT Column value is 1 less than it should be
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ScottRayner
Starting Member

Australia
2 Posts

Posted - 03/22/2012 :  19:49:30  Show Profile  Reply with Quote
Sorry, the subject is difficult to describe in the space allowed for the subject

I 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 errors
Dropped and rebuilt all indexes
No triggers exist
Activated Change Tracking - no indication that anything external is changing the values
Created 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

USA
35954 Posts

Posted - 03/22/2012 :  20:00:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
We're going to need to see some code, especially the retrieval code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ScottRayner
Starting Member

Australia
2 Posts

Posted - 03/22/2012 :  20:46:25  Show Profile  Reply with Quote
SELECT lo.lonum, lo.rowID, SUM(lo.nettweight) AS weightscanned, oi.weightshipped, COUNT(lo.cartonnumber) AS quantityscanned, oi.quantityshipped
FROM dbo.tblBRcartonlo AS lo RIGHT OUTER JOIN
dbo.tblLDorderitem AS oi
ON lo.lonum=oi.lonum AND lo.rowID=oi.rowID
WHERE (NOT (lo.lonum IS NULL)) AND (lo.lonum> 0)
GROUP BY lo.lonum, lo.rowID, oi.weightshipped, oi.quantityshipped

Should 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000