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

by

Troubleshooting Guide: SQL Server Error 451 — Collation Conflict

Why This Error Happens

Error 451 occurs when a SQL query compares, joins, or concatenates columns that have different collations, and SQL Server cannot automatically determine which collation should take precedence. This typically happens when linking tables from databases created with different default collations, or when joining local tables with linked server/external data sources. It can also surface after an in-place upgrade to SQL Server 2025 or a database restore from a server with a different default collation setting.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Collations

  • Run the following query to check collations of the columns referenced in the error message:
  SELECT name, collation_name 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('YourTableName');
  • Also check database-level collation using:
  SELECT name, collation_name FROM sys.databases;
  • Note the two differing collation values (e.g., SQL_Latin1_General_CP1_CI_AS vs. Latin1_General_100_CI_AS_SC).

Step 2: Apply an Explicit COLLATE Clause

  • Modify the query to force a common collation on the comparison:
  SELECT a.Column1, b.Column2
  FROM TableA a
  JOIN TableB b 
    ON a.Column1 COLLATE DATABASE_DEFAULT = b.Column2 COLLATE DATABASE_DEFAULT;
  • Alternatively, specify the exact target collation instead of DATABASE_DEFAULT if you need consistency across environments:
  ON a.Column1 COLLATE Latin1_General_100_CI_AS_SC = b.Column2

Step 3: Standardize Collation at the Column Level (Permanent Fix)

  • If this conflict recurs frequently, alter the column collation permanently:
  ALTER TABLE TableA 
  ALTER COLUMN Column1 NVARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC;
  • Ensure no indexes or constraints depend on the column before altering it—drop and recreate them afterward if necessary.

Step 4: Align Server and Database Default Collations

  • For new databases or after a migration, explicitly set the collation during creation to match your primary application requirement:
  CREATE DATABASE YourDB
  COLLATE Latin1_General_100_CI_AS_SC;
  • Verify the instance-level default collation via SQL Server Configuration Manager or by running SELECT SERVERPROPERTY('Collation');.

Alternative or Verification Steps

  • Re-run the original query or stored procedure that triggered Error 451 to confirm it now executes without the collation conflict message.
  • If the issue persists on linked server queries, use sp_addlinkedserver with the collation compatible option set to false, forcing SQL Server to handle collation translation remotely rather than locally.