Re: Inconsistent negative numerical values in SQL2K database table
From: Steve Kass (skass_at_drew.edu)
Date: 02/04/05
- Next message: Aju: "SQL Agent Performance"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: Torn pages with DL560 and SmartArray 5302-128 controller"
- In reply to: Earl Newcomer: "Re: Inconsistent negative numerical values in SQL2K database table"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 04 Feb 2005 01:25:19 -0500
Earl Newcomer wrote:
>Steve,
>
>Well done. That was exactly the issue. Thank you very much. I tested the
>fix of 1*value and it works as you mentioned. How does that fix the data do
>you know?
>
>
As far as I can tell, so long as SQL Server has to do an arithmetic
problem, it will create a new and proper decimal value and overwrite the
existing one. Fortunately, it appears that the decimal arithmetic
algorithms were not highly tuned to do clever things with the existing
sign byte/bit. Only very simple updates, like SET D = -D might operate
directly on the existing sign information.
SK
>To answer your question about copying between SQL Server instances I don't
>think this condition is caused by copying between SQL Servers. I do know
>that once a column has an incorrect sign bit in a SQL Server table that it
>will copy between SQL Server tables carrying with it the incorrect sign bit.
>
>In our case, I suspect the developers have used different ODBC drivers over
>time and some values were copied correctly while other values suffer from the
>sign bit problem. Since the cube is partitioned some of the partitions are
>built with correctly stored data and AS reports those values correctly while
>other partitions are built with incorrectly stored data and AS reports those
>incorrectly.
>
>We will add another DTS task to check the sign bit from now on.
>
>Thanks again,
>Earl Newcomer
>
>"Steve Kass" wrote:
>
>
>
>>Earl,
>>
>> There are probably some values for which the sign byte of the
>>decimal value is something other than 01 or 00. See the explanation
>>here:
>>
>>http://groups.google.co.uk/groups?q=218AE3EB-60D7-4E5E-A2F1
>>
>>You should be able to find the values with the funky sign and fix them
>>as shown in the above thread like this:
>>
>>update T set
>> decVal = 1*decVal
>>where [if a large table, identify just the ones that have bad signs if
>>possible]
>>
>>or something similar if the values should be negative. A handful of
>>data providers seem to cause this problem, but I don't recall seeing it
>>between SQL Servers before. How did you move the data from
>>one to the other?
>>
>>Steve Kass
>>Drew University
>>
>>
>>
>>Earl Newcomer wrote:
>>
>>
>>
>>>I have a case where the sum of a column containing positive, zero and
>>>negative values is correct in Query Analyzer but the values in the column do
>>>not contain a '-' (minus sign). The rows sort as expected but they do not
>>>have a minus sign. I can update a positive row value by multiplying it by -1
>>>and that value shows with a minus sign. The newly updated row sorts with the
>>>other negative values but it is the only row with a minus sign.
>>>
>>>This "condition" came to light when a cube built on the fact table (where
>>>this is occurring) was not summing correctly. The cube sees all values as
>>>positive as if an absolute function was being used. When the one positive
>>>row was updated to a negative value as described above, the cube recognized
>>>the negative value and the measure changed accordingly. What is it in the
>>>SQL Server table or in the cube interpretation of the table values that is
>>>causing this? Is there a workaround?
>>>
>>>Another twist. The table, pmix_fact1, with which we see this behavior was
>>>copied from another SQL Server 2000 server. The cubes built on that table
>>>sum correctly. I suspect the process of copying the table may be introducing
>>>this behavior. Has anyone experienced anything like this?
>>>
>>>I have seen this once before when using an ODBC driver to copy table rows
>>>
>>>
>>>from an Oracle 7.3.4 database to SQL Server 2000 where the values were all
>>
>>
>>>positive after the copy. I changed to the latest Oracle driver (9.0.1) that
>>>would still work with Oracle 7.3.4 and this resolved that problem. I have
>>>not seen a case where a SQL Server to SQL Server copy would confuse numeric
>>>values.
>>>
>>>Sample data from SQL Server 2000 SP3 Query Analyzer
>>>Extended_amount (sorted descending) No minus sign at the bottom.
>>>8.99
>>>7.99
>>>4.08
>>>3.19
>>>2.99
>>>2.98
>>>2.59
>>>2.29
>>>2.19
>>>2.19
>>>1.99
>>>1.79
>>>1.59
>>>1.49
>>>.69
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>2.29
>>>2.98
>>>2.98
>>>3.19
>>>4.08
>>>4.08
>>>------
>>>27.43
>>>
>>>The cube reports: 66.63
>>>
>>>Update the 8.99 to 8.99 * -1 and the Query Analyzer results are:
>>>Extended_amount
>>>7.99
>>>4.08
>>>3.19
>>>2.99
>>>2.98
>>>2.59
>>>2.29
>>>2.19
>>>2.19
>>>1.99
>>>1.79
>>>1.59
>>>1.49
>>>.69
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>.00
>>>2.29
>>>2.98
>>>2.98
>>>3.19
>>>4.08
>>>4.08
>>>-8.99
>>>------
>>>9.45
>>>
>>>The cube reports: 48.65
>>>
>>>Suggestions are appreciated.
>>>
>>>Earl Newcomer
>>>
>>>
>>>
>>>
>>>
>>>
>>>
- Next message: Aju: "SQL Agent Performance"
- Previous message: Mike Epprecht \(SQL MVP\): "Re: Torn pages with DL560 and SmartArray 5302-128 controller"
- In reply to: Earl Newcomer: "Re: Inconsistent negative numerical values in SQL2K database table"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
