How to fix Error 4864 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4864 – Bulk Load Data Conversion Error

Why This Error Happens

Error 4864 occurs when SQL Server’s BULK INSERT, BCP, or OPENROWSET(BULK...) operation encounters data in the source file that does not match the expected data type or column format defined in the destination table. This is commonly caused by a mismatched format file, incorrect field terminators/row terminators, or a codepage/collation mismatch between the source file encoding (e.g., UTF-8 with BOM) and the SQL Server import settings. It can also occur if the source file contains an invalid character for the specified codepage, such as a non-ASCII character in an ANSI-only field.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Row and Column Causing the Failure

  • Open the source file (CSV/TXT) and navigate to the specific row number mentioned in the error message.
  • Check for extra delimiters, unescaped commas, or hidden characters (use a hex editor like HxD or VS Code’s “Show Whitespace” mode).
  • Confirm the row’s column count matches the destination table’s column count exactly.

Step 2: Verify and Correct the Format File or Column Mappings

  • Open the .fmt (format) file used in the bulk operation (typically located in the same directory as the source file or referenced via FORMATFILE= argument).
  • Ensure each column’s SQLCHAR/SQLNCHAR/SQLBINARY type in the format file matches the destination table schema in SQL Server Management Studio (SSMS).
  • Example correction in format file:
  10.0
  3
  1 SQLCHAR 0 50 "," 1 FirstName SQL_Latin1_General_CP1_CI_AS
  2 SQLCHAR 0 50 "," 2 LastName SQL_Latin1_General_CP1_CI_AS
  3 SQLCHAR 0 10 "\r\n" 3 ZipCode SQL_Latin1_General_CP1_CI_AS

Step 3: Fix the Codepage and Character Encoding Mismatch

  • Re-save the source file using the correct encoding expected by SQL Server (typically UTF-8 without BOM for CODEPAGE='65001' or ACP for CODEPAGE='ACP').
  • Update your BULK INSERT statement to explicitly declare the codepage:
  BULK INSERT dbo.YourTable
  FROM 'C:\ImportData\source.csv'
  WITH (
      FORMATFILE = 'C:\ImportData\format.fmt',
      CODEPAGE = '65001',
      FIRSTROW = 2,
      TABLOCK
  );
  • If using Windows Server 2025 file shares, confirm the file wasn’t altered by a text editor that injected a BOM (Byte Order Mark) unexpectedly.

Step 4: Adjust Data Types or Use a Staging Table

  • If the mismatch is unavoidable (e.g., source always contains mixed types), import data into a staging table where all columns are defined as NVARCHAR(MAX).
  • Perform data type conversion and validation using TRY_CONVERT() after the load, rather than during the bulk operation:
  INSERT INTO dbo.FinalTable (OrderDate)
  SELECT TRY_CONVERT(DATE, OrderDateRaw)
  FROM dbo.StagingTable;

Alternative or Verification Steps

  • Re-run the BULK INSERT command after corrections; if successful, query SELECT COUNT(*) FROM dbo.YourTable to confirm the expected row count matches the source file.
  • If the error persists, use BCP with the -e flag (bcp dbo.YourTable in source.csv -e error.log -S servername -T -c) to generate a detailed error log identifying every problematic row at once instead of one at a time.