Troubleshooting Guide: SQL Server Error 10915 — “Altering ‘%.*ls’ %S_MSG is not allowed”

Why This Error Happens

Error 10915 occurs when an ALTER TABLE or ALTER COLUMN statement attempts to modify an object in a way that conflicts with SQL Server’s internal metadata constraints, such as changing a column that is referenced by a computed column, index, statistics, or replication schema. This often happens because the target object has dependencies (foreign keys, indexed views, full-text indexes, or in-memory OLTP constructs) that lock certain properties from being altered directly. It can also surface when attempting to modify system-versioned temporal tables or memory-optimized tables without first disabling the feature that restricts the change.

Step-by-Step Resolution Guide

Step 1: Identify the Blocking Dependency

• Run sp_depends 'YourTableName' or query sys.sql_expression_dependencies to list objects referencing the table/column you are trying to alter.
• Check if the column is part of a computed column, check constraint, or indexed view definition.
• Look for memory-optimized table flags by running SELECT is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';.

Step 2: Drop or Disable Conflicting Constraints/Indexes

• If the column is used in a non-clustered index or statistics object, drop it temporarily using DROP INDEX IX_Name ON dbo.YourTableName;.
• For system-versioned temporal tables, disable versioning first: ALTER TABLE dbo.YourTableName SET (SYSTEM_VERSIONING = OFF);.
• For memory-optimized (In-Memory OLTP) tables, note that many ALTER operations are restricted in SQL Server 2025; you may need to export data, drop, and recreate the table with the new schema instead.

Step 3: Modify the Schema Using a Safe Workaround

• Create a new table with the desired schema: CREATE TABLE dbo.YourTableName_New (...);.
• Migrate data using INSERT INTO dbo.YourTableName_New SELECT * FROM dbo.YourTableName;.
• Rename objects using sp_rename to swap the old table out and the new table in, minimizing downtime.

Step 4: Re-enable Constraints and Validate Permissions

• Re-create any dropped indexes, constraints, or system-versioning settings removed in Step 2.
• Confirm your login has ALTER permission on the schema by running SELECT * FROM fn_my_permissions('dbo.YourTableName', 'OBJECT');.
• On Windows Server 2025, ensure the SQL Server service account has not been restricted by newly hardened Group Policy settings affecting local file/database permissions.

Alternative or Verification Steps

• Re-run the original ALTER TABLE or ALTER COLUMN statement in SQL Server Management Studio (SSMS) and confirm it completes without Error 10915.
• If the error persists, use DBCC CHECKTABLE('YourTableName') to rule out corruption, and review the SQL Server Error Log (via EXEC sp_readerrorlog) for additional context tied to the same timestamp.

Troubleshooting Guide: SQL Server Error 10916 – Cannot Drop Resource Pool

Why This Error Happens

This error occurs because the Resource Governor architecture enforces a strict parent-child dependency between resource pools and workload groups. SQL Server 2025 prevents a resource pool from being dropped as long as one or more workload groups are still bound to it, since doing so would leave those workload groups orphaned with no defined resource boundaries. This is a logical safeguard in the Resource Governor configuration tree, not a file corruption or permissions issue—it simply means the DROP sequence was executed out of order.

Step-by-Step Resolution Guide

Step 1: Identify All Workload Groups Linked to the Resource Pool

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to list dependent workload groups:

  SELECT wg.name AS WorkloadGroupName, rp.name AS ResourcePoolName
  FROM sys.resource_governor_workload_groups wg
  JOIN sys.resource_governor_resource_pools rp
  ON wg.pool_id = rp.pool_id
  WHERE rp.name = 'YourResourcePoolName';

• Note down every workload group name returned in the results.

Step 2: Drop or Reassign the Workload Groups

• For each workload group identified, either drop it entirely or move it to a different resource pool.
• To drop a workload group, execute:

  DROP WORKLOAD GROUP YourWorkloadGroupName;

• If the workload group must remain active, reassign it instead:

  ALTER WORKLOAD GROUP YourWorkloadGroupName USING [default];

• Repeat this for every workload group tied to the resource pool before proceeding.

Step 3: Apply the Resource Governor Configuration

• Resource Governor changes are not live until reconfigured; run:

  ALTER RESOURCE GOVERNOR RECONFIGURE;

• This pushes the updated configuration tree into memory, clearing the dependency on the resource pool.

Step 4: Drop the Resource Pool

• Once all workload groups are removed or reassigned, retry the original command:

  DROP RESOURCE POOL YourResourcePoolName;

• Run ALTER RESOURCE GOVERNOR RECONFIGURE; again immediately after to finalize the removal.

Alternative or Verification Steps

• Verify the fix by re-running the query from Step 1—it should return zero rows for the dropped resource pool, confirming no orphaned workload groups remain.
• If the error persists, check for cached execution plans or open sessions still referencing the workload group via sys.dm_resource_governor_workload_groups; restarting the SQL Server service (on Windows Server 2025/Windows 11) can clear stale in-memory references as a last resort.

Troubleshooting Guide: SQL Server Error 10914 — Invalid Object Name (Begins with # or ##)

Why This Error Happens

This error occurs when a SQL Server object—such as a table, procedure, or variable—is incorrectly named using a single # or double ## prefix outside the context of temporary object creation. SQL Server reserves these prefixes exclusively for local (#) and global (##) temporary tables, so applying them to permanent objects, constraints, or identifiers violates naming rules. This typically stems from a scripting error, a flawed migration script, or an ORM (Object-Relational Mapper) incorrectly generating object names during a schema update.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

• Open SQL Server Management Studio (SSMS) and check the Activity Monitor or SQL Server Logs to locate the exact query that triggered Error 10914.
• Review the specific T-SQL statement for CREATE TABLE, CREATE PROCEDURE, or DECLARE commands that mistakenly use a # or ## prefix on a permanent object.
• Run the following command to check recent error logs via PowerShell on Windows Server 2025:

  Get-EventLog -LogName Application -Source "MSSQLSERVER" -Newest 20

Step 2: Correct the Object Naming Convention

• If the object is intended to be permanent, remove the # or ## prefix entirely from the object name.
• If the object is intended to be temporary, ensure it is created within a session scope correctly, for example:

  CREATE TABLE #TempTable (ID INT);

• Check your application’s ORM configuration (e.g., Entity Framework or Hibernate) for naming convention templates that might be auto-generating invalid prefixes.

Step 3: Update Linked Server or Migration Scripts

• Navigate to your deployment pipeline or migration folder (e.g., C:\DeploymentScripts\SQL\) and search for hardcoded # symbols in .sql files.
• Use a text editor like VS Code to perform a global search-and-replace for CREATE TABLE # to ensure the syntax matches the intended temporary or permanent scope.
• Re-deploy the corrected script to the Microsoft SQL Server 2025 instance.

Step 4: Validate Compatibility Level Settings

• Open SSMS and right-click the database, then select Properties > Options.
• Verify the Compatibility Level is set appropriately for SQL Server 2025 (Level 170), as older compatibility levels might handle temporary object parsing differently.
• Apply the changes and restart the SQL Server service via services.msc on Windows Server 2025 if necessary.

Alternative or Verification Steps

• Verification: Re-run the original script or application transaction that caused the error; confirm that Error 10914 no longer appears in the SQL Server Error Log or the Windows Event Viewer.
• Alternative: If the error persists in a specific stored procedure, use sp_helptext '[ProcedureName]' to review the exact object definition and manually rename the object using sp_rename, ensuring the new name starts with a standard alphanumeric character.

Troubleshooting Guide: SQL Server Error 10912 — “Dropping predefined %S_MSG is not allowed”

Why This Error Happens

This error occurs when an application, script, or administrator attempts to drop a system-defined or predefined object (such as a built-in schema, sensitivity classification label, security policy, or system role) that SQL Server protects from deletion. SQL Server 2025 enforces strict integrity checks on predefined metadata objects—including default Information Protection labels, system security predicates, or built-in server roles—to maintain internal consistency. The root cause is almost always a script or migration tool incorrectly targeting a reserved object name instead of a custom, user-created object.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Object Being Dropped

• Review the full error message text to extract the %S_MSG placeholder value, which names the specific predefined object (e.g., a default sensitivity label, system role, or built-in schema).
• Check the T-SQL script or application log immediately preceding the error to locate the exact DROP statement that triggered it.
• Run the following query to cross-reference system-protected objects:

  SELECT * FROM sys.objects WHERE is_ms_shipped = 1 AND name = 'YourObjectName';

Step 2: Correct the Script or Deployment Package

• If using SQL Server Data Tools (SSDT) or a DACPAC deployment, open the project and search for references to the predefined object name.
• Remove or comment out the DROP statement targeting the system object in your migration/change script.
• If using a third-party ORM or migration tool (e.g., Entity Framework, Liquibase, Flyway), check the generated migration file located in your project’s Migrations folder and exclude system-owned objects from the teardown logic.
• Re-deploy only after confirming the script no longer references is_ms_shipped = 1 objects.

Step 3: Validate Permissions and Object Ownership

• Confirm the executing login does not have unintended CONTROL SERVER or sysadmin rights that might allow it to attempt drops it shouldn’t logically perform.
• Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and navigate to Security > Logins, verifying role assignments match the principle of least privilege.
• If a custom object was accidentally named identically to a reserved system object, rename your custom object using sp_rename instead of attempting to drop the predefined one.

Step 4: Restart the Affected Service and Clear Cached Plans

• Open Services.msc on Windows Server 2025 and restart the SQL Server (MSSQLSERVER) service to clear any stale execution plans referencing the failed operation.
• Alternatively, run DBCC FREEPROCCACHE; in a new query window to flush cached query plans without a full restart.

Alternative or Verification Steps

• Re-run the original deployment or script in a non-production/staging environment first; confirm it completes with no Error 10912 in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists despite script corrections, use Extended Events (xe_logfile_data) to trace the exact session and statement ID generating the DROP call, as a hidden trigger or stored procedure may be issuing the command indirectly.

Troubleshooting Guide: SQL Server Error 10913 – Workload Group Access Denied

Why This Error Happens

This error occurs when the Resource Governor classifier function assigns a login or session to a workload group that the current security context does not have permission to use. It typically stems from misconfigured classification logic, a disabled or altered Resource Governor configuration that wasn’t reconfigured (RECONFIGURE) after changes, or the login lacking the necessary membership in a role that maps to the target workload group. Occasionally, this also surfaces when a workload group or resource pool is dropped or renamed while sessions or classifier functions still reference the old object.

Step-by-Step Resolution Guide

Step 1: Verify Resource Governor Status and Configuration

• Connect via SQLCMD or SSMS using the Dedicated Administrator Connection (DAC) if normal logins are being blocked.
• Run SELECT * FROM sys.resource_governor_configuration; to confirm Resource Governor is enabled (is_enabled = 1).
• Check SELECT * FROM sys.resource_governor_workload_groups; to confirm the referenced workload group and resource pool actually exist and are spelled correctly.

Step 2: Review and Correct the Classifier Function

• Open the classifier function assigned via sys.resource_governor_configuration.classifier_function_id.
• Ensure the logic correctly maps SUSER_SNAME() or APP_NAME() values to valid, existing workload groups.
• Example fix inside the function:

  IF SUSER_SNAME() = 'ReportingUser'
      RETURN 'ReportingGroup';

• Apply changes and run ALTER RESOURCE GOVERNOR RECONFIGURE; to push the update live.

Step 3: Check Login and Role Permissions

• Confirm the Windows or SQL login has the appropriate server-level permissions and is not blocked by Group Policy on Windows Server 2025/Windows 11 (check Local Security Policy > User Rights Assignment for “Log on as a batch job” if using Agent jobs).
• Validate the login isn’t mapped to default group when the classifier expects a specific pool.
• Grant CONTROL SERVER or appropriate permission if custom classifier logic checks for specific server roles:

  ALTER SERVER ROLE [ResourceGroupUsers] ADD MEMBER [DOMAIN\User];

Step 4: Rebuild Workload Group and Resource Pool Bindings

• If the pool or group was recently renamed/dropped, recreate it explicitly:

  CREATE WORKLOAD GROUP [ReportingGroup] USING [ReportingPool];
  ALTER RESOURCE GOVERNOR RECONFIGURE;

• Restart the SQL Server 2025 service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) if the reconfigure does not take effect immediately.

Alternative or Verification Steps

• Verify the fix by running SELECT session_id, group_id FROM sys.dm_exec_sessions WHERE session_id = @@SPID; and joining against sys.resource_governor_workload_groups to confirm the session lands in the expected group.
• As a fallback, temporarily disable Resource Governor (ALTER RESOURCE GOVERNOR DISABLE;) to confirm the error disappears, isolating the issue to classification logic rather than a broader Windows Server 2025 permissions problem.

Why This Error Happens

Error 10910 typically occurs when SQL Server rejects a value supplied during a DDL operation—most commonly when creating or altering a MEMORYOPTIMIZEDDATA filegroup, adding a container, or specifying a file path that doesn’t conform to expected naming or formatting rules. This is often caused by invalid characters in a path/name, a duplicate logical file name, or a directory that doesn’t exist or lacks proper NTFS permissions for the SQL Server service account. In Windows Server 2025 and Windows 11 environments, stricter path validation and enhanced security defaults can also trigger this error when legacy scripts reference deprecated syntax or malformed UNC paths.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Value Causing the Failure

• Re-run the failing T-SQL statement and capture the full error message, including the specific value substituted into ‘%.*ls’.
• Check SQL Server Error Log (via SSMS > Management > SQL Server Logs) for the full context of the failed command.
• Confirm whether the error relates to a filegroup name, container path, or column/constraint name.

Step 2: Validate the File or Directory Path

• Ensure the target directory exists on disk (e.g., C:\SQLData\MemoryOptimized\) and is not a reserved or system-protected folder.
• Open File Explorer or PowerShell and run:

  Test-Path "C:\SQLData\MemoryOptimized\"

• Verify the path does not contain invalid characters (\ / : * ? " < > |) or exceed the 260-character limit unless long path support is enabled.

Step 3: Check and Correct Permissions

• Open Computer Management > Local Users and Groups or use services.msc to identify the SQL Server service account.
• Grant Full Control NTFS permissions to the SQL Server service account on the target folder:

  icacls "C:\SQLData\MemoryOptimized" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F"

• Restart the SQL Server service from SQL Server Configuration Manager to apply the updated permissions.

Step 4: Correct the DDL Syntax

• Review the CREATE/ALTER statement for duplicate filegroup names, container names, or unsupported special characters.
• Rename the filegroup/container to use only alphanumeric characters and underscores, for example:

  ALTER DATABASE MyDB ADD FILEGROUP MyDB_MOD CONTAINS MEMORY_OPTIMIZED_DATA;
  ALTER DATABASE MyDB ADD FILE (NAME='MyDB_MOD_1', FILENAME='C:\SQLData\MemoryOptimized\MyDB_MOD_1') TO FILEGROUP MyDB_MOD;

• Re-execute the corrected statement and monitor for success.

Step 5: Apply Latest SQL Server 2025 Cumulative Update

• Check your current build via:

  SELECT @@VERSION;

• Download and install the latest Cumulative Update (CU) from Microsoft Update Catalog to resolve any known path-validation bugs in SQL Server 2025.
• Reboot Windows Server 2025 / Windows 11 after installation to finalize the update.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE/ALTER statement; a successful execution with no 10910 error confirms resolution.
• If the issue persists, drop and recreate the filegroup/container using a completely new name and a simplified local path (avoiding UNC shares) to rule out path-resolution conflicts.

Troubleshooting Guide: Error 10911 – Resource Pool Does Not Exist

Why This Error Happens

This error occurs when a T-SQL statement, application connection string, or Resource Governor classifier function references a resource pool name that does not currently exist in the SQL Server instance’s configuration. This commonly happens after a resource pool was dropped or renamed but dependent objects (like workload group mappings or classifier functions) were not updated to match. It can also occur during migration or restore operations where Resource Governor configurations from a source server do not fully apply to the destination server in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Existing Resource Pools

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to list all currently configured resource pools:

SELECT name, pool_id FROM sys.resource_governor_resource_pools;

• Confirm whether the resource pool name referenced in your application, script, or error message actually exists in this list.

Step 2: Recreate the Missing Resource Pool

• If the resource pool was accidentally dropped, recreate it using the CREATE RESOURCE POOL statement.
• Example syntax:

CREATE RESOURCE POOL [YourPoolName]
WITH (
    MIN_CPU_PERCENT = 0,
    MAX_CPU_PERCENT = 100,
    MIN_MEMORY_PERCENT = 0,
    MAX_MEMORY_PERCENT = 100
);
ALTER RESOURCE GOVERNOR RECONFIGURE;

• Ensure the pool name matches exactly (case-sensitive in some collations) with what is referenced in the workload group or classifier function.

Step 3: Update Workload Group and Classifier Function References

• Check the sys.resource_governor_workload_groups system view to confirm the workload group is pointing to a valid, existing pool_id.
• If using a custom classifier function (sys.fn_classifier), open the function definition and search for hardcoded resource pool names.
• Update any stale references to match the current resource pool name, then run ALTER RESOURCE GOVERNOR RECONFIGURE; to apply changes.

Step 4: Check SQL Server Error Logs and Configuration Scripts

• Navigate to the SQL Server error log directory, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for entries related to “Resource Governor” to identify exactly which script or startup process triggered Error 10911.
• If this occurred during a server migration, review any exported Resource Governor DDL scripts for naming mismatches before re-running them.

Alternative or Verification Steps

• To verify the fix, re-run the original failing query or application connection and confirm Error 10911 no longer appears in SSMS or the Windows Event Viewer.
• As an alternative, if the pool name is genuinely obsolete, update the classifier function or application connection string to point to the default resource pool instead of recreating a custom one.

Troubleshooting Guide: Error 10909 – Resource Pool Limit Exceeded in SQL Server 2025

Why This Error Happens

SQL Server enforces a hard-coded limit on the number of user-defined resource pools that can exist within the Resource Governor configuration, in addition to the two predefined pools (internal and default). This error occurs when an administrator or an automated deployment script attempts to create a new resource pool after the maximum threshold (typically 64 or 128 depending on edition) has already been reached. It is almost always caused by orphaned or unused resource pools accumulating over time without proper cleanup, often due to scripts that create pools dynamically for sessions or applications without a corresponding DROP routine.

Step-by-Step Resolution Guide

Step 1: Audit Existing Resource Pools

• Open SQL Server Management Studio (SSMS) and connect to the Database Engine instance.
• Run the following query to view the current count and names of all active pools:

SELECT name, pool_id, is_system 
FROM sys.resource_governor_resource_pools;

• Identify pools that are no longer tied to active applications or workloads, specifically looking for test pools or old application names that are no longer in use.

Step 2: Remove Unused or Redundant Pools

• Before dropping a pool, ensure no active workload groups are mapped to it by checking sys.resource_governor_workload_groups.
• Drop the specific unused pool using T-SQL syntax:

DROP RESOURCE POOL [PoolNameToRemove];

• Apply the configuration changes immediately to update the in-memory metadata:

ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 3: Consolidate Application Logic

• If the error stems from a multi-tenant application creating a new pool for every new tenant or session, review the application’s connection logic.
• Modify the application code to reuse a shared pool via classification functions rather than generating a unique pool for every connection string.
• Restart the SQL Server service via the Services app in Windows Server 2025 (services.msc) if the Resource Governor configuration appears locked or unresponsive after cleanup.

Step 4: Verify Governor Configuration File Integrity

• Check the SQL Server Error Log located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ for any corruption flags related to the Resource Governor XML configuration.
• If corruption is suspected, disable and re-enable the Resource Governor to force a clean state:

ALTER RESOURCE GOVERNOR DISABLE;
ALTER RESOURCE GOVERNOR RECONFIGURE;

Alternative or Verification Steps

• Verification: After dropping the unnecessary pools, attempt to run your original CREATE RESOURCE POOL command again to confirm the error 10909 no longer appears.
• Alternative: If you require more pools than the hard limit allows for a legitimate business case, consider consolidating low-priority workloads into a single generic pool with distinct workload groups rather than creating a dedicated pool for every individual job or user.

Why This Error Happens

Error 10907 is triggered by SQL Server’s internal metadata validation engine when it detects that two related attributes in the system catalog do not follow the expected logical hierarchy (for example, a column’s scale value exceeding its precision value, or a parameter’s max_length being smaller than its actual defined length). This typically indicates corruption in the system metadata tables (sys.columns, sys.parameters, or sys.types) caused by an interrupted schema change, a failed in-place upgrade from a previous SQL Server version, or a bug in a third-party migration script that manually manipulated catalog views. In rarer cases, this surfaces during CREATE or ALTER operations when the Database Engine Tuning Advisor or an ORM tool pushes an invalid combination of column attributes.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Object Throwing the Error

• Run the failing statement again with SQL Server Profiler or Extended Events attached to capture the exact substituted attribute names (the %.*ls placeholders) and their values.
• Check the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full expanded message with the actual column/table names.
• Note whether the error occurs on a specific table, stored procedure, or during a full DBCC CHECKDB pass.

Step 2: Run Consistency Checks Against the Database

• Open SQL Server Management Studio (SSMS) connected to the instance and execute:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for catalog corruption messages (Msg 2514, 8992, or related metadata errors) that often co-occur with 10907.
• If corruption is confirmed, note the affected objectid and columnid values reported for the next step.

Step 3: Repair or Rebuild the Affected Metadata

• For a non-system table, script out the table definition using SSMS (Generate Scripts wizard), drop the table, and recreate it manually, ensuring the PRECISION and SCALE values (or LENGTH attributes) are logically valid (e.g., scale ≤ precision).
• If the corruption resides in system catalogs, restore the database from the last known good backup:

  RESTORE DATABASE [YourDatabaseName] FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE;

• As a last resort for non-recoverable system catalog corruption, run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS only after taking a full backup, since this option can drop corrupted rows:

  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 4: Validate the SQL Server 2025 Installation

• Open Settings > Apps > Installed Apps on Windows Server 2025 / Windows 11 and confirm the SQL Server 2025 build matches the latest Cumulative Update (CU) from Microsoft, since early CU builds have known metadata validation regressions.
• If outdated, download and apply the latest CU from the Microsoft Download Center, then restart the SQL Server (MSSQLSERVER) service via services.msc.

Alternative or Verification Steps

• After applying the fix, re-run the original CREATE/ALTER statement or the failing query to confirm Error 10907 no longer appears in SSMS or the ERRORLOG.
• As a verification pass, execute DBCC CHECKDB one more time with NO_INFOMSGS to ensure zero allocation or consistency errors remain before returning the database to production traffic.

Why This Error Happens

Error 10908 occurs when SQL Server’s internal consistency check detects that a “minimum” threshold attribute has been configured with a value greater than its corresponding “maximum” threshold attribute, most commonly within Resource Governor settings (such as MIN_CPU_PERCENT exceeding MAX_CPU_PERCENT, or similar MIN/MAX pairs in resource pool and workload group definitions). This typically results from a manually scripted ALTER RESOURCE GOVERNOR or CREATE WORKLOAD GROUP statement where the min/max values were entered in the wrong order, or from a configuration migration script that did not account for value dependencies. SQL Server 2025 enforces strict validation during DDL execution, so the engine halts the operation rather than allowing an invalid resource boundary to be committed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Open SQL Server Management Studio (SSMS) and check the Messages tab for the full error text, which names the specific attributes (e.g., MIN_CPU_PERCENT and MAX_CPU_PERCENT).
• Locate the script or job that issued the CREATE/ALTER RESOURCE POOL or CREATE/ALTER WORKLOAD GROUP command, often found in deployment scripts or SQL Agent job steps.

Step 2: Query Current Resource Governor Configuration

• Run the following to inspect existing pool/group values before making changes:

SELECT name, min_cpu_percent, max_cpu_percent, min_memory_percent, max_memory_percent
FROM sys.resource_governor_resource_pools;

• Compare the output against the values in your failing script to pinpoint the mismatched pair.

Step 3: Correct the Attribute Values

• Edit the script so that every MIN value is numerically less than or equal to its corresponding MAX value. Example fix:

ALTER RESOURCE POOL [PoolName]
WITH (MIN_CPU_PERCENT = 10, MAX_CPU_PERCENT = 50);

• Save the corrected script and re-run it in a test environment first, especially if deploying via Windows Server 2025 automation pipelines.

Step 4: Apply and Reconfigure Resource Governor

• After altering the pool or workload group, execute:

ALTER RESOURCE GOVERNOR RECONFIGURE;

• This step is mandatory — changes to resource pools and workload groups do not take effect until Resource Governor is explicitly reconfigured.

Step 5: Re-run the Original Deployment Script

• Execute the full deployment or configuration script again in a staging environment to confirm Error 10908 no longer appears.
• Check SQL Server Error Logs (via SSMS or Get-WinEvent on Windows Server 2025) to confirm no related warnings were logged.

Alternative or Verification Steps

• Verify the fix by querying sys.resource_governor_resource_pools and sys.resource_governor_workload_groups to confirm all MIN values are now less than or equal to their MAX counterparts.
• If the error persists despite correct values, drop and recreate the resource pool/workload group entirely, as cached metadata in Resource Governor can occasionally retain a stale invalid state until a full DROP/CREATE cycle is performed.

Troubleshooting Guide: SQL Server Error 10906 – Invalid Resource Governor Classifier Function

Why This Error Happens

This error occurs when the Resource Governor attempts to assign a classifier function that is not marked as SCHEMABINDING. SQL Server enforces schema-binding on classifier functions to guarantee that the underlying objects referenced by the function cannot be altered or dropped while Resource Governor depends on them, ensuring consistent session classification. If the function was created or altered without the WITH SCHEMABINDING clause, or if it references non-deterministic or external objects that prevent schema-binding, SQL Server rejects it as a valid classifier.

Step-by-Step Resolution Guide

Step 1: Identify the Current Classifier Function

• Run the following query to confirm which function is currently assigned:

  SELECT classifier_function_id, OBJECT_NAME(classifier_function_id) AS FunctionName
  FROM sys.resource_governor_configuration;

• Note the schema and function name returned, as you will need to modify this exact object.

Step 2: Recreate the Function with SCHEMABINDING

• Script out the existing function definition using SSMS (Script Function as ALTER) or sp_helptext.
• Modify the CREATE FUNCTION statement to include the WITH SCHEMABINDING option immediately after the RETURNS data type declaration.
• Ensure the function only references deterministic system functions (e.g., SUSER_SNAME(), APP_NAME(), HOST_NAME()) and does not perform any data access to user tables, as schema-binding will fail if the function queries non-schema-bound objects.
• Example syntax:

  ALTER FUNCTION dbo.rg_classifier()
  RETURNS SYSNAME
  WITH SCHEMABINDING
  AS
  BEGIN
      RETURN (CASE WHEN SUSER_SNAME() = 'ReportingUser' THEN 'ReportingGroup' ELSE 'default' END);
  END;
  GO

Step 3: Re-register the Function with Resource Governor

• Explicitly bind the newly updated function to the Resource Governor configuration:

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);

• Apply the configuration changes to make them active immediately:

  ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Restart the Classification Context (If Needed)

• If the error persists in active sessions, restart the SQL Server service via services.msc on Windows Server 2025 to force a full re-evaluation of the Resource Governor state.
• Alternatively, run ALTER RESOURCE GOVERNOR DISABLE; followed by ALTER RESOURCE GOVERNOR RECONFIGURE; to clear the cached classifier before re-enabling it.

Alternative or Verification Steps

• Verify the fix by querying sys.objects to check the schema_bind status: SELECT OBJECT_NAME(object_id), is_schema_bound FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.rg_classifier');—the value should return 1.
• If schema-binding continues to fail due to dependency issues, temporarily revert to the default classifier function (ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);) to restore server connectivity while you debug the custom logic offline.

Troubleshooting Guide: Resource Governor Error 10904

Why This Error Happens

This error occurs when SQL Server’s internal ALTER RESOURCE GOVERNOR RECONFIGURE command attempts to apply pending configuration changes while one or more workload groups scheduled for removal, modification, or reassignment still have active sessions bound to them. SQL Server cannot safely drop a workload group or move its classifier mapping while live connections are consuming resources within that pool, as doing so would orphan running requests and corrupt resource tracking statistics. This is typically triggered after a DBA modifies sys.resource_governor_workload_groups or reassigns pools without first ensuring session drainage.

Step-by-Step Resolution Guide

Step 1: Identify Active Sessions in the Affected Workload Group

• Run the following query to find sessions still bound to the workload group pending changes:

SELECT s.session_id, s.login_name, s.host_name, g.name AS workload_group, s.status
FROM sys.dm_exec_sessions s
JOIN sys.resource_governor_workload_groups g
    ON s.group_id = g.group_id
WHERE g.name = 'YourWorkloadGroupName';

• Note the session_id values returned — these must be cleared before reconfiguration can succeed.

Step 2: Gracefully Disconnect or Move Active Sessions

• For non-critical sessions, terminate them manually:

KILL <session_id>;

• For production environments, notify application owners and wait for natural session completion instead of forcing a kill, especially for long-running ETL or reporting jobs.
• Alternatively, use ALTER WORKLOAD GROUP to reclassify new connections to a different pool temporarily, preventing new sessions from entering the group while old ones drain.

Step 3: Reapply the Resource Governor Configuration

• Once sys.dm_exec_sessions shows zero active connections for the target group, rerun your pending DDL:

ALTER RESOURCE GOVERNOR RECONFIGURE;

• If the command was part of a larger script (e.g., dropping a pool via DROP RESOURCE POOL), re-execute the full batch in order: disable, reconfigure, drop, reconfigure.

Step 4: Automate Session Draining for Future Changes

• Before any scheduled Resource Governor maintenance window, run a pre-check script using sp_who2 or the DMV query from Step 1 to confirm zero active sessions.
• Consider scheduling changes during low-traffic windows via SQL Server Agent to avoid live session conflicts.

Alternative or Verification Steps

• Verify the fix: After reconfiguration succeeds, query sys.resource_governor_configuration and confirm is_reconfiguration_pending returns 0.
• Alternative: If sessions cannot be killed due to business constraints, create a new workload group with updated settings, redirect new connections via the classifier function, and defer dropping the old group until it naturally empties.

Troubleshooting Guide: Error 10905 – Resource Governor Configuration Memory Failure

Why This Error Happens

Error 10905 occurs when SQL Server 2025 attempts to apply or reconfigure Resource Governor settings (via ALTER RESOURCE GOVERNOR RECONFIGURE) but the engine cannot allocate enough contiguous memory from the internal memory broker to commit the new configuration. This is typically caused by excessive memory pressure from existing workloads, an oversized buffer pool relative to physical RAM, or too many active resource pools/workload groups consuming the memory clerks needed for reconfiguration. In some cases, this is compounded by external memory pressure on Windows Server 2025/Windows 11 from other services competing for the same physical memory.

Step-by-Step Resolution Guide

Step 1: Connect Using the Dedicated Administrator Connection (DAC)

• Open SQL Server Management Studio (SSMS) and prefix your connection with ADMIN: (e.g., ADMIN:YourServerName).
• Alternatively, use sqlcmd: sqlcmd -S ADMIN:YourServerName -E
• This bypasses the regular connection pool and grants access even when standard memory is exhausted.

Step 2: Reduce Current Server Memory Load

• Run SELECT * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC; to identify which memory clerks are consuming the most space.
• Check sys.dm_exec_requests and sys.dm_exec_sessions for long-running or blocking queries and terminate non-critical sessions using KILL <session_id>.
• Temporarily lower max server memory (MB) in sp_configure to force SQL Server to release memory back to the OS, then run RECONFIGURE.

Step 3: Simplify Resource Governor Configuration Before Reapplying

• Reduce the number of active workload groups and resource pools temporarily (drop any non-essential custom pools).
• Run ALTER RESOURCE GOVERNOR DISABLE; to fully disable Resource Governor, clearing its internal memory structures.
• Re-enable with ALTER RESOURCE GOVERNOR RECONFIGURE; using a minimal configuration first, then incrementally add back workload groups.

Step 4: Check Windows-Level Memory Pressure

