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

by

Troubleshooting Guide: SQL Server Error 4191 — Collation Conflict

Why This Error Happens

This error occurs when a T-SQL operation (such as a UNION, JOIN, or comparison) references two or more character columns, variables, or expressions that have different collations, and SQL Server cannot implicitly determine which collation should take precedence. This is extremely common in environments where a database’s collation differs from the instance-level default collation, or when linked servers/temp tables (tempdb) use a different collation than the user database. It can also surface after migrating databases between servers configured with different language/locale settings during SQL Server 2025 setup.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Objects and Their Collations

  • Run the following query to check collation settings at multiple levels:
  SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation') AS DBCollation;
  SELECT SERVERPROPERTY('Collation') AS ServerCollation;
  • For the specific columns involved in the failing query, run:
  SELECT name, collation_name 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('YourTableName');
  • Note any mismatches between ServerCollation, DBCollation, and individual column collations.

Step 2: Apply the COLLATE Clause to Force Consistency

  • Modify the offending query to explicitly cast both sides of the comparison/join to the same collation using the COLLATE keyword.
  • Example fix:
  SELECT a.col1 
  FROM TableA a
  JOIN TableB b 
    ON a.col1 COLLATE DATABASE_DEFAULT = b.col1 COLLATE DATABASE_DEFAULT;
  • For a more permanent fix, alter the column’s collation directly:
  ALTER TABLE TableName 
  ALTER COLUMN ColumnName NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS;

Step 3: Check tempdb Collation Alignment

  • If the error involves temp tables (#TempTable), verify that tempdb is using the same collation as your user database, since tempdb defaults to the instance collation.
  • Navigate to SQL Server Configuration Manager on Windows Server 2025/Windows 11, locate the instance properties, and confirm the server-level collation under Advanced settings.
  • If a mismatch exists, rebuild the instance collation using the command line (requires service restart):
  Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=YourAdminAccount /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Step 4: Restart SQL Server Service and Validate

  • Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and restart the service to ensure all cached execution plans are cleared.
  • Re-run the original query that triggered Error 4191 to confirm resolution.

Alternative or Verification Steps

  • Verify the fix by executing DBCC FREEPROCCACHE; to clear cached query plans, then re-run the original problematic query to ensure Error 4191 no longer appears.
  • As an alternative, if altering column collations is not feasible due to application dependencies, wrap all cross-database or cross-table string comparisons in COLLATE DATABASE_DEFAULT directly within views or stored procedures to normalize behavior without schema changes.