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

by

Troubleshooting Guide: SQL Server Error 4821 – Bulk Load Format File Column Count Error

Why This Error Happens

Error 4821 occurs when SQL Server’s BULK INSERT or bcp utility attempts to read a non-XML format file (.fmt) but fails to parse the number of columns specified on the first line of the file. This typically happens because the format file is corrupted, was saved with an incorrect encoding (such as Unicode when ANSI was expected, or vice versa), or the file was manually edited and the column count integer was accidentally altered or removed. It can also occur if the path to the format file is incorrect, causing SQL Server to read an empty or invalid file as if it were a valid format definition.

Step-by-Step Resolution Guide

Step 1: Validate the Format File Structure

  • Open the .fmt file in a plain text editor (Notepad++ recommended over standard Notepad to detect encoding issues).
  • Confirm the very first line contains the format file version number (e.g., 9.0 or 14.0), and the second line contains a single integer representing the total column count.
  • Check for hidden characters, extra line breaks, or whitespace before the integer value on line two.

Step 2: Regenerate the Format File

  • If manual inspection reveals corruption, do not attempt to patch it; regenerate it using the bcp utility from an elevated Command Prompt on Windows Server 2025.
  • Run the following command, adjusting for your specific database and table:
    bcp YourDatabase.dbo.YourTable format nul -c -f C:\BulkImport\YourTable.fmt -T -S YourServerInstance
  • Ensure the output path (e.g., C:\BulkImport\) exists and the SQL Server service account has read permissions on that directory.

Step 3: Verify File Encoding Compatibility

  • Determine if your format file is Non-XML (fixed/delimited) or XML-based; non-XML files are strictly sensitive to encoding.
  • If the file was transferred from a Linux/Unix system or a different locale, re-save it using ANSI encoding via Notepad++ (Encoding > Convert to ANSI) to strip any Byte Order Mark (BOM) that Windows Server might misinterpret.
  • Re-run the BULK INSERT command, ensuring the FORMATFILE parameter in your T-SQL script points to the exact absolute path (e.g., FORMATFILE = 'C:\BulkImport\YourTable.fmt').

Step 4: Check File System Permissions

  • Verify that the Windows account running the SQL Server service (visible in SQL Server Configuration Manager) has at least Read NTFS permissions on the folder containing the .fmt file.
  • Right-click the folder in File Explorer, navigate to Properties > Security, and add the NT SERVICE\MSSQLSERVER account if it is missing.

Alternative or Verification Steps

  • Verification: Run a test BULK INSERT using a WITH (FIRSTROW = 2) clause on a small subset of data to confirm the format file parses correctly before processing the full dataset.
  • Alternative: If the non-XML format file continues to fail, switch to an XML-based format file by adding the -x flag during generation (e.g., bcp ... -x -f YourTable.xml), as the XML schema is less prone to silent integer parsing errors.