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

by

Troubleshooting Guide: SQL Server Error 4806 – SINGLE_CLOB DBCS Mismatch

Why This Error Happens

This error occurs when the BULK INSERT or OPENROWSET(BULK...) command uses the SINGLE_CLOB option while pointing to a source file encoded in Unicode (UTF-16 or UTF-8 with BOM). SQL Server’s SINGLE_CLOB data type strictly expects a single-byte or double-byte character set (DBCS) file format and cannot parse the byte-order mark (BOM) or multi-byte pairing used in Unicode text files. Essentially, there is a mismatch between the storage object type requested in T-SQL and the actual binary encoding of the file on disk.

Step-by-Step Resolution Guide

Step 1: Verify the Source File Encoding

  • Open the source file in a hex editor or Notepad++ to check for a Byte Order Mark (BOM).
  • Look for FF FE (UTF-16 LE) or EF BB BF (UTF-8) at the start of the file; these indicate Unicode encoding.
  • If a BOM is present, this confirms the file is incompatible with the SINGLE_CLOB option as currently written.

Step 2: Update the T-SQL Syntax to Match File Type

  • If the file must remain in Unicode format, change the clause from SINGLE_CLOB to SINGLE_NCLOB in your OPENROWSET or BULK INSERT statement.
  • Example correction:
  SELECT * FROM OPENROWSET(BULK 'C:\Data\source_file.txt', SINGLE_NCLOB) AS DataSource;
  • SINGLE_NCLOB specifically supports Unicode text, resolving the type mismatch immediately.

Step 3: Convert the File Encoding (If NCLOB is Not an Option)

  • If your application logic specifically requires SINGLE_CLOB, convert the source file to ANSI/DBCS instead.
  • Open the file in Notepad on Windows Server 2025, select File > Save As.
  • In the “Encoding” dropdown menu at the bottom of the save dialog, select ANSI instead of UTF-8 or Unicode.
  • Re-run the import job pointing to this newly saved ANSI file.

Step 4: Check Database Collation Settings

  • Navigate to SQL Server Management Studio (SSMS) and right-click the target Database > Properties.
  • Under the Options tab, verify the Collation setting aligns with a standard DBCS-compatible collation (e.g., SQL_Latin1_General_CP1_CI_AS).
  • Mismatched server collation can sometimes exacerbate encoding read errors during the bulk load process.

Alternative or Verification Steps

  • Verify the Fix: Re-run the exact BULK INSERT or OPENROWSET query. If the command executes without throwing Error 4806 and returns the expected row/column data, the encoding issue is resolved.
  • Alternative Method: If manual conversion is not feasible for large files, use a PowerShell script to batch-convert encoding: Get-Content Source.txt | Set-Content -Encoding Ascii Source_Converted.txt before pointing the SQL query to the new file.