Microsoft KB Archive/136949

{|
 * width="100%"|

BUG: BCP Will Insert Larger Values Than Decimal Column Allows

 * }

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.

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

   use pubs go  CREATE TABLE bcptable (col1 char (4) NULL, col2 decimal(6, 3) NULL) go   create a data file called c:\data.dat similar to:

  row1,123 row2,1234 row3,12345   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 : kbprogramming

Issue type : kbbug

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600