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

by

Why This Error Happens

SQL Server Error 459 occurs when a Unicode-only collation (such as those with _UTF8 or certain supplementary character-aware collations) is explicitly applied to a non-Unicode data type like char, varchar, or text. This typically happens during table creation, column alteration, or database restoration scripts where a collation designed exclusively for nchar/nvarchar columns is mistakenly assigned to a legacy ANSI/non-Unicode column. It’s a data-type compatibility mismatch rather than a permissions or connectivity issue, and it will block DDL execution until corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Collation and Column

  • Locate the exact CREATE TABLE, ALTER TABLE, or CREATE DATABASE statement that triggered Error 459.
  • Run SELECT name FROM sys.fn_helpcollations() WHERE name = 'YourCollationName' to confirm if it is Unicode-only.
  • Check documentation or use sp_help 'TableName' to see current column definitions and collations.

Step 2: Change the Column Data Type to a Unicode Type

  • If the collation must be retained (e.g., for UTF-8 or supplementary character support), convert the column to nchar or nvarchar.
  • Example fix:
  ALTER TABLE dbo.YourTable
  ALTER COLUMN YourColumn NVARCHAR(100) COLLATE Latin1_General_100_CI_AS_SC_UTF8;
  • Ensure application code and ODBC/OLE DB drivers support Unicode round-tripping to avoid downstream truncation issues.

Step 3: Alternatively, Choose a Non-Unicode Compatible Collation

  • If the column must remain char/varchar (e.g., for legacy compatibility), select a standard non-Unicode collation instead.
  • Example fix:
  ALTER TABLE dbo.YourTable
  ALTER COLUMN YourColumn VARCHAR(100) COLLATE Latin1_General_CI_AS;
  • Use SELECT * FROM sys.fn_helpcollations() WHERE name LIKE '%CI_AS%' to browse valid non-Unicode options.

Step 4: Update Database or Server-Level Default Collation (If Applicable)

  • If the error originates from a database-level default collation setting, open SQL Server Management Studio (SSMS) on Windows Server 2025.
  • Navigate to Database Properties > Options > Collation, and select a collation compatible with your existing non-Unicode columns.
  • Re-run the deployment script or migration package after updating the default.

Step 5: Validate Application and ETL Scripts

  • Review any SSIS packages, PowerShell scripts, or ADF pipelines that dynamically generate DDL with hardcoded Unicode-only collations.
  • Update connection string metadata or schema mapping templates to reflect the corrected data type/collation pairing.

Alternative or Verification Steps

  • Re-execute the original CREATE/ALTER script; a successful run without Error 459 confirms the collation-datatype mismatch is resolved.
  • Run SELECT name, collation_name FROM sys.columns WHERE object_id = OBJECT_ID('YourTable') to verify the column now shows a compatible collation assignment.