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

by

Troubleshooting Guide: SQL Server Error 1541 (Sort Failure – Defective CLR Type Comparison Function)

Why This Error Happens

Error 1541 occurs when SQL Server’s sort operator encounters an inconsistent or non-deterministic comparison result while ordering rows based on a CLR (Common Language Runtime) user-defined type or user-defined function. This typically stems from a custom IComparable or IComparable<T> implementation in a CLR assembly that violates transitive ordering rules (e.g., A < B, B < C, but C < A). It can also surface after an in-place upgrade to SQL Server 2025 if the CLR assembly was compiled against an older .NET runtime and is now returning inconsistent results under the new Enable CLR Strict Security model.

Step-by-Step Resolution Guide

Step 1: Identify the Offending CLR Assembly or Type

  • Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact query and assembly name tied to the 1541 event.
  • Run SELECT * FROM sys.assemblies; and SELECT * FROM sys.assembly_types; to list all CLR types currently registered.
  • Cross-reference the failing query’s ORDER BY or GROUP BY clause to confirm it references a CLR-based column type.

Step 2: Audit and Rewrite the Comparison Logic

  • Open the source project for the CLR assembly (usually a .cs or .vb project compiled into a .dll).
  • Locate the CompareTo() method inside the IComparable or IComparable<T> interface implementation.
  • Ensure the method strictly follows transitive and reflexive ordering rules — avoid floating-point equality checks or culture-sensitive string comparisons without explicitly setting StringComparer.Ordinal.
  • Recompile the assembly targeting .NET 8/9 (the runtime bundled with SQL Server 2025) using Visual Studio 2025 or the csc.exe compiler.

Step 3: Redeploy and Re-register the Assembly

  • Drop the old assembly: DROP ASSEMBLY [YourAssemblyName];
  • Re-register the corrected assembly:
CREATE ASSEMBLY [YourAssemblyName]
FROM 'C:\SQLAssemblies\YourAssembly.dll'
WITH PERMISSION_SET = SAFE;
  • Confirm clr strict security is enabled and the assembly is properly signed or added to the trusted assemblies list:
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;
EXEC sys.sp_add_trusted_assembly @hash = <assembly_hash>, @description = N'YourAssembly';

Step 4: Restart the SQL Server Service

  • Open Services.msc on Windows Server 2025 / Windows 11.
  • Restart SQL Server (MSSQLSERVER) to flush cached execution plans referencing the old assembly.
  • Alternatively, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; to clear only affected plans without a full restart.

Alternative or Verification Steps

  • Re-run the original failing query with SET STATISTICS PROFILE ON to confirm the sort operator completes without throwing Error 1541.
  • If the error persists, temporarily revert the column to a native SQL Server data type (e.g., NVARCHAR, DECIMAL) to isolate whether the CLR type itself — rather than the query plan — is the root cause.