• Open Task Manager or Performance Monitor (perfmon.exe) on Windows Server 2025/Windows 11 and check Available MBytes and Memory\% Committed Bytes In Use.
• Close or reduce memory usage from competing services (e.g., SSRS, SSAS, or third-party agents) running on the same host.
• Verify the “Lock Pages in Memory” policy is correctly assigned to the SQL Server service account via gpedit.msc → Local Computer Policy → Security Settings → Local Policies → User Rights Assignment.

Step 5: Restart the SQL Server Service (If Load Reduction Fails)

• If memory remains fragmented or locked by stale clerks, restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER'
• After restart, immediately reapply the Resource Governor configuration script before other workloads ramp up.

Alternative or Verification Steps

• Verify the fix by running ALTER RESOURCE GOVERNOR RECONFIGURE; again through a normal (non-DAC) connection — if it completes without Error 10905, the memory pressure has been resolved.
• As an alternative, query sys.dm_resource_governor_resource_pools and sys.dm_os_sys_memory to confirm available memory is above the threshold needed before attempting another configuration change, especially during peak workload hours.

Troubleshooting Guide: Error 10902 — “User-Defined Function Does Not Exist in Master Database”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to validate or execute a system-level operation that references a user-defined function (UDF) expected to reside in the master database, but the function is missing, was never deployed, or was dropped during an upgrade/migration. It commonly surfaces after an in-place upgrade to SQL Server 2025, a restore of system databases from an older version, or a corrupted/incomplete installation where setup scripts failed to create required helper functions. Insufficient permissions on the master database can also prevent the function from being created or recognized during startup routines.

Step-by-Step Resolution Guide

Step 1: Verify the Missing Function in the Master Database

• Open SQL Server Management Studio (SSMS) and connect to the instance using an account with sysadmin privileges.
• Run the following query to check if the function exists:

  USE master;
  SELECT * FROM sys.objects WHERE type = 'FN' AND name = '%s';

• If no rows are returned, the function is confirmed missing and must be recreated.

Step 2: Re-run Setup Repair to Restore System Objects

• Launch SQL Server 2025 Setup from C:\SQLServer2025Media\setup.exe.
• Select Maintenance > Repair and choose the affected instance.
• This triggers a reinstallation of system scripts, including UDFs in master, without affecting user databases.
• Restart the SQL Server service afterward via services.msc or:

  Restart-Service -Name 'MSSQLSERVER'

Step 3: Manually Recreate the Function (If Repair Fails)

• Locate the original system script (commonly named instmsdb.sql or a custom deployment script) in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Install\.
• Execute the script manually in master context using SSMS or sqlcmd:

  sqlcmd -S localhost -i "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Install\instmsdb.sql"

• Ensure the executing login has CREATE FUNCTION permission on master.

Step 4: Check and Correct Permissions on Master Database

• Verify the service account (e.g., NT SERVICE\MSSQLSERVER) has appropriate rights:

  USE master;
  GRANT CREATE FUNCTION TO [NT SERVICE\MSSQLSERVER];

• Confirm no Group Policy or Windows Server 2025 security baseline is restricting local system account permissions on SQL Server directories.

Alternative or Verification Steps

• Re-run the original query from Step 1 to confirm the function now appears in sys.objects; the error should no longer trigger during the failing operation.
• If the issue persists, restore master from a known-good backup taken before the upgrade, then reapply any post-upgrade patches or cumulative updates.

Troubleshooting Guide: Error 10903 — Classifier Function Schema Not Found

Why This Error Happens

Error 10903 occurs when the Resource Governor’s classifier user-defined function (UDF) is associated with a schema name that no longer exists or was never properly created in the target database. This typically happens after a database restore, schema drop operation, or migration where the schema containing the classifier function was not recreated before Resource Governor attempted to reference it. In SQL Server 2025 running on Windows Server 2025 or Windows 11, this can also surface if the classifier function was created in a user database instead of the required master database context, or if case-sensitivity/collation mismatches cause the schema lookup to fail.

Step-by-Step Resolution Guide

Step 1: Verify the Classifier Function’s Current Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to check the current classifier function setting:

  SELECT * FROM sys.resource_governor_configuration;

• Note the classifier_function_id returned; if it is NULL or references an invalid object, this confirms the broken link.

Step 2: Confirm the Schema Exists in the Master Database

• Classifier functions must reside in the master database. Run:

  USE master;
  SELECT name FROM sys.schemas;

• Check whether the schema referenced in the error message (e.g., dbo or a custom schema) is present in the output list.
• If the schema is missing, recreate it using:

  CREATE SCHEMA [YourSchemaName];

Step 3: Recreate and Reassign the Classifier Function

• If the schema was missing or the function itself is orphaned, recreate the classifier UDF in master:

  USE master;
  GO
  CREATE FUNCTION dbo.fn_RGClassifier()
  RETURNS sysname
  WITH SCHEMABINDING
  AS
  BEGIN
      RETURN N'default';
  END;
  GO

• Reassign it to Resource Governor:

  ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_RGClassifier);
  ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Restart the SQL Server Service (If Required)

• On Windows Server 2025 / Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to ensure Resource Governor reloads the corrected configuration.

Step 5: Check SQL Server Error Logs for Residual Errors

• Navigate to the default log path:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Open the most recent log file and confirm no further references to Error 10903 appear after the restart.

Alternative or Verification Steps

• Run SELECT * FROM sys.resource_governor_configuration; again and confirm classifier_function_id now points to a valid object ID (not NULL).
• If the error persists, disable the classifier function entirely as a temporary workaround by running ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL); ALTER RESOURCE GOVERNOR RECONFIGURE;, then rebuild the schema and function from a clean script before reassigning it.

Troubleshooting Guide: Error 10901 – Resource Governor Permission Denied (SQL Server 2025)

Why This Error Happens

Error 10901 occurs when the login attempting to alter the Resource Governor configuration lacks the ALTER ANY RESOURCE GOVERNOR server-level permission or does not hold the sysadmin fixed server role. This is commonly seen when a standard login, application service account, or junior DBA account is used to run ALTER RESOURCE GOVERNOR statements without the elevated privileges required by SQL Server’s security model. It can also occur if the permission was explicitly revoked or if the login is attempting the operation through a context (such as a contained database user) that does not carry server-level scope.

Step-by-Step Resolution Guide

Step 1: Verify the Current Login and Its Permissions

• Open SQL Server Management Studio (SSMS) and connect using an account known to have sysadmin privileges.
• Run the following query to check the current user’s permissions:

SELECT * FROM fn_my_permissions(NULL, 'SERVER') 
WHERE permission_name LIKE '%RESOURCE GOVERNOR%';

• Confirm whether ALTER ANY RESOURCE GOVERNOR appears in the result set.

Step 2: Grant the Required Server-Level Permission

• Using a sysadmin account, execute the following T-SQL to grant the specific permission to the affected login:

USE master;
GRANT ALTER ANY RESOURCE GOVERNOR TO [DOMAIN\UserOrServiceAccount];

• Alternatively, if the account should have full administrative rights, add it to the sysadmin role instead:

ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\UserOrServiceAccount];

• Ensure you are connected to the correct SQL Server instance, especially in multi-instance Windows Server 2025 deployments.

Step 3: Re-Run the Resource Governor Configuration Command

• Log out and reconnect in SSMS (or restart the session) to refresh the token/permission cache.
• Attempt the original command again, for example:

ALTER RESOURCE GOVERNOR RECONFIGURE;

• If using a Windows-authenticated group account, confirm that the user’s Active Directory group membership has synced (may require a gpupdate /force on Windows Server 2025 domain-joined machines).

Step 4: Check for Contained Database or Policy-Based Restrictions

• If using contained databases, confirm the operation is being run against the master database context, not a contained user database, since Resource Governor is a server-level feature only.
• Review any Policy-Based Management (PBM) policies or Azure Arc-enabled SQL Server governance policies on Windows Server 2025 that might override or block permission grants.

Alternative or Verification Steps

• Verify the fix by running SELECT IS_SRVROLEMEMBER('sysadmin'); — a return value of 1 confirms elevated access, and re-running ALTER RESOURCE GOVERNOR RECONFIGURE; should now complete without Error 10901.
• If permissions still fail to apply, restart the SQL Server service via SQL Server Configuration Manager to clear any cached security tokens, then retest the configuration change.

Troubleshooting Guide: SQL Server Error 10802 – SPN Registration Failure

Why This Error Happens

This error occurs when the SQL Server service account lacks the necessary permissions to register a Service Principal Name (SPN) in Active Directory, which is required for Kerberos authentication to function correctly. It can also arise when SQLOS fails to enqueue the internal background task responsible for automatic SPN registration due to resource constraints, Active Directory connectivity issues, or domain controller unavailability at service startup. Additionally, running SQL Server under a built-in account (like Network Service) without proper delegation rights, or using a domain account without “Read/Write servicePrincipalName” permissions on the computer/service object, commonly triggers this failure.

Step-by-Step Resolution Guide

Step 1: Verify the SQL Server Service Account Permissions

• Open Active Directory Users and Computers (ADUC) on a domain controller.
• Locate the SQL Server service account (or the computer object if using a virtual account/MSA).
• Ensure the account has “Validated write to service principal name” permission delegated to it.
• If using a Group Managed Service Account (gMSA), confirm it was created with Set-ADServiceAccount and has the correct PrincipalsAllowedToRetrieveManagedPassword.

Step 2: Manually Register the SPN Using SETSPN

• Open an elevated Command Prompt on a domain-joined machine with domain admin rights.
• Run the following command to check existing SPNs:

  setspn -L <SQLServiceAccount>

• Manually register the missing SPN using:

  setspn -S MSSQLSvc/<FQDN>:<Port> <Domain>\<SQLServiceAccount>

• Example: setspn -S MSSQLSvc/sql01.contoso.com:1433 contoso\svc_sql

Step 3: Check Domain Controller Connectivity and DNS Resolution

• From the SQL Server host, run nltest /sc_query:<DomainName> to confirm a secure channel to the domain controller.
• Verify DNS resolution using nslookup <DomainControllerFQDN> to rule out name resolution failures.
• Check Windows Event Viewer under System and Application logs for related Kerberos (Event ID 4) or Netlogon errors occurring at the same timestamp as Error 10802.

Step 4: Restart the SQL Server Service to Re-trigger SPN Registration

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Right-click the SQL Server (MSSQLSERVER) or named instance service and select Restart.
• Monitor the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that the SPN was registered successfully on startup.

Step 5: Grant Explicit SPN Delegation via dsacls (If Step 1 Fails)

• Run the following command from an elevated prompt with domain admin privileges:

  dsacls "CN=<SQLServiceAccount>,OU=ServiceAccounts,DC=contoso,DC=com" /G "contoso\svc_sql:WP;servicePrincipalName"

• This explicitly grants write permission to the servicePrincipalName attribute, bypassing default inheritance issues.

Alternative or Verification Steps

• To verify the fix, run klist on a client machine after connecting to SQL Server, then confirm a Kerberos ticket (not NTLM) was issued for the MSSQLSvc service class.
• If manual SPN registration continues to fail, switch the SQL Server service account to a Group Managed Service Account (gMSA), which automates SPN management and eliminates manual permission errors.

Troubleshooting Guide: SQL Server Error 10900 – Resource Governor Configuration Failure

Why This Error Happens

Error 10900 occurs when SQL Server 2025 fails to configure the Resource Governor during the startup sequence, which is typically caused by corruption or inconsistency within the system metadata tables in the master database. This can result from improperly terminated transactions during a previous resource pool or workload group modification, incomplete upgrade processes, or disk-level corruption affecting system catalog views. Since Resource Governor configuration is loaded from internal system tables at instance startup, any mismatch in catalog data (such as orphaned resource pool definitions) will cause SQL Server to halt the configuration process and log this error.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Open the most recent log file and search for the string “Resource Governor” or “Error: 10900” to identify the specific sub-error (e.g., missing pool reference or invalid XML configuration blob).
• Note the timestamp of the failure to cross-reference with Windows Event Viewer under Windows Logs > Application.

Step 2: Run DBCC CHECKCATALOG to Verify Master Database Integrity

• Open SQL Server Management Studio (SSMS) and connect using the Dedicated Admin Connection (DAC) if the instance fails to start normally by prefixing the server name with ADMIN: (e.g., ADMIN:YourServerName).
• Execute the following query to check for catalog consistency: DBCC CHECKCATALOG('master') WITH NO_INFOMSGS;
• If corruption is reported, note the specific object IDs or system table names flagged in the output for targeted repair.

Step 3: Start SQL Server in Minimal Configuration Mode

• Stop the SQL Server service via Services.msc or net stop MSSQLSERVER.
• Restart the instance using the -f startup parameter (Minimal Configuration) via Command Prompt: NET START MSSQLSERVER /f
• This bypasses the Resource Governor configuration load, allowing you to connect and manually reset the configuration using ALTER RESOURCE GOVERNOR RESET STATISTICS; followed by ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Restore Resource Governor to Default Settings

• If reconfiguration fails, disable Resource Governor entirely to allow the service to start normally: ALTER RESOURCE GOVERNOR DISABLE; GO ALTER RESOURCE GOVERNOR RECONFIGURE;
• Drop any custom workload groups or resource pools that may be referencing corrupted metadata using DROP RESOURCE POOL [PoolName];
• Remove the -f startup parameter from SQL Server Configuration Manager under the Startup Parameters tab once the server starts successfully.

Alternative or Verification Steps

• Verify the fix by restarting the SQL Server service normally (without the -f flag) and confirming Error 10900 no longer appears in the ERRORLOG upon boot.
• If corruption persists in system tables, restore the master database from the last known good backup using RESTORE DATABASE master FROM DISK = 'PathToBackup.bak' WITH REPLACE; while the instance is running in single-user mode (-m flag).

Troubleshooting Guide: SQL Server Error 10800 — WSFC Listener Failed to Start

Why This Error Happens

Error 10800 typically occurs when the SQL Server Availability Group Listener cannot bind to the Windows Server Failover Clustering (WSFC) resource due to a port conflict, a missing or incorrectly configured IP address resource, or insufficient permissions on the SQL Server service account. This can also happen if the underlying cluster network name (CNO) has not been granted the necessary “Create Computer Objects” permission in Active Directory, or if DNS registration for the listener’s virtual network name has failed. Corrupted cluster resource DLLs (such as hadrres.dll) in Windows Server 2025 can also prevent the listener resource from initializing properly.

Step-by-Step Resolution Guide

Step 1: Verify Network and Port Availability

• Open PowerShell as Administrator and run Get-NetTCPConnection -LocalPort 1433 to check if the listener port is already in use.
• Confirm no other listener or SQL instance is bound to the same IP/port combination.
• If a conflict exists, either stop the conflicting service or assign a unique port to the Availability Group Listener.

Step 2: Validate WSFC Resource and IP Configuration

• Open Failover Cluster Manager and navigate to Roles > [Your AG Name] > Resources.
• Right-click the IP Address resource and select Properties to confirm the static IP matches your subnet and is not already assigned elsewhere.
• Run Get-ClusterResource | Where-Object {$_.ResourceType -eq "IP Address"} | Get-ClusterParameter in PowerShell to verify correct subnet mask and network values.
• Bring the resource online manually using Start-ClusterResource -Name "<ListenerName>".

Step 3: Check Permissions on the Cluster Name Object (CNO)

• Open Active Directory Users and Computers and locate the Cluster Name Object (CNO).
• Ensure the CNO has “Create Computer Objects” and “Read/Write” permissions delegated in the Organizational Unit (OU) where the listener’s Virtual Computer Object (VCO) will be created.
• If missing, go to Delegate Control on the OU, select the CNO, and grant the required permissions manually.

Step 4: Repair or Re-register Cluster Resource DLLs

• Open an elevated Command Prompt and navigate to C:\Windows\System32.
• Run regsvr32 hadrres.dll to re-register the SQL Server Always On Availability Group resource DLL.
• Restart the Cluster Service via Restart-Service -Name ClusSvc -Force to reload the resource definitions.

Step 5: Restart the Availability Group Listener Resource

• In Failover Cluster Manager, take the listener resource offline, then bring it back online.
• Alternatively, use PowerShell: Stop-ClusterResource -Name "<ListenerName>" followed by Start-ClusterResource -Name "<ListenerName>".

Alternative or Verification Steps

• Run nslookup <ListenerName> to confirm the virtual network name resolves correctly to the assigned IP address in DNS.
• If the listener still fails, drop and recreate the Availability Group Listener using T-SQL (ALTER AVAILABILITY GROUP ... ADD LISTENER) after confirming cluster health with Test-Cluster in PowerShell.

Why This Error Happens

Error 10801 occurs when SQL Server’s resource DLL attempts to take the Availability Group Listener’s network name and IP address resource offline within Windows Server Failover Clustering (WSFC), but the cluster service denies or fails the request. This is typically caused by the cluster service account lacking sufficient permissions on the Computer Object in Active Directory, a stale or orphaned cluster resource state, or network connectivity issues between cluster nodes preventing the resource DLL from communicating with the Cluster Service (clussvc.exe). In some cases, this also surfaces when the listener’s VCO (Virtual Computer Object) has been manually modified or deleted in AD, leaving the cluster resource in an inconsistent state.

Step-by-Step Resolution Guide

Step 1: Verify Cluster Service Account Permissions

• Open Active Directory Users and Computers (ADUC) on a domain controller.
• Locate the Virtual Computer Object (VCO) tied to the Listener name.
• Confirm the WSFC “Cluster Name Object” (CNO) has Full Control permissions on the listener’s VCO under the Security tab.
• If permissions are missing, add the CNO manually and grant Full Control, then propagate permissions down.

Step 2: Check Cluster Resource Health via PowerShell

• Open an elevated PowerShell session on the active node.
• Run the following to inspect the listener resource state:

Get-ClusterResource | Where-Object {$_.ResourceType -eq "Network Name"} | Get-ClusterParameter

• Look for a RequireKerberos mismatch or Status = Failed on the resource.
• If the resource shows as “Failed” or “Online Pending” indefinitely, take it offline manually:

Stop-ClusterResource -Name "<ListenerName>"

Step 3: Restart Cluster and Network Name Resource Dependencies

• On the node currently owning the Availability Group role, restart the Cluster Service:

Restart-Service -Name ClusSvc -Force

• After the service restarts, bring the Network Name resource back online:

Start-ClusterResource -Name "<ListenerName>"

• Confirm there are no DNS resolution conflicts for the Listener name by running nslookup <ListenerName> from a separate client machine.

Step 4: Rebuild the Listener Resource (If Still Failing)

• If the resource remains stuck, drop and recreate the Availability Group Listener using SSMS or T-SQL:

ALTER AVAILABILITY GROUP [AGName]
REMOVE LISTENER 'ListenerName';

• Recreate it with the correct IP and port binding:

ALTER AVAILABILITY GROUP [AGName]
ADD LISTENER 'ListenerName' (
    WITH IP (('10.0.0.50','255.255.255.0')),
    PORT = 1433
);

Alternative or Verification Steps

• Verify success by checking the Failover Cluster Manager console — the Network Name resource for the listener should show a status of Online without flapping between states.
• If the issue persists after rebuilding the listener, check the System Event Log for related FailoverClustering Event IDs (1205, 1069) which often indicate the underlying network or AD communication fault causing SQL Server Error 10801.

Troubleshooting Guide: Error 10799 – Invalid Data Compression Setting for Columnstore Index

Why This Error Happens

Error 10799 occurs when a CREATE INDEX, ALTER INDEX, or CREATE TABLE statement specifies a DATA_COMPRESSION value (such as ROW, PAGE, or NONE) that is incompatible with a columnstore index definition. SQL Server 2025 strictly enforces that columnstore indexes only accept COLUMNSTORE or COLUMNSTORE_ARCHIVE as valid compression types, since the underlying storage engine for columnstore data uses a different compression architecture than traditional rowstore indexes. This typically happens due to copy-pasted T-SQL scripts from rowstore index templates, legacy migration scripts, or ORM-generated DDL that doesn’t distinguish between index types.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Review the error stack in SQL Server Management Studio (SSMS) or the application log to locate the exact CREATE INDEX or ALTER INDEX statement that triggered Error 10799.
• Check if the script was auto-generated by a migration tool, deployment pipeline (e.g., DACPAC/SSDT), or copied from a non-columnstore index template.
• Run the following to inspect current index metadata:

  SELECT name, type_desc, data_compression_desc 
  FROM sys.indexes i 
  JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
  WHERE i.type IN (5,6); -- 5 = Clustered Columnstore, 6 = Nonclustered Columnstore

Step 2: Correct the DATA_COMPRESSION Clause

• Edit the script to replace invalid values (ROW, PAGE, NONE) with a supported columnstore compression type.
• For standard columnstore compression:

  CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData
  ON dbo.SalesData
  WITH (DATA_COMPRESSION = COLUMNSTORE);

• For archival-level compression (higher compression ratio, more CPU overhead):

  ALTER INDEX CCI_SalesData ON dbo.SalesData
  REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Step 3: Validate Deployment Scripts and ORM Templates

• Search your SSDT/DACPAC project files or Entity Framework migration scripts (typically found in C:\Projects\<YourSolution>\Migrations\) for hardcoded DATA_COMPRESSION values tied to columnstore objects.
• Update any schema comparison tool settings (in SSMS: Tools > Options > SQL Server Object Explorer > Schema Compare) to prevent auto-generating incompatible compression clauses on future deployments.

Step 4: Rebuild the Index with Correct Syntax

• Drop and recreate the index if an in-place alteration isn’t feasible:

  DROP INDEX CCI_SalesData ON dbo.SalesData;
  GO
  CREATE CLUSTERED COLUMNSTORE INDEX CCI_SalesData
  ON dbo.SalesData
  WITH (DATA_COMPRESSION = COLUMNSTORE);

• Ensure no open transactions are locking the table (sys.dm_tran_locks) before executing the rebuild on production environments.

Alternative or Verification Steps

• Verify the fix by re-running the metadata query from Step 1 — the data_compression_desc column should now display COLUMNSTORE or COLUMNSTORE_ARCHIVE with no errors on execution.
• If the error persists in automated deployment pipelines, check for cached DACPAC artifacts in C:\TFSBuilds\ or your CI/CD agent’s temp directory and force a clean rebuild to ensure stale scripts aren’t being redeployed.

Troubleshooting Guide: Error 10797 – Only One MEMORYOPTIMIZEDDATA Filegroup Allowed Per Database

Why This Error Happens

SQL Server 2025 restricts each database to a single MEMORY_OPTIMIZED_DATA filegroup because the In-Memory OLTP engine manages memory-optimized tables through one logical container per database. This error is triggered when a CREATE DATABASE or ALTER DATABASE statement attempts to add a second filegroup of type MEMORY_OPTIMIZED_DATA, often due to a scripting error, a migration script run twice, or a developer misunderstanding that multiple files can be added to the existing filegroup instead of creating a new one. It can also occur when automated deployment tools (like DACPAC deployments or Flyway/Liquibase migrations) re-execute a filegroup creation script against a database that was only partially provisioned.

Step-by-Step Resolution Guide

Step 1: Identify the Existing Memory-Optimized Filegroup

• Open SQL Server Management Studio (SSMS) 2025 or Azure Data Studio and connect to the target instance.
• Run the following query to check existing filegroups:

  SELECT name, type_desc 
  FROM sys.filegroups 
  WHERE type = 'FX';

• Confirm whether a MEMORY_OPTIMIZED_DATA filegroup already exists for the database in question.

Step 2: Correct the Deployment Script or T-SQL Statement

• Locate the script (often in C:\Scripts\DBDeploy\ or your CI/CD pipeline artifact folder) that issues the ADD FILEGROUP ... CONTAINS MEMORY_OPTIMIZED_DATA command.
• Remove or comment out the duplicate CREATE DATABASE / ALTER DATABASE ... ADD FILEGROUP statement.
• If you need additional storage, add a file to the existing filegroup instead of creating a new one:

  ALTER DATABASE [YourDB] 
  ADD FILE (
      NAME = 'MemOpt_Data2', 
      FILENAME = 'C:\SQLData\MemOpt_Data2.ndf'
  ) TO FILEGROUP [MemoryOptimizedFG];

Step 3: Clean Up Partially Created Objects (If Applicable)

• If a previous failed deployment left an orphaned filegroup, verify it using sys.filegroups again.
• If the filegroup is empty and unused, drop it before re-running the deployment:

  ALTER DATABASE [YourDB] REMOVE FILEGROUP [UnwantedFG];

• Re-run the corrected deployment script from Step 2 against the Windows Server 2025 SQL instance.

Step 4: Validate CI/CD and Migration Tooling Idempotency

• Check your deployment pipeline (Azure DevOps, GitHub Actions, etc.) configuration files, typically found in .pipelines\sql-deploy.yml.
• Add idempotency checks (IF NOT EXISTS) around filegroup creation logic to prevent re-execution errors in future deployments.

Alternative or Verification Steps

• Re-run SELECT name, type_desc FROM sys.filegroups WHERE type = 'FX'; and confirm only one row is returned — this verifies the fix was applied successfully.
• If the error persists despite no visible duplicate filegroup, restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) on Windows Server 2025 to clear any cached metadata, then retry the deployment.

Troubleshooting Guide: SQL Server Error 10798 – Invalid Data Compression Setting

Why This Error Happens

This error occurs because the COLUMNSTORE or COLUMNSTORE_ARCHIVE data compression setting is being applied to a table, index, or partition type that does not support it, such as a standard rowstore clustered or nonclustered index. SQL Server 2025 strictly validates compression types against the underlying storage structure, and columnstore-specific compression can only be assigned to actual columnstore indexes. This typically happens when a script is copied from a columnstore table definition and reused on a traditional heap or B-tree index without updating the WITH (DATA_COMPRESSION = ...) clause.

Step-by-Step Resolution Guide

Step 1: Identify the Object and Index Type

• Run sp_help 'YourTableName' or query sys.indexes to check the type_desc column for the object in question.
• Confirm whether the index is CLUSTERED, NONCLUSTERED, CLUSTERED COLUMNSTORE, or NONCLUSTERED COLUMNSTORE.
• Look specifically for any CREATE INDEX or ALTER TABLE statement that references COLUMNSTORE or COLUMNSTORE_ARCHIVE.

Step 2: Correct the Compression Syntax in T-SQL

• If the object is a standard rowstore index, update the script to use a valid setting: NONE, ROW, or PAGE.
• Example fix for a rowstore table:

  ALTER TABLE dbo.YourTable REBUILD WITH (DATA_COMPRESSION = PAGE);

• If compression is not required, explicitly set it to NONE as suggested by the error message:

  ALTER TABLE dbo.YourTable REBUILD WITH (DATA_COMPRESSION = NONE);

Step 3: Validate Index Architecture via SSMS GUI

• Open SQL Server Management Studio (SSMS) on your Windows Server 2025/Windows 11 machine.
• Navigate to Object Explorer > Databases > [YourDB] > Tables > [YourTable] > Storage > Compression.
• Ensure the “Compression Type” dropdown matches the actual index architecture (do not select Columnstore options for Rowstore tables).
• Click Script to generate the corrected T-SQL and verify the syntax before execution.

Step 4: Rebuild the Index with Correct Partition Alignment

• If the table is partitioned, ensure you specify compression settings for every partition individually, as a global setting can sometimes conflict with partition-specific columnstore remnants.
• Use the following syntax to apply settings per partition number:

  ALTER INDEX ALL ON dbo.YourTable
  REBUILD PARTITION = ALL
  WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1));

Alternative or Verification Steps

• Verification: Execute SELECT partition_number, data_compression_desc FROM sys.partitions WHERE object_id = OBJECT_ID('dbo.YourTable'); to confirm the compression type now reads NONE, ROW, or PAGE successfully.
• Alternative: If you specifically intended to use columnstore compression, drop the existing rowstore index and create a new NONCLUSTERED COLUMNSTORE INDEX instead of attempting to alter the existing B-tree structure.

Troubleshooting Guide: SQL Server Error 10795 – “The variable ‘%.*ls’ was declared as not null”

Why This Error Happens

This error occurs when a T-SQL statement or stored procedure attempts to assign a NULL value to a variable that was explicitly declared with a NOT NULL constraint, which is non-standard behavior that SQL Server’s engine rejects during execution. It is frequently triggered by legacy code, third-party applications, or migrated scripts that use an unsupported or deprecated variable declaration syntax expecting strict null-handling enforcement at the variable level. In some cases, this surfaces after an in-place upgrade to SQL Server 2025 on Windows Server 2025, where the Query Optimizer or parser now flags syntax that was previously tolerated in older compatibility levels.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Stored Procedure

• Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) or Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > SQLSERVER for the full error text including the variable name.
• Run SELECT * FROM sys.dm_exec_query_stats cross-applied with sys.dm_exec_sql_text to correlate the error timestamp with the exact query that failed.
• Note the specific variable name referenced in the %.*ls placeholder, as this will point directly to the DECLARE statement in question.

Step 2: Correct the Variable Declaration Syntax

• Open the script in SSMS and locate the DECLARE @VariableName <datatype> NOT NULL statement.
• Remove the invalid NOT NULL constraint from the variable declaration, since T-SQL variables do not support this modifier outside of table-type definitions:

  -- Incorrect
  DECLARE @OrderID INT NOT NULL;

  -- Corrected
  DECLARE @OrderID INT;
  SET @OrderID = ISNULL(@InputValue, 0);

• If the intent was to enforce non-null logic, use ISNULL() or COALESCE() within the procedure body instead of a declaration-level constraint.

Step 3: Review Database Compatibility Level

• Open SSMS, right-click the affected database, select Properties > Options, and check the Compatibility Level field.
• If recently upgraded, temporarily test with an older compatibility level (e.g., ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 150;) to confirm whether the parser behavior changed between versions.
• Update any application or ORM-generated scripts (e.g., Entity Framework, legacy ADO.NET) that may be dynamically generating the deprecated NOT NULL variable syntax.

Step 4: Apply SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog and confirm the instance is running the latest Cumulative Update (CU) for SQL Server 2025.
• Check current build version with SELECT @@VERSION; and compare against Microsoft’s official build reference page.
• Install any pending CU via the Windows Server 2025 Server Manager or standalone installer, then restart the SQL Server service (net stop MSSQLSERVER followed by net start MSSQLSERVER).

Alternative or Verification Steps

• Re-run the corrected stored procedure or script in SSMS and confirm no Error 10795 appears in Messages; cross-check the SQL Server Error Log to ensure the event is not repeating.
• If the error persists despite syntax correction, use sp_helptext 'ProcedureName' to scan for any nested or dynamically executed SQL (via EXEC or sp_executesql) that may contain the same invalid declaration pattern elsewhere in the codebase.

Troubleshooting Guide: Error 10796 – SCHEMABINDING Option Supported Only for Natively Compiled Modules

Why This Error Happens

This error occurs when the WITH SCHEMABINDING clause is applied to a standard (interpreted) Transact-SQL module—such as a regular view, function, or stored procedure—rather than a natively compiled module designed for memory-optimized tables. SQL Server 2025 restricts SCHEMABINDING on natively compiled syntax exclusively to modules created with the WITH NATIVE_COMPILATION option. This typically happens due to a copy-paste error from memory-optimized table templates or a misunderstanding of which object types support this specific binding syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object Definition

• Open SQL Server Management Studio (SSMS) and locate the exact CREATE or ALTER statement referenced in the error log.
• Check if the object is a standard T-SQL object or intended to be a memory-optimized object.
• Run sp_helptext 'YourObjectName' to review the current schema definition and locate the SCHEMABINDING keyword placement.

Step 2: Correct the Syntax Based on Intent

• If the object should be a standard T-SQL module: Remove the NATIVE_COMPILATION reference if present, and ensure SCHEMABINDING is used correctly as a standalone option (e.g., WITH SCHEMABINDING is valid for standard views/functions, but invalid if paired with native compilation syntax).
• If the object should be natively compiled: Add the required NATIVE_COMPILATION and SCHEMABINDING together, and ensure the underlying table is a memory-optimized table (MEMORY_OPTIMIZED = ON).
• Example fix for a natively compiled procedure:

  CREATE PROCEDURE dbo.MyNativeProc
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
      SELECT 1;
  END

