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

by

Troubleshooting Guide: SQL Server Error 496

Why This Error Happens

Error 496 occurs when a stored procedure, function, or trigger references a user-defined type (UDT) parameter using a one-part (unqualified) name instead of a fully qualified two-part name (schema.typename). SQL Server 2025 enforces stricter type-resolution checks, so when the UDT was altered, dropped and recreated, or exists in a different schema than the module expects, the engine can no longer verify that the parameter’s type matches the type it was originally compiled with. This mismatch is common after schema migrations, UDT redeployment scripts, or when a type is recreated without updating dependent modules.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Module and Type

  • Capture the full error text, including the module name and parameter name from the message.
  • Run the following query to locate the module definition:
SELECT o.name, o.type_desc, m.definition
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE o.name = '<ModuleName>';
  • Note the schema associated with the UDT referenced in the parameter list.

Step 2: Verify the User-Defined Type’s Schema

  • Run this query to confirm the type’s actual schema:
SELECT s.name AS SchemaName, t.name AS TypeName
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name = '<TypeName>';
  • Compare this schema against the one used in the module’s parameter declaration (e.g., dbo.MyType vs just MyType).

Step 3: Recreate the Module with a Two-Part Name

  • Open the module definition in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Modify the parameter declaration to explicitly include the schema, for example:
ALTER PROCEDURE dbo.MyProcedure
    @Param1 dbo.MyType READONLY
AS
BEGIN
    -- procedure body
END;
  • Execute the ALTER PROCEDURE (or DROP and CREATE) statement to recompile the module with the corrected reference.

Step 4: Refresh Dependent Objects

  • Use sys.sp_refreshsqlmodule on any other objects that depend on the same UDT to force SQL Server to re-validate type bindings:
EXEC sys.sp_refreshsqlmodule 'dbo.AnotherDependentProcedure';
  • Restart the SQL Server 2025 service only if changes do not take effect immediately (Services.msc > SQL Server (MSSQLSERVER) > Restart).

Alternative or Verification Steps

  • Re-execute the originally failing query or application call and confirm Error 496 no longer appears in the SQL Server error log (Application log in Event Viewer or sys.fn_xe_file_target_read_file for extended events).
  • If the error persists, drop and fully recreate both the UDT and all dependent modules in a single deployment script to ensure metadata consistency, rather than relying on incremental ALTER statements.