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

by

Why This Error Happens

Error 2247 occurs when SQL Server 2025 encounters a data type mismatch during a metadata or extended property operation, typically when a stored procedure like sp_addextendedproperty or sp_updateextendedproperty receives a value whose underlying type does not match what the engine expects (e.g., passing an nvarchar where a sql_variant base type of int or bit is required). This can also surface when application code or a migration script inserts incompatible JSON or CLR type values into system catalogs. Corrupted or outdated database compatibility level settings can further cause the type resolution engine to misinterpret the value being passed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

  • Open SQL Server Management Studio (SSMS) and reproduce the error by running the query/script from the Activity Log or Profiler trace.
  • Check the exact statement calling sp_addextendedproperty, sp_updateextendedproperty, or any JSON/CLR type conversion function.
  • Run: SELECT * FROM sys.extended_properties WHERE major_id = OBJECT_ID('YourObjectName'); to inspect current stored values and types.

Step 2: Correct the Data Type Mismatch

  • Explicitly cast the value to the expected sql_variant compatible type before passing it, for example:
    EXEC sp_addextendedproperty @name = N'MS_Description', @value = CAST(N'Sample description' AS nvarchar(500));
  • Verify the parameter order and types against Microsoft’s documented signature for the procedure in use.
  • If using JSON functions, confirm the extracted value type with ISJSON() and JSON_VALUE(... , '$.path' RETURNING int) instead of relying on implicit conversion.

Step 3: Update Compatibility Level and Patch Level

  • Check current compatibility level: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB';
  • Set it to the latest supported level for SQL Server 2025: ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 170;
  • Ensure the instance is fully patched: open SQL Server Setup or run SELECT @@VERSION; and compare against the latest Cumulative Update from Microsoft’s release notes.

Step 4: Rebuild Affected Metadata (If Corruption Suspected)

  • Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out catalog corruption.
  • If extended properties are corrupted, drop and recreate them:
    EXEC sp_dropextendedproperty @name = N'MS_Description', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'YourTable';
  • Recreate using the corrected, properly typed value from Step 2.

Alternative or Verification Steps

  • Re-run the original script or application call that triggered Error 2247 and confirm it completes without exception; check sys.messages for message_id 2247 context to ensure no residual warnings appear in the SQL Server error log.
  • If the error persists, isolate the issue in a test database using tempdb, apply the same extended property or JSON operation, and use SQL Server Profiler to capture the exact parameter types being sent at the protocol level.