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

by

Troubleshooting Guide: SQL Server Error 4843 — Bulk Data Source Provider String Syntax Error

Why This Error Happens

Error 4843 occurs when the BULK INSERT or OPENROWSET(BULK...) statement contains a malformed provider string, typically caused by incorrect syntax in the FORMATFILE, DATA_SOURCE, or format specification arguments. This is often the result of manually editing a query, copy-pasting from an incompatible source (introducing hidden characters), or using an unsupported character/delimiter in the path string. It can also happen when a format file (XML or non-XML) referenced in the bulk operation is corrupted or has been edited with an incompatible text encoding.

Step-by-Step Resolution Guide

Step 1: Inspect and Correct the T-SQL Syntax

  • Open the exact query that triggered the error and locate the character position referenced in the error message (%d).
  • Check for unescaped single quotes, missing commas, or malformed bracket/parenthesis pairing in the BULK INSERT or OPENROWSET clause.
  • Rewrite the query manually in SQL Server Management Studio (SSMS) rather than pasting from Word or web sources, as hidden Unicode characters (like smart quotes ’ vs ') commonly trigger this error.
  • Example of a clean syntax reference:
  BULK INSERT dbo.TargetTable
  FROM 'C:\Data\import.csv'
  WITH (
      FORMATFILE = 'C:\Data\import.fmt',
      FIRSTROW = 2
  );

Step 2: Validate the Format File Integrity

  • Navigate to the format file location (commonly stored under C:\SQLData\Formats\ or a custom ETL directory).
  • Open the .fmt or .xml format file in a plain text editor (Notepad++ recommended over standard Notepad) to check for corruption or invalid XML tags.
  • Regenerate the format file using the bcp utility if corruption is suspected:
  bcp YourDatabase.dbo.YourTable format nul -c -f C:\Data\import.fmt -S YourServerName -T
  • Ensure there are no extra spaces or line breaks inside the connection string parameters.

Step 3: Verify Provider and Permissions Configuration

  • Confirm that Ad Hoc Distributed Queries is enabled if using OPENROWSET, via SQL Server Configuration Manager or using the following script:
  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
  RECONFIGURE;
  • On Windows Server 2025 / Windows 11, verify that the SQL Server service account has Read permissions on the target folder (Right-click folder → Properties → Security tab).
  • Restart the SQL Server (MSSQLSERVER) service via services.msc after making configuration changes.

Step 4: Re-run the Bulk Operation in Isolation

  • Strip the query down to the minimum required clause (just FROM and the file path) and execute it alone to confirm the base path syntax is valid.
  • Incrementally add back WITH options (FORMATFILE, FIRSTROW, TABLOCK) one at a time to isolate which specific parameter is causing the parser failure.

Alternative or Verification Steps

  • Run SELECT * FROM sys.dm_exec_query_stats cross-referenced with recent query text to confirm the corrected statement executes without Error 4843 appearing in the SQL Server Error Log (Management → SQL Server Logs in SSMS).
  • If the error persists, bypass the format file entirely by using simplified BULK INSERT syntax with inline field/row terminators (e.g., FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') instead of referencing an external .fmt file.