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

by

Troubleshooting Guide: SQL Server Error 967 – Disabled XML Index Due to Negative Date Values

Why This Error Happens

This error occurs because SQL Server 2025 has deprecated support for negative xs:date and xs:dateTime values (dates representing years before 0000) within XML data types. When the XML Indexing engine encounters these legacy negative date formats during an index rebuild or validation pass, it automatically disables the index to prevent data corruption or query miscalculation. This is typically triggered immediately following an in-place upgrade from an older SQL Server version (such as 2016 or 2019) where these legacy date formats were previously permitted and stored in the database.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Columns and Indexes

  • Run a diagnostic query against sys.indexes and sys.xml_indexes to isolate the specific disabled index mentioned in the Error 967 message.
  • Use the following T-SQL snippet to locate the disabled status:
  SELECT name, is_disabled FROM sys.indexes WHERE is_disabled = 1;
  • Cross-reference the table name provided in the error string (%.*ls) to locate the specific XML column causing the conflict.

Step 2: Scan and Sanitize the XML Data

  • Before rebuilding the index, you must find the “poison” rows containing the invalid negative dates.
  • Write a SELECT query using .exist() or .value() XQuery methods to search for date patterns matching a negative year signature (e.g., values preceding 0000-01-01).
  • Update these specific rows to use a NULL value or a valid minimum date (e.g., 0001-01-01T00:00:00) to comply with the new ISO 8601 standard enforced in SQL Server 2025.

Step 3: Drop and Rebuild the XML Index

  • Once the underlying data is sanitized, the old disabled index metadata must be cleared.
  • Navigate to SQL Server Management Studio (SSMS) or your query terminal and execute the drop command:
  DROP INDEX [IndexName] ON [dbo].[YourTable];
  • Recreate the index using the standard CREATE PRIMARY XML INDEX syntax, ensuring the ONLINE = ON option is used if running on Enterprise Edition to minimize downtime.

Step 4: Check Database Compatibility Level

  • Verify that the database is not incorrectly flagged for an older compatibility level that might cause conflicting date parsing logic.
  • Navigate to Database Properties in SSMS, or run ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170; to ensure full alignment with SQL Server 2025 engine standards.

Alternative or Verification Steps

  • Verification: After rebuilding, run DBCC CHECKDB or simply query sys.indexes again to confirm is_disabled now reads 0 for the target index.
  • Alternative: If the data cannot be modified due to compliance/archival reasons, consider migrating the specific table to a VARCHAR(MAX) storage format instead of native XML type to bypass the native XML indexing date validation entirely.