Step 3: Validate Underlying Table Compatibility

• Navigate to Object Explorer > Databases > Tables in SSMS and check the MEMORY_OPTIMIZED property of the base table(s) referenced in the module.
• If the table is not memory-optimized, you cannot use native compilation syntax; revert the module to a standard T-SQL definition instead.

Step 4: Redeploy the Corrected Script

• Drop the faulty object using DROP PROCEDURE/VIEW/FUNCTION [Name].
• Re-run the corrected CREATE script via SSMS or sqlcmd.
• Check the Messages tab in SSMS for a “Command(s) completed successfully” confirmation.

Alternative or Verification Steps

• Verify the fix by querying sys.sql_modules and checking the uses_native_compilation column to confirm it matches your intended object type (1 for native, 0 for standard).
• If errors persist, restart the SQL Server (MSSQLSERVER) service via the Services app in Windows Server 2025 to clear any cached execution plans related to the old schema.

Troubleshooting Guide: Error 10794 — “%SMSG ‘%ls’ is not supported with %SMSG”

Why This Error Happens

This error occurs when a Transact-SQL statement, clause, or feature is executed against a database object type that does not support it — commonly seen when applying memory-optimized table syntax, columnstore operations, or specific index hints to incompatible table types (such as temporal tables, external tables, or system-versioned tables). It is a compatibility-layer restriction enforced by the SQL Server Engine rather than a corruption or permissions issue. This typically surfaces after an upgrade to SQL Server 2025 where deprecated syntax or legacy compatibility-level scripts conflict with newer engine restrictions.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Statement and Object Triggering the Error

• Capture the full error message text, replacing %S_MSG and %ls placeholders with the actual values shown in SSMS or the SQL Server Error Log.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full context of the failed query.
• Run SELECT object_id, name, type_desc FROM sys.objects WHERE name = 'YourObjectName'; to confirm the object type (e.g., memory-optimized, temporal, external).

Step 2: Verify Feature Compatibility with the Target Object Type

• Query the current database compatibility level: SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• Cross-reference the unsupported clause (e.g., WITH (MEMORY_OPTIMIZED = ON), ONLINE = ON, or columnstore hints) against Microsoft’s SQL Server 2025 Books Online compatibility matrix for that object type.
• Remove or rewrite the unsupported clause from the script — for example, drop ONLINE = ON when rebuilding indexes on system-versioned temporal tables, which still have partial restrictions.

Step 3: Update or Rewrite the Offending Script

• Modify the T-SQL script to use a supported alternative syntax (e.g., switch from an inline index hint to a separate CREATE INDEX statement executed after table creation).
• If using ORM-generated queries (Entity Framework, Dapper, etc.), check the ORM version for SQL Server 2025 compatibility patches and update the NuGet package.
• Save the corrected script and re-run it in a non-production environment first.

Step 4: Apply the Latest Cumulative Update

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11 and confirm the current build number via SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as this error message’s underlying feature gaps are frequently patched.
• Restart the SQL Server (MSSQLSERVER) service from services.msc after the update completes.

Alternative or Verification Steps

• Re-execute the original query or application workload against a test database to confirm Error 10794 no longer appears in SSMS or the Error Log.
• If the error persists, isolate the feature in question by testing it against a standard (non-specialized) table — if it succeeds there, the issue is confirmed as an object-type incompatibility requiring a schema redesign rather than a server-side fix.

Troubleshooting Guide: SQL Server Error 10793 — “Both an Index and a PRIMARY KEY Constraint Have Been Defined Inline”

Why This Error Happens

This error occurs when a CREATE TABLE statement attempts to define an inline index (using the INDEX keyword within a column definition) on the same column that is also marked as a PRIMARY KEY constraint inline. SQL Server 2025 parses inline index syntax and constraint syntax separately, and when both target the same column simultaneously, the DDL compiler cannot resolve which index definition should take precedence. This is purely a T-SQL syntax conflict introduced by new inline indexing capabilities, not a permissions, port, or corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Column Definition

• Open the CREATE TABLE script that triggered Error 10793 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the column where PRIMARY KEY and INDEX index_name are both declared inline, for example:

  CREATE TABLE dbo.Orders (
      OrderID INT PRIMARY KEY INDEX IX_OrderID,
      OrderDate DATETIME2
  );

• Confirm this is the exact column causing the conflict by checking the line number referenced in the error message output.

Step 2: Remove the Redundant Inline Index Declaration

• Delete the inline INDEX index_name clause from the column definition since PRIMARY KEY already creates a clustered index by default.
• Revised syntax should look like:

  CREATE TABLE dbo.Orders (
      OrderID INT PRIMARY KEY,
      OrderDate DATETIME2
  );

• If you specifically need a custom index (e.g., nonclustered with different options), move it outside the column list using a table-level constraint instead.

Step 3: Define the Index Separately at the Table Level (If Needed)

• If a nonclustered index is required on the same primary key column, declare the PRIMARY KEY inline and define the index as a separate CREATE INDEX statement after the table is created.
• Example:

  CREATE TABLE dbo.Orders (
      OrderID INT PRIMARY KEY NONCLUSTERED,
      OrderDate DATETIME2
  );
  GO
  CREATE CLUSTERED INDEX IX_OrderID ON dbo.Orders(OrderID);

• Save and re-run the script in SSMS or via sqlcmd from Windows Server 2025 / Windows 11 Command Prompt to confirm the table is created without errors.

Step 4: Validate the Script Using SQL Server’s Syntax Checker

• Use SET PARSEONLY ON; before executing the script to validate syntax without actually creating the table.
• Run the corrected script again with SET PARSEONLY OFF; to execute it fully once validated.

Alternative or Verification Steps

• Verify the fix by running sp_help 'dbo.Orders'; or querying sys.indexes and sys.key_constraints to confirm only one index exists per intended key structure.
• If the error persists, drop and recreate the table entirely using a fresh script with PRIMARY KEY and any additional indexes fully separated into distinct CREATE INDEX statements outside the column definition block.

Troubleshooting Guide: SQL Server Error 10792 in Natively Compiled Modules

Why This Error Happens

Error 10792 occurs when a natively compiled module (a stored procedure, function, or trigger compiled directly into native machine code) attempts to use a Transact-SQL construct, data type, or operator that is not supported in the natively compiled execution environment. This typically happens because natively compiled modules operate under strict limitations compared to interpreted T-SQL, and certain features—such as specific functions, cross-database references, or particular data type conversions—simply have no native code equivalent. The error can also surface after a SQL Server upgrade (such as moving to SQL Server 2025) if deprecated syntax was previously tolerated but is now flagged more strictly by the native compiler.

Step-by-Step Resolution Guide

Step 1: Identify the Unsupported Construct

• Open the natively compiled module definition using SQL Server Management Studio (SSMS) and locate the exact line referenced in the error message.
• Cross-reference the flagged syntax against the official “Supported Features for Natively Compiled T-SQL Modules” documentation for SQL Server 2025.
• Check for common culprits: CURSOR declarations, MERGE statements, non-deterministic functions, or unsupported CONVERT/CAST combinations.

Step 2: Modify the Module Code

• Rewrite the unsupported construct using an equivalent supported pattern (for example, replace a cursor-based loop with a WHILE loop using supported native syntax).
• Remove or isolate any cross-database or cross-instance references, as natively compiled modules only support access to memory-optimized tables within the same database.
• Save the revised module script to a local path, such as C:\SQLScripts\NativeModules\Revised_Proc.sql, before redeploying.

Step 3: Recompile and Redeploy the Module

• Drop the existing natively compiled module using DROP PROCEDURE [SchemaName].[ProcedureName];.
• Recreate it with the corrected syntax, ensuring the WITH NATIVE_COMPILATION, SCHEMABINDING clause is still included in the CREATE PROCEDURE statement.
• Execute the deployment script through SSMS or via sqlcmd -S ServerName -i C:\SQLScripts\NativeModules\Revised_Proc.sql on Windows Server 2025.

Step 4: Update SQL Server Compatibility Level

• Verify the database compatibility level matches SQL Server 2025 requirements by running SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';.
• If outdated, update it using ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; (adjust value per the current SQL Server 2025 release).
• Restart the SQL Server service via Windows Services (services.msc) if compatibility changes do not take effect immediately.

Alternative or Verification Steps

• Verify the fix by executing the module manually with test parameters (EXEC [SchemaName].[ProcedureName] @param1 = 'test';) and confirming no errors return in the Messages pane of SSMS.
• If native compilation still fails, consider converting the module to a standard (interpreted) T-SQL stored procedure as a temporary workaround while refactoring the unsupported logic.

Troubleshooting Guide: SQL Server Error 10791 – “Hash Indexes Are Permitted Only in Memory Optimized Tables”

Why This Error Happens

This error occurs when a CREATE TABLE or CREATE INDEX statement attempts to define a HASH index on a standard, disk-based table rather than a memory-optimized table. Hash indexes are a feature exclusive to the In-Memory OLTP engine and rely on a bucket-count structure that only memory-optimized tables support. This typically happens due to a scripting error, a copy-pasted template, or a misunderstanding of the table’s MEMORY_OPTIMIZED property during schema migration or deployment.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Table Definition

• Open the script or stored procedure that triggered Error 10791 in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Locate the CREATE TABLE statement containing the INDEX ... HASH WITH (BUCKET_COUNT = N) clause.
• Confirm whether the table was intended to be disk-based or memory-optimized by checking the original design documentation or schema diagram.

Step 2: Convert the Table to Memory-Optimized (If Hash Index Is Required)

• Add the MEMORY_OPTIMIZED = ON clause to the table definition, along with a required DURABILITY setting.
• Ensure a memory-optimized filegroup exists first by running:

  ALTER DATABASE [YourDatabase] ADD FILEGROUP [YourDB_MOD] CONTAINS MEMORY_OPTIMIZED_DATA;
  ALTER DATABASE [YourDatabase] ADD FILE (NAME='YourDB_MOD_File', FILENAME='C:\SQLData\YourDB_MOD') TO FILEGROUP [YourDB_MOD];

