SQL Server 2000 distinguishes between decimal zero 0.00 and -.00 while ADO.NET does not

Question

We're using SQL Server Standard Edition 8.00.760 (SP3) on a Small Business Server 2003 platform. While tracking down an inexplainable System.Data.DBConcurrencyException for a Strongly Typed DataSet, I discovered the following problem:

Given is this table:

CREATE TABLE [dbo].[Auszahlung](
    [auszahlungid] [int] IDENTITY(1,1) NOT NULL,
    [spesenkonto] [decimal](10, 2) NOT NULL,

And this query for a row originally inserted with decimals calculated in .NET 2.0 using a Strongly Typed DataSet:

SELECT [auszahlungid], [spesenkonto]
FROM [Auszahlung] 
WHERE [auszahlungid] = 35

When run on a Sql Server Management Studio 2005 client, I get this result:

auszahlungid spesenkonto
------------ ---------------------------------------
35           0.00

(1 Zeile(n) betroffen)

But when run on the Query Analyzer on the Sql Server 2000, I get this negative zero:

auszahlungid spesenkonto  
------------ ------------ 
35           -.00

(1 row(s) affected)

Consequently, the queries

SELECT [auszahlungid], [spesenkonto] 
FROM [Auszahlung] 
WHERE [auszahlungid] = 35
AND [spesenkonto] = 0.00

and (inconsequently on the SQL 2000 Query Analyzer)

SELECT [auszahlungid], [spesenkonto]
FROM [Auszahlung] 
WHERE [auszahlungid] = 35
and spesenkonto = -.00

both yield 0 rows, and any row update using a .NET Strongly Typed DataSet will raise a System.Data.DBConcurrencyException because of the the optimistic concurrency restriction.

Questions:

Is this a known bug in MSSQL?

How can I make our system reliable again without sacrificing optimistic concurrency?

Answer 1

Perhaps, when working with SQL2000 driver interprets numeric as float... Can you try the following workaround

SELECT [auszahlungid], [spesenkonto] 
FROM [Auszahlung] 
WHERE [auszahlungid] = 35
AND ABS([spesenkonto]) < 0.01