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

by

Troubleshooting Guide: Error 5346 — “DATAFILETYPE can only be used with schema inference or WITH clause”

Why This Error Happens

This error occurs when a BULK INSERT or OPENROWSET(BULK...) statement specifies the DATAFILETYPE option without pairing it with a compatible schema discovery mechanism, such as automatic schema inference or an explicit WITH clause defining column formats. SQL Server 2025 enforces stricter parsing rules for bulk data operations, requiring the engine to know the structure of incoming data before it can correctly interpret the DATAFILETYPE flag. Without this context, the parser cannot validate how to map raw file bytes (native, char, widechar, etc.) to the destination schema, causing the statement to fail during compilation.

Step-by-Step Resolution Guide

Step 1: Review the Bulk Operation Syntax

  • Open the query or SSIS/T-SQL script that triggered the error and locate the BULK INSERT or OPENROWSET(BULK...) statement.
  • Confirm whether a WITH clause is present immediately after the file path declaration.
  • Check for a missing comma or malformed clause structure, as this often causes SQL Server to treat DATAFILETYPE as an orphaned parameter.

Step 2: Add an Explicit WITH Clause

  • Modify the statement to include a properly formatted WITH clause, for example:
  BULK INSERT dbo.TargetTable
  FROM 'C:\Data\import.dat'
  WITH (
      DATAFILETYPE = 'widechar',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
  );
  • Ensure DATAFILETYPE values match the actual file encoding (char, native, widechar, or widenative).

Step 3: Enable Schema Inference (If Using OPENROWSET)

  • For OPENROWSET(BULK...) queries targeting Parquet, CSV, or JSON sources, confirm that FORMAT = 'CSV' or FORMAT = 'PARQUET' is declared alongside DATAFILETYPE.
  • If schema inference is required, add SINGLE_CLOB, SINGLE_BLOB, or a format file reference (FORMATFILE = 'C:\Data\format.xml') to give SQL Server enough metadata to resolve columns automatically.
  • Restart the SQL Server service via services.msc on Windows Server 2025 if cached execution plans continue referencing the old malformed syntax.

Step 4: Validate File Path and Permissions

  • Confirm the SQL Server service account has read access to the file path (e.g., C:\Data\import.dat) under Windows Server 2025 security policies.
  • Use icacls C:\Data /grant "NT SERVICE\MSSQLSERVER":(R) to grant read permissions if access is denied, which can sometimes surface as a misleading syntax error.

Alternative or Verification Steps

  • Run the corrected BULK INSERT statement in SQL Server Management Studio (SSMS) and confirm it executes without Error 5346, then check sys.dm_exec_requests to verify the session completed successfully.
  • If the error persists, test with a minimal reproduction script using a sample .csv file and a basic WITH (DATAFILETYPE = 'char') clause to isolate whether the issue is syntax-related or environment-related (file permissions, SQL Server patch level).