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

by

Troubleshooting Guide: Error 10769 – “SET Options Are Not Supported Inside ATOMIC Blocks”

Why This Error Happens

This error occurs when a natively compiled stored procedure or module containing an ATOMIC block attempts to execute a SET statement (such as SET NOCOUNT, SET ANSI_WARNINGS, or SET DATEFORMAT) that is explicitly disallowed within the atomic execution context. SQL Server’s natively compiled modules require a fixed set of session options determined entirely at compile time via the WITH clause of the BEGIN ATOMIC statement, rather than being altered dynamically at runtime. This typically results from legacy T-SQL code or migration scripts that were written for interpreted Transact-SQL and have not been properly adapted for memory-optimized, natively compiled procedures in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending SET Statement

  • Open the stored procedure or function definition using SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Locate the CREATE PROCEDURE or CREATE FUNCTION definition and scan the body for any SET keyword occurring after the BEGIN ATOMIC declaration.
  • Common culprits include SET NOCOUNT ON, SET XACT_ABORT ON, and SET ANSI_NULLS.

Step 2: Remove Inline SET Statements from the Procedure Body

  • Delete or comment out any SET commands found directly inside the atomic block body.
  • Ensure no application-side middleware or ORM (like Entity Framework) injects dynamic SET commands into the natively compiled module at runtime.

Step 3: Define Required Options in the ATOMIC Block Header

  • Modify the BEGIN ATOMIC clause to include the necessary options as parameters rather than runtime commands.
  • Use the following syntax structure: BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
  • Verify that TRANSACTION ISOLATION LEVEL and LANGUAGE are the only two parameters permitted in this clause, as these replace the need for standard SET commands.

Step 4: Recompile the Module

  • Drop the existing procedure using DROP PROCEDURE [SchemaName].[ProcedureName];
  • Re-create the procedure using the corrected script via SSMS or a sqlcmd command line execution: sqlcmd -S localhost -d YourDatabase -i C:\Scripts\FixedProcedure.sql
  • Check the “Messages” tab in SSMS to confirm the compilation succeeded without throwing Error 10769 again.

Alternative or Verification Steps

  • Verification: Execute the procedure manually using EXEC [SchemaName].[ProcedureName]; and confirm that the query returns results without the native compilation error appearing in the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
  • Alternative Fix: If specific SET behavior is strictly required for the business logic, convert the natively compiled procedure back into a standard interpreted T-SQL procedure by removing the NATIVE_COMPILATION option from the WITH clause, allowing for full support of session-level SET commands.