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

by

Troubleshooting Guide: SQL Server Error 11742 — NEXT VALUE FOR in MERGE Statements

Why This Error Happens

This error occurs because SQL Server restricts the use of the NEXT VALUE FOR sequence function within MERGE statements to a very specific scenario: it must be referenced only through a default constraint defined on the target table, and it can only apply to INSERT actions. When a query attempts to call NEXT VALUE FOR directly inside the MERGE statement’s INSERT clause (rather than relying on the table’s default constraint), SQL Server 2025 rejects the operation because this violates the deterministic execution rules required for merge operations. This typically happens when developers migrate legacy identity-based insert logic to sequence-based logic without properly reconfiguring the table schema.

Step-by-Step Resolution Guide

Step 1: Identify the Offending MERGE Statement

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
  • Locate the stored procedure, script, or application query that triggered Event ID 11742 in the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
  • Confirm the exact line where NEXT VALUE FOR [SchemaName].[SequenceName] appears inside the INSERT clause of the MERGE statement.

Step 2: Modify the Target Table to Use a Default Constraint

  • Instead of calling NEXT VALUE FOR directly in the MERGE statement, bind the sequence to the target column via a default constraint.
  • Run the following T-SQL to alter the table:
ALTER TABLE dbo.YourTargetTable
ADD CONSTRAINT DF_YourTargetTable_ID 
DEFAULT (NEXT VALUE FOR dbo.YourSequenceName) FOR ID_Column;
  • Ensure the sequence object (dbo.YourSequenceName) already exists using SELECT * FROM sys.sequences; to verify.

Step 3: Rewrite the MERGE Statement to Omit the Column

  • Update your MERGE statement so the INSERT clause no longer explicitly references the sequence-generated column.
  • Allow the default constraint to populate the value automatically:
MERGE INTO dbo.YourTargetTable AS Target
USING dbo.SourceData AS Source
ON Target.BusinessKey = Source.BusinessKey
WHEN NOT MATCHED THEN
    INSERT (BusinessKey, OtherColumn)
    VALUES (Source.BusinessKey, Source.OtherColumn);
  • Do not include the ID_Column in the INSERT column list or VALUES clause since the default constraint now handles it.

Step 4: Deploy and Test in a Non-Production Environment

  • Push the schema change and updated stored procedure to a staging environment first.
  • Execute the MERGE operation manually to confirm Error 11742 no longer appears in the SSMS output or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.

Alternative or Verification Steps

  • Run sp_helpconstraint 'dbo.YourTargetTable'; to confirm the new default constraint referencing NEXT VALUE FOR is correctly attached to the target column.
  • If modifying the table schema is not feasible, replace the MERGE statement with separate UPDATE and INSERT statements, calling NEXT VALUE FOR directly within the standalone INSERT statement instead.