• Rewrite the table creation script, for example:

  CREATE TABLE dbo.YourTable
  (
      Id INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
      Data NVARCHAR(100)
  ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Step 3: Remove the Hash Index (If Memory-Optimization Is Not Needed)

• If the table must remain disk-based, remove the HASH WITH (BUCKET_COUNT = N) syntax entirely.
• Replace it with a standard clustered or nonclustered B-tree index instead:

  CREATE NONCLUSTERED INDEX IX_YourTable_Id ON dbo.YourTable (Id);

• Re-run the modified script in SSMS or via sqlcmd on Windows Server 2025 to confirm the table is created without error.

Step 4: Validate File Paths and Permissions for Memory-Optimized Filegroups

• If converting to memory-optimized storage, verify that the service account running SQL Server has write access to the directory specified in the FILENAME parameter (e.g., C:\SQLData\).
• Check Windows Security permissions via File Explorer > Right-click folder > Properties > Security tab, ensuring the SQL Server service account has Full Control.
• Restart the SQL Server service via services.msc if filegroup changes do not take effect immediately.

Alternative or Verification Steps

• Run SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTable'; to confirm the table’s memory-optimized status returns 1 before applying hash indexes again.
• If BUCKET_COUNT sizing is uncertain, use a nonclustered (B-tree) index on the memory-optimized table instead of HASH, as it auto-manages range scans without requiring a fixed bucket count.

Troubleshooting Guide: SQL Server Error 10790 — “The option ‘%ls’ can be specified only for hash indexes”

Why This Error Happens

This error occurs when a table or index definition attempts to apply a hash-specific option — such as BUCKET_COUNT — to an index that is not actually configured as a hash index. This commonly happens on memory-optimized tables (In-Memory OLTP) where the INDEX clause defaults to a nonclustered (B-tree) type unless HASH is explicitly declared. It can also occur during migration or script restoration from an older SQL Server version where syntax compatibility for memory-optimized indexes was not properly validated against SQL Server 2025 engine requirements.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index Definition

• Locate the CREATE TABLE or CREATE INDEX statement referenced in the error stack or deployment script.
• Look specifically for lines containing BUCKET_COUNT paired with an index declaration.
• Example of invalid syntax:

  INDEX ix_Example NONCLUSTERED (ColumnA) WITH (BUCKET_COUNT = 1024)

Step 2: Correct the Index Type Declaration

• If a hash index was intended, explicitly declare HASH alongside BUCKET_COUNT:

  INDEX ix_Example HASH (ColumnA) WITH (BUCKET_COUNT = 1024)

• If a nonclustered (range) index was intended instead, remove the BUCKET_COUNT option entirely:

  INDEX ix_Example NONCLUSTERED (ColumnA)

Step 3: Validate Memory-Optimized Table Compatibility

• Open SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Run the following to confirm the table’s durability and memory-optimized settings:

  SELECT name, is_memory_optimized, durability_desc 
  FROM sys.tables 
  WHERE name = 'YourTableName';

• Ensure the filegroup supports MEMORY_OPTIMIZED_DATA under %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 4: Redeploy the Corrected Script

• Drop and recreate the table/index using the corrected CREATE TABLE or ALTER TABLE statement.
• If using a deployment pipeline (DACPAC/SSDT), rebuild the project after editing the .sql schema file to ensure the compiled model reflects the fix.

Alternative or Verification Steps

• Verify the fix by re-running the deployment script or executing DBCC CHECKCONSTRAINTS and confirming no errors reference Error 10790.
• If the issue persists, check for leftover cached execution plans using DBCC FREEPROCCACHE, then re-attempt table creation, as stale plans referencing old index metadata can sometimes resurface this error during schema comparisons.

Troubleshooting Guide: Error 10789 in SQL Server 2025

Why This Error Happens

This error occurs when creating or altering a memory-optimized table with a hash index but the BUCKET_COUNT option is omitted from the index definition. SQL Server’s In-Memory OLTP engine requires an explicit bucket count for hash indexes because it pre-allocates a fixed hash table structure, unlike nonclustered (range) indexes which size dynamically. This is purely a syntax/configuration omission rather than a permissions or corruption issue, and it will consistently block DDL execution until corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Index Definition

• Locate the CREATE TABLE or CREATE INDEX statement referenced in the error message, including the table and index name shown in the %.*ls placeholders.
• Open the script in SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Confirm the index is defined with WITH (BUCKET_COUNT = N) missing from its INDEX clause.

Step 2: Add the Required BUCKET_COUNT Option

• Edit the index definition to include BUCKET_COUNT, for example:

CREATE TABLE dbo.MyTable
(
    Id INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    INDEX ix_Id HASH (Id) WITH (BUCKET_COUNT = 1048576)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

• Set BUCKET_COUNT to roughly 1-2x the expected number of unique index key values (rounded up to the nearest power of two for optimal hash distribution).

Step 3: Recreate or Alter the Table/Index

• If the table already exists, drop and recreate the memory-optimized table, since hash index bucket counts cannot be altered in-place prior to certain compatibility levels.
• Alternatively, use ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = N) if your SQL Server 2025 build supports online bucket count changes.
• Re-run the script and confirm the DDL statement completes without raising Error 10789.

Step 4: Validate Memory-Optimized Filegroup Configuration

• Ensure the database has a MEMORY_OPTIMIZED_DATA filegroup properly configured under C:\SQLData\MemoryOptimized\ (or your chosen path) on Windows Server 2025/Windows 11.
• Run sp_helpfilegroup or query sys.filegroups to confirm the filegroup exists before attempting to create memory-optimized tables with hash indexes.

Alternative or Verification Steps

• Verify the fix by querying sys.hash_indexes and checking the bucket_count column matches your intended value for the affected table.
• If the error persists after adding BUCKET_COUNT, check compatibility level with SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB' and ensure it’s set to 150 or higher to support full In-Memory OLTP hash index syntax.

Troubleshooting Guide: Error 10788 – “The option ‘%ls’ is not supported with table types”

Why This Error Happens

This error occurs when a Transact-SQL statement attempts to apply an unsupported option or constraint to a table-valued type (TVT), such as specifying IDENTITY seeding behaviors, FOREIGN KEY constraints, or certain index options that are restricted for user-defined table types. SQL Server 2025 enforces stricter schema validation on table types since they are designed to be lightweight, in-memory structures without the full feature set of persistent tables. This typically happens during the execution of a CREATE TYPE ... AS TABLE statement where incompatible syntax was copied from a standard CREATE TABLE script.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

• Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance and locate the exact script causing the error in the Activity Monitor or query history.
• Look specifically for CREATE TYPE [SchemaName].[TypeName] AS TABLE definitions in the batch that failed.
• Check the error log for the specific unsupported keyword referenced in the ‘%ls’ placeholder (e.g., WITH (DATA_COMPRESSION) or FOREIGN KEY).

Step 2: Remove Unsupported Table Options

• Edit the T-SQL definition to strip out options incompatible with table types.
• Common options to remove include: FOREIGN KEY references, DEFAULT constraints on computed columns, and WITH (DATA_COMPRESSION = PAGE).
• Ensure you are only using supported elements: PRIMARY KEY, UNIQUE, CHECK constraints, and NOT NULL.

Step 3: Validate Syntax Using a Staging Table

• Before converting to a Type, test your column definitions by creating a temporary standard table first.
• Run the command: SELECT * INTO #TempValidation FROM (VALUES (1, 'test')) AS Source(ID, Val);
• Once the schema logic is confirmed to be sound, migrate only the valid syntax into your CREATE TYPE statement.

Step 4: Rebuild the Type Definition

• Since table types cannot be altered once created, you must drop the broken dependency first.
• Execute: DROP TYPE IF EXISTS [SchemaName].[TypeName];
• Re-run the corrected CREATE TYPE script without the restricted options identified in Step 2.

Alternative or Verification Steps

• Verification: Run sys.table_types catalog view query (SELECT * FROM sys.table_types WHERE name = 'YourTypeName') to confirm the type exists and is bound correctly without throwing Error 10788.
• Alternative: If the required functionality (like a Foreign Key) is strictly necessary, do not use a Table Type; instead, revert to using a standard global temporary table (##TempTable) which supports the full breadth of CREATE TABLE syntax.

Troubleshooting Guide: Error 10787 – REPEATABLEREAD/SERIALIZABLE Hints Not Permitted on Hekaton Tables with Columnstore Indexes

Why This Error Happens

This error occurs because memory-optimized (Hekaton) tables that have a columnstore index enabled only support a limited set of transaction isolation levels at the table-hint level. The REPEATABLEREAD and SERIALIZABLE hints require locking and versioning behavior that conflicts with the lock-free, row-versioning architecture used by In-Memory OLTP columnstore storage. SQL Server 2025 enforces this restriction strictly to prevent inconsistent read phenomena that these isolation levels cannot guarantee on natively compiled columnstore structures.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Table

• Review the error message text to locate the exact query batch or stored procedure name referencing the Hekaton table.
• Run the following to confirm the table is memory-optimized with a columnstore index:

  SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';
  SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName') AND type = 5;

• Note every location in application code, views, or stored procedures where WITH (REPEATABLEREAD) or WITH (SERIALIZABLE) is used against this table.

Step 2: Replace the Unsupported Table Hints

• Edit the query and remove the REPEATABLEREAD or SERIALIZABLE table hint from the FROM clause.
• Replace it with a supported isolation level such as SNAPSHOT or READ COMMITTED using the session-level SET TRANSACTION ISOLATION LEVEL statement instead of a table hint:

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  BEGIN TRANSACTION;
  SELECT * FROM dbo.YourTable;
  COMMIT TRANSACTION;

• If the application logic strictly requires serializable guarantees, redesign the transaction to use SNAPSHOT isolation combined with explicit conflict-detection logic in application code.

Step 3: Update Natively Compiled Stored Procedures

• Open any natively compiled stored procedure (WITH NATIVE_COMPILATION) referencing the table and check the BEGIN ATOMIC WITH block.
• Change the isolation level declared in the atomic block header (e.g., TRANSACTION ISOLATION LEVEL = SNAPSHOT) since natively compiled procedures only support SNAPSHOT, REPEATABLE READ, or SERIALIZABLE at the procedure level, not as inline table hints.
• Recompile the procedure in SQL Server Management Studio (SSMS) or Azure Data Studio:

  ALTER PROCEDURE dbo.YourNativeProc
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      SELECT * FROM dbo.YourTable;
  END;

Step 4: Validate Columnstore Index Compatibility

• Confirm your SQL Server 2025 instance has the latest Cumulative Update installed via SQL Server Configuration Manager or SELECT @@VERSION;.
• Check Windows Server 2025 / Windows 11 for pending OS updates under Settings > Windows Update, as in-memory OLTP performance libraries occasionally receive patches affecting columnstore handling.

Alternative or Verification Steps

• Re-run the original query or application transaction and confirm Error 10787 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, drop and recreate the columnstore index temporarily to test whether isolation-level hints work correctly on a standard rowstore version of the table, isolating whether the issue is columnstore-specific or Hekaton-specific.

Troubleshooting Guide: Error 10786 in SQL Server Availability Groups

Why This Error Happens

Error 10786 occurs because the ALTER AVAILABILITY GROUP ... MODIFY REPLICA statement attempts to change more than one replica property (such as ENDPOINT_URL, FAILOVER_MODE, or AVAILABILITY_MODE) within a single T-SQL batch. SQL Server 2025 enforces strict parsing rules that require each replica modification to be isolated into its own distinct command. This is typically caused by poorly generated scripts, legacy automation tools, or copy-pasted templates that concatenate multiple options under one MODIFY REPLICA clause.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script

• Locate the exact T-SQL script or SSMS-generated job that triggered the error.
• Check the %ls placeholder in the error message via SQL Server Management Studio (SSMS) or the SQL Server Error Log to see which specific options (e.g., SESSION_TIMEOUT, ENDPOINT_URL) were bundled together.
• Open the script in a text editor to isolate the MODIFY REPLICA block.

Step 2: Split the Command into Separate Statements

• Rewrite the single command into multiple, sequential ALTER AVAILABILITY GROUP statements.
• Ensure every distinct property change (Failover Mode, Availability Mode, Endpoint URL, Seeding Mode, etc.) has its own dedicated line.
• Example of the correct syntax structure:

 ALTER AVAILABILITY GROUP [AG_Prod] 
 MODIFY REPLICA ON N'SQLNODE01' WITH (ENDPOINT_URL = N'TCP://SQLNODE01.domain.com:5022');
 GO
 ALTER AVAILABILITY GROUP [AG_Prod] 
 MODIFY REPLICA ON N'SQLNODE01' WITH (FAILOVER_MODE = AUTOMATIC);
 GO

Step 3: Validate Syntax Using SQL Server 2025 Intellisense

• Run the corrected script in a Query Window with SQLCMD Mode disabled to utilize updated Intellisense red-squiggle checks.
• Verify there are no trailing commas or nested parentheses combining two WITH clauses into one replica modification block.

Step 4: Re-Deploy via Automation Tools

• If using PowerShell (Set-SqlAvailabilityReplica) or Ansible playbooks, check the underlying cmdlet logic.
• Update the automation script to loop through properties individually rather than passing a combined hash table of settings into a single native T-SQL execution.

Alternative or Verification Steps

• Verification: Run SELECT * FROM sys.dm_hadr_availability_replica_states; to confirm that the specific replica properties (Failover Mode, Operational State) have updated correctly without generating new errors in the SQL Server Logs.
• Alternative: If manual scripting continues to fail, use the Always On Availability Groups Wizard in SSMS 21 (compatible with SQL Server 2025) to modify replica properties via the GUI, as the wizard automatically separates commands into valid individual batches before execution.

Troubleshooting Guide: SQL Server Error 10785

Why This Error Happens

Error 10785 occurs when a Transact-SQL statement, clause, or feature is executed against an edition, compatibility level, or configuration of SQL Server 2025 that does not support it. This typically happens when a syntax element (such as a specific %ls clause or option) requires a particular database compatibility level, server configuration, or Enterprise-only feature that is either disabled or unavailable in the current environment. It can also surface after an in-place upgrade to SQL Server 2025 where legacy T-SQL scripts use syntax that has been deprecated or restricted to specific feature combinations.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Statement and Context

• Capture the full error message text, including the specific %ls placeholder values filled in by SQL Server (check SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• Run the failing query manually in SQL Server Management Studio (SSMS) 21 to reproduce the exact error and note which clause triggers it.
• Cross-reference the message in sys.messages using:

  SELECT * FROM sys.messages WHERE message_id = 10785 AND language_id = 1033;

Step 2: Verify Database Compatibility Level

• Check the current compatibility level of the database involved:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If the feature requires compatibility level 170 (SQL Server 2025) or higher, update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Restart the query execution to confirm whether the compatibility level was the blocking factor.

Step 3: Confirm Edition and Feature Availability

• Run SELECT SERVERPROPERTY('Edition'); to confirm you are running an edition (Enterprise, Standard, Developer) that supports the feature in question.
• Review the official “Features Supported by the Editions of SQL Server 2025” documentation to confirm the clause or option isn’t Enterprise-only while running on Standard Edition.
• If running Standard Edition, remove or replace the unsupported clause with a supported alternative, or upgrade the SQL Server license/edition.

Step 4: Check Server-Level Configuration Flags

• Some features require specific sp_configure options to be enabled (e.g., clr enabled, polybase enabled, or remote data archive).
• Verify current configuration:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure;

• Enable the missing option if required, then run RECONFIGURE and restart the SQL Server service via services.msc on Windows Server 2025.

Step 5: Update SQL Server to the Latest Cumulative Update

• Open SQL Server Setup or check Windows Update for the latest Cumulative Update (CU) for SQL Server 2025, as this error can stem from a known bug fixed in later patches.
• Download and install the CU from the Microsoft Update Catalog.
• Reboot the Windows Server 2025 / Windows 11 host if prompted, then retest the original query.

Alternative or Verification Steps

• To verify the fix, re-run the original failing script or stored procedure in SSMS and confirm it completes without raising Error 10785.
• As an alternative, rewrite the query using fully supported ANSI SQL syntax or an equivalent feature that doesn’t depend on edition-specific or compatibility-level-specific extensions, then validate using SET STATISTICS IO, TIME ON to confirm successful execution.

Troubleshooting Guide: SQL Server Error 10784

Why This Error Happens

This error occurs when creating or altering a natively compiled module (such as a stored procedure or scalar function) that uses the BEGIN ATOMIC block, but one or more required options in the WITH clause have been omitted. SQL Server mandates that atomic blocks explicitly declare certain session-level behaviors—such as TRANSACTION ISOLATION LEVEL and LANGUAGE—because these objects compile to native code and cannot inherit ambient session settings at runtime. Without these explicit values, the compiler cannot resolve the execution context needed to generate the native DLL, so it throws Error 10784 and halts compilation.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Module Definition

• Open SSMS or Azure Data Studio and find the exact CREATE PROCEDURE / CREATE FUNCTION / CREATE TRIGGER statement referenced in the error message.
• Check the Messages tab for the specific parameter name (%ls) that SQL Server substituted into the error text—this tells you exactly which option is missing.
• Use sp_helptext 'YourProcedureName' if you need to pull the current definition from the database.

Step 2: Add the Required WITH Clause Options

• Locate the BEGIN ATOMIC WITH (...) block in your T-SQL code.
• Ensure both mandatory options are explicitly set, for example:

  BEGIN ATOMIC WITH 
      (TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
       LANGUAGE = N'us_english')

• Confirm TRANSACTION ISOLATION LEVEL is set to a supported value (SNAPSHOT, REPEATABLE READ, or SERIALIZABLE depending on your memory-optimized table durability settings).
• Confirm LANGUAGE matches an installed language on the instance (query sys.syslanguages if unsure).

Step 3: Recompile and Deploy the Corrected Module

• Drop and recreate the procedure/function using DROP PROCEDURE IF EXISTS followed by the corrected CREATE PROCEDURE statement.
• If this module is part of a deployment script or DACPAC, update the source .sql file in your project repository to prevent regression on the next deployment.
• Rebuild the project (if using SSDT) to catch any other natively compiled modules with the same omission before pushing to production.

Step 4: Validate Memory-Optimized Table Dependencies

• If the module references memory-optimized tables, confirm the database has MEMORY_OPTIMIZED_DATA filegroup properly configured.
• Run SELECT * FROM sys.filegroups WHERE type = 'FX' to verify the filegroup exists and is accessible.

Alternative or Verification Steps

• After redeploying, run EXEC sp_helptext 'YourProcedureName' again to confirm the WITH clause now shows both TRANSACTION ISOLATION LEVEL and LANGUAGE explicitly defined, then execute the procedure manually to confirm it compiles and runs without error.
• If the error persists despite correct syntax, check for conflicting collation or regional settings on Windows Server 2025 by running SELECT SERVERPROPERTY('Collation') and ensure it aligns with the LANGUAGE value specified in the atomic block.

Troubleshooting Guide: Error 10783 – Natively Compiled Module Must Be an ATOMIC Block

Why This Error Happens

This error occurs when creating or altering a natively compiled stored procedure, trigger, or scalar function in SQL Server 2025 without wrapping the module body in a required BEGIN ATOMIC ... END block. Natively compiled modules, used with memory-optimized tables, mandate atomic block syntax because they compile to native machine code and cannot rely on standard transaction control statements like BEGIN TRAN. This is almost always a T-SQL syntax issue in the module definition rather than a server configuration or permissions problem.

Step-by-Step Resolution Guide

Step 1: Locate the Problematic Module Definition

• Open SSMS or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following to find the object causing the error:

SELECT definition FROM sys.sql_modules 
WHERE object_id = OBJECT_ID('YourProcedureName');

• Identify whether the module uses WITH NATIVE_COMPILATION in its header.

Step 2: Rewrite the Module Body Using an ATOMIC Block

• Ensure the body begins with BEGIN ATOMIC instead of a plain BEGIN.
• Include the required WITH clause specifying TRANSACTION ISOLATION LEVEL and LANGUAGE.
• Example corrected syntax:

CREATE PROCEDURE dbo.MyNativeProc
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH 
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    -- procedure logic here
END

Step 3: Drop and Recreate the Module

• Since ALTER is not supported for natively compiled modules in most versions, drop the existing object first:

DROP PROCEDURE IF EXISTS dbo.MyNativeProc;

• Re-run the corrected CREATE PROCEDURE script from Step 2.

Step 4: Validate Dependent Memory-Optimized Objects

• Confirm that any tables referenced in the module are memory-optimized (SELECT is_memory_optimized FROM sys.tables).
• If the table is not memory-optimized, the ATOMIC block requirement still applies for natively compiled modules, so fix syntax rather than table type.

Alternative or Verification Steps

• Re-execute the CREATE PROCEDURE statement and confirm it completes without Error 10783; then test execution with sample parameters to verify logic integrity.
• If the error persists, check for nested or hidden BEGIN...END blocks inside the module that might be conflicting with the outer ATOMIC block—only one ATOMIC block is allowed per natively compiled module.

Troubleshooting Guide: SQL Server Error 10782 — “BEGIN ATOMIC is supported only in natively compiled modules”

Why This Error Happens

This error occurs because the BEGIN ATOMIC...END block syntax is exclusive to natively compiled modules, such as natively compiled stored procedures, functions, or triggers used with memory-optimized tables. If a developer attempts to use BEGIN ATOMIC inside a standard (interpreted) T-SQL stored procedure or ad-hoc batch, SQL Server 2025 rejects the syntax because the standard execution engine does not support atomic blocks. This typically happens due to a misunderstanding of module types or copying code from a natively compiled template into a regular procedure.

Step-by-Step Resolution Guide

Step 1: Verify the Module Type

• Check the CREATE statement for the procedure or function in question.
• Ensure it includes the WITH NATIVE_COMPILATION clause; standard modules cannot utilize BEGIN ATOMIC.
• Run the following query to check existing procedures:

  SELECT name, uses_native_compilation 
  FROM sys.sql_modules 
  WHERE object_id = OBJECT_ID('YourProcedureName');

Step 2: Convert the Module to Natively Compiled (If Intended)

• If the atomic block logic is required, modify the CREATE PROCEDURE statement to include the necessary native compilation syntax.
• Add WITH NATIVE_COMPILATION, SCHEMABINDING immediately after the procedure name and parameters.
• Ensure the BEGIN ATOMIC block includes required options, such as TRANSACTION ISOLATION LEVEL and LANGUAGE:

  CREATE PROCEDURE dbo.MyNativeProc
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS 
  BEGIN ATOMIC WITH 
  (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      -- Your logic here
  END

Step 3: Remove Atomic Syntax for Standard Procedures (If Native Compilation is Not Needed)

• If native compilation is not a requirement, strip the BEGIN ATOMIC wrapper entirely.
• Replace it with standard transaction handling logic instead:

  BEGIN TRY
      BEGIN TRANSACTION
          -- Your logic here
      COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
      ROLLBACK TRANSACTION
  END CATCH

Step 4: Validate Memory-Optimized Filegroup Prerequisites

• If converting to native compilation, confirm the database has a MEMORY_OPTIMIZED_DATA filegroup configured.
• Navigate to SQL Server Management Studio (SSMS) > Database Properties > Filegroups to verify this exists before deploying the native module.
• If missing, create one via T-SQL:

  ALTER DATABASE YourDB ADD FILEGROUP YourDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;

Alternative or Verification Steps

• Verification: Re-run the CREATE PROCEDURE or CREATE FUNCTION script in SSMS; if the module compiles without Error 10782, the syntax conflict is resolved.
• Alternative: If native compilation is throwing further compatibility errors (e.g., unsupported data types), consider refactoring the logic into a standard interpreted T-SQL module using TRY...CATCH blocks instead of pursuing memory-optimized native compilation.

Troubleshooting Guide: Error 10781 – Conflicting Query Optimizer Hints in SQL Server 2025

Why This Error Happens

This error occurs when a query simultaneously uses a QUERYTRACEON hint and a corresponding OPTIMIZER hint (such as USE HINT or a legacy trace flag equivalent) that govern the same internal optimizer behavior. SQL Server’s query optimizer cannot reconcile two explicit directives attempting to control the same execution path or cardinality estimation model at once. This typically surfaces after migrating queries from older SQL Server versions where trace flags were applied inline, while newer code also introduces OPTION (USE HINT(...)) syntax, creating a duplicate or contradictory instruction set.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query Text

• Capture the full query text referenced in the error message, including the specific %d trace flag number and the %.*ls hint string.
• Use SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance to re-run the query in isolation.
• Check the Messages tab for the exact trace flag number (e.g., 4199, 9481) and the named hint (e.g., DISABLE_OPTIMIZER_ROWGOAL).

Step 2: Cross-Reference Trace Flag and Hint Mappings

• Open the official SQL Server 2025 documentation or query sys.dm_exec_valid_use_hints to view the current list of supported USE HINT values.
• Match the numeric trace flag from the error against its corresponding named hint to confirm they are indeed duplicates.
• Example conflict: OPTION (QUERYTRACEON 9481, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) — both target legacy CE behavior.

Step 3: Edit the Query to Remove the Duplicate Hint

• Open the stored procedure, view, or ad-hoc script in your IDE (SSMS, VS Code with mssql extension).
• Remove either the QUERYTRACEON %d clause or the USE HINT('%.*ls') clause from the OPTION() list — keep only one.
• Save the script and deploy the change via your standard release pipeline or directly via ALTER PROCEDURE.

Step 4: Clear the Plan Cache for the Affected Object

• Run DBCC FREEPROCCACHE (scoped with sys.dm_exec_cached_plans lookup if production-safe) or use ALTER PROCEDURE ... WITH RECOMPILE for a one-time fix.
• This ensures no stale execution plan referencing the old conflicting hint combination remains cached.

Step 5: Validate Server-Level Trace Flags

• Check DBCC TRACESTATUS(-1) to see if the conflicting trace flag is also enabled globally via startup parameters in SQL Server Configuration Manager.
• If found, navigate to SQL Server Configuration Manager > SQL Server Services > [Your Instance] > Properties > Startup Parameters on Windows Server 2025 and remove the -T%d entry if it duplicates the query-level hint.

Alternative or Verification Steps

• Re-execute the corrected query and confirm it completes without Error 10781, then check the actual execution plan (Ctrl+M in SSMS) to verify the intended optimizer behavior is still applied.
• As an alternative, use OPTION (USE HINT(...)) exclusively going forward instead of QUERYTRACEON, since Microsoft recommends named hints for better forward compatibility in SQL Server 2025 and beyond.

Troubleshooting Guide: Error 10780 in Microsoft SQL Server 2025

Why This Error Happens

This error occurs when creating a foreign key constraint on a memory-optimized table where the referenced column list does not exactly match an existing primary key on the target table. Memory-optimized tables in SQL Server have a strict requirement that foreign keys can only reference primary key columns, unlike disk-based tables which allow referencing any unique constraint or unique index. This typically happens due to a schema design oversight, a missing primary key definition, or a mismatch in column order/data types between the foreign key and the intended primary key.

Step-by-Step Resolution Guide

Step 1: Verify the Primary Key on the Referenced Table

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to inspect existing constraints:

  EXEC sp_pkeys 'ReferencedTableName';

• Confirm whether a primary key actually exists, and note the exact column names and their order.

Step 2: Compare Column Definitions Between Tables

• Check that the data types, lengths, and precision of the foreign key columns in the referencing table exactly match the primary key columns in the referenced table.
• Pay special attention to column order—composite primary keys must be referenced in the same sequence.
• Use this query to review column metadata:

  SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'ReferencedTableName';

Step 3: Add or Correct the Primary Key Constraint

• If no primary key exists, add one directly in the table definition (memory-optimized tables require the primary key to be defined at creation time in most configurations).
• Example syntax:

  ALTER TABLE dbo.ReferencedTableName 
  ADD CONSTRAINT PK_ReferencedTableName PRIMARY KEY NONCLUSTERED (ColumnID);

• Note: For memory-optimized tables, primary keys are typically NONCLUSTERED or NONCLUSTERED HASH, not CLUSTERED.

Step 4: Recreate the Foreign Key Constraint

• Drop the failed foreign key constraint attempt if it partially exists.
• Re-run the ALTER TABLE or CREATE TABLE statement with the corrected foreign key definition, ensuring the referencing column list matches the primary key exactly.
• Example:

  ALTER TABLE dbo.OrderDetails 
  ADD CONSTRAINT FK_OrderDetails_Orders 
  FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID);

Step 5: Validate Memory-Optimized Table Configuration

• Confirm both tables are properly configured with MEMORY_OPTIMIZED = ON and compatible durability settings (SCHEMA_AND_DATA or SCHEMA_ONLY).
• Ensure the database has a MEMORY_OPTIMIZED_DATA filegroup properly configured on Windows Server 2025/Windows 11.

Alternative or Verification Steps

• Run sys.foreign_keys and sys.key_constraints system views to confirm the foreign key was created successfully and is correctly linked to the primary key.
• If the issue persists, consider temporarily converting the referenced table to a disk-based table to validate the relational design before reapplying memory optimization, as this isolates whether the issue is schema-related or memory-optimization-specific.

Troubleshooting Guide: SQL Server Error 10779 — “Durability Option ‘SCHEMA_ONLY’ Supported Only With Memory Optimized Tables”

Why This Error Happens

This error occurs when you attempt to set the DURABILITY = SCHEMA_ONLY option on a standard disk-based table rather than a memory-optimized table. The SCHEMA_ONLY durability setting is an In-Memory OLTP feature exclusive to tables created with the MEMORY_OPTIMIZED = ON clause, since it relies on the Hekaton engine to persist only the schema (not data) across restarts. SQL Server 2025 enforces this restriction strictly, so attempting to apply this syntax to a conventional table immediately throws Error 10779 during table creation or alteration.

Step-by-Step Resolution Guide

Step 1: Verify the Table Definition Causing the Error

• Open the failing CREATE TABLE or ALTER TABLE script in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Locate the WITH (DURABILITY = SCHEMA_ONLY) clause in the T-SQL statement.
• Confirm whether the table was intended to be memory-optimized by checking for the MEMORY_OPTIMIZED = ON keyword in the same CREATE TABLE statement.

Step 2: Ensure the Database Has a Memory-Optimized Filegroup

• Run the following query to check if your database supports In-Memory OLTP:

SELECT name, type_desc FROM sys.filegroups WHERE type = 'FX';

• If no row is returned, create a memory-optimized filegroup and file first:

ALTER DATABASE [YourDatabaseName]
ADD FILEGROUP [YourDB_MOD] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [YourDatabaseName]
ADD FILE (NAME='YourDB_MOD_File', FILENAME='C:\SQLData\YourDB_MOD')
TO FILEGROUP [YourDB_MOD];

• Ensure the path C:\SQLData\ exists and the SQL Server service account has write permissions to it.

Step 3: Correct the Table Creation Script

• Add the MEMORY_OPTIMIZED = ON clause alongside the DURABILITY setting so the syntax is valid:

CREATE TABLE dbo.YourTable
(
    Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(100)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

• If the table should instead be a normal disk-based table, simply remove the DURABILITY = SCHEMA_ONLY clause entirely, since disk-based tables are durable by default and do not require this option.

Step 4: Re-run the Script and Validate Compatibility Level

• Confirm the database compatibility level supports In-Memory OLTP (130 or higher) using:

SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• Update it if necessary:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 160;

• Execute the corrected CREATE TABLE script again and confirm no errors are returned.

Alternative or Verification Steps

• Verify the fix by querying sys.tables and checking the durability_desc and is_memory_optimized columns for your table — both should align (e.g., SCHEMA_ONLY paired with is_memory_optimized = 1).
• If memory-optimized tables are not a business requirement, the simplest alternative is to remove the DURABILITY clause entirely and let the table default to standard disk-based, fully durable storage.

Troubleshooting Guide: Error 10778 – Unsupported Foreign Key Between Memory-Optimized and Disk-Based Tables

Why This Error Happens

This error occurs because SQL Server’s In-Memory OLTP engine does not allow foreign key constraints to span between memory-optimized tables and traditional disk-based (non-memory-optimized) tables. The two table types use fundamentally different storage engines, transaction isolation mechanisms, and row versioning systems, making cross-referential integrity enforcement via foreign keys architecturally incompatible. This typically surfaces when a database schema is migrated or altered without accounting for the memory-optimization status of referenced tables.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Tables and Constraint

• Run the following query to locate the offending foreign key and its parent/child tables:

SELECT fk.name AS FK_Name, 
       OBJECT_NAME(fk.parent_object_id) AS ChildTable,
       OBJECT_NAME(fk.referenced_object_id) AS ParentTable
FROM sys.foreign_keys fk;

• Check the is_memory_optimized column in sys.tables for both the parent and child tables to confirm the mismatch.

SELECT name, is_memory_optimized FROM sys.tables WHERE name IN ('YourChildTable', 'YourParentTable');

Step 2: Remove or Redesign the Incompatible Foreign Key

• Drop the existing foreign key constraint causing the error:

ALTER TABLE dbo.YourChildTable DROP CONSTRAINT FK_YourConstraintName;

• Decide whether to convert the disk-based table to memory-optimized, or revert the memory-optimized table to a disk-based table, so both sides match.

Step 3: Convert Table Storage Type to Match (If Required)

• If converting the disk-based table to memory-optimized, ensure a MEMORY_OPTIMIZED_DATA filegroup exists on the database first:

ALTER DATABASE YourDatabase ADD FILEGROUP YourMemFG CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE YourDatabase ADD FILE (NAME='YourMemFile', FILENAME='C:\SQLData\YourMemFile') TO FILEGROUP YourMemFG;

• Recreate the table using WITH (MEMORY_OPTIMIZED = ON) and migrate the data using INSERT INTO ... SELECT.
• Alternatively, if keeping both tables disk-based is acceptable, simply remove the MEMORY_OPTIMIZED = ON clause from the table definition and rebuild it as a standard table.

Step 4: Enforce Referential Integrity Without a Native FK (If Tables Must Stay Mixed)

• Since native foreign keys are unsupported across this boundary, implement application-level checks or use a natively compiled stored procedure/trigger to validate referential integrity manually.
• Use CHECK constraints or scheduled validation jobs as a lightweight alternative where strict FK enforcement isn’t critical.

Alternative or Verification Steps

• After applying the fix, re-run sys.foreign_keys and sys.tables queries to confirm the constraint now links two tables of the same storage type (both memory-optimized or both disk-based).
• If redesigning the schema isn’t feasible immediately, disable the foreign key logically and rely on application-layer validation, then re-run DBCC CHECKCONSTRAINTS periodically to catch orphaned records.

Troubleshooting Guide: Error 10777 – Triggers on Memory-Optimized Tables Must Use NATIVE_COMPILATION

Why This Error Happens

This error occurs because SQL Server 2025 requires that any DML trigger created on a memory-optimized table be natively compiled, as interpreted T-SQL triggers are not supported against the in-memory OLTP engine. The root cause is typically a trigger definition that omits the WITH NATIVE_COMPILATION clause, or an attempt to migrate a legacy disk-based trigger directly onto a memory-optimized table without rewriting it for native compilation. Additionally, this can surface when a schema migration script generated by an older tool (pre-2025) creates standard triggers against tables that have since been converted to MEMORY_OPTIMIZED = ON.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Trigger

• Run a query against sys.triggers joined with sys.tables to find triggers where is_memory_optimized = 0 but the parent table has is_memory_optimized_table = 1.
• Use the following diagnostic script in SSMS:

  SELECT t.name AS TriggerName, tb.name AS TableName
  FROM sys.triggers t
  JOIN sys.tables tb ON t.parent_id = tb.object_id
  WHERE tb.is_memory_optimized = 1 AND t.is_memory_optimized = 0;

Step 2: Rewrite the Trigger with Native Compilation Syntax

• Drop the existing interpreted trigger using DROP TRIGGER [TriggerName];.
• Recreate the trigger using the required syntax, ensuring you specify NATIVE_COMPILATION, SCHEMABINDING, and the execution context:

  CREATE TRIGGER dbo.trg_Inventory_Update
  ON dbo.Inventory
  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
  AFTER UPDATE
  AS
  BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
      -- Trigger logic here
  END;

• Verify that all T-SQL inside the BEGIN ATOMIC block only uses constructs supported by natively compiled modules (no non-deterministic functions or unsupported data types).

Step 3: Check for Compilation Environment Compatibility

• Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 to confirm the VC++ Runtime for Native Compilation component is installed, as this is required by the SQL Server engine to generate the DLLs for native triggers.
• If missing, run the SQL Server 2025 setup media and select Add Features > Native Client Compilation Support.
• Restart the MSSQLSERVER service via services.msc to ensure the native compilation module is loaded correctly after installation.

Step 4: Update Deployment Scripts and DACPACs

• If using SQL Server Data Tools (SSDT) or Azure Data Studio for deployment, update the .sql definition files in your project to include the NATIVE_COMPILATION keyword permanently.
• Re-publish the DACPAC to ensure future deployments do not regenerate the faulty interpreted trigger.

Alternative or Verification Steps

• Verification: Execute an UPDATE or INSERT statement against the memory-optimized table in a test environment and check the SQL Server Error Log (via xp_readerrorlog or SSMS Object Explorer) to confirm Error 10777 no longer appears.
• Alternative: If the trigger logic is too complex for native compilation (e.g., requires unsupported functions), consider moving the business logic out of the trigger and into a natively compiled stored procedure called by the application layer instead.

Why This Error Happens

Error 10776 occurs because the WITH NATIVE_COMPILATION clause is only supported for triggers that are bound to memory-optimized tables, not traditional disk-based tables. SQL Server’s native compilation engine requires the underlying table to use the In-Memory OLTP engine (MEMORY_OPTIMIZED = ON) so it can generate machine code at creation time. When a developer attempts to apply this clause to a standard trigger, the SQL Server 2025 parser rejects the DDL statement because the compilation target is incompatible with the storage engine of the referenced table.

Step-by-Step Resolution Guide

Step 1: Identify the Target Table’s Storage Type

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your Windows Server 2025 instance.
• Run the following query to check if the table is memory-optimized:

  SELECT name, is_memory_optimized FROM sys.tables WHERE name = 'YourTableName';

• If is_memory_optimized returns 0, this confirms the table is disk-based and incompatible with native compilation.

Step 2: Remove the Native Compilation Clause

• Edit the CREATE TRIGGER or ALTER TRIGGER script.
• Delete the WITH NATIVE_COMPILATION syntax, along with the associated SCHEMABINDING and EXECUTE AS options if they were only added to support native compilation.
• Re-run the trigger creation script as a standard interpreted T-SQL trigger:

  CREATE TRIGGER trg_Example
  ON dbo.YourTableName
  AFTER INSERT
  AS
  BEGIN
      -- Standard T-SQL logic
  END;

Step 3: Convert the Table to Memory-Optimized (If Native Compilation is Required)

• If native compilation is a hard requirement for performance, you must migrate the table instead of the trigger.
• Ensure your database has a MEMORY_OPTIMIZED_DATA filegroup configured:

  ALTER DATABASE YourDB ADD FILEGROUP YourDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;

• Recreate the table with the MEMORY_OPTIMIZED = ON argument and DURABILITY setting before reapplying the native trigger.

Step 4: Validate Compatibility Level

• Confirm the database compatibility level supports the native compilation features you are attempting to use.
• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'; to ensure it is set to 160 or higher for SQL Server 2025 features.

Alternative or Verification Steps

• Verification: Execute sp_helptrigger 'YourTableName' to confirm the trigger exists and check the is_memory_optimized flag to ensure it matches the parent table’s storage engine.
• Alternative: If migrating the table to memory-optimized storage is not feasible due to hardware constraints or unsupported data types (like XML or TEXT), keep the trigger as a standard interpreted trigger; the performance difference for low-volume triggers is often negligible compared to the overhead of migration.

Troubleshooting Guide: SQL Server Error 10775

Why This Error Happens

This error occurs when a natively compiled stored procedure or module attempts to reference a table or inline table-valued function that is not memory-optimized. Natively compiled modules in SQL Server are restricted to accessing only memory-optimized tables and natively compiled inline table-valued functions, so any reference to a disk-based table within this context will fail. This typically happens after a schema change, a table migration, or a copy-paste error where a disk-based table name was used instead of its memory-optimized counterpart.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object in the Module

• Open the natively compiled stored procedure or function definition using SQL Server Management Studio (SSMS).
• Locate the exact object name referenced in the error message (the %.*ls placeholder will show the real name in your error log).
• Cross-reference this object in sys.tables to check its is_memory_optimized column value:

  SELECT name, is_memory_optimized 
  FROM sys.tables 
  WHERE name = 'YourTableName';

Step 2: Convert or Correct the Table Reference

• If the table was intended to be memory-optimized but isn’t, you will need to migrate it. Since you cannot ALTER a disk-based table into a memory-optimized one directly, create a new memory-optimized table with the MEMORY_OPTIMIZED = ON clause.
• Example syntax for the new table:

  CREATE TABLE dbo.NewMemOptTable
  (
      [ID] INT NOT NULL PRIMARY KEY NONCLUSTERED,
      [Data] NVARCHAR(100) NULL
  )
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

• Migrate the data from the old disk-based table to the new memory-optimized table using an INSERT INTO... SELECT statement.
• If the reference was simply a typo or incorrect join, edit the T-SQL code in the natively compiled module to point to the correct existing memory-optimized table.

Step 3: Recompile the Natively Compiled Module

• Since natively compiled modules are compiled into native DLLs upon creation, you must drop and recreate the procedure after fixing the table reference.
• Use DROP PROCEDURE followed by CREATE PROCEDURE with the corrected WITH NATIVE_COMPILATION syntax.
• Ensure the SCHEMABINDING option remains intact during the recreation to lock the schema dependency correctly.

Step 3: Verify Database Compatibility Level

• Check that the database compatibility level supports the memory-optimized features you are trying to use on SQL Server 2025.
• Run the following command to check the current level:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName';

• If necessary, update it to the latest supported level for Windows Server 2025 environments.

Alternative or Verification Steps

• Verification: Execute the natively compiled stored procedure manually via SSMS after the fix. If the code compiles and executes without throwing Error 10775, the dependency has been resolved successfully.
• Alternative: If migrating the table to memory-optimized is not feasible due to unsupported data types (like XML or CLR types), rewrite the specific procedure as a standard (interpreted) T-SQL stored procedure instead of a natively compiled one, as interpreted T-SQL can freely access both disk-based and memory-optimized tables.

Why This Error Happens

Error 10774 occurs when the WHERE clause defined in a CREATE INDEX statement for a filtered index contains an expression that SQL Server 2025’s query optimizer cannot resolve into a simple, deterministic comparison. This typically happens when the predicate references non-deterministic functions, implicit data type conversions, or comparison operators (such as <>, NOT IN, or OR) that are not supported in filtered index definitions. It can also occur if the column referenced in the filter is computed, nullable in an unsupported way, or part of a schema that was altered after the index was created, leaving metadata inconsistent.

Step-by-Step Resolution Guide

Step 1: Review the Filtered Index Definition

• Open SQL Server Management Studio (SSMS) and script out the failing index using sp_helpindex 'YourTableName' or by right-clicking the index and selecting Script Index as > CREATE To.
• Check the WHERE clause for unsupported operators. Filtered indexes only support simple comparison operators: =, <>, >, <, >=, <=, IS NULL, and IS NOT NULL, combined only with AND.
• Remove any use of OR, BETWEEN, IN, subqueries, or non-deterministic functions like GETDATE() or NEWID() from the predicate.

Step 2: Correct Data Type and Column References

• Ensure every column referenced in the WHERE clause exists in the base table and is not a computed column referencing non-deterministic logic.
• Explicitly cast literal values to match the column’s data type to avoid implicit conversion errors, for example:

  WHERE [Status] = CAST(1 AS tinyint)

• Avoid referencing columns that are part of a different schema or have been recently altered; run sp_help 'YourTableName' to confirm current column metadata.

Step 3: Drop and Recreate the Index with a Valid Predicate

• Drop the problematic index:

  DROP INDEX IX_FilteredIndex_Name ON dbo.YourTableName;

• Recreate it using a corrected, simplified predicate:

  CREATE NONCLUSTERED INDEX IX_FilteredIndex_Name
  ON dbo.YourTableName (ColumnA)
  WHERE ColumnB = 1 AND ColumnC IS NOT NULL;

• Execute the statement in a test environment first to confirm Error 10774 no longer appears.

Step 4: Update Database Compatibility Level (If Applicable)

• Confirm the database compatibility level supports filtered indexes (SQL Server 2008 or later, compatibility level 100+):

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If outdated, update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;

Alternative or Verification Steps

• After recreating the index, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS to confirm no corruption remains and the new index integrates correctly with existing statistics.
• If the error persists, use SELECT * FROM sys.indexes WHERE name = 'IX_FilteredIndex_Name' alongside sys.index_columns to manually inspect filter definitions at the metadata level and isolate any residual invalid expression.

Troubleshooting Guide: Error 10773 – REMOTEDATAARCHIVE_OVERRIDE Hint Not Applicable

Why This Error Happens

This error occurs when a query explicitly specifies the REMOTE_DATA_ARCHIVE_OVERRIDE table hint against a table that does not have Stretch Database (Remote Data Archive) enabled. SQL Server validates this hint at parse time, and since the target object has no cold-data archive relationship configured in Azure or a linked remote endpoint, the hint has nothing to override. This typically happens after a table’s Stretch configuration is disabled, migrated, or was never properly enabled despite the application code still referencing it.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Object

• Capture the exact T-SQL statement from the error log or SQL Server Management Studio (SSMS) output.
• Note the object name (%.*ls) referenced in the error message—this is the table the hint is incorrectly applied to.
• Run the following to confirm the table’s Stretch status:

SELECT name, is_remote_data_archive_enabled 
FROM sys.tables 
WHERE name = 'YourTableName';

Step 2: Remove or Correct the Hint in the Query

• Open the stored procedure, view, or application query containing the hint.
• Locate the clause similar to:

SELECT * FROM YourTable WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = ...)

• Remove the entire WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = ...) clause if the table is not meant to use Stretch Database.
• Save and redeploy the corrected script to the SQL Server 2025 instance.

Step 3: Verify Stretch Database Configuration (If Hint Was Intentional)

• If the table was supposed to have Remote Data Archive enabled, check whether Stretch was disabled accidentally:

EXEC sys.sp_help 'YourTableName';

• Re-enable Stretch Database on the table via SSMS: right-click the table → Tasks → Stretch → Enable.
• Ensure the linked Azure SQL Database endpoint credentials are still valid under Database Properties → Stretch Database settings.

Step 4: Clear Cached Execution Plans

• After modifying the query or table configuration, clear any stale cached plans referencing the old hint:

DBCC FREEPROCCACHE;

• This prevents SQL Server from reusing an invalid execution plan tied to the removed hint.

Step 5: Restart Dependent Services (If Applicable)

• On Windows Server 2025 / Windows 11, restart the SQL Server service via Services.msc (SQL Server (MSSQLSERVER)) if the application connection pool is caching old query plans.
• Confirm the SQL Server Agent service is also running if scheduled jobs reference the affected table.

Alternative or Verification Steps

• Re-run the original query without the REMOTE_DATA_ARCHIVE_OVERRIDE hint and confirm it executes successfully without Error 10773.
• If Stretch Database functionality is still required, validate end-to-end by querying the table and confirming cold-tier rows are retrieved from the remote Azure endpoint using sys.remote_data_archive_tables.

Troubleshooting Guide: Error 10772 – Multiple REMOTEDATAARCHIVE_OVERRIDE Hints Specified

Why This Error Happens

This error occurs when a T-SQL query references the same table or view with more than one REMOTE_DATA_ARCHIVE_OVERRIDE table hint in a single statement. SQL Server 2025’s query parser only permits a single instance of this hint per table reference because it dictates a specific, singular behavior (such as MIGRATION_STATE = DISABLED or TARGET = STANDBY) for Stretch Database or data archive operations. The conflict typically arises from manually written queries, auto-generated ORM scripts, or copy-paste errors where hints were duplicated during query modification.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Query

• Identify the exact query that triggered Error 10772 by checking the SQL Server Error Log or the application’s query execution trace.
• Use SQL Server Management Studio (SSMS) on Windows Server 2025 to run SELECT * FROM sys.dm_exec_query_stats cross-applied with sys.dm_exec_sql_text to pinpoint the exact batch text if the error was logged without the full query.
• Search the query text specifically for the string REMOTE_DATA_ARCHIVE_OVERRIDE to find all instances.

Step 2: Audit the Table Hints Syntax

• Examine the WITH ( ) clause following the table or view reference in the FROM or JOIN section of the query.
• Look for duplicate entries, such as:
  FROM dbo.Orders WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = DISABLE, REMOTE_DATA_ARCHIVE_OVERRIDE = STANDBY)
• Confirm whether the duplication exists within a single WITH clause or across multiple aliases referencing the same physical table.

Step 3: Remove the Redundant Hint

• Edit the query to retain only the single, correct hint required for the intended operation (e.g., MIGRATION_STATE, DISABLE, or STANDBY).
• If the query was generated dynamically (e.g., via a stored procedure variable or application code), check the source code logic to ensure the hint-building function does not append the hint twice during loop iterations.
• Save the corrected script and resubmit it to the SQL Server 2025 instance.

Step 4: Clear the Query Cache (If Using a Cached Plan)

• If the application is using a parameterized query or stored procedure that was previously cached with the faulty syntax, execute DBCC FREEPROCCACHE to clear the plan cache.
• Alternatively, target the specific plan handle using DBCC FREEPROCCACHE (plan_handle) to avoid flushing the entire server cache in a production environment.

Alternative or Verification Steps

• Verification: Re-run the corrected query in SSMS or Azure Data Studio connected to your Windows 11 or Windows Server 2025 environment; a successful execution (or a different, unrelated result set) confirms the hint conflict is resolved.
• Alternative: If the error persists despite a clean query, check for views or synonyms with embedded hints in their definition, as a hint in the base view combined with a hint in the outer query can trigger this error even if the outer syntax appears correct.

Troubleshooting Guide: Error 10771 – CREDENTIAL Option Cannot Be Used with FEDERATEDSERVICEACCOUNT = ON

Why This Error Happens

This error occurs because SQL Server 2025 does not allow a login to be explicitly mapped to a credential (such as a mapped certificate, asymmetric key, or external credential object) while simultaneously using FEDERATED_SERVICE_ACCOUNT = ON. The FEDERATED_SERVICE_ACCOUNT option is designed to let SQL Server automatically leverage the Windows service account’s identity for Kerberos-constrained delegation or Azure AD/Entra ID federated authentication, which inherently conflicts with manually assigning a separate credential for authentication. Essentially, the engine cannot resolve two competing identity-mapping mechanisms (manual credential vs. automatic federated identity) for the same login object, so it rejects the T-SQL statement outright.

Step-by-Step Resolution Guide

Step 1: Review the Login Creation Script

• Open the CREATE LOGIN or ALTER LOGIN T-SQL script that triggered Error 10771.
• Look specifically for the coexistence of WITH CREDENTIAL = <credential_name> and FEDERATED_SERVICE_ACCOUNT = ON in the same statement.
• Example of the problematic syntax:

  CREATE LOGIN [DOMAIN\SQLServiceAccount] 
  WITH FEDERATED_SERVICE_ACCOUNT = ON, CREDENTIAL = MyCredential;

Step 2: Decide Which Authentication Mechanism You Actually Need

• If you want SQL Server to use the Windows Service Account identity for delegation (common in Kerberos or Entra ID hybrid scenarios), keep FEDERATED_SERVICE_ACCOUNT = ON and remove the CREDENTIAL clause entirely.
• If you need a specific credential (e.g., for mapping to an external file location, Azure Blob Storage, or a specific certificate/key), set FEDERATED_SERVICE_ACCOUNT = OFF (or omit it, as OFF is default) and keep the CREDENTIAL clause.

Step 3: Modify the T-SQL Statement

• Rewrite the statement to include only one of the two options.
• For a federated identity login:

  CREATE LOGIN [DOMAIN\SQLServiceAccount] 
  WITH FEDERATED_SERVICE_ACCOUNT = ON;

• For a credential-based login:

  CREATE LOGIN [DOMAIN\SQLServiceAccount] 
  WITH CREDENTIAL = MyCredential;

Step 4: Apply the Change via SQL Server Management Studio (SSMS) or Configuration Manager

• If the login already exists, use ALTER LOGIN instead of CREATE LOGIN to update the configuration without dropping the login.
• Navigate to SQL Server Configuration Manager > SQL Server Services, right-click the instance, and verify the service account settings align with your chosen authentication path (federated vs. credential-mapped).
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) if you changed the service account properties at the OS level.

Step 5: Validate Group Policy and Kerberos Settings (If Using Federated Account)

• If choosing FEDERATED_SERVICE_ACCOUNT = ON, confirm the Windows Server 2025 domain has the Group Managed Service Account (gMSA) correctly configured in Active Directory.
• Run Get-ADServiceAccount -Identity <gMSA_Name> -Properties * in PowerShell on a Domain Controller to confirm the account is enabled and linked to the correct SPNs.

Alternative or Verification Steps

• Verify the Fix: Re-run the corrected CREATE LOGIN/ALTER LOGIN script in SSMS; Error 10771 should no longer appear, and you can confirm success by querying SELECT name, type_desc FROM sys.server_principals WHERE name = 'DOMAIN\SQLServiceAccount';.
• Alternative Approach: If you need both delegation and credential-based external access, create two separate logins—one federated for internal Windows/Entra ID authentication, and a second contained login or proxy account mapped to the required credential for external resource access.

Troubleshooting Guide: Error 10770 – REMOTEDATAARCHIVE Configuration Failure in SQL Server 2025

Why This Error Happens

This error occurs when the ALTER DATABASE statement attempts to enable REMOTE_DATA_ARCHIVE without properly specifying the required SERVER option alongside either a CREDENTIAL or FEDERATED_SERVICE_ACCOUNT = ON clause. SQL Server 2025 enforces this dependency because Stretch Database and Remote Data Archive functionality rely on an authenticated link to the remote Azure endpoint, and without both a target server and a valid authentication method, the engine cannot establish or validate that connection. This is typically a syntax or script-authoring oversight rather than a system-level permissions or corruption issue.

