Search code examples
sql-serverbcp

BCP File not loaded completely when Numeric value out of range error occurs for 10 times

I am trying to import a tab delimited file in to SQL. One of the field in DB is decimal(15,2). But the file that I receive may have some exponential values like -2.702159776E17 which clearly does not fit for the field I have in DB.

The problem is if there are more than 10 records of that exponential file, BCP is not loading in the complete data. As soon as it is encountering the 10th instance of the exponential value, it is skipping the rest of the file.

I am using below BCP command to load the data:

dbname.dbo.tablename in "filelocation" -c -F 2 -b 10000 -h "TABLOCK" -T -S servername

The file I am trying to import has 200,000 records. But we can clearly see that it has processed only 18,000 records, rest are skipped. Output from the BCP as below.

Starting copy...
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range
SQLState = 22003, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Numeric value out of range

18149 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 265    Average : (68486.79 rows per sec.)
Solution

  • Look into the following BCP option:

    -m max_errors Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. A syntax error implies a data conversion error to the target data type. The max_errors total excludes any errors that can be detected only at the server, such as constraint violations.

    A row that cannot be copied by the bcp utility is ignored and is counted as one error. If this option is not included, the default is 10.

    You can see that the default is 10 errors. Specify this parameter and supply a very large value if you want to skip all errors.

    If you want to import all the records, you could import the records to a staging table that has the FLOAT data type instead of the DECIMAL(15,2). Then afterwards, copy the data from the staging table to the table you want the data in, by converting the float value to a decimal value.