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

by

Troubleshooting Guide: SQL Server Error 445 – Nested COLLATE Clause Conflict

Why This Error Happens

This error occurs because Transact-SQL does not permit a COLLATE clause to be applied to an expression that already contains another COLLATE clause within the same statement. This typically happens when a query dynamically concatenates strings, views, or computed columns that each carry their own explicit collation, and a subsequent COLLATE operation is applied on top of the result. It is a syntax/logic-level restriction in the query itself rather than a server configuration, permissions, or corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Capture the exact statement from the application log or SQL Server error details (Error 445 will reference the batch that failed).
  • Use SQL Server Management Studio (SSMS) or Azure Data Studio to run the query in isolation and reproduce the error.
  • Check for nested COLLATE keywords by searching the query text for multiple instances of the word COLLATE.

Step 2: Simplify Expressions with Redundant COLLATE Clauses

  • Remove the outer COLLATE clause if the inner expression (such as a column or subquery) already specifies a collation.
  • If combining columns from different collations (e.g., in a JOIN or UNION), apply COLLATE only once — on the final combined result — using a syntax like:
  SELECT ColumnA COLLATE DATABASE_DEFAULT
  FROM TableA
  • Avoid wrapping already-collated computed columns or views with an additional COLLATE statement.

Step 3: Check Views, Computed Columns, and Linked Servers

  • Inspect any views or computed columns referenced in the query for embedded COLLATE definitions.
  • Run the following to locate computed columns with collation settings:
  SELECT name, definition 
  FROM sys.computed_columns 
  WHERE definition LIKE '%COLLATE%'
  • If the query involves linked servers, confirm that the remote and local server collations aren’t both being explicitly cast in the same expression.

Step 4: Rewrite Using CAST or CONVERT to Reset Collation Context

  • Instead of chaining COLLATE clauses, use CAST or CONVERT to normalize the data type first, then apply a single COLLATE clause.
  • Example fix:
  SELECT CAST(ColumnA AS NVARCHAR(100)) COLLATE SQL_Latin1_General_CP1_CI_AS
  FROM TableA
  • Test the rewritten query in a non-production environment before deploying.

Step 5: Validate Application-Generated Queries

  • If the query is generated by an ORM (Entity Framework, Dapper, etc.) or a reporting tool, review the query-building logic for automatic collation injection.
  • Update application code or stored procedures to eliminate duplicate COLLATE clauses before sending queries to SQL Server 2025.

Alternative or Verification Steps

  • Re-run the corrected query in SSMS/Azure Data Studio and confirm Error 445 no longer appears in the Messages tab.
  • As a quick alternative, temporarily set the database default collation using ALTER DATABASE ... COLLATE (if consistent across all tables) to avoid needing per-column COLLATE clauses altogether.