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

by

Troubleshooting Guide: Error 5340 — WITH Schema Clause Conflict in SQL Server 2025

Why This Error Happens

This error occurs because the OPENROWSET(BULK...) or BULK INSERT statement is attempting to use a WITH <schema> clause simultaneously with a FORMATFILE parameter or a SINGLE_BLOB/SINGLE_CLOB/SINGLE_NCLOB option. SQL Server’s Bulk Import engine treats these as mutually exclusive instructions: the WITH schema clause manually defines column structure, while FORMATFILE reads structure from an external XML/non-XML file, and the SINGLE_* options treat the entire file as a single binary object with no structure at all. Since the engine cannot determine whether to parse the file using your manual schema, an external file definition, or treat it as raw unstructured data, it throws a syntax conflict rather than guessing your intent.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Syntax in Your Query

  • Open the T-SQL script containing the BULK INSERT or OPENROWSET(BULK...) command triggering Error 5340.
  • Look specifically for a WITH (...) clause that contains column definitions (e.g., col1 INT, col2 VARCHAR(50)) appearing alongside FORMATFILE = 'path' or SINGLE_BLOB.
  • Example of the broken pattern:
  SELECT * FROM OPENROWSET(BULK 'C:\Data\file.csv', FORMATFILE = 'C:\Data\format.xml', SINGLE_CLOB) AS Data;

Step 2: Choose a Single Schema-Definition Method

  • Decide whether you want to use a Format File (recommended for complex, multi-column CSV/flat files) or Single Blob Mode (recommended for loading one entire file into one variable, like JSON or XML parsing).
  • If using FORMATFILE, remove the manual WITH (col1 INT...) schema clause entirely and let the .xml format file handle column mapping.
  • If using SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB, remove the FORMATFILE parameter, as these options ignore column structure entirely and return the whole file as one row/one column.

Step 3: Rewrite the Query Using Correct Syntax

  • For structured data with a Format File (no manual schema needed):
  SELECT * FROM OPENROWSET(BULK 'C:\Data\file.csv', FORMATFILE = 'C:\Data\format.xml') AS Data;
  • For raw/unstructured ingestion (e.g., loading a JSON file into a variable):
  SELECT BulkColumn FROM OPENROWSET(BULK 'C:\Data\file.json', SINGLE_CLOB) AS Data;
  • Save the script and re-run it against the Windows Server 2025 instance.

Step 4: Validate File Paths and Permissions

  • Ensure the SQL Server (MSSQLSERVER) service account has Read NTFS permissions on the folder housing the file and format file (e.g., C:\Data\).
  • Verify the path is accessible locally to the SQL instance; UNC paths require the service account to have domain-level read access if running on Windows Server 2025 in a clustered environment.

Alternative or Verification Steps

  • Verify the Fix: Re-execute the corrected query in SSMS (SQL Server Management Studio) connected to your SQL Server 2025 instance; a successful run will return the data preview grid without throwing the 5340 syntax error.
  • Alternative Approach: If you specifically need both a custom schema and raw byte access, use OPENROWSET(BULK...) with SINGLE_BLOB first to pull the data into a staging table, then use a secondary SELECT INTO statement with explicit CAST/CONVERT functions to apply your schema afterward.