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

by

Troubleshooting Guide: SQL Server Error 4823 – “Cannot Bulk Load. Invalid Column Number in the Format File”

Why This Error Happens

Error 4823 typically occurs when the format file (.fmt or .xml) used during a BULK INSERT or bcp operation references a column number that doesn’t exist or is out of sequence relative to the destination table’s actual schema. This mismatch commonly arises when the format file was generated against an older or different version of the table, or when columns were added, dropped, or reordered after the file was created. Corruption or manual editing errors in the format file—such as duplicate column IDs or skipped numbering—can also trigger this error in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Validate the Current Table Schema

  • Run sp_help 'YourTableName'; in SQL Server Management Studio (SSMS) to list the exact column order, names, and data types.
  • Compare the output against the column numbering sequence inside your format file.
  • Confirm no columns were added or removed since the format file was last generated.

Step 2: Regenerate the Format File

  • Open PowerShell or Command Prompt on Windows Server 2025 / Windows 11 with administrative privileges.
  • Navigate to your SQL Server binaries path, typically: C:\Program Files\Microsoft SQL Server\170\Tools\Binn\
  • Run the bcp utility to generate a fresh format file:
  bcp YourDatabase.dbo.YourTableName format nul -c -f C:\BulkData\NewFormatFile.fmt -T -S YourServerName
  • This ensures column numbers in the file align exactly with the live schema.

Step 3: Manually Inspect and Correct the Format File

  • Open the .fmt file in a plain text editor (e.g., Notepad++ or VS Code).
  • Check the “Host File Field Order” column—ensure numbering starts at 1 and increments sequentially without gaps or duplicates.
  • Verify the “Server Column Order” values match the actual ordinal position of columns in the destination table.
  • Save the file in the same encoding (ANSI or Unicode) as the original to avoid parsing errors.

Step 4: Re-Run the Bulk Load Operation

  • Execute the BULK INSERT or bcp command again, explicitly pointing to the corrected format file path:
  BULK INSERT YourTableName
  FROM 'C:\BulkData\datafile.csv'
  WITH (FORMATFILE = 'C:\BulkData\NewFormatFile.fmt');
  • Monitor the SQL Server Error Log or SSMS output pane for confirmation of success or any secondary errors.

Alternative or Verification Steps

  • Verification: After the load completes, run SELECT COUNT(*) FROM YourTableName; and spot-check a few rows with SELECT TOP 10 * FROM YourTableName; to confirm data landed in the correct columns.
  • Alternative: If format file issues persist, bypass the file entirely by using OPENROWSET(BULK...) with explicit column mapping in a SELECT INTO statement, which avoids format file dependency altogether.