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

by

Troubleshooting Guide: SQL Server Error 2231 – “The name ‘%ls’ does not denote a defined type”

Why This Error Happens

This error occurs when a Transact-SQL statement references a user-defined type (UDT), alias type, or CLR type that does not exist in the current database schema context, often because it was created in a different schema or database. It can also arise after a database restore or migration where type bindings became orphaned, or when a CLR assembly backing a user-defined type was dropped or failed to deploy correctly. In some cases, this is triggered by case-sensitivity mismatches or an incorrect schema prefix being used when referencing the type.

Step-by-Step Resolution Guide

Step 1: Verify the Type Exists in the Target Database

  • Run the following query to confirm the type is registered in sys.types:
SELECT name, schema_id, is_assembly_type 
FROM sys.types 
WHERE name = 'YourTypeName';
  • Confirm you are connected to the correct database context using USE [YourDatabaseName]; before running queries.
  • Check if the type exists under a different schema than expected (e.g., dbo.MyType vs custom.MyType).

Step 2: Correct Schema Qualification in Your Query

  • Explicitly qualify the type name with its correct schema in your DDL or DML statement:
CREATE TABLE dbo.MyTable (
    Col1 custom.MyType
);
  • Update any stored procedures, views, or functions referencing the type without a schema prefix.
  • Ensure case sensitivity matches if the instance collation is case-sensitive (e.g., SQL_Latin1_General_CP1_CS_AS).

Step 3: Re-register or Rebuild CLR-Backed Types (If Applicable)

  • If the type is a CLR user-defined type, confirm the backing assembly is still present:
SELECT a.name AS AssemblyName, t.name AS TypeName 
FROM sys.assembly_types t
JOIN sys.assemblies a ON t.assembly_id = a.assembly_id;
  • If the assembly is missing, re-deploy it using CREATE ASSEMBLY followed by CREATE TYPE ... EXTERNAL NAME.
  • Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after redeployment to clear cached metadata.

Step 4: Rebuild Orphaned Type References After Restore/Migration

  • If the error appeared after a database restore, run sp_refreshsqlmodule on dependent procedures/views to refresh bindings:
EXEC sp_refreshsqlmodule 'dbo.YourProcedureName';
  • Validate dependencies using sys.sql_expression_dependencies to locate broken links to the type.

Alternative or Verification Steps

  • Re-run the original query or application call that triggered Error 2231; if it executes without error, the fix is confirmed.
  • As an alternative, drop and recreate the type in the correct schema using DROP TYPE and CREATE TYPE, then update all dependent objects to reference the corrected definition.