FIX: Arithmetic Overflow When Convert Real Value
  
PSS ID Number: Q112693
Article last modified on 10-31-1994
 
4.20
 
WINDOWS
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2
----------------------------------------------------------------------
 
BUG# NT: 470 (4.2)
 
SYMPTOMS
========
 
Using the CONVERT command to convert a valid real value from a column
which allows nulls to a varchar or char large enough to hold the value
causes arithmetic overflow or returns of zero.
 
For example, the following script :
 
   create table t1
   (col1 float NULL)
   go
   insert t1
   values (4.5)
   go
   select convert( varchar(10), col1)
   from t1
   go
 
Would generate the following error message:
 
   Msg 232, Level 16, State 2:
   Arithmetic overflow error for type varchar, value = 0.000000
   Arithmetic overflow occurred.
 
NOTE: This is not a problem when the column does not allow nulls.
 
CAUSE
=====
 
SQL Server incorrectly handles the conversion of a real column which
allows nulls to varchar or char.
 
WORKAROUND
==========
 
Convert the real value to float or real before converting to varchar
or char datatypes.
 
For the above example, use the following query to generate the correct
result:
 
   select convert( varchar(10), convert(float, col1))
   from t1
 
STATUS
======
 
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 4.2. This problem was corrected in SQL Server version 4.2P33. For
more information, contact your primary support provider.
 
Additional reference words: 4.20 Windows NT
KBCategory: kbprg
KBSubcategory: SSrvWinNT
 
=============================================================================
 
Copyright Microsoft Corporation 1994.