Step-by-Step Resolution Guide

Step 1: Review the Current ALTER DATABASE Syntax

• Open the T-SQL script or job that triggered the error in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.
• Confirm whether the SERVER = '<server_name>' clause is present in the WITH options of the REMOTE_DATA_ARCHIVE statement.
• Check if either CREDENTIAL = <credential_name> or FEDERATED_SERVICE_ACCOUNT = ON is included; missing both is the direct cause of Error 10770.

Step 2: Create or Verify the Required Database Credential

• If using CREDENTIAL, ensure it exists by running: SELECT * FROM sys.database_credentials;
• If it does not exist, create one with: CREATE DATABASE CREDENTIAL [RDA_Credential] WITH IDENTITY = '<identity>', SECRET = '<secret>';
• Verify the credential maps correctly to your Azure SQL remote endpoint used for data archiving.

Step 3: Correct and Re-run the ALTER DATABASE Statement

• Rewrite the statement to include both required elements, for example:
• ALTER DATABASE [YourDB] SET REMOTE_DATA_ARCHIVE = ON (SERVER = 'yourserver.database.windows.net', CREDENTIAL = [RDA_Credential]);
• Alternatively, for federated identity scenarios, use FEDERATED_SERVICE_ACCOUNT = ON instead of CREDENTIAL if your environment uses Windows Server 2025 integrated authentication with Azure AD.

Step 4: Validate Network and Firewall Configuration

• Confirm Windows Server 2025 firewall rules allow outbound traffic on port 1433 to the Azure remote endpoint.
• Check that the server name specified matches the fully qualified domain name (FQDN) of the remote Azure SQL Database used for archiving.

Alternative or Verification Steps

• After re-running the corrected script, confirm success by querying sys.databases and checking the is_remote_data_archive_enabled column equals 1 for your target database.
• If the error persists, drop and recreate the database credential using DROP DATABASE CREDENTIAL followed by CREATE DATABASE CREDENTIAL, ensuring the secret and identity values exactly match your Azure remote server’s authentication requirements.

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.

Troubleshooting Guide: Error 10768 – LABEL Hint Used Multiple Times in Query

Why This Error Happens

Error 10768 occurs when a T-SQL query references the same LABEL query hint more than once within a single statement, which violates SQL Server’s query optimizer rules for hint scoping. This typically happens when queries are dynamically constructed, copy-pasted from templates, or merged from multiple sources without proper validation of existing hints. SQL Server 2025 enforces stricter parsing on the OPTION (LABEL = 'string') clause to prevent ambiguous query plan caching and Query Store tracking conflicts.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Query

• Identify the exact query batch returning Error 10768 using SQL Server Management Studio (SSMS) or Azure Data Studio.
• Check the Messages tab in SSMS for the specific line number referenced in the error output.
• If the query was generated by an application (e.g., via Entity Framework, Dapper, or a custom ORM), capture the raw SQL using SQL Server Profiler or Extended Events (sqlserver.sql_batch_completed).

Step 2: Inspect the OPTION Clause for Duplicate LABEL Hints

• Scroll to the end of the query and locate the OPTION (...) clause.
• Search specifically for multiple instances of LABEL = 'your_label_name' within the same OPTION block or across nested subqueries that get flattened into one execution plan.
• Example of the problematic pattern:

  SELECT * FROM dbo.Orders
  OPTION (LABEL = 'ReportQuery', LABEL = 'ReportQuery_v2');

Step 3: Remove or Consolidate the Duplicate Hint

• Delete all but one LABEL declaration from the OPTION clause.
• If the query is dynamically built in application code (common in C# string builders or stored procedures using sp_executesql), add a check to strip existing LABEL hints before appending a new one.
• Corrected example:

  SELECT * FROM dbo.Orders
  OPTION (LABEL = 'ReportQuery_v2');

Step 4: Update Stored Procedures and Dynamic SQL Generators

• Open the stored procedure or application layer responsible for generating the query (commonly found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Jobs\ for scheduled jobs on Windows Server 2025).
• Search for string concatenation logic that appends OPTION (LABEL = ...) without first checking if a label already exists in the base query template.
• Implement a regex or string-replace check to ensure only one LABEL hint persists before execution.

Step 5: Re-run the Query in a Test Environment

• Execute the corrected query in a non-production database first to confirm Error 10768 no longer appears.
• Validate that the query plan is cached correctly by checking sys.query_store_query for the expected label metadata.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) to confirm the corrected query is now executing and being cached without hint conflicts.
• If the error persists in automated jobs, temporarily disable Query Store label tracking via ALTER DATABASE [YourDB] SET QUERY_STORE = OFF;, re-run the query to isolate whether the issue is hint-based or Query Store-related, then re-enable it once confirmed.

Troubleshooting Guide: SQL Server Error 10767 — DISTRIBUTED_AGG Hint with Duplicate GROUP BY Columns

Why This Error Happens

This error occurs because the SQL Server 2025 query optimizer cannot resolve the DISTRIBUTED_AGG hint when the GROUP BY clause references the same column more than once. The hint relies on a one-to-one mapping between grouping columns and aggregation buckets, and duplicate column references in the GROUP BY list break this mapping logic. This is a query syntax issue rather than a server configuration, permissions, or networking problem, meaning the fix must happen at the T-SQL code level.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open the query that triggered Error 10767 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the GROUP BY clause and check for repeated column names (e.g., GROUP BY CustomerID, OrderDate, CustomerID).
• Use Ctrl+F within the query editor to search for duplicate column tokens if the query is long.

Step 2: Remove Duplicate Columns from GROUP BY

• Edit the GROUP BY clause so each column appears only once.
• Example fix:
• Incorrect: GROUP BY CustomerID, Region, CustomerID
• Corrected: GROUP BY CustomerID, Region
• If the duplication was intentional (e.g., for grouping sets), replace it with GROUPING SETS, ROLLUP, or CUBE instead of repeating the column.

Step 3: Validate the DISTRIBUTED_AGG Hint Placement

• Confirm the hint is applied correctly using the syntax: OPTION (USE HINT('DISTRIBUTED_AGG')) or the applicable hint syntax for SQL Server 2025.
• Ensure the hint is placed at the end of the query, after the GROUP BY and HAVING clauses, not inline within the SELECT statement.

Step 4: Re-run and Monitor Execution

• Execute the corrected query in SSMS.
• Check the Messages tab for confirmation that Error 10767 no longer appears.
• Review the Actual Execution Plan to confirm the DISTRIBUTED_AGG hint was successfully applied.

Step 5: Update Application or ORM-Generated Queries

• If the query was auto-generated by an ORM (e.g., Entity Framework) or a reporting tool, inspect the generated SQL for duplicate GROUP BY entries.
• Update the application’s LINQ/query logic or stored procedure to prevent the tool from re-emitting the same column twice.

Alternative or Verification Steps

• Run SELECT * FROM sys.messages WHERE message_id = 10767; in SSMS to confirm the error description and severity level match what you encountered, ensuring you’re troubleshooting the correct issue.
• As a quick verification, temporarily remove the DISTRIBUTED_AGG hint and run the query without it — if it executes successfully, this confirms the duplicate GROUP BY column was the root cause, and you can safely reapply the hint after cleanup.

Why This Error Happens

Error 10766 is thrown by the SQL Server 2025 query parser because CENTRAL_AGG is a new GROUP BY modifier (introduced to support centralized aggregation pushdown in distributed/columnstore scenarios) that has a strict positional requirement — it must trail every grouping column, similar to legacy WITH ROLLUP or WITH CUBE syntax. This typically happens when a query is migrated from an older compatibility level script, when CENTRAL_AGG is placed before or in the middle of the column list, or when an ORM (like Entity Framework) dynamically builds the GROUP BY clause without accounting for the new ordering rule. It can also surface after a database is upgraded to compatibility level 170 without updating legacy stored procedures.

Step-by-Step Resolution Guide

Step 1: Capture the Exact Failing Query

• Open SQL Server Management Studio (SSMS) and check the Messages tab for the full query batch that triggered Error 10766.
• If the error originates from an application, enable an Extended Events session filtered on error_reported with error_number = 10766 to capture the exact T-SQL text.
• Note the full GROUP BY clause, including any CENTRAL_AGG, WITH ROLLUP, or GROUPING SETS keywords present.

Step 2: Correct the GROUP BY Clause Syntax

• Relocate CENTRAL_AGG so it appears strictly after all grouping columns.
• Example fix:
• Incorrect: GROUP BY CENTRAL_AGG, Column1, Column2
• Correct: GROUP BY Column1, Column2 CENTRAL_AGG
• Remove any conflicting legacy modifiers (WITH ROLLUP / WITH CUBE) mixed in before CENTRAL_AGG, as they cannot coexist in the same clause position.

Step 3: Update Stored Procedures and Views

• Use sp_helptext 'YourProcedureName' to extract the current procedure definition.
• Search for any GROUP BY statements referencing CENTRAL_AGG and apply the corrected ordering from Step 2.
• Redeploy the updated procedure using ALTER PROCEDURE and recompile with EXEC sp_recompile 'YourProcedureName'.

Step 4: Check Application/ORM Query Generation

• If the query is generated dynamically (e.g., Entity Framework Core, Dapper, or a reporting tool), inspect the query-building logic for GROUP BY clause assembly.
• Update the code so any custom SQL fragments appending CENTRAL_AGG are concatenated last, after the full column list.
• Redeploy the application layer and clear any cached query plans with DBCC FREEPROCCACHE if testing locally.

Step 5: Validate Database Compatibility Level

• Run: SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabase';
• Confirm the database is at compatibility level 170 (SQL Server 2025). If not, update it with:
• ALTER DATABASE YourDatabase SET COMPATIBILITY_LEVEL = 170;
• Re-run the corrected query to confirm the new syntax rules are enforced correctly.

Alternative or Verification Steps

• Re-execute the corrected query directly in SSMS or Azure Data Studio and confirm the Messages pane no longer reports Error 10766, then verify the result set aggregates as expected.
• As a workaround if CENTRAL_AGG continues to fail in complex nested queries, replace it with standard GROUPING SETS syntax, which achieves similar aggregation behavior without the strict positional requirement.

Troubleshooting Guide: Error 10765 — DISTRIBUTEDAGG/CENTRALAGG Hints Incompatible with CUBE or ROLLUP

Why This Error Happens

Error 10765 occurs because the query optimizer in SQL Server 2025 cannot reconcile the aggregation hints DISTRIBUTED_AGG or CENTRAL_AGG with the expanded grouping sets generated by a CUBE or ROLLUP clause. These hints are designed to force a specific aggregation execution plan (distributed vs. centralized), but CUBE/ROLLUP require the engine to dynamically generate multiple grouping combinations, which conflicts with the rigid execution path the hint demands. This is a T-SQL syntax/logic restriction rather than a server configuration or permissions issue, meaning it cannot be resolved through service restarts or patching—only through query modification.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query Syntax

• Open the query that triggered Error 10765 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the GROUP BY clause and check for the presence of WITH CUBE or WITH ROLLUP.
• Search the same query block for OPTION (DISTRIBUTED_AGG) or OPTION (CENTRAL_AGG) in the hint section, typically found at the end of the statement.

Step 2: Remove or Isolate the Aggregation Hint

• Delete the DISTRIBUTED_AGG or CENTRAL_AGG hint from the OPTION() clause at the end of the query.
• Example of the problematic line to remove: OPTION (DISTRIBUTED_AGG)
• If the hint is required for performance reasons on other parts of a larger script, isolate the CUBE/ROLLUP logic into a separate query batch that does not include the hint.

Step 3: Refactor Using GROUPING SETS as an Alternative

• Replace GROUP BY column_a, column_b WITH ROLLUP with the more flexible GROUP BY GROUPING SETS ((column_a, column_b), (column_a), ()) syntax.
• Test if the DISTRIBUTED_AGG hint is compatible with GROUPING SETS, as this syntax sometimes allows for more granular optimizer control compared to the legacy CUBE/ROLLUP syntax.
• Save the modified query as a new stored procedure or script to prevent overwriting the original logic.

Step 4: Re-Execute and Check the Execution Plan

• Run the modified query in SSMS using Ctrl + M to include the “Actual Execution Plan.”
• Verify that the query compiles without throwing Error 10765 in the Messages tab.
• Check the execution plan XML to confirm whether the Database Engine defaulted to a CENTRAL_AGG or DISTRIBUTED_AGG strategy automatically based on cost estimation.

Alternative or Verification Steps

• Verification: Run the query with SET STATISTICS PROFILE ON to confirm the aggregation step now resolves correctly without hint-based errors, ensuring the logical aggregation operator matches your expected CUBE/ROLLUP output.
• Alternative: If the specific hint is mandatory for performance in a distributed topology, bypass the CUBE/ROLLUP syntax entirely by using UNION ALL to manually combine the different levels of aggregation required, allowing the hint to apply to each individual SELECT statement.

Troubleshooting Guide: SQL Server Error 10764 — Grouping Columns Limit Exceeded

Why This Error Happens

This error occurs when a single query references more columns in a GROUP BY, GROUPING SETS, CUBE, or ROLLUP clause than SQL Server’s internal limit of 64,000 (64k) grouping columns. This is typically not caused by a user manually listing that many columns, but rather by dynamically generated SQL, ORM-generated queries (such as Entity Framework or Hibernate), or BI/reporting tools that programmatically construct grouping sets and inadvertently create a cartesian-style explosion of grouping combinations. It can also happen when GROUPING SETS or CUBE/ROLLUP operators are nested or combined, causing SQL Server to internally expand the number of grouping column combinations far beyond the visible column list in the query text.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text using SQL Server Extended Events, the Query Store, or the application’s logging layer (set Include Actual Execution Plan off since the query will fail before execution).
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the full error context, including the session ID and application name tied to Error 10764.
• If the query is dynamically generated by an ORM or BI tool, enable SQL Server Profiler or sys.dm_exec_query_stats to trace the generated SQL text before it reaches the engine.

Step 2: Reduce or Restructure the Grouping Columns

• Manually inspect the GROUP BY, CUBE, ROLLUP, or GROUPING SETS clause and remove redundant or unnecessary columns.
• If using CUBE or ROLLUP, replace them with explicit GROUPING SETS listing only the specific combinations your report actually needs, rather than allowing SQL Server to generate every permutation.
• Break a single large aggregation query into multiple smaller queries using temp tables (#TempResults) or table variables, then UNION ALL the results together instead of grouping on all columns simultaneously.
• Example fix pattern:

  -- Instead of:
  SELECT ColA, ColB, ColC, SUM(Value)
  FROM SalesData
  GROUP BY CUBE(ColA, ColB, ColC);

  -- Use targeted grouping sets:
  SELECT ColA, ColB, ColC, SUM(Value)
  FROM SalesData
  GROUP BY GROUPING SETS ((ColA, ColB), (ColA, ColC), ());

Step 3: Fix the Application or Report Logic

• If the query originates from a BI tool (Power BI, SSRS, Tableau), check the dataset’s pivot or matrix configuration — wide pivots with many dynamic row/column fields often auto-generate excessive grouping columns.
• For ORM-generated queries (Entity Framework, NHibernate), review the LINQ GroupBy() clause and ensure it isn’t chaining multiple .GroupBy() calls or grouping on composite/navigation objects that expand into dozens of underlying columns.
• Update the application code or report definition to explicitly select only the required grouping dimensions rather than passing entire row objects into the grouping logic.

Step 4: Update SQL Server Cumulative Updates (If Applicable)

• Verify you are running the latest Cumulative Update (CU) for SQL Server 2025, since query optimizer handling of large GROUPING SETS expansions is occasionally refined in CU releases.
• Check your version with:

  SELECT @@VERSION;

• Download and apply the latest CU from the Microsoft Update Catalog if you are behind on patches.

Alternative or Verification Steps

• After rewriting the query, re-run it in SQL Server Management Studio (SSMS) and confirm it executes without Error 10764, then validate the result set row count matches expected business logic.
• As an alternative, if restructuring isn’t immediately feasible, use OPTION (RECOMPILE) combined with a reduced column set as a temporary workaround while the application team schedules a permanent fix to the query/report logic.

Troubleshooting Guide: SQL Server Error 10763

Why This Error Happens

Error 10763 occurs when a GROUP BY clause in a Transact-SQL query contains more than one query hint, such as combining WITH ROLLUP, WITH CUBE, or GROUPING SETS syntax incorrectly, or duplicating the same hint multiple times. SQL Server 2025’s query parser strictly enforces that only a single grouping hint can be applied per GROUP BY clause to maintain unambiguous execution plan generation. This is typically a T-SQL syntax issue introduced during manual query edits, ORM-generated queries, or migration scripts rather than a server configuration or permissions problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
• Check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs or query sys.messages for Error 10763 context.
• Locate the exact stored procedure, view, or ad-hoc query referenced in the error output pane.

Step 2: Review and Isolate the GROUP BY Hints

• Open the query text and locate the GROUP BY clause.
• Look for multiple occurrences of hints like:

  GROUP BY column1 WITH ROLLUP, column2 WITH CUBE

• Confirm whether GROUPING SETS, ROLLUP, or CUBE are being combined incorrectly in the same clause.

Step 3: Remove Duplicate or Conflicting Hints

• Rewrite the query to include only one hint type per GROUP BY clause:

  GROUP BY GROUPING SETS ((column1), (column2))

• If both rollup and cube behavior are needed, restructure using UNION ALL with separate GROUP BY statements instead of combining hints.
• Save the corrected script to C:\Scripts\SQLFixes\ for version tracking before re-deployment.

Step 4: Validate Application and ORM-Generated Queries

• If the query originates from an application (e.g., Entity Framework, Dapper, or a reporting tool), check the generated SQL in the application logs located typically at C:\inetpub\wwwroot\<App>\Logs\ on Windows Server 2025.
• Update the LINQ or query-builder logic to avoid stacking multiple grouping hints in a single aggregation call.

Step 5: Re-run and Deploy the Corrected Query

• Execute the revised query in SSMS to confirm successful execution without Error 10763.
• Redeploy the updated stored procedure or view to the production database using sqlcmd or a deployment pipeline:

  sqlcmd -S ServerName -d DatabaseName -i C:\Scripts\SQLFixes\fixed_query.sql

Alternative or Verification Steps

• Run the corrected query directly in SSMS and confirm the execution plan generates without syntax errors; check Messages tab for confirmation of row counts returned.
• If the error persists, use SET SHOWPLAN_TEXT ON before executing the query to inspect how SQL Server parses the GROUP BY clause and pinpoint any remaining hint conflicts.

Troubleshooting Guide: Error 10762 in SQL Server 2025

Why This Error Happens

This error occurs when a T-SQL query uses a function or syntax feature (such as certain sqrt, sequence-related calls, or other functions introduced in SQL Server 2012) that requires a database compatibility level of 110 or higher. The database in question is likely still running under an older compatibility level (100 or below) because it was migrated or restored from a legacy instance without updating this setting. SQL Server enforces these compatibility gates to prevent breaking changes when databases are upgraded from older versions.

Step-by-Step Resolution Guide

Step 1: Identify the Current Compatibility Level

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 / Windows 11 instance.
• Run the following query to check the current setting:

  SELECT name, compatibility_level 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Confirm whether the value returned is below 110 (e.g., 80, 90, or 100).

Step 2: Alter the Database Compatibility Level

• Execute the following command to raise the compatibility level to at least 110 (or higher, matching SQL Server 2025 standards, typically 160):

  ALTER DATABASE YourDatabaseName 
  SET COMPATIBILITY_LEVEL = 160;

• Ensure you run this command in the context of the master database or with USE master; executed first.

Step 3: Clear the Procedure Cache

• Since changing compatibility levels can leave old execution plans cached, flush the cache to prevent stale plan errors.
• Run the following command to force SQL Server to recompile queries using the new mode:

  DBCC FREEPROCCACHE;

Step 4: Restart the SQL Server Service (If Necessary)

• Navigate to the Services console on Windows Server 2025 by typing services.msc in the Run dialog.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart to ensure all session states reset the error.

Alternative or Verification Steps

• Verification: Re-run the original query or stored procedure that triggered Error 10762. If the compatibility level update was successful, the function should execute without the “not allowed in the current compatibility mode” error.
• Alternative: If you cannot upgrade the compatibility level due to legacy application dependencies, rewrite the query to avoid the specific function flagged in the error message, substituting it with a syntax compatible with the lower compatibility level.

Troubleshooting Guide: SQL Server Error 10761 – Invalid Data Type in Function

Why This Error Happens

Error 10761 occurs when a Transact-SQL statement passes a parameter of an incompatible or unsupported data type into a built-in or user-defined function. This is commonly caused by implicit conversion failures between newer data types introduced in SQL Server 2025 (such as extended vector or JSON-native types) and legacy functions that do not recognize them. It can also result from corrupted metadata in system catalogs after an in-place upgrade from a previous SQL Server version, or from a mismatched CLR assembly that references outdated type definitions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Function

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact query text and function name associated with Error 10761.
• Run DBCC INPUTBUFFER(<session_id>) to capture the exact statement if the error occurred in an active session.
• Note the specific data type mentioned in the error message (e.g., VECTOR, JSON, sql_variant).

Step 2: Validate Data Type Compatibility

• Cross-reference the data type against the SQL Server 2025 Data Type Compatibility Matrix (available in Microsoft Docs) to confirm the function supports it natively.
• If using a new SQL Server 2025 type (such as VECTOR(n)), ensure the database compatibility level is set correctly:

  ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170;

• Explicitly cast or convert the parameter to a supported type before passing it into the function:

  SELECT dbo.YourFunction(CAST(YourColumn AS NVARCHAR(MAX)));

Step 3: Rebuild or Refresh Corrupted Metadata

• If the error persists after upgrade, refresh module metadata to clear stale type bindings:

  EXEC sp_refreshsqlmodule 'dbo.YourFunction';

• For system-level corruption, run DBCC CHECKDB('YourDB') WITH DATA_PURITY to rule out catalog corruption.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear any cached execution plans referencing invalid types.

Step 4: Update CLR Assemblies and Drivers

• If the function is a CLR-based UDF, verify the assembly was recompiled against the SQL Server 2025 SDK.
• Update the SQL Server Native Client or ODBC Driver 19+ on the client machine (Windows 11) to ensure type mapping aligns with the server version.
• Reinstall the assembly using ALTER ASSEMBLY with the WITH PERMISSION_SET = SAFE clause if type bindings were lost.

Alternative or Verification Steps

• Verify the fix by re-running the original query in a test environment and confirming Error 10761 no longer appears in the Error Log or SSMS Messages pane.
• If the issue persists, create a minimal repro script isolating just the function call and data type, then compare execution plans using SET SHOWPLAN_XML ON to spot implicit conversion warnings.

Troubleshooting Guide: SQL Server Error 10760 – Invalid Scale Argument

Why This Error Happens

Error 10760 occurs when a data type declaration (such as DECIMAL, NUMERIC, DATETIME2, or TIME) includes a scale argument that is not a literal integer constant or a valid constant expression. This typically happens when a variable, column reference, function call, or an expression involving non-deterministic values is mistakenly passed as the scale parameter. SQL Server’s parser requires the scale to be resolvable at compile time, so any dynamic or non-constant value will trigger this failure before the query even executes.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

• Review the exact T-SQL script referenced in the error message or SQL Server Management Studio (SSMS) error pane.
• Look specifically for data type declarations like DECIMAL(p, s), NUMERIC(p, s), or DATETIME2(n) where s or n might be a variable.
• Check dynamic SQL blocks (sp_executesql or EXEC) since concatenated strings often hide invalid scale values.

Step 2: Replace Dynamic Values with Constants

• Ensure the scale argument is a hard-coded integer (e.g., DECIMAL(18, 2) instead of DECIMAL(18, @Scale)).
• If the scale must be dynamic, restructure the logic using Dynamic SQL to build the string and inject the integer value safely before execution:

  DECLARE @SQL NVARCHAR(MAX);
  DECLARE @Scale INT = 2;
  SET @SQL = N'CREATE TABLE dbo.TestTable (Amount DECIMAL(18, ' + CAST(@Scale AS NVARCHAR(10)) + N'))';
  EXEC sp_executesql @SQL;

• Avoid using functions (like GETDATE() or RAND()) directly inside the parentheses of a type declaration.

Step 3: Validate Compatibility Level and Syntax

• Open SSMS and right-click the database > Properties > Options to check the Compatibility Level.
• Since this involves SQL Server 2025, ensure the compatibility level is set to 170 (or the latest applicable level) to support newer constant-folding behaviors.
• Re-run the script in a new query window to rule out cached execution plan corruption by executing DBCC FREEPROCCACHE; (use with caution in production).

Step 4: Check for Corrupted Metadata or Extended Properties

• If the error persists on an existing table rather than a new script, verify the system catalog views using:

  SELECT name, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID('dbo.YourTableName');

• If the catalog shows NULL or invalid scale values, you may need to export the data and recreate the table schema from scratch.

Alternative or Verification Steps

• Verification: Execute the corrected script in a transaction (BEGIN TRAN... ROLLBACK) to confirm the table or variable creation succeeds without triggering Error 10760.
• Alternative: If using an ORM (like Entity Framework) or a migration tool, check the auto-generated migration scripts for incorrect parameter mapping, as ORMs sometimes pass string lengths as scale values incorrectly during type conversion.

Troubleshooting Guide: Error 10759 — “Use of DISTINCT is Not Allowed with the OVER Clause”

Why This Error Happens

This error occurs because SQL Server’s T-SQL syntax explicitly disallows combining the DISTINCT keyword with window functions that use the OVER clause (e.g., COUNT(DISTINCT column) OVER (...)). This is a parser-level restriction in the SQL Server 2025 Database Engine rather than a configuration, permissions, or file corruption issue. The query is rejected during the parsing/compilation phase before execution ever begins, meaning the fix must happen in the T-SQL code itself, not in server settings.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Locate the exact query in your application, stored procedure, or report that triggered Error 10759.
• Check SQL Server Management Studio (SSMS) or Azure Data Studio error output for the line number referenced.
• Look specifically for patterns like SUM(DISTINCT col) OVER (PARTITION BY ...) or COUNT(DISTINCT col) OVER (ORDER BY ...).

Step 2: Rewrite the Query Using a CTE or Subquery

• Replace the DISTINCT + OVER combination with a Common Table Expression (CTE) that pre-aggregates distinct values first.
• Example pattern:

  WITH DistinctValues AS (
      SELECT DISTINCT CustomerID, OrderAmount
      FROM Sales.Orders
  )
  SELECT CustomerID,
         SUM(OrderAmount) OVER (PARTITION BY CustomerID) AS TotalAmount
  FROM DistinctValues;

• This removes duplicates in a separate step before the window function is applied.

Step 3: Use APPLY or GROUP BY as an Alternative

• For scenarios needing distinct counts, consider using OUTER APPLY or CROSS APPLY with a correlated subquery instead.
• Example:

  SELECT o.OrderID,
         da.DistinctCount
  FROM Sales.Orders o
  OUTER APPLY (
      SELECT COUNT(DISTINCT CustomerID) AS DistinctCount
      FROM Sales.Orders o2
      WHERE o2.RegionID = o.RegionID
  ) da;

• This avoids the parser restriction entirely while achieving the same analytical result.

Step 4: Validate Compatibility Level Settings

• Confirm the database compatibility level under Database Properties > Options > Compatibility Level in SSMS.
• Even on SQL Server 2025, legacy compatibility levels (e.g., 130 or lower) do not change this specific restriction, but verifying ensures no other syntax-related errors are masked.
• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the current setting.

Alternative or Verification Steps

• After rewriting the query, execute it in SSMS or Azure Data Studio and confirm it returns results without raising Error 10759.
• If the issue persists, use SET SHOWPLAN_TEXT ON to review the execution plan and ensure no nested DISTINCT/OVER combinations remain in dynamically generated SQL (common in ORMs like Entity Framework).

Troubleshooting Guide: SQL Server Error 10758

Why This Error Happens

This error occurs because certain window (analytic) functions in T-SQL do not support an ORDER BY clause inside the OVER() clause by design, yet the query attempts to use one anyway. It is typically caused by a syntax mismatch where functions like AVG, SUM, MIN, MAX, or COUNT are used as aggregate window functions alongside an ORDER BY, which is only valid for ranking or offset functions (such as ROW_NUMBER, RANK, LAG, or LEAD) or when a frame specification is explicitly required. This is a query-authoring issue rather than a server configuration, permissions, or corruption problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Call

• Locate the exact line referenced by the error message '%.*ls' may not have ORDER BY in OVER clause.
• Check the stored procedure, view, or ad-hoc query currently executing against the Microsoft SQL Server 2025 instance.
• Use SQL Server Management Studio (SSMS) or Azure Data Studio to isolate the specific OVER() clause causing the failure.

Step 2: Remove or Relocate the ORDER BY Clause

• If the function is a simple aggregate (e.g., SUM(Column) OVER (ORDER BY Date)) being used only for a total (not a running total), remove the ORDER BY entirely: SUM(Column) OVER (PARTITION BY GroupID).
• If a running total or sequential calculation was the intent, ensure you are using it correctly with a frame clause: SUM(Column) OVER (ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
• Verify the function in question actually supports ordering; functions like CHECKSUM_AGG or GROUPING do not support ORDER BY under any configuration.

Step 3: Validate Function Compatibility List

• Cross-reference the function against Microsoft’s official documentation for “OVER Clause (Transact-SQL)” to confirm which functions require, allow, or disallow ORDER BY.
• Pay special attention to new T-SQL enhancements introduced in SQL Server 2025, as some legacy aggregate functions remain restricted despite the newer engine version.
• Update any linked server queries or ORM-generated SQL (e.g., Entity Framework) that may be auto-generating incompatible OVER() syntax.

Step 4: Rewrite Using a Subquery or CTE (If Required)

• If the business logic absolutely requires ordering on a restricted aggregate function, refactor the query using a Common Table Expression (CTE) to pre-sort the data before applying the aggregate.
• Example: Create a CTE that selects ROW_NUMBER() OVER (ORDER BY Date) first, then join back to apply the aggregate function in the outer query.

Alternative or Verification Steps

• Run the corrected query in SSMS and confirm execution completes without raising Error 10758 in the Results or Messages pane.
• If the error persists in a compiled stored procedure, drop and recreate the procedure to ensure the query plan cache isn’t referencing an old, cached execution plan with the invalid syntax.

Troubleshooting Guide: SQL Server Error 10757

Why This Error Happens

This error occurs when a T-SQL query attempts to apply the WITHIN GROUP clause to an aggregate or analytic function that does not support ordered-set semantics. In SQL Server, only specific functions like STRING_AGG, PERCENTILE_CONT, and PERCENTILE_DISC are designed to accept WITHIN GROUP; applying it to standard aggregates like SUM, AVG, or COUNT triggers this parsing failure. This is typically a query syntax issue rather than a server configuration or permissions problem, often introduced during manual query writing or a flawed database migration script.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function in the Query

• Open the query that triggered Error 10757 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Locate the specific function referenced in the error message (the %.*ls placeholder will be replaced with the actual function name, e.g., SUM or AVG).
• Check the exact syntax: SELECT SUM(SalesAmount) WITHIN GROUP (ORDER BY SalesDate) FROM Sales; is an example of invalid syntax that causes this error.

Step 2: Validate Function Compatibility with WITHIN GROUP

• Cross-reference the function against the Microsoft documentation list of supported WITHIN GROUP functions for SQL Server 2025.
• Confirm you are only using this clause with STRING_AGG(), PERCENTILE_CONT(), or PERCENTILE_DISC().
• If you intended to use a standard aggregate function, remove the WITHIN GROUP clause entirely, as it is not required for functions like SUM or COUNT.

Step 3: Rewrite the Query Using Correct Syntax

• If sorting logic was the goal for a standard aggregate, refactor the query to use a subquery or a ROW_NUMBER() window function instead, since standard aggregates do not support ordered input via WITHIN GROUP.
• For STRING_AGG, ensure the syntax follows the correct pattern: SELECT STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName ASC) FROM Products;
• Save the corrected script and clear the query cache in SSMS by closing and reopening the query tab to ensure no stale execution plan is cached.

Step 4: Check for Deprecated or Migrated Code

• If this error surfaced after a migration from an older SQL Server version (such as 2016 or 2019), search your migration scripts for legacy syntax that may have been auto-generated incorrectly by a third-party tool.
• Use Ctrl+Shift+F in SSMS to search across all open script files for the string WITHIN GROUP to catch multiple instances in large stored procedures.

Alternative or Verification Steps

• Execute the corrected query in a test environment first; a successful execution with no Error 10757 and a valid result set confirms the fix.
• If the error persists, run sp_helptext '<ProcedureName>' to inspect the actual stored procedure definition on the server, as the cached version might differ from your local edit if deployment didn’t complete successfully.

Troubleshooting Guide: Error 10756 – Window Frame Requires ORDER BY Clause

Why This Error Happens

This error occurs because SQL Server 2025’s query parser requires an explicit ORDER BY clause within the OVER() clause whenever a window frame (ROWS or RANGE) is specified. Window frames define a logical subset of rows relative to the current row, and without a defined sort order, SQL Server cannot deterministically calculate which rows fall before, after, or within that frame. This typically surfaces after migrating T-SQL scripts from older engines or ORMs that auto-generate window functions without validating syntax compliance against the latest Database Engine parser rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact line number referenced in the Error 10756 stack trace.
• Look specifically for aggregate or analytic functions (e.g., SUM(), AVG(), COUNT()) that include a ROWS BETWEEN or RANGE BETWEEN clause.
• Example of broken syntax: SUM(Sales) OVER (PARTITION BY Region ROWS BETWEEN 1 PRECEDING AND CURRENT ROW).

Step 2: Inject a Valid ORDER BY Clause into the OVER() Function

• Modify the window function to include an ORDER BY subclause immediately before the frame specification.
• Ensure the column used for ordering exists in the base table and is deterministic (avoid NEWID() or non-deterministic functions).
• Corrected syntax: SUM(Sales) OVER (PARTITION BY Region ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW).

Step 3: Audit Stored Procedures for Compatibility Level Conflicts

• Navigate to the database properties in SSMS by right-clicking the Database > Properties > Options.
• Check the Compatibility Level; if the database was recently upgraded to SQL Server 2025 (160), stricter ANSI parsing rules may now flag previously “tolerated” syntax errors.
• Run sys.sp_helptext 'YourProcedureName' to export the logic and manually patch any missing ORDER BY clauses in legacy windowing code.

Step 4: Recompile and Clear the Execution Plan Cache

• Once the T-SQL syntax is corrected, force a recompile to ensure Windows Server 2025 isn’t reading a cached, invalid execution plan.
• Execute the following command in a query window: EXEC sp_recompile 'YourTableOrProcName';
• Alternatively, clear the specific plan cache using DBCC FREEPROCCACHE (plan_handle); to avoid a full server cache flush in a production Windows 11 development environment.

Alternative or Verification Steps

• Verification: Re-run the query in SSMS; a successful execution with no red squiggly lines in the IntelliSense or syntax errors in the Messages tab confirms the fix.
• Alternative: If ORDER BY cannot logically be applied to the business data (e.g., unordered sets), remove the ROWS/RANGE frame specification entirely and rely on the default window frame behavior, provided this does not alter the required business logic for the aggregation.

Troubleshooting Guide: SQL Server Error 10755

Why This Error Happens

Error 10755 occurs when a T-SQL statement calls a built-in or user-defined function with an incorrect number of arguments, violating the function’s defined signature. This is typically caused by a syntax mistake in recently modified or migrated code, a mismatched function overload, or a stored procedure/script that was written against a different version of a function definition (e.g., after an in-place upgrade to SQL Server 2025 where a function signature changed). In some cases, corrupted or outdated cached execution plans on Windows Server 2025 / Windows 11 environments can also reference stale function metadata, triggering this error incorrectly.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Function and Statement

• Open SQL Server Management Studio (SSMS) and locate the full error message in the Messages tab, noting the function name and the batch/line number.
• Run the failing query in isolation to confirm reproducibility.
• Use SELECT OBJECT_DEFINITION(OBJECT_ID('YourFunctionName')) to inspect the function’s current definition if it is user-defined.

Step 2: Validate the Function Signature

• Compare the number of arguments in your calling code against the official syntax.
• For system functions, check Microsoft’s official SQL Server 2025 documentation, as argument requirements may have changed between versions (e.g., CONCAT, FORMAT, JSON_OBJECT, etc.).
• For user-defined functions (UDFs), run:

  SELECT ROUTINE_NAME, DATA_TYPE, ORDINAL_POSITION, PARAMETER_NAME
  FROM INFORMATION_SCHEMA.PARAMETERS
  WHERE SPECIFIC_NAME = 'YourFunctionName';

• Correct the calling statement to match the expected minimum/maximum argument count shown in the error message.

Step 3: Clear Cached Execution Plans (If Applicable)

• If the function was recently altered but the error persists, clear stale plan cache entries:

  DBCC FREEPROCCACHE;

• Alternatively, target a specific plan using DBCC FREEPROCCACHE (plan_handle) to avoid a full cache flush on production servers.
• Restart the SQL Server (MSSQLSERVER) service via Services.msc on Windows Server 2025 if the issue persists after cache clearing.

Step 4: Recompile and Redeploy the Function

• If the function is user-defined, drop and recreate it with the corrected parameter list:

  DROP FUNCTION IF EXISTS dbo.YourFunctionName;
  GO
  CREATE FUNCTION dbo.YourFunctionName (@Param1 INT, @Param2 VARCHAR(50))
  RETURNS INT
  AS
  BEGIN
      -- function body
  END;

• Redeploy any dependent stored procedures or views that reference this function using sp_refreshsqlmodule.

Step 5: Check Application/ORM Layer

• If the call originates from an application (e.g., Entity Framework, a .NET service, or a reporting tool), verify that the ORM-generated query matches the updated function signature.
• Update any hardcoded SQL strings in application code and redeploy the application on Windows 11 development machines or Windows Server 2025 hosts.

Alternative or Verification Steps

• Re-run the originally failing query/stored procedure and confirm no Error 10755 appears in SSMS or the SQL Server Error Log (via EXEC sp_readerrorlog).
• As a quick verification, use sys.dm_exec_describe_first_result_set or sp_help 'FunctionName' to confirm the function now accepts the correct argument range before rolling the fix out to production.

Troubleshooting Guide: SQL Server Error 10754 — Missing WITHIN GROUP Clause

Why This Error Happens

This error occurs when a T-SQL query calls an ordered-set aggregate function—such as PERCENTILE_CONT, PERCENTILE_DISC, STRING_AGG, or LISTAGG—without including the mandatory WITHIN GROUP (ORDER BY ...) clause. SQL Server 2025’s parser strictly enforces this syntax because these functions require an explicit ordering definition to calculate percentile ranks or concatenation order correctly. This is typically a query-authoring mistake, a leftover artifact from migrating code from another RDBMS (like Oracle or PostgreSQL), or a side effect of an ORM tool generating malformed SQL.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from the error message or SQL Server Management Studio (SSMS) error pane.
• Check the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the full stack trace if the query was triggered by an application rather than a manual script.
• Note the specific function name referenced in the error (e.g., PERCENTILE_CONT, STRING_AGG).

Step 2: Add the Required WITHIN GROUP Clause

• Modify the query to include WITHIN GROUP (ORDER BY column_name [ASC|DESC]) immediately after the function call.
• Example fix for a percentile calculation:

SELECT DISTINCT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SalesAmount)
    OVER (PARTITION BY Region) AS MedianSales
