SQL Server – BCP Error – “String Truncation”

Background

Trying to Bulk copy data but running into error.

Error

Error Image

stringdatarighttruncation.20190108.0552pm

Error Text


SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
1000 rows sent to SQL Server. Total sent: 1591000
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

1591585 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 347468 Average : (4580.52 rows per sec.)

Troubleshooting

We copied the data over using character mode and it is possible carriage return our default line terminator is naturally occurring.

Remediation

Change from character mode (-c) to native mode ( -n)

Next Error

Image

unexpectedEOFEncounteredInBCPDatafile.20190108.0604PM.PNG

Textual


1000 rows sent to SQL Server. Total sent: 3198000
1000 rows sent to SQL Server. Total sent: 3199000
1000 rows sent to SQL Server. Total sent: 3200000
1000 rows sent to SQL Server. Total sent: 3201000
1000 rows sent to SQL Server. Total sent: 3202000
1000 rows sent to SQL Server. Total sent: 3203000
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]
Unexpected EOF encountered in BCP data-file

 

References

  1. Github
    • Microsoft
      • msphpsql
        • PDOException: String data, right truncation when insert a long string #169
          Link