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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s