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

by

Troubleshooting Guide: SQL Server Error 446 – Collation Conflict

Why This Error Happens

This error occurs when SQL Server attempts to compare, concatenate, or join columns that have different collations without an explicit instruction on which collation should take precedence. It commonly arises in Windows Server 2025 / Windows 11 environments when a database or tempdb is configured with a different default collation than the server instance, or when linked servers, imported data, or migrated databases retain their original source collation settings. Since SQL Server 2025 cannot automatically infer which collation “wins” in operations like UNION, JOIN, or WHERE clauses, it throws Error 446 instead of silently choosing one.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Collations

  • Open SQL Server Management Studio (SSMS) and run the following query to check database-level collation:
  SELECT name, collation_name FROM sys.databases;
  • Check column-level collation for the specific tables involved in the query:
  SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTableName');
  • Note the exact collation names reported in the error message (e.g., SQL_Latin1_General_CP1_CI_AS vs. Latin1_General_100_CI_AS_SC).

Step 2: Apply an Explicit COLLATE Clause

  • Modify the problematic query to force a common collation using the COLLATE keyword:
  SELECT a.column1
  FROM TableA a
  JOIN TableB b
  ON a.column1 COLLATE SQL_Latin1_General_CP1_CI_AS = b.column1;
  • This is the fastest fix for ad-hoc queries, linked server operations, and cross-database joins without altering underlying schema.

Step 3: Align Database and Server Collation Settings

  • If the conflict persists across multiple objects, standardize the collation at the database level using SSMS or T-SQL:
  ALTER DATABASE YourDatabase COLLATE SQL_Latin1_General_CP1_CI_AS;
  • Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and verify the instance-level collation via the Installation Center if a full server rebuild is required.
  • For column-specific fixes, use ALTER TABLE to permanently change the column collation:
  ALTER TABLE YourTable ALTER COLUMN column1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS;

Step 4: Rebuild tempdb Collation (If Applicable)

  • If the conflict involves temporary tables, confirm tempdb is inheriting the correct collation by running:
  SELECT DATABASEPROPERTYEX('tempdb', 'Collation');
  • If misaligned, this typically requires reinstalling the instance or rebuilding system databases via command line using the /SQLCOLLATION switch during setup.

Alternative or Verification Steps

  • Verify the Fix: Re-run the original query or stored procedure that triggered Error 446. If successful, run DBCC CHECKDB to ensure no corruption occurred during collation changes.
  • Alternative Solution: If altering production schema is too risky, wrap the query logic in a view using the COLLATE DATABASE_DEFAULT keyword to dynamically normalize collation differences without touching the base tables.