Inconsistent negative numerical values in SQL2K database table

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 NewcomerEarl,
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
>
>
>|||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?
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
> >
> >
> >
> >
> >
>|||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
>>
>>
>>