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

SQL Server – BulkCopy (Bcp) – Error – Unexpected EOF encountered in BCP data-file

Background

Having problems copying data using bulkcopy.

Error Message

bcp dbo.tblThirdPartyRooms in C:\Users\daniel\AppDdbo.tblThirdPartyRooms_v1.data -E -T -c -b20000 -S.\SQLEXPRESS_V2014 

Starting copy... SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file 0 rows copied.
Network packet size (bytes): 4096 Clock Time (ms.) Total : 15

What is the problem?

Let us make sure that structurally the tables are the same.

To do so, we can try using tablediff

TableDiff

 

Syntax

Item Explanation Sample
 Binary  Application Name  C:\Program Files\Microsoft SQL Server\120\COM\tablediff.exe
 -sourceServer  Source Server  SeattleDB
 -sourcedatabase  Source Database  Hotel
 -sourceschema  Source Schema  dbo
 -sourcetable  Source Table  tblThirdPartyRoomMapping
 -f Name of SQL file that will bring destination database object inline with source object %temp%\alignSQL.sql
 -o  BCP Output file %temp% \bcpOutput.txt
 -q  Quick Row Count a) Do not list individual record differences
b) Quicker

 

 

Sample

 

SETLOCAL

	set TABLEDIFF_BIN="C:\Program Files\Microsoft SQL Server\120\COM\tablediff.exe"
	set fixSQLFile=%TEMP%\alignSQL.sql
	set BCPOutputFile=%TEMP%\BCPOutput.txt

	rem Source
	set ss=SeattleDB
	set sd=Hotel
	set ss2=dbo
	set st=tblThirdPartyRoomMapping

	rem Destination
	set ds=.\SQLEXPRESS_V2014
	set dd=Hotel
	set ds2=dbo
	set dt=tblThirdPartyRoomMapping

	rem Misc Options
	set miscOptions=-f %fixSQLFile% -o %BCPOutputFile% -q 

        rem reset ERRORLEVEL FLAG
        verify >nul
	%TABLEDIFF_BIN% -sourceServer %ss% -sourcedatabase %sd% -sourceschema %ss2% -sourcetable %st% ^
     	-destinationserver %ds% -destinationdatabase %dd% -destinationschema %ds2% -destinationtable %dt% ^
		%miscOptions% 

       rem display errorlevel
       echo ErrorLevel is %ErrorLevel%
ENDLOCAL

Output:

Here is our run output …

TableDiffOutput
Error Level:
ErrorLevel

 

And, here is what we discovered upon inspecting our BCPOutput.txt file.

BCPOutput

Quick Explanation:

  • We were able to connect to both servers
  • As we expected, we have data in the Source Database, but not in the Destination database
  • We were hoping that we will find schema differences, but none showed up

 

Compare BCP Out to BCP Input Command

Went back and reviewed our BCP Commands

BCP Output


bcp Hotel.dbo.[tblThirdPartyRoomMapping] out %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data  -t"||" -T -c -S%DBSourceServer%

 

BCP Input


bcp %DBTargetDB%.dbo.tblThirdPartyRoomMapping in %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data -E -T -c -b%BATCH_SIZE% -S%DBTargetServer%

Upon a more careful comparison of the BCP Export and Import statements, now noticed that we have a column delimeter in the export, but we missed it on the input.

BCP Input (Corrected)


rem please notice the -t"||" -- adding delimeter
bcp %DBTargetDB%.dbo.tblThirdPartyRoomMapping in %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data  -t"||" -E -T -c -b%BATCH_SIZE% -S%DBTargetServer%

Summary

Once again, this is just a stupid error on my part.

Wrote it up as the initial hits that came up via Google, were a bit muddled.

I suppose no one makes mistakes like this.