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

by

Troubleshooting Guide: SQL Server Error 122 — “The %ls option is allowed only with %ls syntax”

Why This Error Happens

This error occurs when a T-SQL statement uses a command-line style option or clause in conjunction with an incompatible syntax structure, most commonly seen with BACKUP, RESTORE, BULK INSERT, or OPENROWSET statements. SQL Server 2025’s parser enforces strict compatibility rules between certain options (like WITH FORMATFILE, SINGLE_BLOB, or FILESTREAM-related clauses) and the specific syntax variant they must be paired with. This typically stems from a malformed or legacy query—often migrated from an older SQL Server version—where an option valid in one syntax mode (e.g., BULK rowset provider) is mistakenly applied to another (e.g., standard INSERT).

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Check the SQL Server Error Log or the client application’s error output to capture the full, non-truncated error message and the exact query text.
  • Look specifically for placeholders like %ls being replaced with clause names such as SINGLE_CLOB, CODEPAGE, or ORDER.
  • Use SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) to re-run the isolated statement to reproduce the error in a controlled environment.

Step 2: Cross-Reference Syntax Documentation

  • Open the current Microsoft Docs reference for the specific command (e.g., BULK INSERT or OPENROWSET(BULK...)) to verify the exact syntax tree for SQL Server 2025.
  • Verify that options like FORMATFILE, FIRSTROW, or ROWTERMINATOR are being used strictly within the WITH (...) clause and not mixed with deprecated inline syntax.
  • Pay close attention to the placement of the BULK keyword; it must directly follow OPENROWSET( and cannot be used as a standalone modifier in a standard SELECT statement.

Step 3: Rewrite and Sanitize the Query

  • Remove any options that are mutually exclusive. For example, ensure SINGLE_BLOB or SINGLE_CLOB is not used alongside row-based options like FIELDTERMINATOR.
  • Update file paths to use fully qualified UNC paths (e.g., \\ServerName\Share\file.csv) rather than local paths, as Windows Server 2025 network path resolution can sometimes trigger parser conflicts if paths are ambiguous.
  • Restructure the query to align with the specific syntax variant demanded by the error (e.g., moving from BULK INSERT syntax to OPENROWSET(BULK...) syntax if the option requires it).

Step 4: Update SQL Server Client Tools

  • Ensure that sqlcmd or the OLE DB driver used to submit the query is updated to the latest version supporting SQL Server 2025 (Version 18 or higher).
  • Older drivers on Windows 11 client machines may format queries using deprecated syntax patterns that trigger this parser error on the newer server engine.

Alternative or Verification Steps

  • Verification: Execute the corrected query in a SET PARSEONLY ON session first. If the query compiles without throwing Error 122, toggle SET PARSEONLY OFF and run it live to confirm data retrieval.
  • Alternative: If the error persists specifically with BULK INSERT, pivot to using the OPENROWSET(BULK...) syntax instead, as it often provides more flexible support for the specific option causing the conflict.