Re: Inconsistent negative numerical values in SQL2K database table

From: Earl Newcomer (EarlNewcomer_at_discussions.microsoft.com)
Date: 02/03/05 

Date: Thu, 3 Feb 2005 08:13:02 -0800

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
> >
> >
> >
> >
> >
>

Relevant Pages

  • Re: Hotfix Post SP4
    ... Build 2148, to fix the SQLDiag issue, but also fixes another 10 or so. ... to fix the Query Analyzer DTC issue. ... SQL Server Version Database ... You receive an error message if you use the sp_addalias or sp_dropalias ...
    (microsoft.public.sqlserver.clustering)
  • Inconsistent negative numerical values in SQL2K database table
    ... not contain a '-' (minus sign). ... This "condition" came to light when a cube built on the fact table (where ... SQL Server table or in the cube interpretation of the table values that is ... Sample data from SQL Server 2000 SP3 Query Analyzer ...
    (microsoft.public.sqlserver.server)
  • Re: Inconsistent negative numerical values in SQL2K database table
    ... >and that value shows with a minus sign. ... The cube sees all values as ... >copied from another SQL Server 2000 server. ... >The cube reports: 66.63 ...
    (microsoft.public.sqlserver.server)
  • OLAP not recognizing negative SQL Server table values
    ... not contain a '-' (minus sign). ... This "condition" came to light when a cube built on the fact table (where ... SQL Server table or in the cube interpretation of the table values that is ... Sample data from SQL Server 2000 SP3 Query Analyzer ...
    (microsoft.public.sqlserver.olap)
  • Re: long processing time
    ... The AS Operations Guide was just recently posted and got dropped from their ... >> 1) Have you ran the Optimize Schema wizard in the Cube Editor? ... >> inner join between the fact table and *all* of the dimension tables. ... >>> through SQL server. ...
    (microsoft.public.sqlserver.olap)