Having problems copying data using bulkcopy.
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
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
Here is our run output …
And, here is what we discovered upon inspecting our BCPOutput.txt file.
- 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 Hotel.dbo.[tblThirdPartyRoomMapping] out %TEMP%\dbo.tblThirdPartyRoomMapping_v1.data -t"||" -T -c -S%DBSourceServer%
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%
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.