Lately is was confronted with a couple of textfiles that wouldn't load into the database (SQL Server 2005). I was getting the errors
Msg 4832, Level 16, State 1, Line 1Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
when trying to acquire the file through
BULK INSERT tblImport FROM 'Z:\concat-file.txt'WITH(TABLOCK, FORMATFILE = 'Z:\xmlformatfile.xml')
Turned out I had an arrow sitting at EOF (end of file) with the troublesome textfiles, which where concatenated using the copy-command;
COPY source*.txt concat-file.txt
The arrow representation was new to me, but after a little digging it boiled down to CTRL+Z and the difference of ASCII and binary copying... by default copy is ASCII and when merging files, so an end of file character gets inserted. The EOF character is omitted when doing a binary copy, so
COPY source*.txt /b concat-file.txt
resolved the error.
Curious about the arrow representation on Vista, I decided to verify with Windows Server 2003. Here the EOF story is (almost) the same, only the arrow is shown as a rectangle. Read more about copy here.