Search code examples
sql-serverbcp

Unexpected EOF encountered in BCP data-file bcp version 2014.120.6169.19

I get [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file on my production server but not on my test server.

Both servers have exactly the same set up, I am testing with same file from the same folder path on both servers. The database on the test server is a restored back-up taken from the production server.

bcp.exe version 2014.120.6169.19 
SQL server version 12.0.6169.19 
Windows Server 2012 R2

This is not a problem with the file encoding or the arguments of the BCP command, I have checked:

  • the Windows Registry and all ODBC and SQL Server values are the same for both servers
  • the versions of the files in C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110 and they are all the same.
  • I compared MD5 checksum of bcp.exe and on both servers it is the same 5f f0 d6 cf 46 b8 71 47 bc a0 fb ca 96 97 c8 e8
  • stopping starting SQL and rebooting the server did not help
  • the last SQL update in cache is "KB5014165" and was on 2022-06-21 and was applied to both servers

This has been working for years and stopped working within the last month.

UPDATE 2022-10-17

  • we installed latest service pack and SQL version is now 12.0.6439.10 but it made no difference
  • the file failed yesterday at 06:00 then imported okay this morning at 04:00 but then failed again at 06:00 and again when I tested at 10:30

Question: Is there anything else I could check? Perhaps an environment variable which could be different on the production server?

Solution

  • This was caused by the code which prepares the BCP file: it selects the field -t and row -r delimiters to be the lowest char value not present in the data itself.

    It caused BCP to fail for these two problems:

    A) Badly formatted file: the same char was used for both field and row delimiter, which although already a problem in itself manifested in BCP as an uneven number of field delimiters, meaning the end of file was reached while reading a field.

    Solution: make sure field -t and row -r delimiters are different

    B) Unsupported delimiter chars: an incompatible character chosen for use as one of the delimiters, specifically in this case it was char(127). Similarly from further testing I found I also have to avoid using chars <=21, in the range 127 to 160, and char(173)

    Solution: make sure you avoid delimiter characters in the ranges listed above

    Both showed the same unhelpful error message [Microsoft][ODBC Driver 11 for SQL Server]Unexpected EOF encountered in BCP data-file

    The reason this did not also happen on the test server: For optimisation the each server remembers and re-uses delimiters detected during previous imports, at some point the production server had been trained on to the problematic delimiters by an older data import which the test server had never seen. So the test server still had valid delimiters cached but the production server had bad ones.