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

by

Why This Error Happens

Error 4842 occurs when SQL Server attempts to use the OPENROWSET(BULK...) function or a linked server configuration for bulk import operations, but the provider string referencing an external data source (such as a flat file or OLE DB provider) is missing the mandatory FormatFile property. This typically happens when a BCP (Bulk Copy Program) format file path is omitted, incorrectly referenced, or when the XML/non-XML format file was deleted or moved after the job was scheduled. It can also surface if a linked server definition was manually edited and the provider string syntax was broken during the update.

Step-by-Step Resolution Guide

Step 1: Verify the Format File Path and Existence

  • Check the physical path referenced in your BULK INSERT or OPENROWSET statement (e.g., C:\SQLData\FormatFiles\import.fmt).
  • Confirm the file exists on the SQL Server 2025 host and hasn’t been moved, renamed, or deleted.
  • Run dir "C:\SQLData\FormatFiles\" from an elevated PowerShell/Command Prompt on Windows Server 2025 to validate presence.

Step 2: Correct the Provider String Syntax

  • Open the T-SQL script or linked server definition referencing the bulk operation.
  • Ensure the provider string explicitly includes the FormatFile= property, for example:
  BULK INSERT dbo.TargetTable
  FROM 'C:\SQLData\ImportFiles\data.csv'
  WITH (FORMATFILE = 'C:\SQLData\FormatFiles\import.fmt');
  • If using OPENROWSET, confirm the syntax matches:
  SELECT * FROM OPENROWSET(BULK 'C:\SQLData\ImportFiles\data.csv',
  FORMATFILE = 'C:\SQLData\FormatFiles\import.fmt') AS t1;

Step 3: Regenerate the Format File (If Corrupted or Missing)

  • Use the bcp utility to regenerate a valid format file:
  bcp YourDatabase.dbo.TargetTable format nul -c -x -f "C:\SQLData\FormatFiles\import.fmt" -T -S YourServerName
  • Confirm the new .fmt file opens correctly and matches the column structure of the target table.

Step 4: Validate SQL Server Service Account Permissions

  • Ensure the SQL Server service account (visible in SQL Server Configuration Manager) has Read access to the folder containing the format file.
  • On Windows Server 2025, right-click the folder → Properties → Security tab → confirm NT SERVICE\MSSQLSERVER (or your named instance) has at least Read & Execute permissions.

Step 5: Restart SQL Server Service and Re-Test

  • Restart the SQL Server service via SQL Server Configuration Manager or run:
  Restart-Service -Name 'MSSQLSERVER'
  • Re-execute the bulk import or linked server query to confirm Error 4842 no longer appears.

Alternative or Verification Steps

  • Run the original BULK INSERT or OPENROWSET query again and confirm it completes without throwing Error 4842 in SQL Server Management Studio (SSMS) or the Windows Event Viewer.
  • If the error persists, bypass the format file entirely by using the WITH (FIELDTERMINATOR, ROWTERMINATOR) options directly in BULK INSERT, which avoids dependency on an external .fmt file.