BUG: BCP Will Insert Larger Values Than Decimal Column Allows

• Microsoft SQL Server version 6.0

SYMPTOMS

When a column is defined as decimal with precision and scale, and data is being inserted that is larger than the column, BCP will allow one additional value to be inserted to the left of the decimal. If the same data is entered using INSERT, a warning message of "Arithmetic overflow occurred" will be displayed and NULL will be inserted into that column.

WORKAROUND

Create a new table with the same attributes as the original table. Transfer the data from the original table to the new table, then drop and recreate the original table, increasing the decimal column to a large enough precision to handle the largest number in the data file.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Decimal is a new datatype in SQL Server version 6.0. If a column is defined with a datatype of (6,3), with six being the precision and three being the scale, BCP will allow a total of seven decimal digits with four being stored to the left of the decimal and three to the right. BCP will successfully enter these values without any warning message that an arithmetic overflow occurred or that the data has been truncated.

The following script can be used as an example:

1. 
      use pubs
      go
      CREATE TABLE bcptable
      (col1 char (4) NULL , col2 decimal(6, 3) NULL)
      go
    




2. create a data file called c:\data.dat similar to:
   

   
      row1,123
      row2,1234
      row3,12345
    




3. create a format file called c:\data.fmt similar to the following:

   
   6.0
   2
   1       SQLCHAR       0       4       ","        1       col1
   2       SQLCHAR       0       6       "\r\n"     2       col2 




4. BCP the data in by going to a command prompt and typing:
   

   bcp pubs..bcptable in c:\data.dat /fc:\data.fmt /Usa /P




5. Select * from bcptable. Results should look like:
   

   
         col1 col2
         ---- --------
         row1 123.000
         row2 1234.000
         row3 2345.000
    

Additional query words: sql6 windows nt

Keywords          : kbprg SSrvProg kbbug6.00 
Version           : 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 21, 1999