FROM dbo.SalesData;

• Example fix for string aggregation:

SELECT STRING_AGG(ProductName, ', ') WITHIN GROUP (ORDER BY ProductName ASC)
FROM dbo.Products;

Step 3: Audit Application and ORM-Generated Queries

• If the error originates from an application (not ad-hoc SSMS queries), check the data access layer—Entity Framework, Dapper, or a custom stored procedure wrapper—for auto-generated SQL that omits the clause.
• Update stored procedures on the server: open SSMS > Databases > YourDB > Programmability > Stored Procedures, locate the procedure using sp_helptext ProcedureName, and patch the syntax.
• Redeploy the corrected stored procedure using ALTER PROCEDURE rather than dropping and recreating it to preserve permissions.

Step 4: Validate Compatibility Level Settings

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database isn’t running in an older compatibility mode that mishandles newer ordered-set functions.
• If needed, update it with ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; to align with SQL Server 2025 standards.

Alternative or Verification Steps

• Re-run the corrected query in SSMS or Azure Data Studio and confirm it executes without raising Error 10754; check the Messages tab for a clean completion status.
• If the error persists in application logs despite a fixed query, clear the SQL Server Plan Cache using DBCC FREEPROCCACHE; to ensure no stale execution plan referencing the old malformed syntax is being reused.

Troubleshooting Guide: SQL Server Error 10753 — “The function ‘%.*ls’ must have an OVER clause”

Why This Error Happens

This error occurs because certain window (ranking, aggregate, or analytic) functions such as RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER(), LAG(), or LEAD() are syntactically required to be used with an OVER clause in Transact-SQL. The root cause is almost always a query authoring mistake where a developer calls one of these functions like a standard scalar function without specifying the partitioning or ordering logic via OVER (...). This is not a server configuration, permissions, or port issue — it is a T-SQL syntax validation error raised by the Query Optimizer/Parser in SQL Server 2025 during compilation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact failing statement from the application log, SSMS error pane, or SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025).
• Note the specific function name inserted into the %.*ls placeholder in the error message (e.g., RANK, ROW_NUMBER, LAG).
• Reproduce the issue in SQL Server Management Studio (SSMS) connected to the SQL Server 2025 instance to confirm the exact line number.

Step 2: Add the Required OVER Clause

• Rewrite the function call to include a valid OVER clause with at least a basic ordering definition.
• Example fix for ROW_NUMBER:

  SELECT ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNum
  FROM Sales.Orders;

• Example fix for RANK with partitioning:

  SELECT RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS SalesRank
  FROM Sales.Orders;

• Ensure the ORDER BY inside OVER() references a valid column or expression present in the result set.

Step 3: Validate Compatibility Level and Syntax Support

• Confirm the database compatibility level supports the window function syntax being used:

  SELECT name, compatibility_level
  FROM sys.databases
  WHERE name = 'YourDatabaseName';

• If compatibility level is below 130 (SQL Server 2016), update it to leverage full window function support in SQL Server 2025:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Re-run the corrected query to confirm the parser no longer raises Error 10753.

Step 4: Audit Stored Procedures and Views for Similar Patterns

• Search all stored procedures, functions, and views for other instances of ranking/analytic functions missing an OVER clause.
• Use the following query to locate potentially affected objects:

  SELECT OBJECT_NAME(object_id) AS ObjectName, definition
  FROM sys.sql_modules
  WHERE definition LIKE '%RANK(%' 
     OR definition LIKE '%ROW_NUMBER(%' 
     OR definition LIKE '%NTILE(%' 
     OR definition LIKE '%LAG(%' 
     OR definition LIKE '%LEAD(%';

• Manually inspect each result and add the required OVER clause where missing.

Alternative or Verification Steps

• After applying the fix, execute the corrected query/procedure in SSMS or via sqlcmd and confirm it compiles and returns results without Error 10753.
• If the error persists, check for cached or stale execution plans by running DBCC FREEPROCCACHE (in a non-production environment first) to force recompilation of the corrected query.

Troubleshooting Guide: SQL Server Error 10752 — “The function ‘%.*ls’ may not have a window frame”

Why This Error Happens

This error occurs when a T-SQL query attempts to apply a ROWS or RANGE window frame clause (an OVER (... ROWS BETWEEN ...) construct) to a window function that does not support framing, such as LAG(), LEAD(), NTILE(), RANK(), DENSE_RANK(), ROW_NUMBER(), PERCENT_RANK(), or CUME_DIST(). SQL Server 2025’s query parser strictly validates that only aggregate-style or frame-compatible analytic functions (like SUM(), AVG(), FIRST_VALUE(), or LAST_VALUE()) can be paired with a window frame definition. This is a T-SQL syntax/semantic validation rule enforced at compile time, not a configuration, permissions, or file corruption issue — it is almost always caused by a malformed or incorrectly migrated query.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Function

• Capture the exact query text from the application log, SSMS error pane, or Extended Events session that raised Error 10752.
• Look specifically for an OVER clause containing ROWS BETWEEN, RANGE BETWEEN, ROWS UNBOUNDED PRECEDING, or similar frame syntax.
• Cross-reference the function name reported in the error message (it replaces %.*ls) against Microsoft’s list of ranking/analytic functions that do not support framing.

Step 2: Remove or Correct the Window Frame Clause

• If the function is RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(), LAG(), or LEAD(), simply strip the ROWS/RANGE clause from the OVER() definition, since these functions operate on the entire partition by design.
• Example of the broken syntax:

  SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM Orders;

• Corrected syntax:

  SELECT ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) FROM Orders;

Step 3: Substitute a Frame-Compatible Function if Framing Is Required

• If the original intent was to calculate a running total or moving value, replace the ranking function with an aggregate function that does support ROWS/RANGE, such as SUM(), AVG(), MIN(), MAX(), FIRST_VALUE(), or LAST_VALUE().
• Example: use SUM(SalesAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) instead of trying to frame a ranking function.

Step 4: Re-deploy and Recompile Affected Objects

• Update any stored procedures, views, or functions on the Windows Server 2025 instance using ALTER PROCEDURE / ALTER VIEW / ALTER FUNCTION with the corrected T-SQL.
• Run sp_recompile 'YourObjectName' in SSMS to force SQL Server to drop the cached execution plan and recompile with the fixed syntax on next execution.
• If the query originated from an ORM (Entity Framework, Dapper, etc.) or a BI tool report, locate the generated SQL template in the application source or report definition (e.g., .rdl file) and patch it at the source so it isn’t regenerated with the same error.

Alternative or Verification Steps

• Re-run the corrected query directly in SQL Server Management Studio (SSMS) connected to the Windows Server 2025 instance; confirm it executes without raising Error 10752 and returns the expected result set.
• If the error persists after correcting the syntax, check for cached query plans or parameterized queries using DBCC FREEPROCCACHE (in a non-production environment) to rule out a stale execution plan referencing the old, broken statement.

Troubleshooting Guide: Error 10751 – ORDER BY in WITHIN GROUP Clause Must Have Exactly One Expression

Why This Error Happens

This error occurs when a T-SQL query uses a function requiring the WITHIN GROUP (ORDER BY ...) clause—such as STRING_AGG, PERCENTILE_CONT, or PERCENTILE_DISC—but the ORDER BY inside that clause specifies zero, two, or more sorting expressions instead of exactly one. SQL Server’s query parser strictly validates this syntax at compile time because these specific functions are mathematically or logically defined to operate on a single sort key. This is typically a query authoring mistake rather than a server configuration or permissions issue, often introduced during code migration or copy-pasting from a standard ORDER BY clause used elsewhere in the query.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Check the Activity Monitor or the error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to find the specific batch or stored procedure that triggered Error 10751.
• Isolate the specific line containing the WITHIN GROUP clause.

Step 2: Audit the ORDER BY Syntax

• Locate the WITHIN GROUP (ORDER BY ...) statement in the query.
• Verify if the clause contains a comma-separated list (e.g., ORDER BY ColumnA, ColumnB), which is invalid for this context.
• Check if the ORDER BY clause is empty or missing an expression entirely (e.g., ORDER BY ()).

Step 3: Correct the Expression Count

• Modify the query to include exactly one column or expression inside the ORDER BY.
• If you need to sort by multiple criteria, concatenate the columns into a single expression using CONCAT() or string operators (e.g., ORDER BY CONCAT(LastName, FirstName)) to satisfy the single-expression requirement.
• Save the script and prepare to re-run it in a test environment first.

Step 4: Recompile and Execute

• Clear the procedure cache for the specific query if it was previously cached with a different structure, using DBCC FREEPROCCACHE (use with caution on production servers).
• Execute the corrected query against the Windows Server 2025 hosted database instance.
• Monitor the Results pane in SSMS to ensure the query returns data without triggering the parser error.

Alternative or Verification Steps

• Verification: Run the query with SET SHOWPLAN_TEXT ON to ensure the execution plan compiles successfully without hitting the Error 10751 parser exception before executing it against live data.
• Alternative: If multiple sort keys are strictly required for complex aggregation logic, restructure the query to use a CROSS APPLY or subquery to pre-sort the data, allowing the outer function to reference a single pre-sorted key.

Troubleshooting Guide: SQL Server Error 10750 – FORCESCAN Hint Conflict with Multiple INDEX Hints

Why This Error Happens

This error occurs because the FORCESCAN hint is designed to work with query optimization logic that only supports a single table/index scan target, and SQL Server’s parser rejects any query plan that attempts to apply FORCESCAN alongside more than one INDEX hint in the same table reference. This is a T-SQL syntax validation rule enforced by the Query Optimizer in SQL Server 2025, not a configuration, permissions, or file corruption issue. It typically surfaces after query refactoring, ORM-generated SQL, or copy-pasted hint syntax from older scripts where multiple index hints were mistakenly combined with FORCESCAN.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Query

• Identify the exact query raising Error 10750 by checking SQL Server Management Studio (SSMS) error output or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• Use SQL Server Profiler or Extended Events (xEvents) to capture the failing query text if it’s generated dynamically by an application.
• Search the query body for the WITH (INDEX(...)) and FORCESCAN clauses to confirm multiple index names are listed.

Step 2: Edit the Query to Use a Single INDEX Hint

• Open the query in SSMS or your source code editor.
• Locate the hint block, which looks similar to:
  WITH (INDEX(IX_Index1, IX_Index2), FORCESCAN)
• Remove all but one index name, keeping only the most selective or relevant index:
  WITH (INDEX(IX_Index1), FORCESCAN)
• If multiple indexes were intended for different access paths, consider splitting the query into separate statements or using UNION instead.

Step 3: Validate Hint Necessity

• Determine whether FORCESCAN is actually required by reviewing the execution plan (Ctrl+M in SSMS) to see if SQL Server’s optimizer already selects an index scan naturally.
• If the optimizer consistently chooses a seek over a scan incorrectly, keep FORCESCAN but ensure only one valid INDEX hint accompanies it.
• Remove FORCESCAN entirely if query performance is acceptable without forcing the scan behavior.

Step 4: Test and Deploy the Corrected Query

• Run the corrected query in a non-production environment (Dev/Test instance on Windows Server 2025) to confirm Error 10750 no longer appears.
• Check the Messages tab in SSMS for successful execution with no hint-related warnings.
• Deploy the fix to stored procedures, views, or application code repositories, ensuring version control systems capture the change.

Step 5: Audit for Similar Hint Patterns Across the Codebase

