BUG: BCP Will Insert Larger Values Than Decimal Column Allows

 Last reviewed: May 1, 1997
Article ID: Q136949

The information in this article applies to:
  • Microsoft SQL Server, version 6.0
BUG# NT: 11404 (6.00)

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

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

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

  • 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 : kbbug6.00 kbprg SSrvProg
    Version : 6.0
    Platform : WINDOWS

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: May 1, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.