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

by

Troubleshooting Guide: SQL Server Error 485 – XML Schema Collection Cross-Database Reference

Why This Error Happens

This error occurs because a view or inline table-valued function references an XML column whose schema collection is registered in a different database than the one where the view or function itself resides. SQL Server 2025 enforces strict local scoping for XML schema collections when returning results through views or inline functions, meaning the collection must exist in the same database context to be validated. This typically happens after migrating objects between databases, restoring a database without its dependent schema collections, or when a developer references a cross-database table directly in a view definition.

Step-by-Step Resolution Guide

Step 1: Identify the Schema Collection and Its Origin Database

  • Query sys.xml_schema_collections in the suspected source database to confirm the schema collection name referenced in the error message.
  • Run the following to locate it across all databases:
  EXEC sp_MSforeachdb 'SELECT ''?'' AS DatabaseName, name FROM [?].sys.xml_schema_collections WHERE name = ''YourSchemaCollectionName'''
  • Note the exact database where the collection currently resides versus where your view/function is created.

Step 2: Script and Recreate the Schema Collection in the Local Database

  • In SQL Server Management Studio (SSMS), right-click the schema collection under Databases > [SourceDB] > Programmability > Types > XML Schema Collections, and select Script Collection as > CREATE To > New Query Editor Window.
  • Switch the query context (USE [TargetDB];) to the database where your view or inline function is defined.
  • Execute the scripted CREATE XML SCHEMA COLLECTION statement to register a local copy in the target database.

Step 3: Update the Table or Column Definition to Use the Local Collection

  • Alter the underlying table column to reference the newly created local schema collection instead of the cross-database one:
  ALTER TABLE dbo.YourTable
  ALTER COLUMN YourXmlColumn XML (LocalSchemaCollectionName);
  • If altering the base table isn’t feasible, redesign the view/function to avoid cross-database XML column exposure by casting the column to a non-typed XML type within the view definition instead.

Step 4: Rebuild the View or Inline Function

  • Drop and recreate the view or inline table-valued function using sys.sp_refreshsqlmodule or a manual DROP/CREATE script.
  • Confirm the object compiles without referencing the external database’s schema collection path (e.g., no [OtherDB].dbo.[SchemaCollection] syntax remains in metadata).

Alternative or Verification Steps

  • Verify the fix by re-executing SELECT * FROM YourView or invoking the inline function directly; Error 485 should no longer appear, and the XML column should return typed data correctly.
  • If recreating the schema collection locally isn’t an option (e.g., due to strict database isolation policies), convert the column to untyped XML in the view’s SELECT list using CAST(YourXmlColumn AS XML) to bypass the schema validation requirement entirely.