• Run a text search across all .sql files and stored procedures using tools like grep, PowerShell Select-String, or SSMS’s “Search Object Text” feature for the pattern FORCESCAN combined with INDEX(.
• Correct any additional instances found to prevent recurring Error 10750 across other queries or jobs.

Alternative or Verification Steps

• Verification: Re-execute the corrected query and confirm Error 10750 does not appear in SSMS, Event Viewer, or application logs; also confirm the execution plan reflects the intended index usage.
• Alternative: If hint behavior is still unpredictable, remove both FORCESCAN and INDEX hints entirely and rely on SQL Server 2025’s Intelligent Query Processing (IQP) features to let the optimizer choose the best execution path automatically.

Troubleshooting Guide: Error 10749 – FORCESEEK Hint Cannot Be Used with Index 0

Why This Error Happens

This error occurs when a query uses the FORCESEEK hint with an explicit index value of 0, which represents a heap (a table without a clustered index) rather than an actual seekable index. Since a heap has no index structure to seek through, SQL Server’s query optimizer cannot satisfy the forced seek operation and rejects the query at compile time. This typically happens after a clustered index is dropped from a table but queries referencing the old index ID were not updated, or when index IDs were hardcoded into stored procedures without verifying the table’s current index layout.

Step-by-Step Resolution Guide

Step 1: Identify the Query and Index Reference Causing the Error

• Capture the exact query text from the application or SQL Server error log using SQL Server Management Studio (SSMS) or Extended Events.
• Look for the FORCESEEK (0) or FORCESEEK(0, (column)) syntax in the WITH clause of the table reference.
• Note the table name referenced in the hint, as this is the table currently structured as a heap.

Step 2: Verify the Current Index Structure of the Table

• Run the following query in SSMS to confirm whether the table has a clustered index:

SELECT name, index_id, type_desc 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('YourTableName');

• An index_id of 0 with type_desc of HEAP confirms the table has no clustered index, explaining why FORCESEEK (0) fails.
• Identify a valid non-clustered or clustered index (index_id 1 or higher) that matches the intended query plan.

Step 3: Correct the FORCESEEK Hint in the Query or Procedure

• Edit the query to reference a valid index ID or index name instead of 0. Example correction:

SELECT * FROM YourTableName WITH (FORCESEEK(1, (ColumnName)))
WHERE ColumnName = @Value;

• If the query is embedded in a stored procedure, open it via SSMS under Databases > YourDatabase > Programmability > Stored Procedures, right-click, and select Modify.
• Save and recompile the procedure using EXEC sp_recompile 'YourProcedureName';.

Step 4: Rebuild or Create a Proper Index if None Exists

• If no suitable index exists, create one to match the query’s filtering and seek requirements:

CREATE NONCLUSTERED INDEX IX_YourTableName_ColumnName 
ON YourTableName (ColumnName);

• Deploy this change during a maintenance window on production instances running on Windows Server 2025 to avoid blocking active transactions.

Step 5: Audit Other Queries for Hardcoded Index IDs

• Search your codebase and stored procedures for other instances of FORCESEEK(0 to prevent recurrence.
• Use SQL Server’s built-in search via sys.sql_modules to scan for the pattern:

SELECT OBJECT_NAME(object_id), definition 
FROM sys.sql_modules 
WHERE definition LIKE '%FORCESEEK(0%';

Alternative or Verification Steps

• Re-run the originally failing query after applying the index correction; it should execute without raising Error 10749, and the actual execution plan should show a Seek operation against the specified index.
• If hardcoding index IDs is not required, replace FORCESEEK(0, ...) with the generic FORCESEEK hint (without an index number) to let SQL Server automatically choose any valid seekable index, reducing future maintenance overhead.

Troubleshooting Guide: SQL Server Error 10748 – Multiple FORCESEEK Hints Specified

Why This Error Happens

This error occurs because the T-SQL query contains conflicting or duplicate FORCESEEK hints applied to the same table or view, either directly in the OPTION clause, inline table hints, or through a combination of both query hints and index hints. SQL Server’s query optimizer cannot resolve which seek behavior to prioritize when the same object has more than one FORCESEEK directive attached to it. This typically happens after query refactoring, merging of query fragments from different sources, or when hints are inherited from a template/stored procedure and manually added hints are layered on top.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact failing query text from the application log, SQL Server error log, or Extended Events session.
• In SQL Server Management Studio (SSMS) on Windows Server 2025/Windows 11, run the query manually to reproduce Error 10748 in the Messages pane.
• Note the table or view name referenced in the error context, as this narrows down which object has duplicate hints.

Step 2: Locate and Review All FORCESEEK Hints

• Search the query text for all instances of FORCESEEK, including:
• Table hints: FROM TableName WITH (FORCESEEK)
• Index-specific hints: FROM TableName WITH (FORCESEEK (IndexName (ColumnName)))
• Query-level hints: OPTION (TABLE HINT(TableName, FORCESEEK))
• Check if the query is a stored procedure by running:

  EXEC sp_helptext 'YourStoredProcedureName';

• Confirm whether hints are being injected twice due to dynamic SQL concatenation or ORM-generated queries (e.g., Entity Framework, Dapper).

Step 3: Remove Duplicate or Conflicting Hints

• Edit the query to retain only one FORCESEEK hint per table/view reference.
• If both an inline hint and an OPTION (TABLE HINT(...)) clause exist for the same table, delete one of them—typically keep the inline hint for clarity:

  -- Before (causes Error 10748)
  SELECT * FROM Sales.Orders WITH (FORCESEEK)
  OPTION (TABLE HINT(Sales.Orders, FORCESEEK));

  -- After (corrected)
  SELECT * FROM Sales.Orders WITH (FORCESEEK);

• Save the corrected query and update any stored procedures using:

  ALTER PROCEDURE YourStoredProcedureName
  AS
  BEGIN
      -- corrected query here
  END

Step 4: Check for Application-Level Hint Injection

• If the query originates from an application (e.g., .NET application running on Windows Server 2025), inspect the data access layer code for hardcoded hints combined with dynamically appended hints.
• Review any query-building middleware, ORM configuration files, or stored procedure wrappers that may duplicate hints during query construction.
• Disable or consolidate any hint-injection logic so only one FORCESEEK directive is applied per execution path.

Step 5: Test and Deploy the Fix

• Re-run the corrected query in SSMS or Azure Data Studio to confirm Error 10748 no longer appears.
• Deploy the updated stored procedure or application code to the relevant environment (Dev/Test/Prod).
• Monitor query execution plans using SQL Server Management Studio > Include Actual Execution Plan to ensure the seek operation still performs as intended without the duplicate hint.

Alternative or Verification Steps

• Verify the fix by executing the query multiple times under load using SQL Server Profiler or Extended Events, confirming no 10748 errors appear in the sys.dmexecrequests or SQL Server error log.
• If removing one hint changes the execution plan unexpectedly, use OPTION (RECOMPILE) temporarily to validate the optimizer’s chosen plan, then selectively reapply only the necessary FORCESEEK hint on the correct index.

Troubleshooting Guide: SQL Server Error 10747 – Conflicting FORCESEEK Hint Syntax

Why This Error Happens

This error occurs because the SQL Server Query Optimizer does not allow a parameterized FORCESEEK (index_val (column_name)) hint to coexist with a separate INDEX() hint or a basic, non-parameterized FORCESEEK hint targeting the same table object. The query parser views these as conflicting directives, as it cannot determine whether to force the seek based on the index listed in the INDEX hint or the specific index defined within the FORCESEEK parameters. This is typically a T-SQL syntax logic error introduced during manual query writing or stored procedure updates, rather than a database corruption or permissions issue.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs to find the exact timestamp and associated spid for Error 10747.
• Use sp_who2 or Extended Events to identify the specific stored procedure or ad-hoc query currently executing that triggered the halt.

Step 2: Locate the Hint Syntax in the Code

• Open the offending stored procedure or script in a query editor.
• Search specifically for the keywords FORCESEEK and INDEX appearing within the same WITH () table hint clause.
• Look for a pattern similar to this invalid syntax:
• SELECT * FROM Table1 WITH (INDEX(IX_Table1_ColA), FORCESEEK(IX_Table1_ColA (ColA)))

Step 3: Correct the Hint Logic

• Choose Option A (Recommended for specific index targeting): Remove the INDEX hint entirely and rely solely on the parameterized FORCESEEK hint to specify the index and columns.
• Corrected Syntax: SELECT * FROM Table1 WITH (FORCESEEK(IX_Table1_ColA (ColA)))
• Choose Option B (If using generic forcing): Keep the INDEX hint but remove the parameters from FORCESEEK, leaving it as a generic boolean flag.
• Corrected Syntax: SELECT * FROM Table1 WITH (INDEX(IX_Table1_ColA), FORCESEEK)
• Save the script and update the stored procedure using ALTER PROCEDURE.

Step 4: Clear the Procedure Cache

• To ensure the old, erroring execution plan is not reused, clear the specific plan cache.
• Run the following command in a new query window: DBCC FREEPROCCACHE (plan_handle);
• Alternatively, if in a non-production environment, use DBCC FREEPROCCACHE; to clear the entire instance cache.

Alternative or Verification Steps

• Verification: Re-execute the corrected stored procedure or query manually in SSMS. Check the “Messages” tab to confirm the code completes successfully without throwing Error 10747, and review the “Execution Plan” tab to ensure the Seek operator is being utilized as intended.
• Alternative: If the specific index path is no longer valid due to schema drift, consider removing the FORCESEEK hint entirely and allow the Query Optimizer to select the best index automatically based on updated statistics (UPDATE STATISTICS Table1;).

Troubleshooting Guide: SQL Server Error 10746 — Conflicting FORCESCAN and FORCESEEK Hints

Why This Error Happens

This error occurs because the T-SQL query optimizer received two mutually exclusive table hints — FORCESCAN and FORCESEEK — applied to the same table reference within a single query. Since FORCESCAN instructs the optimizer to use an index or table scan while FORCESEEK forces an index seek operation, the Query Optimizer in SQL Server 2025 cannot reconcile both directives simultaneously and halts compilation. This is typically caused by manually written queries, legacy query hints carried over from older codebases, or dynamically generated SQL from an ORM or stored procedure that appends conflicting hints.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from the application error log, SQL Server error log, or Extended Events session.
• On Windows Server 2025, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the timestamped entry correlating with Error 10746.
• If using SQL Server Management Studio (SSMS), re-run the query interactively to reproduce the exact compilation error.

Step 2: Inspect the Table Hint Syntax

• Locate the WITH (...) clause in the FROM or JOIN portion of the query.
• Look specifically for both hints appearing together, such as:
  SELECT * FROM dbo.Orders WITH (FORCESCAN, FORCESEEK)
• Confirm whether the hints were added manually by a developer or auto-generated by an ORM (e.g., Entity Framework, Hibernate) or a query hint injection tool.

Step 3: Remove the Conflicting Hint

• Decide which access method is actually required based on the query’s execution plan goals:
• Use FORCESEEK if the table has a selective index and point lookups are expected.
• Use FORCESCAN if a full scan is genuinely more efficient (e.g., large batch aggregations).
• Edit the query to retain only one hint, for example:
  SELECT * FROM dbo.Orders WITH (FORCESEEK)
• Save the corrected query in source control or the stored procedure definition.

Step 4: Update Stored Procedures or Application Code

• If the hint originates from a stored procedure, use ALTER PROCEDURE to redeploy the corrected definition.
• If the hint is generated dynamically by application code or an ORM, update the query-building logic or hint configuration file to prevent both hints from being appended together.
• Recompile the procedure using EXEC sp_recompile 'dbo.ProcedureName'; to clear any cached invalid plan reference.

Step 5: Test the Corrected Query

• Run the modified query in SSMS or Azure Data Studio against a non-production environment first.
• Confirm the query compiles and returns an execution plan without triggering Error 10746.

Alternative or Verification Steps

• Verify the fix by executing the query with SET SHOWPLAN_TEXT ON; to confirm the query compiles successfully and generates a valid execution plan using only the single specified hint.
• If the conflicting hints are being injected by a third-party plan-guide or a sp_create_plan_guide definition, run sys.plan_guides to check for stored hint conflicts and drop or recreate the plan guide with corrected hint syntax using sp_control_plan_guide.

Troubleshooting Guide: SQL Server Error 10745 – “The FORCESCAN hint is not allowed for target tables of INSERT”

Why This Error Happens

This error occurs because the FORCESCAN table hint is only valid for tables being read from (SELECT sources), not for tables that are the target of a data modification operation like INSERT. SQL Server 2025’s query optimizer explicitly blocks this hint on INSERT target tables because scan-forcing logic conflicts with how the storage engine plans row insertion operations. This is almost always caused by a query template, ORM-generated statement, or migrated T-SQL script that incorrectly carries over a FORCESCAN hint meant for a different part of the query (such as a subquery or JOIN source).

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact failing statement using SQL Server Profiler, Extended Events, or the Query Store (sys.query_store_query_text) on the affected database.
• Search the query text for the keyword FORCESCAN to locate which table hint clause is causing the conflict.
• Check if the query was auto-generated by an ORM (Entity Framework, Hibernate) or a stored procedure template that applies hints globally.

Step 2: Remove or Relocate the Hint

• Open the query in SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
• Delete the WITH (FORCESCAN) clause specifically from the INSERT INTO TableName WITH (FORCESCAN) portion of the statement.
• If a scan hint is genuinely needed, move it to the SELECT portion of the statement instead, e.g.:
  INSERT INTO TargetTable SELECT * FROM SourceTable WITH (FORCESCAN) WHERE ...

Step 3: Update Stored Procedures and Scripts

• Search your deployed stored procedures for recurring instances of this pattern using:
  SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%FORCESCAN%'
• Edit and redeploy any procedure found using ALTER PROCEDURE, ensuring the hint only appears on SELECT-based table references.
• If the procedures were generated by a third-party tool or migration script, check the tool’s configuration file (often located in C:\Program Files\<VendorApp>\config\) for a query hint template setting and disable automatic hint injection on INSERT statements.

Step 4: Clear Cached Execution Plans

• Run DBCC FREEPROCCACHE (in a test/maintenance window only) or use ALTER PROCEDURE ... WITH RECOMPILE on the corrected procedure to ensure no stale plan with the invalid hint remains cached.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell if the issue persists after plan cache clearing.

Alternative or Verification Steps

• Re-run the corrected INSERT statement in SSMS and confirm it executes without Error 10745; also check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm no recurrence.
• As an alternative, replace FORCESCAN with an index hint directly on the source table (e.g., WITH (INDEX(IndexName))) if query performance tuning was the original intent behind the hint.

Troubleshooting Guide: Error 10744 — “The number of rows provided for a FETCH clause must be greater than zero”

Why This Error Happens

This error occurs when a T-SQL query uses the OFFSET...FETCH NEXT clause with a variable, parameter, or expression that evaluates to zero or a negative number. SQL Server 2025 strictly enforces that the FETCH NEXT row count must be a positive integer greater than zero, since a value of zero logically requests no rows, which is an invalid operation for this clause. This is most commonly caused by uninitialized variables, faulty pagination logic in application code, or a stored procedure receiving an unexpected @PageSize or @RowCount parameter of 0.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log via Object Explorer > Management > SQL Server Logs.
• Correlate the timestamp of Error 10744 with recent query activity using Extended Events or the sys.dm_exec_query_stats DMV.
• Look specifically for stored procedures or dynamic SQL containing OFFSET @StartRow ROWS FETCH NEXT @PageSize ROWS ONLY.

Step 2: Validate Input Parameters in Application Code

• Check the application or API layer (e.g., C#, Java, or PowerShell scripts) that passes pagination values to SQL Server.
• Ensure @PageSize or equivalent variable is never set to 0 before being passed into the query; default it to a safe value like 10 or 25 if null.
• Add a server-side guard clause in the stored procedure:
  IF @PageSize <= 0 SET @PageSize = 10;

Step 3: Patch and Update SQL Server 2025

• Navigate to Start > Settings > Windows Update on Windows Server 2025 or Windows 11 and ensure the OS is fully patched.
• Open SQL Server Configuration Manager and verify you are running the latest Cumulative Update (CU) for SQL Server 2025, as early builds had edge-case bugs validating FETCH NEXT with computed columns.
• Restart the MSSQLSERVER service via services.msc after applying any updates.

Step 4: Rewrite the Query Logic

• Modify the query to explicitly cast the row count variable: FETCH NEXT (SELECT CASE WHEN @PageSize < 1 THEN 1 ELSE @PageSize END) ROWS ONLY.
• Avoid using sub-queries or functions directly inside the FETCH NEXT clause that might return NULL or 0 unexpectedly; declare a local variable instead.

Alternative or Verification Steps

• Run the stored procedure manually in SSMS with a hardcoded value (e.g., EXEC GetData @PageSize = 5) to confirm the query executes without Error 10744.
• If the error persists in a linked server scenario, check the compatibility level of the database via sys.databases and ensure it is set to 160 (SQL Server 2022) or higher to support current FETCH syntax validation.

Troubleshooting Guide: SQL Server Error 10743 — Invalid OFFSET Clause Data Type

Why This Error Happens

This error occurs when the value passed to the OFFSET clause in a SELECT ... ORDER BY ... OFFSET ... FETCH NEXT statement is not an integer data type, such as a VARCHAR, DECIMAL, or FLOAT value. SQL Server 2025 strictly enforces that OFFSET and FETCH NEXT arguments must evaluate to BIGINT-compatible integer expressions (typically INT or BIGINT). This commonly happens when application code dynamically builds pagination queries using string concatenation, or when a stored procedure parameter is declared with the wrong data type and passed directly into the OFFSET clause without explicit casting.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact T-SQL statement throwing Error 10743 using SQL Server Profiler, Extended Events, or the application’s query log.
• Check the OFFSET and FETCH NEXT values being passed — look specifically for string literals (e.g., OFFSET '10' ROWS) or parameters bound as NVARCHAR.
• Run the query manually in SQL Server Management Studio (SSMS) to confirm the error reproduces outside the application.

Step 2: Correct the Data Type in the Query or Procedure

• Explicitly cast the offset and fetch values using CAST(@Offset AS INT) or CAST(@Offset AS BIGINT) directly in the OFFSET clause.
• If using a stored procedure, verify the parameter declaration matches an integer type, for example:

  CREATE PROCEDURE dbo.GetPagedResults
      @PageOffset INT,
      @PageSize INT
  AS
  BEGIN
      SELECT * FROM dbo.Orders
      ORDER BY OrderID
      OFFSET @PageOffset ROWS
      FETCH NEXT @PageSize ROWS ONLY;
  END

• Avoid passing NULL, empty strings, or computed expressions that can resolve to non-integer types.

Step 3: Audit Application-Side Parameter Binding

• Inspect the application’s data access layer (e.g., Entity Framework, ADO.NET, or ODBC connection strings) for incorrect parameter type mapping.
• On Windows Server 2025 / Windows 11, check the ODBC Data Source Administrator (odbcad32.exe) if a DSN is used, and confirm the driver version is SQL Server Native Client 18 or the Microsoft ODBC Driver 18 for SQL Server.
• Update any SqlParameter or SqlDbType declarations in code to use SqlDbType.Int or SqlDbType.BigInt instead of SqlDbType.VarChar or SqlDbType.NVarChar for pagination values.

Step 4: Restart the SQL Server Service and Clear Cached Plans

• Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and restart the service to clear any cached execution plans referencing the faulty query.
• Alternatively, run DBCC FREEPROCCACHE; in SSMS to flush the plan cache without a full service restart.
• Re-run the corrected query to confirm Error 10743 no longer appears.

Alternative or Verification Steps

• Execute the corrected query directly in SSMS with hardcoded integer values (e.g., OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY) to confirm the syntax now parses and returns results without error.
• If the error persists after casting, check for implicit conversions in linked server queries or dynamic SQL built via sp_executesql, and ensure parameters are declared with @Offset INT rather than @Offset NVARCHAR(10) in the parameter definition string.

Troubleshooting Guide: SQL Server Error 10742 – Negative OFFSET Value

Why This Error Happens

This error occurs when a T-SQL query uses an OFFSET clause (typically paired with FETCH NEXT for pagination) where the offset value evaluates to a negative number. SQL Server strictly requires that the OFFSET row count—whether a literal, variable, or parameter—be zero or a positive integer, as it represents the number of rows to skip before returning results. This commonly happens due to incorrect calculations in application pagination logic (e.g., (PageNumber - 1) * PageSize where PageNumber is 0 or negative), unvalidated user input passed into a stored procedure, or a sign error in dynamically constructed SQL. This is a query/application-layer logic issue rather than a Windows Server 2025 or SQL Server configuration/service problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the SQL Server error log or Extended Events session for the exact statement that raised Error 10742.
• Run SELECT * FROM sys.dm_exec_query_stats cross-referenced with sys.dm_exec_sql_text to locate recently failed plans containing OFFSET/FETCH.
• Use SQL Server Management Studio (SSMS) “Include Actual Execution Plan” on the suspect query to confirm the clause triggering the fault.

Step 2: Validate and Correct the OFFSET Calculation

• Inspect the application or stored procedure code that generates the OFFSET value, especially pagination logic such as:

  DECLARE @PageNumber INT = 0;
  DECLARE @PageSize INT = 10;
  SELECT * FROM Orders
  ORDER BY OrderID
  OFFSET (@PageNumber - 1) * @PageSize ROWS
  FETCH NEXT @PageSize ROWS ONLY;

• Correct the logic to ensure @PageNumber starts at 1, or add a guard clause: SET @PageNumber = CASE WHEN @PageNumber < 1 THEN 1 ELSE @PageNumber END;
• Ensure no negative values can be passed from front-end controls, REST API query parameters, or configuration files.

Step 3: Add Server-Side Input Validation

• Modify the stored procedure or parameterized query to explicitly reject or sanitize negative offsets before execution:

  IF (@Offset < 0)
  BEGIN
      RAISERROR('Offset cannot be negative. Value adjusted to 0.', 10, 1);
      SET @Offset = 0;
  END

• Deploy this validation at the data access layer (e.g., Entity Framework, Dapper, or ADO.NET) in addition to the database layer for defense-in-depth.

Step 4: Test the Fix in a Non-Production Environment

• Use SSMS or Azure Data Studio connected to a Windows Server 2025 test instance to re-run the corrected query with boundary values (0, 1, and a previously failing negative value).
• Confirm the query executes without raising Error 10742 and returns the expected row set.

Alternative or Verification Steps

• Re-run the application workflow (e.g., navigate to “page 1” or trigger the initial paginated request) and confirm no errors appear in the SQL Server error log (Windows Server 2025 path: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• If the error persists after code changes, enable SQL Server Query Store (ALTER DATABASE [YourDB] SET QUERY_STORE = ON;) to continuously monitor for any new query plans still generating negative offsets.

Troubleshooting Guide: Error 10741 – TOP Cannot Be Used with OFFSET

Why This Error Happens

This error occurs because Transact-SQL syntax rules explicitly prohibit combining the TOP clause with OFFSET/FETCH within the same query or sub-query, as both constructs are designed to limit result sets and are considered mutually exclusive row-limiting mechanisms. This is not a bug, permissions issue, or configuration problem—it is a T-SQL parser validation rule enforced by the SQL Server 2025 Database Engine during query compilation. The error typically surfaces after a migration, an ORM-generated query, or manual code where a developer mixed legacy TOP pagination with newer OFFSET...FETCH pagination syntax in the same SELECT statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from the application error log, SQL Server Error Log, or Extended Events session.
• Look for the specific statement referencing Error 10741 using SSMS (SQL Server Management Studio) or Azure Data Studio.
• Check if the query was generated dynamically by an ORM (Entity Framework, Hibernate, Dapper) since these tools sometimes append OFFSET clauses automatically for pagination.

Step 2: Choose a Single Row-Limiting Method

• Decide whether the query logic requires simple row limiting (TOP) or paginated results (OFFSET...FETCH).
• If pagination is required, remove TOP (n) entirely and rely solely on the ORDER BY ... OFFSET x ROWS FETCH NEXT y ROWS ONLY syntax.
• If a simple limit is required, remove the OFFSET 0 ROWS clause and use only SELECT TOP (n) ... ORDER BY.

Step 3: Rewrite the Query Syntax

• Replace mixed syntax like:
  SELECT TOP (10) * FROM Orders ORDER BY OrderDate OFFSET 5 ROWS;
• With valid pagination syntax:
  SELECT * FROM Orders ORDER BY OrderDate OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
• Ensure an ORDER BY clause is present, as OFFSET...FETCH requires it to function correctly.

Step 4: Update Application or ORM Query Builders

• Check the data access layer configuration (e.g., appsettings.json or connection string provider settings) for pagination logic that auto-injects TOP.
• Update the ORM version or pagination helper method to ensure it generates only OFFSET...FETCH for paged results rather than appending TOP.
• Deploy the corrected stored procedure or application code to the Windows Server 2025 instance.

Step 5: Recompile and Test the Query

• Clear the plan cache for the specific query using DBCC FREEPROCCACHE (only in non-production environments) to ensure no stale execution plans are referenced.
• Re-run the corrected query in SSMS to confirm the Database Engine accepts the syntax without throwing Error 10741.

Alternative or Verification Steps

• Verify the fix by executing the corrected query directly against the SQL Server 2025 instance and confirming it returns results without syntax errors.
• If the error persists, use SELECT @@VERSION to confirm the server is running a fully patched build of SQL Server 2025, and check for third-party query generation tools that might be reintroducing the conflicting syntax.

Troubleshooting Guide: SQL Server Error 10740 — “A search property list statement must end with a semicolon (;)”

Why This Error Happens

This error occurs when the T-SQL statement used to create, alter, or drop a Search Property List is missing a terminating semicolon, which SQL Server 2025 now strictly enforces as part of its statement termination requirements. It can also surface when batch scripts are concatenated incorrectly by deployment tools, migration scripts, or ORM-generated SQL, leaving a dangling CREATE SEARCH PROPERTY LIST or ALTER SEARCH PROPERTY LIST clause without proper statement closure. Additionally, legacy scripts written for older SQL Server versions (where semicolons were optional) will fail under SQL Server 2025’s tightened T-SQL parser rules.

Step-by-Step Resolution Guide

Step 1: Locate the Offending Script or Query

• Open the query or migration script that triggered Error 10740, typically found in SSMS query windows, deployment .sql files, or SQL Agent Job steps.
• Check the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the exact line number referenced in the error detail.
• Use grep or findstr to search for SEARCH PROPERTY LIST across script repositories: findstr /s /i "SEARCH PROPERTY LIST" C:\Scripts\*.sql

Step 2: Correct the Statement Syntax

• Add a semicolon immediately after the closing parenthesis or GO boundary of the CREATE SEARCH PROPERTY LIST, ALTER SEARCH PROPERTY LIST, or DROP SEARCH PROPERTY LIST statement.
• Example corrected syntax:
• CREATE SEARCH PROPERTY LIST MySearchPropertyList FROM AnotherList;
• Ensure no other statements in the same batch are missing semicolons, as SQL Server 2025 validates the entire batch before execution.

Step 3: Update Source Scripts and Deployment Pipelines

• Edit the source .sql files in version control (Git, Azure DevOps repos) to permanently include the semicolon.
• If using SSDT or DACPAC-based deployments, rebuild the project in Visual Studio 2022/2025 after the fix to regenerate a valid deployment script.
• Re-run the corrected script via SSMS or sqlcmd: sqlcmd -S localhost -d YourDatabase -i "C:\Scripts\FixSearchPropertyList.sql"

Step 4: Validate Full Batch Syntax

• Run the entire script through SQL Server Management Studio’s Parse feature (Ctrl+F5) before execution to catch any remaining missing terminators.
• Check for other T-SQL statements in the same batch (e.g., CREATE FULLTEXT INDEX, ALTER FULLTEXT CATALOG) that may also require semicolons under SQL Server 2025’s stricter parser.

Alternative or Verification Steps

• Verify the fix by re-executing the script and confirming the Search Property List appears correctly: SELECT * FROM sys.registered_search_property_lists;
• If the error persists in automated deployment pipelines, enable SQLCMD mode in SSMS and step through the script interactively to isolate exactly which statement is malformed.

Troubleshooting Guide: SQL Server Error 10739 — Multi-Part Identifiers in MERGE Insert Column List

Why This Error Happens

This error occurs because the T-SQL parser for the MERGE statement strictly requires that column names listed in the INSERT clause’s column list be specified using single-part identifiers (e.g., ColumnName) rather than qualified multi-part identifiers (e.g., TableName.ColumnName or Alias.ColumnName). When a developer qualifies the column name with a table alias inside the INSERT (columns) portion of the MERGE statement, SQL Server 2025 rejects the syntax because the engine does not allow scope qualification at that specific point in the query tree. This is typically a coding/syntax oversight rather than a system configuration or permissions issue.

Step-by-Step Resolution Guide

Step 1: Locate the Offending MERGE Statement

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Use the “Messages” tab to identify the specific line number referenced by Error 10739.
• Isolate the WHEN NOT MATCHED THEN INSERT (...) clause within your script.

Step 2: Remove Table Aliases from the Insert Column List

• Locate the column list immediately following the INSERT keyword.
• Strip any prefixes such as Target. or src. from the column names inside the parentheses.
• Example Fix:
• Incorrect: INSERT (Target.ID, Target.Name)
• Correct: INSERT (ID, Name)

Step 3: Verify the VALUES Clause Remains Qualified (If Necessary)

• Ensure that while the INSERT column list is single-part, the corresponding VALUES clause can still reference the source alias correctly (e.g., VALUES (src.ID, src.Name)).
• Save the script and re-run the compilation to check for syntax validation.

Step 4: Check for Nested or Dynamic SQL Issues

• If the MERGE statement is generated dynamically via a stored procedure, search the underlying string concatenation logic for hard-coded aliases.
• Update the dynamic SQL generation script to strip aliases before the INSERT clause specifically.

Alternative or Verification Steps

• Verification: Execute the corrected MERGE statement in a test environment (Windows 11 dev machine or Server 2025 staging) to confirm the “Command(s) completed successfully” message appears without Error 10739.
• Alternative: If multiple dynamic scripts are affected, utilize a sp_helptext query to export all MERGE-based stored procedures and use a global search/regex tool (like VS Code’s search-and-replace) to batch-remove alias prefixes within the specific INSERT (...) capture groups.

Troubleshooting Guide: SQL Server Error 10738 – Row Value Expressions Exceed Maximum

Why This Error Happens

This error occurs because SQL Server enforces a hard-coded limit of 1,000 row value expressions within a single multi-row INSERT statement using the VALUES clause. When an application, migration script, or ORM-generated query attempts to batch-insert more than 1,000 rows in one statement, the SQL Server 2025 query parser rejects the statement during compilation. This is not a permissions or configuration issue, but a T-SQL language limitation that must be handled at the query-construction level.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the application logs or SQL Server error logs (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact statement triggering Error 10738.
• Use SQL Server Profiler or Extended Events (xEvents) to capture the batch text at the time of failure.
• Confirm the row count in the VALUES clause by counting comma-separated row groups (val1, val2), (val3, val4)....

Step 2: Break the INSERT Statement into Smaller Batches

• Split the single INSERT statement into multiple statements, each containing 1,000 rows or fewer.
• If using application code (C#, Python, PowerShell), implement a loop that chunks the dataset into batches of 1,000 before constructing the INSERT string.
• Example T-SQL pattern:

  INSERT INTO dbo.TargetTable (Col1, Col2)
  VALUES (1,'A'), (2,'B'), ... -- up to 1000 rows

  INSERT INTO dbo.TargetTable (Col1, Col2)
  VALUES (1001,'X'), (1002,'Y'), ... -- next batch

Step 3: Use Table-Valued Parameters or Bulk Insert for Large Datasets

• For datasets exceeding a few thousand rows, replace multi-row VALUES inserts with BULK INSERT, OPENROWSET(BULK...), or SSIS/Azure Data Factory pipelines.
• Alternatively, use a Table-Valued Parameter (TVP) in stored procedures to pass large row sets from the application layer without hitting the 1,000-row VALUES limit.
• On Windows Server 2025, ensure the SQL Server service account has read access to any staging files used by BULK INSERT (e.g., C:\SQLStaging\ImportFile.csv).

Step 4: Update ORM or Code Generation Logic

• If using Entity Framework, Dapper, or a custom data access layer, verify the batching logic enforces the 1,000-row cap per INSERT command.
• Adjust SaveChanges() batch size settings (e.g., MaxBatchSize in EF Core) to stay under the SQL Server limit.

Alternative or Verification Steps

• Re-run the corrected batch-size INSERT statements in SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance and confirm no Error 10738 is raised.
• As an alternative for very large data loads, use bcp.exe or BULK INSERT from a flat file, which bypasses the VALUES-clause row limit entirely.

Microsoft SQL Server 2025 Error 10737 Troubleshooting Guide

Why This Error Happens

Error 10737 occurs when an ALTER TABLE REBUILD or ALTER INDEX REBUILD statement specifies a data compression option (such as COLUMNSTORE or COLUMNSTORE_ARCHIVE) that is incompatible with the target table’s structure, such as attempting to apply columnstore compression to a table containing unsupported data types or constraints. This commonly stems from a mismatch between the compression type requested and the edition of SQL Server 2025 in use, since certain compression features are restricted to Enterprise or specific licensing tiers. It can also occur when the table already has an incompatible index type (like a XML or spatial index) present that blocks the requested rebuild operation.

Step-by-Step Resolution Guide

Step 1: Verify the Table Schema and Data Types

• Run sp_help 'YourTableName' in SQL Server Management Studio (SSMS) to inspect column data types.
• Check for unsupported types such as text, ntext, image, or XML when attempting columnstore compression.
• Note any existing indexes using SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName').

Step 2: Confirm SQL Server Edition and Feature Availability

• Open SSMS and run SELECT SERVERPROPERTY('Edition') to confirm your current installation tier.
• Cross-reference the result against Microsoft’s “Editions and supported features for SQL Server 2025” documentation.
• If running Standard or Web edition, downgrade your compression request to PAGE or ROW instead of COLUMNSTORE.

Step 3: Drop Conflicting Indexes Before Rebuild

• Identify incompatible indexes using SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID('YourTableName').
• Drop any XML, spatial, or full-text indexes temporarily using DROP INDEX IndexName ON YourTableName.
• Re-run the ALTER TABLE YourTableName REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE) statement.

Step 4: Adjust the Compression Syntax

• Update your T-SQL statement to match supported syntax for SQL Server 2025, for example:

  ALTER TABLE YourTableName REBUILD WITH (DATA_COMPRESSION = PAGE)

• Ensure partition-level compression settings align with table-level settings if using partitioned tables.
• Validate by checking sys.partitions.data_compression_desc after the rebuild completes.

Step 5: Restart the SQL Server Service (If Metadata Cache Is Stale)

• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to clear any cached execution plans causing the error to persist incorrectly.

Alternative or Verification Steps

• After applying fixes, run ALTER INDEX ALL ON YourTableName REBUILD and confirm no error 10737 is returned in the Messages pane.
• If the issue persists, use DBCC CHECKTABLE('YourTableName') to rule out underlying corruption, then retry the rebuild with DATA_COMPRESSION = NONE as a fallback baseline.

Troubleshooting Guide: SQL Server Error 10736 – Full-Text Stoplist Statement Termination

Why This Error Happens

This error occurs when a Transact-SQL statement involving full-text stoplist operations (such as CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, or DROP FULLTEXT STOPLIST) is executed without a terminating semi-colon. SQL Server 2025 has tightened statement-termination enforcement for full-text catalog and stoplist DDL commands, meaning syntax that was previously tolerated in earlier versions (like SQL Server 2019 or 2022) will now throw Error 10736. This is purely a T-SQL syntax parsing issue and is not related to permissions, corrupted files, or service configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the script that triggered Error 10736.
• Check the SQL Server Error Log (via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to confirm the exact line number and batch referenced in the error message.
• Look specifically for statements using CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST ADD, or DROP FULLTEXT STOPLIST.

Step 2: Add the Required Semi-Colon Terminator

• Edit the script and ensure every full-text stoplist statement ends with a semi-colon (;).
• Example of corrected syntax:

  CREATE FULLTEXT STOPLIST MyStopList;
  ALTER FULLTEXT STOPLIST MyStopList ADD 'the' LANGUAGE 1033;

• If the script contains multiple chained statements, verify each one is individually terminated rather than relying on line breaks alone.

Step 3: Update Legacy Scripts and Stored Procedures

• Search existing stored procedures, SSIS packages, and deployment scripts for full-text stoplist commands using a text search tool like grep (via WSL) or the “Find in Files” feature in SSMS.
• Replace all unterminated stoplist statements with properly semi-colon-terminated versions.
• Save and redeploy affected .sql migration files to your source control repository (e.g., Git) to prevent regression.

Step 4: Re-run the Corrected Script

• Execute the updated script in a test environment first, using a non-production database.
• Confirm no further syntax-related errors appear in the Messages pane of SSMS.

Alternative or Verification Steps

• Run SELECT * FROM sys.fulltext_stoplists; after execution to confirm the stoplist object was created or altered successfully without errors.
• If Error 10736 persists despite adding semi-colons, check for invisible Unicode characters or incorrect script encoding (UTF-8 with BOM) by re-saving the .sql file in plain ANSI or UTF-8 format using Notepad++ or VS Code.

Troubleshooting Guide: Error 10735 — Incorrect WHERE Clause for Filtered Index/Statistics

Why This Error Happens

Error 10735 occurs when the filter predicate defined on a filtered index or filtered statistics object contains a WHERE clause expression that SQL Server 2025’s query optimizer can no longer validate against the current table schema. This typically happens after a column referenced in the filter predicate has been altered (data type change, collation change, or dropped and recreated), or when the filtered object’s metadata becomes out of sync due to an interrupted DDL operation or replication/migration process. In rarer cases, this is triggered by corrupted system metadata in sys.indexes or sys.stats following an improper upgrade from an older SQL Server version.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Object and Filter Definition

• Run the following query to locate the problematic filtered index or statistics:

  SELECT name, object_id, filter_definition 
  FROM sys.indexes 
  WHERE filter_definition IS NOT NULL;

• Cross-reference the object_id with sys.stats if the error references statistics instead of an index:

  SELECT name, object_id, filter_definition 
  FROM sys.stats 
  WHERE filter_definition IS NOT NULL;

• Note the exact table name and column(s) mentioned in the error message output.

Step 2: Validate the Underlying Column Schema

• Check the data type, nullability, and collation of the column(s) used in the WHERE clause:

  EXEC sp_help 'dbo.YourTableName';

• Confirm there is no mismatch between the filter predicate’s expected data type and the actual column type (a common cause after ALTER TABLE ... ALTER COLUMN operations).
• If using Windows Server 2025, verify the SQL Server instance patch level via SELECT @@VERSION; to rule out a known build-specific metadata bug.

Step 3: Drop and Recreate the Filtered Index or Statistics

• Drop the broken filtered index:

  DROP INDEX IX_FilteredIndexName ON dbo.YourTableName;

• Recreate it with a corrected, explicit WHERE clause that matches current column types:

  CREATE NONCLUSTERED INDEX IX_FilteredIndexName
  ON dbo.YourTableName (ColumnName)
  WHERE ColumnName IS NOT NULL;

• If the issue is with statistics, drop and recreate using DROP STATISTICS followed by CREATE STATISTICS with the updated filter syntax.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption.
• If corruption is found in system metadata tables, restore from a known good backup rather than attempting repair on production.

Alternative or Verification Steps

• Verify the fix by re-running the original query or DDL operation that triggered Error 10735; it should now execute without throwing the filtered predicate error.
• As an alternative, if recreating the index does not resolve the issue, use sp_updatestats followed by a full index rebuild (ALTER INDEX ALL ON dbo.YourTableName REBUILD) to force SQL Server 2025 to regenerate all internal metadata references.

Troubleshooting Guide: SQL Server Error 10734 — Variable Assignment with Top-Level UNION

Why This Error Happens

This error occurs because Transact-SQL does not permit a variable assignment (using SELECT @variable = column) in a query that also contains a top-level UNION, UNION ALL, EXCEPT, or INTERSECT operator. The SQL Server parser cannot determine which result set row should be used to populate the variable when multiple result sets are being combined at the outermost level. This is a syntax-level restriction enforced by the Query Parser/Compiler and is not related to permissions, corrupted files, or server configuration — it is purely a T-SQL coding issue in the batch, stored procedure, or script being executed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open the stored procedure, function, or ad-hoc script referenced in the error message using SQL Server Management Studio (SSMS) on Windows Server 2025 / Windows 11.
• Locate the SELECT @variable = ColumnName statement that is combined with UNION or UNION ALL at the top level.
• Check SQL Server error logs (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) if the query was executed via an automated job or SSIS package to confirm the exact batch that failed.

Step 2: Wrap the UNION Query in a Subquery or CTE

• Rewrite the query so the UNION operation is isolated inside a derived table, Common Table Expression (CTE), or temporary table, and perform the variable assignment separately outside of it.
• Example corrected pattern:

DECLARE @Result INT;

SELECT @Result = CombinedData.Value
FROM (
    SELECT Value FROM TableA
    UNION
    SELECT Value FROM TableB
) AS CombinedData;

• This isolates the UNION as a non-top-level operation, satisfying the parser’s requirement.

Step 3: Use a Temporary Table or Table Variable as an Alternative

• If the result set from the UNION needs further filtering or aggregation before assignment, insert the combined results into a #TempTable or @TableVariable first.
• Example:

CREATE TABLE #Combined (Value INT);

INSERT INTO #Combined (Value)
SELECT Value FROM TableA
UNION
SELECT Value FROM TableB;

DECLARE @Result INT;
SELECT @Result = Value FROM #Combined;

DROP TABLE #Combined;

• This approach is especially useful in complex stored procedures with multiple UNION branches and additional business logic.

Step 4: Validate and Redeploy the Script

• Save the corrected script and re-run it in a Development or Test environment first using SSMS or Azure Data Studio.
• Use SET PARSEONLY ON; followed by executing the batch to confirm the syntax compiles without triggering Error 10734 before deploying to Production on your Windows Server 2025 instance.

Alternative or Verification Steps

• Verification: Execute the modified stored procedure or script manually in SSMS and confirm it returns a result/completes without throwing Error 10734; check the Messages tab for a clean execution with no syntax errors.
• Alternative: If rewriting the query isn’t immediately feasible, use INSERT ... EXEC to capture the UNION query’s output into a table first, then perform the variable assignment from that table as a quick workaround while a permanent fix is scheduled.

Troubleshooting Guide: Error 10733 — “A nested INSERT…” in Microsoft SQL Server 2025

Why This Error Happens

Error 10733 occurs when a Transact-SQL statement attempts to nest an INSERT...EXEC or INSERT statement containing another data-modifying common table expression (CTE) or OUTPUT clause inside another INSERT operation, which SQL Server’s query engine does not support. This typically happens due to flawed stored procedure logic, nested triggers that fire additional INSERT operations, or ORM-generated queries (like Entity Framework) that improperly wrap bulk insert operations. Rather than a system corruption issue, this is a T-SQL syntax/architecture violation that the SQL Server 2025 parser rejects at compile time.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) 20 or later (required for full SQL Server 2025 compatibility) and locate the exact batch referenced in the error log.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025 to extract the full query text and line number tied to Error 10733.
• Run DBCC INPUTBUFFER(<session_id>) while the session is active to capture the exact nested statement if the log output is truncated.

Step 2: Refactor the Nested INSERT Logic

• Rewrite the query to eliminate direct nesting — use an intermediate temporary table or table variable instead.
• Example fix pattern:

  INSERT INTO #TempStaging
  SELECT * FROM SourceTable;

  INSERT INTO FinalTable
  SELECT * FROM #TempStaging;

• If the issue stems from INSERT ... EXEC combined with an OUTPUT clause, decouple the EXEC call into its own step and capture results separately before the final INSERT.

Step 3: Audit Triggers and Stored Procedures

• Use sp_helptext '<TriggerName>' to inspect any AFTER or INSTEAD OF triggers on the target table that may be firing a secondary INSERT internally.
• Disable suspect triggers temporarily using DISABLE TRIGGER <TriggerName> ON <TableName> to confirm whether the trigger is the nesting source.
• Update application-layer code (e.g., Entity Framework migrations or Dapper queries) to avoid auto-generated nested INSERT batches.

Step 4: Validate Compatibility Level Settings

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database isn’t running under an older compatibility mode that handles nested statements differently.
• Update to the SQL Server 2025 native compatibility level (170) using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Re-run the original transaction in a test/staging environment and confirm Error 10733 no longer appears in the ERRORLOG or in SSMS output.
• If the error persists after refactoring, use SQL Server Profiler (or Extended Events in Windows 11 client tools) to trace the exact execution plan and confirm no hidden nested INSERT is being generated by a linked server call or CLR procedure.

Troubleshooting Guide: Error 10732 – Nested INSERT Structure Not Supported

Why This Error Happens

Error 10732 occurs when a Transact-SQL statement attempts to nest an INSERT statement directly inside another INSERT, UPDATE, DELETE, or MERGE statement without using an intermediate construct such as a Common Table Expression (CTE), derived table, or OUTPUT clause. SQL Server 2025’s query parser strictly validates statement nesting during the compilation phase, and this restriction exists because the engine cannot resolve execution order and row-locking semantics when write operations are directly embedded within one another. This is almost always a T-SQL syntax/design issue in application code, stored procedures, or ORM-generated queries rather than a server configuration or permissions problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from SQL Server Management Studio (SSMS), Extended Events, or the application’s error log/stack trace.
• Look specifically for an INSERT INTO ... VALUES (SELECT ... INSERT ...) pattern or similar nested write operations.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025 for the full query context if the error originated from a background job.

Step 2: Refactor the Query Using a Staging Table or OUTPUT Clause

• Replace the nested INSERT with an OUTPUT INTO clause to capture inserted rows into a temporary table, for example:
  INSERT INTO dbo.TargetTable OUTPUT INSERTED.* INTO #TempCapture SELECT * FROM dbo.SourceTable;
• Alternatively, break the logic into two sequential statements: first populate a temporary table (#Temp or a table variable), then perform the second INSERT referencing that temporary result set.
• Avoid wrapping an INSERT inside a subquery passed to another DML statement.

Step 3: Update Stored Procedures and ORM-Generated Code

• If using Entity Framework, Dapper, or another ORM on a Windows 11 development machine, inspect the generated SQL via SQL Profiler or the ORM’s logging feature.
• Manually override auto-generated nested insert logic by writing a custom stored procedure instead of relying on the ORM’s default batch insert behavior.
• Deploy the corrected stored procedure using ALTER PROCEDURE and recompile with sp_recompile 'dbo.YourProcName';.

Step 4: Validate Trigger Logic (If Applicable)

• Check any AFTER INSERT or INSTEAD OF INSERT triggers on the target table, as triggers that themselves issue an INSERT against the same table structure can trigger this error indirectly.
• Run sp_helptrigger 'dbo.TargetTable'; to list all triggers and review their definitions in SSMS’s Object Explorer.
• Modify the trigger to use a staging table pattern rather than a direct nested insert call.

Alternative or Verification Steps

• Re-execute the corrected query in SSMS or Azure Data Studio and confirm it completes without Error 10732, then check sys.dm_exec_requests to ensure no blocking or unexpected locks occurred.
• If the error persists in a third-party application, enable SQL Server Extended Events tracing (sqlserver.error_reported) to capture the exact query text at runtime and escalate to the vendor with the captured batch text for a permanent code fix.

Troubleshooting Guide: Error 10731 – Nested INSERT Structure Not Supported

Why This Error Happens

Error 10731 occurs when a T-SQL statement attempts to nest an INSERT statement inside another INSERT, UPDATE, or MERGE operation beyond what SQL Server 2025’s query parser supports. This typically happens when developers try to use multi-level Common Table Expressions (CTEs) combined with OUTPUT INTO clauses that feed directly into another DML operation. The root cause is almost always a T-SQL syntax/structure violation rather than a permissions or connectivity issue, meaning the fix lives in the query itself, not the server configuration.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio and locate the exact batch that threw Error 10731 using the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Run SELECT * FROM sys.dm_exec_query_stats cross-applied with sys.dm_exec_sql_text to pull the exact query handle that failed.
• Confirm the query contains a pattern like INSERT INTO TableA OUTPUT INSERTED.* INTO TableB (SELECT ... INSERT ...).

Step 2: Refactor the Nested INSERT Logic

• Break the single nested statement into two sequential, independent statements using a temporary table or table variable as an intermediary.
• Example fix: First run INSERT INTO #TempStaging SELECT ... OUTPUT INSERTED.ID INTO @LogTable, then run a second INSERT INTO FinalTable SELECT * FROM #TempStaging separately.
• Avoid placing OUTPUT clauses that reference nested SELECT statements containing their own INSERT logic.

Step 3: Update Compatibility Level and Query Store Settings

• Open SSMS, right-click the database, go to Properties > Options, and verify the Compatibility Level is set to 170 (SQL Server 2025) to ensure the parser is using the latest DML nesting rules.
• Run the following command to confirm:
  ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170;
• Clear the plan cache for the affected database to remove any stale execution plans referencing the old faulty syntax: DBCC FREEPROCCACHE;

Step 4: Re-deploy and Test the Corrected Stored Procedure

• Drop and recreate the stored procedure or trigger containing the fixed logic using DROP PROCEDURE IF EXISTS followed by CREATE PROCEDURE.
• Deploy the change via SQL Server Management Studio or through your CI/CD pipeline (Azure DevOps/GitHub Actions) if using Windows Server 2025 automated deployment pipelines.

Alternative or Verification Steps

• Verification: Execute the refactored procedure manually in a test environment using EXEC sp_YourProcedure and confirm no Error 10731 appears in the Messages tab of SSMS.
• Alternative: If refactoring is not immediately possible, wrap the nested logic inside a MERGE statement with a single OUTPUT clause instead of chaining separate INSERT commands, as MERGE natively supports more complex output routing in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 10730 — “A nested INSERT…”

Why This Error Happens

Error 10730 occurs when a query attempts to execute an INSERT statement that calls a table-valued function (TVF) or a stored procedure which itself contains another INSERT...EXEC or nested data modification operation targeting the same or a dependent object. SQL Server’s query processor does not support nesting INSERT statements more than one level deep when combined with EXEC calls, multi-statement TVFs, or recursive CTE logic that triggers DML internally. This is typically a T-SQL design/architecture issue rather than a server configuration or permissions problem, though corrupted compiled plans or outdated statistics can sometimes surface this error unexpectedly in SQL Server 2025’s updated query optimizer.

Step-by-Step Resolution Guide

Step 1: Identify the Nested INSERT Source

• Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and locate the exact stored procedure or function referenced in the error message.
• Use sp_helptext '<ProcedureName>' to review the full definition and find where INSERT INTO ... EXEC or a nested INSERT inside a called object occurs.
• Check Dynamic Management Views (DMVs) via sys.dm_exec_query_plan to confirm which execution plan triggered the error.

Step 2: Refactor the Query to Remove Nesting

• Replace the nested INSERT...EXEC with a staging table approach: first EXEC the procedure into a temporary table (#TempResults), then perform the second INSERT separately.
• Example pattern:
• CREATE TABLE #TempResults (...)
• INSERT INTO #TempResults EXEC dbo.ProcedureName
• INSERT INTO FinalTable SELECT * FROM #TempResults
• If using a multi-statement table-valued function, convert it to an inline TVF where possible, as inline TVFs do not trigger this nesting restriction.

Step 3: Clear Cached Execution Plans and Update Statistics

• Run DBCC FREEPROCCACHE to remove potentially corrupted or stale cached plans that may be causing the optimizer to misinterpret the nested call.
• Update statistics on all affected tables using UPDATE STATISTICS <TableName> WITH FULLSCAN.
• Restart the SQL Server service via services.msc on Windows Server 2025 (locate “SQL Server (MSSQLSERVER)”) if the issue persists after cache clearing.

Step 4: Validate Compatibility Level Settings

• Check the database compatibility level using SELECT compatibility_level FROM sys.databases WHERE name = 'YourDBName'.
• If the database was upgraded from an older SQL Server version, ensure compatibility level is set to 170 (SQL Server 2025) using:
• ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170
• Lower compatibility levels can sometimes cause the new optimizer to handle nested EXEC/INSERT patterns inconsistently.

Alternative or Verification Steps

• Re-run the original query or application process that triggered Error 10730 and confirm no error is returned; check SQL Server Logs via EXEC sp_readerrorlog to verify no recurrence.
• As an alternative, if refactoring isn’t immediately feasible, wrap the outer INSERT in a TRY...CATCH block combined with a service broker queue or an intermediate view to decouple the nested execution calls entirely.

Troubleshooting Guide: SQL Server Error 10729 — “A Nested INSERT…”

Why This Error Happens

Error 10729 occurs when a stored procedure or trigger attempts to execute an INSERT statement containing an EXEC or nested INSERT...EXEC call from within another INSERT...EXEC context that is already active. SQL Server 2025 does not support recursive or nested INSERT...EXEC operations because it cannot maintain a consistent rowset buffer across multiple simultaneously open execution contexts. This typically surfaces when a trigger fires on a table that is the target of an INSERT...EXEC, and that trigger (or a procedure it calls) also attempts its own INSERT...EXEC operation.

Step-by-Step Resolution Guide

Step 1: Identify the Nested Call Chain

• Review the stored procedure or trigger definition using sp_helptext 'YourProcedureName' in SQL Server Management Studio (SSMS).
• Look for any INSERT INTO TableName EXEC ... statement that occurs inside a procedure already being called via another INSERT...EXEC.
• Check triggers on the target table with: SELECT name FROM sys.triggers WHERE parent_id = OBJECT_ID('YourTableName');

Step 2: Refactor the Procedure to Avoid Nesting

• Replace the inner INSERT...EXEC with a table-valued function (TVF) if the nested procedure only needs to return a result set.
• Alternatively, use a temporary table (#TempTable) to capture the first-level EXEC output, then perform a separate INSERT INTO FinalTable SELECT * FROM #TempTable outside the nested context.
• Example pattern:

  CREATE TABLE #Temp (Col1 INT, Col2 VARCHAR(50));
  INSERT INTO #Temp EXEC dbo.InnerProcedure;
  INSERT INTO FinalTable SELECT * FROM #Temp;

Step 3: Disable or Modify Conflicting Triggers

• If the error originates from a trigger performing its own INSERT...EXEC, convert the trigger logic to use OUTPUT clauses or a staging table instead.
• Temporarily disable the trigger for testing: DISABLE TRIGGER YourTriggerName ON YourTableName;
• Re-enable once refactored: ENABLE TRIGGER YourTriggerName ON YourTableName;

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to Microsoft Update Catalog or use SQL Server Setup on Windows Server 2025 / Windows 11 to check for the latest Cumulative Update (CU).
• Apply via: Control Panel > Programs > Microsoft SQL Server 2025 > Check for Updates.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after installation.

Alternative or Verification Steps

• Run the original query or procedure again in SSMS and confirm Error 10729 no longer appears in the Messages tab.
• If the issue persists, use SQL Server Profiler or Extended Events to trace the exact call stack and confirm no remaining nested INSERT...EXEC paths exist within linked triggers or procedures.

Troubleshooting Guide: Error 10728 — “A nested INSERT…” in SQL Server 2025

Why This Error Happens

Error 10728 occurs when a stored procedure, trigger, or function attempts to execute an INSERT...EXEC statement from within another INSERT...EXEC call, a nesting pattern that SQL Server explicitly disallows due to transaction log and rowset-buffering constraints. This typically happens after application updates introduce chained procedure calls, or when ORM-generated code (Entity Framework, Dapper, etc.) wraps data-returning procedures inside another data-consuming insert operation. It can also surface when a DBA refactors procedures and inadvertently creates a call chain where Procedure A calls INSERT...EXEC ProcedureB, and Procedure B itself contains another INSERT...EXEC.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Procedure Chain

• Open SQL Server Management Studio (SSMS) 21 connected to your SQL Server 2025 instance.
• Run SELECT * FROM sys.dm_exec_procedure_stats to find recently executed procedures around the error timestamp.
• Check the Windows Server 2025 Event Viewer under Applications and Services Logs > MSSQLSERVER for the full error stack, which lists the exact procedure names in the nested call.

Step 2: Refactor the Nested INSERT…EXEC Logic

• Locate the outer procedure (e.g., dbo.ProcessOrders) using SSMS’s Object Explorer > Databases > [YourDB] > Programmability > Stored Procedures.
• Replace the inner INSERT...EXEC call with a table-valued function (TVF) or populate a temporary table (#TempResults) using OPENQUERY or direct SELECT INTO instead of a second nested insert.
• Example fix: Change INSERT INTO #Temp EXEC dbo.InnerProc so that dbo.InnerProc no longer contains its own INSERT...EXEC call — have it return a result set directly via SELECT instead.

Step 3: Use a Service Broker or Table Variable Workaround

• For cases where restructuring isn’t feasible, use sp_executesql combined with a table variable passed via OUTPUT parameters instead of nested inserts.
• Alternatively, break the chain using an intermediate physical or temp table: Procedure A inserts results into ##GlobalTemp, and Procedure B reads from ##GlobalTemp rather than being called inline.

Step 4: Apply Latest SQL Server 2025 Cumulative Update

• Navigate to Microsoft Update Catalog and confirm your instance is on the latest Cumulative Update (CU) for SQL Server 2025, as early CUs had edge-case bugs in nested execution detection.
• Run SELECT @@VERSION; in SSMS to confirm your current build number matches the latest released CU.

Alternative or Verification Steps

• Re-run the original transaction or application workflow that triggered Error 10728 and confirm no exception is logged in SQL Server Profiler or Extended Events session sqlserver.error_reported.
• If refactoring isn’t immediately possible, as a temporary workaround, wrap the outer call in a TRY...CATCH block and route data through a permanent staging table instead of nested INSERT...EXEC, then validate data integrity with a SELECT COUNT(*) comparison against expected row counts.

Troubleshooting Guide: SQL Server Error 10727 — “A nested INSERT…”

Why This Error Happens

Error 10727 occurs when a stored procedure, trigger, or user-defined function attempts to execute an INSERT statement that calls another procedure or function which itself tries to perform a nested INSERT...EXEC operation. SQL Server 2025 does not support nesting INSERT...EXEC calls beyond one level deep, so this typically results from poorly structured legacy T-SQL code or an ORM-generated query that chains multiple procedure calls. This can also surface after an upgrade from older SQL Server versions where compatibility-level changes expose previously tolerated nesting patterns.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query Chain

• Open SQL Server Management Studio (SSMS) and enable Include Actual Execution Plan to trace which procedure is triggering the nested call.
• Check the application or job log for the exact batch that threw Error 10727; note the procedure names involved.
• Run sp_helptext '<ProcedureName>' against each suspected procedure to review its INSERT...EXEC logic.

Step 2: Refactor the Nested INSERT Logic

• Replace the inner INSERT...EXEC call with a table-valued function (TVF) or a temporary table populated via an intermediate step instead of direct nesting.
• Example fix: Instead of INSERT INTO TableA EXEC ProcB where ProcB also runs INSERT...EXEC, modify ProcB to write to a staging table first, then insert from that staging table in the outer call.
• Save and redeploy the updated procedure using SSMS or via a deployment script in Azure Data Studio.

Step 3: Adjust Database Compatibility Level (If Applicable)

• Open SSMS, right-click the affected database → Properties → Options.
• Check the Compatibility Level field; if recently upgraded, temporarily test reverting to the prior level (e.g., SQL Server 2022 compatibility) to confirm this is the triggering factor.
• Run:
  ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160;
• Note: This is a diagnostic step only — plan to fix the underlying nested query rather than permanently downgrading compatibility.

Step 4: Clear Cached Execution Plans

• After redeploying the corrected procedure, clear any stale cached plans that may still reference the old nested structure.
• Run:
  DBCC FREEPROCCACHE;
• Restart the SQL Server service via Services.msc on Windows Server 2025 if the error persists after cache clearing.

Alternative or Verification Steps

• Re-run the original failing transaction in SSMS and confirm Error 10727 no longer appears in the Messages tab.
• If the error persists, use SQL Server Profiler or Extended Events to capture the exact call stack in real time and confirm no other nested procedure in the chain still violates the single-level INSERT...EXEC restriction.

Troubleshooting Guide: Error 10726 — User Defined Aggregates Do Not Support Default Parameters

Why This Error Happens

Error 10726 occurs when a CLR user-defined aggregate (UDA) definition in SQL Server 2025 includes a parameter with a default value assigned, which is an unsupported configuration for aggregate functions. SQL Server’s parser validates aggregate signatures at creation time, and because the SQL Server engine does not permit optional/default parameters in CREATE AGGREGATE statements, the DDL operation is rejected. This is typically caused by a developer porting a scalar function or table-valued function signature (which does support defaults) directly into an aggregate definition without adjusting the parameter list.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Aggregate Definition

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 10726.
• Run the following query to locate recently failed or existing aggregate objects:

  SELECT name, object_id, create_date 
  FROM sys.objects 
  WHERE type = 'AF';

• Review the T-SQL script or deployment pipeline (e.g., Azure DevOps, SSDT project) that issued the CREATE AGGREGATE statement causing the error.

Step 2: Remove Default Values from the Aggregate Parameters

• Locate the CREATE AGGREGATE statement in your source .sql file or CLR project deployment script.
• Edit the parameter list to strip any = DEFAULT_VALUE assignments. For example, change:

  CREATE AGGREGATE dbo.MyAggregate (@value INT = 0)
  RETURNS INT
  EXTERNAL NAME MyAssembly.[MyNamespace.MyAggregateClass];

to:

  CREATE AGGREGATE dbo.MyAggregate (@value INT)
  RETURNS INT
  EXTERNAL NAME MyAssembly.[MyNamespace.MyAggregateClass];

• Save the corrected script in your source control repository.

Step 3: Rebuild and Redeploy the CLR Assembly

• If the underlying .NET class also expects a default parameter, remove the default value logic from the Accumulate method signature in your C# source code.
• Recompile the assembly in Visual Studio 2022/2025 targeting the .NET version supported by SQL Server 2025 CLR integration.
• Redeploy the .dll using CREATE ASSEMBLY or update via SSDT, then re-run the corrected CREATE AGGREGATE script.

Step 4: Clear Cached Deployment Artifacts (If Using SSDT/DACPAC)

• Navigate to the project’s bin\Debug or bin\Release folder on your Windows 11 workstation and delete any stale .dacpac files.
• Rebuild the SSDT project to regenerate a clean deployment package free of the invalid default parameter metadata.
• Redeploy the DACPAC to the target SQL Server 2025 instance on Windows Server 2025.

Alternative or Verification Steps

• Verify the fix by re-running CREATE AGGREGATE in SSMS — a successful execution with no Error 10726 confirms the parameter definition is now valid.
• If defaults are a hard business requirement, implement default-value logic inside a wrapper scalar function that calls the aggregate internally, since scalar functions (unlike aggregates) support default parameters natively.

Troubleshooting Guide: Error 10725 – “Cannot use the VARYING option in a DECLARE”

Why This Error Happens

This error occurs because the VARYING keyword is only valid when declaring a cursor return parameter for a stored procedure, not when declaring a standard local variable with DECLARE @variable. SQL Server’s T-SQL parser rejects the VARYING option in this context because it is reserved exclusively for cursor-type OUTPUT parameters, as defined by the ODBC/ISO SQL specification. This is almost always a syntax error in the script rather than a server configuration, permissions, or corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Line in the Script

• Open the query in SQL Server Management Studio (SSMS) and locate the line number referenced in the error message (Msg 10725, Level 15, State 1, Line X).
• Look specifically for a DECLARE statement that includes the word VARYING immediately after a data type, for example: DECLARE @MyVar VARCHAR VARYING(50).
• Confirm whether the intent was to declare a simple variable or a cursor output parameter.

Step 2: Correct the Syntax Based on Intent

• If declaring a standard variable, remove the VARYING keyword entirely. Correct syntax: DECLARE @MyVar VARCHAR(50);
• If the goal was to use VARYING with a cursor, ensure it is used only within a stored procedure parameter list in the correct context: CREATE PROCEDURE MyProc @MyCursor CURSOR VARYING OUTPUT AS ...
• Save the corrected script to a local path, such as C:\Scripts\FixedQuery.sql, before re-running it.

Step 3: Validate the Script in a Test Environment

• Open a new query window in SSMS connected to a non-production instance of SQL Server 2025.
• Execute the corrected script using SQLCMD or the SSMS “Execute” button (F5) to confirm the parser no longer throws Error 10725.
• Check the Messages tab in SSMS for “Command(s) completed successfully.”

Step 4: Audit Related Scripts and Stored Procedures

• Use SSMS “Object Explorer” to search stored procedures and functions for similar misuse of VARYING by running:

  SELECT OBJECT_NAME(object_id), definition 
  FROM sys.sql_modules 
  WHERE definition LIKE '%VARYING%';

• Update any additional instances found to prevent recurring deployment failures.

Alternative or Verification Steps

• To verify the fix, re-deploy the corrected .sql file through your CI/CD pipeline or manually via SSMS and confirm the object (procedure/script) compiles without Error 10725 in the Activity Monitor or Error Log.
• If the error persists despite removing VARYING, check for invisible/hidden Unicode characters in the script by re-typing the DECLARE line manually in a plain text editor like Notepad before re-pasting it into SSMS.

Why This Error Happens

Error 10724 occurs because the FORCESEEK table hint is only valid when SQL Server is reading data from a table (SELECT, UPDATE source, DELETE source), not when the table is the target of an INSERT operation. This happens because INSERT operations don’t use seek-based access against the destination table—they append or merge rows directly, so the query optimizer has no seek plan to force. The error typically surfaces after migrating stored procedures or ETL scripts to SQL Server 2025, where hint validation has become stricter, or when a hint originally written for a SELECT subquery was mistakenly carried over to the outer INSERT statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the failing batch and note the exact line number referenced in the error message.
• Search the procedure or script for INSERT INTO ... WITH (FORCESEEK) or INSERT INTO ... WITH (FORCESEEK, INDEX(...)).

Step 2: Remove the Invalid Hint from the INSERT Target

• Edit the script and strip FORCESEEK from the target table clause, e.g. change:
  INSERT INTO dbo.Orders WITH (FORCESEEK) (OrderID, CustomerID) SELECT ...
  to:
  INSERT INTO dbo.Orders (OrderID, CustomerID) SELECT ...
• If the hint was intended to optimize the source query, relocate it to the SELECT or FROM clause instead:
  INSERT INTO dbo.Orders (OrderID, CustomerID) SELECT * FROM dbo.Staging WITH (FORCESEEK)

Step 3: Validate Index Usage on the Source Table

• Confirm the source table referenced in the SELECT actually has a usable nonclustered index for the FORCESEEK hint to be effective.
• Run sp_helpindex 'dbo.Staging' to list available indexes.
• If no suitable index exists, consider using FORCESEEK (IndexName) explicitly or removing the hint entirely and letting the optimizer choose.

Step 4: Recompile and Test the Procedure

• Execute EXEC sp_recompile 'dbo.YourProcedureName'; in SSMS to clear any cached invalid plans.
• Re-run the corrected script in a test/staging environment on Windows Server 2025 before deploying to production.

Alternative or Verification Steps

• Verify the fix by executing the corrected INSERT statement directly in SSMS — Error 10724 should no longer appear, and the batch should complete with the expected row count affected.
• As an alternative, if you need to control the join/access strategy in complex INSERT…SELECT…JOIN statements, apply FORCESEEK only inside the SELECT portion or use OPTION (TABLE HINT(SourceTable, FORCESEEK)) at the query level instead of attaching it directly to the INSERT target.

Troubleshooting Guide: SQL Server Error 10723 in MERGE Statement

Why This Error Happens

Error 10723 occurs when a MERGE statement references a target table or view that contains a column with an incompatible or unsupported data type for the operation being performed (commonly TIMESTAMP/ROWVERSION columns being explicitly referenced in UPDATE or INSERT clauses). This is a syntax/schema validation error, not a permissions or connectivity issue, and it is thrown during query compilation before execution begins. It typically surfaces after a schema change, an improperly generated MERGE script, or when migrating MERGE logic from an older SQL Server version to SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column

• Open the full MERGE statement in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Run EXEC sp_help 'YourTableName'; against the target table to list all column data types.
• Look specifically for ROWVERSION, TIMESTAMP, or computed columns referenced in the INSERT, UPDATE, or VALUES clause of the MERGE statement.

Step 2: Modify the MERGE Statement to Exclude Incompatible Columns

• Remove any explicit reference to ROWVERSION/TIMESTAMP columns from the UPDATE SET clause.
• In the INSERT column list, exclude the incompatible column entirely and let SQL Server auto-generate its value.
• Example correction:

  MERGE INTO TargetTable AS T
  USING SourceTable AS S
  ON T.ID = S.ID
  WHEN MATCHED THEN
      UPDATE SET T.Name = S.Name  -- do NOT include T.RowVersionCol here
  WHEN NOT MATCHED THEN
      INSERT (ID, Name)          -- exclude RowVersionCol from column list
      VALUES (S.ID, S.Name);

Step 3: Validate Table Schema and Compatibility Level

• Check the database compatibility level with: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If the database was recently migrated, ensure compatibility level is set to 170 (SQL Server 2025) using: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
• Re-run sp_help on the target table after any schema alterations to confirm no residual incompatible columns remain in the MERGE scope.

Step 4: Rebuild and Test the MERGE Statement

• Drop and recreate any stored procedures or triggers that encapsulate the MERGE logic to clear cached execution plans.
• Use SET SHOWPLAN_TEXT ON; before running the corrected MERGE statement to validate the execution plan compiles without error.
• Execute the MERGE statement against a test dataset in a non-production environment first.

Alternative or Verification Steps

• Run the corrected MERGE statement with SET NOCOUNT ON; and check for successful row counts (inserted/updated) with no Error 10723 returned.
• If the error persists, use sys.dm_exec_describe_first_result_set or OBJECT_DEFINITION() on the target table/view to confirm no underlying view or synonym is reintroducing the incompatible column type indirectly.

Why This Error Happens

This error occurs because SQL Server 2025 does not permit the DISTINCT keyword in a SELECT statement when the FROM clause references a nested INSERT...OUTPUT statement (a data-modifying common table expression or derived table). This restriction exists because the engine cannot guarantee deterministic row elimination when the underlying operation is actively modifying data. It is a T-SQL syntax/design limitation rather than a configuration, permissions, or file-corruption issue, meaning the query itself must be rewritten to comply with SQL Server’s execution model.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
• Locate the query where SELECT DISTINCT ... FROM (INSERT INTO ... OUTPUT ...) AS alias pattern is used.
• Confirm the error references Event ID / Error 10722 in the Messages tab or in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Remove DISTINCT from the Outer Query

• Edit the query to eliminate the DISTINCT keyword directly from the outer SELECT statement.
• Example fix:

  -- Instead of:
  SELECT DISTINCT col1, col2
  FROM (INSERT INTO TargetTable OUTPUT inserted.col1, inserted.col2) AS ins

  -- Use:
  SELECT col1, col2
  FROM (INSERT INTO TargetTable OUTPUT inserted.col1, inserted.col2) AS ins

• Save the modified script to a .sql file in your working directory, e.g., C:\Scripts\FixedQuery.sql.

Step 3: Deduplicate Results Using a Temporary Table or CTE

• Capture the INSERT...OUTPUT results into a temporary table first, then apply DISTINCT separately.
• Example:

  CREATE TABLE #TempOutput (col1 INT, col2 VARCHAR(50));

  INSERT INTO TargetTable
  OUTPUT inserted.col1, inserted.col2 INTO #TempOutput
  SELECT col1, col2 FROM SourceTable;

  SELECT DISTINCT col1, col2 FROM #TempOutput;

  DROP TABLE #TempOutput;

• This approach separates the data-modification step from the deduplication step, which SQL Server fully supports.

Step 4: Re-run and Validate the Query

• Execute the rewritten script in SSMS or via sqlcmd -S localhost -i C:\Scripts\FixedQuery.sql.
• Confirm no Error 10722 appears in the output or in the SQL Server error log.

Alternative or Verification Steps

• Run SELECT * FROM sys.messages WHERE message_id = 10722; to confirm the exact wording and severity level of the error for your SQL Server 2025 build.
• As an alternative, use a MERGE statement with an OUTPUT clause into a staging table, then apply DISTINCT on the staging table — this avoids the nested INSERT restriction entirely while achieving the same deduplicated result set.

Troubleshooting Guide: Error 10721 – “The WHERE CURRENT OF Clause Is Not Allowed in a Nested INSERT”

Why This Error Happens

This error occurs when a cursor-based UPDATE or DELETE statement using WHERE CURRENT OF is executed inside an INSERT...EXEC block or a nested stored procedure call that references the same cursor context. SQL Server 2025’s query processor cannot resolve the active cursor position when the statement is wrapped inside another INSERT operation, because cursor state is not propagated across nested execution contexts. This typically results from legacy T-SQL code that mixes server-side cursors with nested procedure calls, often surfacing after a migration or upgrade from an older SQL Server version.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and enable Include Actual Execution Plan.
• Search the stored procedure or batch for WHERE CURRENT OF combined with INSERT ... EXEC.
• Use sp_helptext '<procedure_name>' to export the full procedure definition for review.
• Check SQL Server Error Logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact line number referenced alongside Error 10721.

Step 2: Refactor the Cursor Logic

• Remove the WHERE CURRENT OF clause from within the nested INSERT...EXEC block.
• Replace the cursor-based row targeting with an explicit WHERE clause using a primary key or unique identifier column instead of CURRENT OF.
• If the nested call is a stored procedure, move the INSERT...EXEC call outside the cursor loop, or capture cursor row data into a temporary table (#TempCursorRows) first, then perform the nested insert/update against that temp table.

Step 3: Update Compatibility Level and Rebuild Logic

• Verify the database compatibility level:

  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If the database is running on an older compatibility level (e.g., 130 or 140), update it to 170 (SQL Server 2025) using:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Recompile all affected stored procedures using sp_recompile '<procedure_name>' to clear any cached execution plans referencing the old cursor logic.

Step 4: Test in Isolation

• Run the refactored procedure independently in a test/staging instance on Windows Server 2025 before deploying to production.
• Confirm no other nested procedures in the call chain reuse the same cursor name, as cursor scope conflicts can reintroduce the error.

Alternative or Verification Steps

• Re-run the original failing transaction or batch job; confirm Error 10721 no longer appears in the SQL Server Error Log or SSMS output pane.
• If refactoring the cursor is not immediately feasible, use a MERGE statement or set-based UPDATE...FROM logic as an alternative to eliminate the dependency on WHERE CURRENT OF entirely.

Why This Error Happens

Error 10720 occurs because SQL Server does not allow an OUTPUT INTO clause to be used inside an INSERT statement that is itself nested within another INSERT...EXEC or a derived table/CTE acting as an insert source. This is a T-SQL language limitation rather than a configuration or permissions issue—the query optimizer cannot reconcile capturing output rows into a table variable or temp table while the INSERT operation is simultaneously being consumed by an outer statement. It typically surfaces when developers try to chain multiple data modification operations together (e.g., using INSERT INTO ... OUTPUT INTO ... as a source for another INSERT).

Step-by-Step Resolution Guide

Step 1: Identify the Nested Statement Causing the Conflict

• Review the query plan or script that triggered Error 10720 in SQL Server Management Studio (SSMS) or Azure Data Studio.
• Look specifically for patterns like INSERT INTO TableA SELECT * FROM (INSERT INTO TableB OUTPUT INSERTED.* INTO @TempVar ...).
• Confirm whether the nested INSERT is wrapped inside a CTE, subquery, or INSERT...EXEC block.

Step 2: Refactor the Query to Separate the Operations

• Break the single nested statement into two sequential, independent statements.
• First, run the inner INSERT with its OUTPUT INTO clause targeting a table variable or temporary table (e.g., DECLARE @OutputTable TABLE (...)).
• Second, run a separate INSERT INTO TableA SELECT * FROM @OutputTable to move the captured data into the final destination.

Step 3: Update Stored Procedures or ORM-Generated Scripts

• If the nested INSERT originates from an Entity Framework, Dapper, or other ORM-generated query, check the mapping configuration for triggers or computed columns that may be forcing an automatic OUTPUT INTO injection.
• Manually override the generated SQL in the stored procedure to use the two-step pattern from Step 2.
• Save the updated procedure using ALTER PROCEDURE and redeploy via SQL Server Management Studio or your CI/CD pipeline.

Step 4: Test in a Non-Production Environment First

• Deploy the refactored script to a staging instance of SQL Server 2025 running on Windows Server 2025 or Windows 11.
• Execute the script manually in SSMS to confirm Error 10720 no longer appears.
• Check SQL Server Error Logs (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for any residual warnings.

Alternative or Verification Steps

• Run SELECT * FROM @OutputTable immediately after the refactored insert to confirm rows were captured correctly before the second insert executes.
• If refactoring is not immediately possible, use a trigger on the target table to log INSERTED values instead of relying on OUTPUT INTO within a nested structure.