Troubleshooting Guide: Error 7139 – LOB Data Exceeds Configured Maximum (SQL Server 2025)

Why This Error Happens

This error occurs when SQL Server’s replication engine attempts to process a Large Object (LOB) data value—such as text, ntext, image, varchar(max), or nvarchar(max)—that exceeds the size limit defined by the max text repl size configuration option. By default, this setting is capped at 65536 bytes, and any transaction containing LOB data larger than this threshold will fail replication or Change Data Capture (CDC) processing. This is a configuration constraint rather than a corruption issue, and it commonly surfaces after migrating databases with large documents or BLOBs into a replicated topology.

Step-by-Step Resolution Guide

Step 1: Verify the Current Configuration Value

• Open SQL Server Management Studio (SSMS) or a query window connected to the Publisher instance.
• Run the following command to check the current setting:

  EXEC sp_configure 'max text repl size';

• Note the run_value and config_value columns; if they are set to the default (65536), this confirms the root cause.

Step 2: Increase the Max Text Repl Size Value

• Execute the following T-SQL script to raise the limit (example sets it to 2 GB using -1, or specify a byte value):

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  GO
  EXEC sp_configure 'max text repl size', -1; -- -1 allows unlimited size up to 2GB
  RECONFIGURE;
  GO

• Alternatively, specify a precise byte limit (e.g., 104857600 for 100MB) instead of -1 if unlimited size is a security concern.

Step 3: Restart the SQL Server Service

• On Windows Server 2025 or Windows 11, open SQL Server Configuration Manager.
• Navigate to SQL Server Services, right-click your instance (e.g., MSSQLSERVER), and select Restart.
• Note: While RECONFIGURE often applies this setting dynamically, a service restart ensures the Log Reader Agent and Distribution Agent fully refresh their cached configuration values.

Step 4: Restart the Replication Agents

• Open the Replication Monitor in SSMS.
• Locate the specific Publication throwing the error and right-click the Log Reader Agent or Distribution Agent.
• Select Start Agent to force it to re-read the newly configured max text repl size value and retry the failed transaction.

Alternative or Verification Steps

• Verification: Re-run EXEC sp_configure 'max text repl size'; and confirm that run_value now matches your new setting (e.g., -1 or your specified byte limit) without requiring a server reboot.
• Alternative Fix: If the error persists on the Subscriber side specifically, ensure the setting is applied identically on both the Publisher and Distributor instances, as replication requires consistent configuration across all nodes in the topology.

Troubleshooting Guide: SQL Server Error 7140 — Cannot Create Additional Orphans

Why This Error Happens

Error 7140 occurs when the internal orphan handle table maintained by sp_createorphan reaches its configured maximum capacity, typically due to an application or process failing to release orphan handles after use. This is commonly caused by connection pooling issues, long-running transactions that never commit or roll back, or a runaway loop repeatedly calling sp_createorphan without corresponding cleanup calls. Additionally, this can stem from insufficient orphan_handle_limit configuration values at the database or instance level, especially after migrating workloads to SQL Server 2025 on Windows Server 2025 or Windows 11 environments.

Step-by-Step Resolution Guide

Step 1: Identify Active Orphan Handles

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run EXEC sp_listorphans; to view all currently allocated orphan handles and their creation timestamps.
• Note any handles with unusually old timestamps, as these indicate “stuck” or leaked orphans from incomplete transactions.

Step 2: Free Up Existing Orphan Handles

• Execute EXEC sp_deleteorphan @handle_id = <id>; for each stale handle identified in Step 1.
• For bulk cleanup, run a loop script using sp_deleteorphan against a cursor of handle IDs older than a defined threshold (e.g., 30 minutes).
• Restart the SQL Server Agent service via Services.msc (Windows Server 2025) if background jobs are holding handles open.

Step 3: Increase the Orphan Handle Limit

• Navigate to SQL Server Configuration Manager > SQL Server Services > right-click your instance > Properties > Startup Parameters.
• Add or modify the trace flag -T7140 combined with the configuration value: run EXEC sp_configure 'orphan handle limit', 5000; followed by RECONFIGURE;.
• Restart the SQL Server service for the new limit to take effect.

Step 4: Patch Application Logic to Prevent Recurrence

• Audit application code (stored procedures, .NET connection handlers, or ETL scripts) that calls sp_createorphan.
• Ensure every sp_createorphan call is paired with a corresponding sp_deleteorphan in a TRY...CATCH or finally block to guarantee cleanup even on failure.
• Deploy the corrected application build to your Windows Server 2025 application tier and restart the associated application pool in IIS if applicable.

Alternative or Verification Steps

• Verify the fix by running EXEC sp_createorphan; again — it should execute successfully without triggering Error 7140.
• As an alternative if handles remain stuck, perform a full service restart of the SQL Server instance via Windows Services (net stop MSSQLSERVER then net start MSSQLSERVER), which clears all transient orphan handle allocations from memory.

Troubleshooting Guide: SQL Server Error 7135 – “Deletion Length Not in Range of Available Text”

Why This Error Happens

Error 7135 typically occurs when an application attempts to perform a text, ntext, or image column update using the WRITETEXT or UPDATETEXT functions with an invalid offset or deletion length parameter that exceeds the actual size of the existing data. This often stems from corrupted application logic, outdated client drivers miscalculating byte offsets, or concurrent transactions modifying the same LOB (Large Object) pointer before the update completes. In some cases, it also surfaces due to inconsistencies in the underlying text/image page chain caused by improper index maintenance or abrupt server shutdowns.

Step-by-Step Resolution Guide

Step 1: Verify the Application Query Logic

• Review the UPDATETEXT or WRITETEXT statement being executed; confirm the @delete_length parameter does not exceed DATALENGTH() of the target column.
• Run a validation query first: SELECT DATALENGTH(ColumnName) FROM TableName WHERE ID = @ID; to confirm current data size before executing the update.
• If using an ORM or legacy driver, check for hardcoded offset values that may no longer match schema changes.

Step 2: Check for Database Corruption

• Open SQL Server Management Studio (SSMS) and run:
  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Look specifically for allocation errors on text/image pages (Msg 8928 or 8976 alongside 7135).
• If corruption is found, restore from the latest clean backup rather than attempting an in-place repair on LOB data.

Step 3: Update SQL Server Drivers and Cumulative Updates

• Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 and confirm the installed version of the SQL Native Client or ODBC Driver.
• Download the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog and install it via setup.exe in administrative mode.
• Restart the SQL Server service via services.msc or run:
  net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Rebuild Affected Indexes

• Identify the table referenced in the error using SQL Server error logs located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Execute: ALTER INDEX ALL ON TableName REBUILD; to resolve fragmented LOB pointers that may trigger offset miscalculations.

Alternative or Verification Steps

• Re-run the original transaction that triggered Error 7135 in a test environment; confirm no error is thrown and DATALENGTH() matches expected values post-update.
• If the issue persists, consider migrating legacy text/image columns to varchar(max)/varbinary(max) using ALTER TABLE ... ALTER COLUMN, as deprecated LOB types are more prone to this error in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 7137

Why This Error Happens

Error 7137 occurs when an attempt is made to modify a column’s schema (such as altering its data type, name, or dropping it) while that column is actively engaged in a replication process. SQL Server locks certain schema-modification operations on columns that are being read by a concurrent snapshot generation, replicated to a non-SQL Server Subscriber, published via a DTS-enabled publication, or tracked by Change Data Capture (CDC). This restriction exists because allowing schema changes mid-process could corrupt the replication stream or create inconsistencies between the publisher and subscriber(s).

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Replication or CDC Process

• Open SQL Server Management Studio (SSMS) and connect to the instance running Windows Server 2025.
• Run the following query to check if the table is enabled for CDC:

  SELECT name, is_tracked_by_cdc FROM sys.tables WHERE is_tracked_by_cdc = 1;

• Check the Replication Monitor (right-click Replication node in SSMS > Launch Replication Monitor) to see if a snapshot agent is currently running against the publication.

Step 2: Pause or Disable the Active Process Temporarily

• If CDC is enabled on the column, disable it temporarily using:

  EXEC sys.sp_cdc_disable_table 
      @source_schema = N'dbo', 
      @source_name = N'YourTableName', 
      @capture_instance = N'all';

• If a Snapshot Agent is mid-run, wait for it to complete or stop it manually via the Replication Monitor > Agents tab > Stop.
• For non-SQL Server Subscribers or DTS-enabled publications, verify the publication properties in SSMS under Replication > Local Publications > Properties > Subscription Options.

Step 3: Apply the Schema Change Safely

• Once the blocking process is paused or disabled, re-run your ALTER TABLE or ALTER COLUMN statement.
• Example:

  ALTER TABLE dbo.YourTableName ALTER COLUMN YourColumnName VARCHAR(100);

• Ensure no other sessions are holding schema locks by checking sys.dm_tran_locks for SCH-M or SCH-S lock types on the target object.

Step 4: Re-enable CDC or Resume Replication

• If you disabled CDC in Step 2, re-enable it after the schema change:

  EXEC sys.sp_cdc_enable_table 
      @source_schema = N'dbo', 
      @source_name = N'YourTableName', 
      @role_name = NULL;

• Restart the Snapshot Agent or Log Reader Agent job via SQL Server Agent (Windows Server 2025 > SQL Server Agent > Jobs) if it was manually stopped.

Alternative or Verification Steps

• Verify the fix by re-running the original ALTER statement — it should complete without Error 7137, and confirm CDC/replication status returns to normal using sys.dm_cdc_log_scan_sessions or the Replication Monitor dashboard.
• If the error persists, consider scheduling schema changes during a maintenance window when replication and CDC jobs are fully stopped, rather than just paused, to avoid race conditions with the SQL Server Agent scheduler.

Troubleshooting Guide: SQL Server Error 7138 – WRITETEXT Statement Blocked by Replication/CDC

Why This Error Happens

Error 7138 occurs because the WRITETEXT statement performs a non-logged or minimally-logged operation that bypasses the standard transaction log mechanisms relied upon by Data Transformation Services (DTS) and Change Data Capture (CDC). Since CDC and replication depend on reading the transaction log to capture row-level changes, SQL Server blocks WRITETEXT on any column currently being tracked to prevent data inconsistency between the source table and the change tables. This is a hard-coded safeguard in SQL Server 2025 rather than a configuration bug, meaning the fix requires altering how data is written rather than forcing the statement through.

Step-by-Step Resolution Guide

Step 1: Identify Which Tracking Mechanism Is Active

• Open SQL Server Management Studio (SSMS) and connect to the instance running on Windows Server 2025.
• Run the following query to check if CDC is enabled on the target table:

  SELECT name, is_tracked_by_cdc FROM sys.tables WHERE name = 'YourTableName';

• If using legacy DTS packages, check the SQL Server Agent job history for active replication tasks tied to the table in question.

Step 2: Replace WRITETEXT with UPDATE or .WRITE Syntax

• Since WRITETEXT and UPDATETEXT are deprecated and incompatible with CDC/replication, rewrite your logic using standard UPDATE statements with the .WRITE clause for large object (LOB) manipulation.
• Example replacement syntax:

  UPDATE YourTable
  SET YourColumn .WRITE (N'NewValue', NULL, NULL)
  WHERE PrimaryKeyColumn = 1;

• This method is fully logged and compatible with CDC capture jobs, resolving the blocking error immediately.

Step 3: Temporarily Disable CDC (If Legacy Code Must Run As-Is)

• If modifying the application code is not immediately feasible, you may disable CDC on the specific table to allow the legacy WRITETEXT command to execute.
• Run the following command in a query window:

  EXEC sys.sp_cdc_disable_table 
  @source_schema = N'dbo', 
  @source_name = N'YourTableName', 
  @capture_instance = N'dbo_YourTableName';

• Execute your WRITETEXT operation, then immediately re-enable CDC using sys.sp_cdc_enable_table to resume change tracking.

Step 4: Restart the SQL Server Agent Service

• After making schema or job changes, restart the SQL Server Agent service to ensure the CDC capture and cleanup jobs refresh their metadata cache.
• Navigate to Services.msc on Windows Server 2025, locate “SQL Server Agent (MSSQLSERVER),” right-click, and select Restart.

Alternative or Verification Steps

• To verify the fix, run sys.sp_cdc_help_change_data_capture to confirm the capture instance is active and check the SQL Server Error Log to ensure Error 7138 no longer appears during data writes.
• If the issue persists despite code changes, consider migrating fully away from DTS (which is deprecated) to SQL Server Integration Services (SSIS) to avoid legacy conflicts with modern CDC architecture.

Troubleshooting Guide: SQL Server Error 7125

Why This Error Happens

Error 7125 in SQL Server typically occurs when the image_text or off-row data structure linked to a BLOB column (text, ntext, or image) is corrupted or references invalid page allocations. This often results from abrupt shutdowns, storage-level corruption, or disk I/O failures that disrupt the write operations to the LOB (Large Object) pages. In SQL Server 2025 running on Windows Server 2025/Windows 11, this can also surface due to mismatched file system permissions (NTFS ACLs) following an OS upgrade, which prevents the Database Engine service account from properly accessing legacy BLOB storage paths.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following command to identify the scope of corruption:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Look specifically for errors referencing “text, ntext, and image page” allocation errors in the output log.

Step 2: Check NTFS Permissions and Service Account Access

• Navigate to the SQL Server data directory, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click the .mdf and .ldf files, select Properties > Security, and ensure the NT SERVICE\MSSQLSERVER account has Full Control.
• If permissions were reset during a Windows Server 2025 update, restore them via PowerShell:

  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T

Step 3: Restore from a Clean Backup (If Corruption is Severe)

• If DBCC CHECKDB reports unrepairable allocation errors, failover to the last known good backup.
• Use the following T-SQL syntax to restore the database, replacing paths as necessary:

  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'D:\Backups\YourDatabaseName.bak' 
  WITH REPLACE, RECOVERY;

• Ensure the backup predates the specific “Getting Data” query failure to avoid re-importing corrupted BLOB pointers.

Step 4: Rebuild Corrupted Indexes

• For tables containing the affected text/image columns, rebuild the clustered index to force a reallocation of page structures.
• Execute the following command for the specific table:

  ALTER INDEX ALL ON YourTableName REBUILD;

Alternative or Verification Steps

• Verification: Re-run the specific query or application action that triggered the “Getting Data” symptom to confirm the BLOB data retrieves without throwing Error 7125.
• Alternative: If the error persists only on specific rows, use DBCC CHECKTABLE with the REPAIR_ALLOW_DATA_LOSS option as a last resort, understanding that this will remove the specific corrupted BLOB chain rather than recovering it.

Troubleshooting Guide: SQL Server Error 7133 – “NULL textptr”

Why This Error Happens

Error 7133 (“NULL textptr”) occurs when SQL Server attempts to access a text, ntext, or image pointer that has not been properly initialized before an UPDATE operation using WRITETEXT or UPDATETEXT is performed. This typically happens because the row was inserted with a NULL value in the LOB (large object) column instead of an initialized empty value, meaning no valid text pointer exists for the engine to reference. It can also surface due to application logic that skips the required initialization step (such as using an empty string literal instead of a proper placeholder) or from corruption in the underlying LOB page chain.

Step-by-Step Resolution Guide

Step 1: Verify the Column Initialization State

• Run a diagnostic query to check if the column contains a true NULL rather than an initialized empty value: SELECT TEXTPTR(ColumnName) FROM TableName WHERE ID = @ID.
• If the result returns NULL, this confirms the text pointer was never created, which is the direct trigger for Error 7133.
• Check the application insert logic to see if it is passing NULL directly into the LOB column during the initial INSERT statement.

Step 2: Correct the Insert Logic to Initialize the Pointer

• Modify the application or ad-hoc script to initialize the column with a zero-length placeholder before attempting a WRITETEXT or UPDATETEXT operation.
• Use the following syntax to force an empty string (not NULL) during insertion: INSERT INTO TableName (ID, ColumnName) VALUES (@ID, '').
• Avoid casting the value as NULL explicitly, as this reintroduces the uninitialized pointer error on subsequent updates.

Step 3: Update Legacy Code to Modern LOB Syntax

• Since WRITETEXT, UPDATETEXT, and TEXTPTR are deprecated features being phased out, update the Transact-SQL code to use .WRITE() syntax with VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) instead.
• Replace legacy column types (TEXT, NTEXT, IMAGE) with their modern MAX equivalents to eliminate the dependency on text pointers entirely.
• Example migration: Change UPDATE TableName SET ColumnName.WRITE(@NewData, NULL, 0) to bypass the need for a physical pointer reference.

Step 4: Run Database Consistency Checks

• If initialization logic is correct but the error persists, check for underlying page corruption in the LOB data chain.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS to rule out physical corruption as the root cause.
• Review the SQL Server Error Log located in the default path C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log for correlating I/O warnings.

Alternative or Verification Steps

• Verification: Re-run the SELECT TEXTPTR(ColumnName) query against the affected row; a successful fix will return a valid 16-byte binary pointer value instead of NULL.
• Alternative: If migrating the schema is not immediately feasible, wrap the UPDATETEXT command in a conditional check (IF TEXTPTR(ColumnName) IS NOT NULL) to prevent the application from crashing, while scheduling the column type migration for the next maintenance window.

Troubleshooting Guide: Error 7134 – LOB Locator Not Supported as Text Pointer

Why This Error Happens

This error occurs because UPDATETEXT and WRITETEXT statements require a traditional 16-byte TEXTPTR value, but a LOB (Large Object) locator was passed instead of the expected text pointer. This typically happens when columns were migrated to use the newer max data types (varchar(max), nvarchar(max), varbinary(max)) or when the TEXTPTR() function is called against a locator-based value rather than a legacy text, ntext, or image column. Microsoft SQL Server 2025 continues to deprecate legacy LOB handling functions in favor of .WRITE() syntax, making this mismatch increasingly common during upgrades.

Step-by-Step Resolution Guide

Step 1: Identify the Column Data Type Causing the Conflict

• Run sp_help 'YourTableName' or query sys.columns joined with sys.types to confirm whether the target column is text/ntext/image or has been converted to varchar(max)/nvarchar(max)/varbinary(max).
• Check application code or stored procedures for explicit calls to UPDATETEXT, WRITETEXT, or TEXTPTR().
• Example diagnostic query:

  SELECT c.name, t.name AS DataType 
  FROM sys.columns c 
  JOIN sys.types t ON c.user_type_id = t.user_type_id 
  WHERE c.object_id = OBJECT_ID('YourTableName');

Step 2: Replace Legacy LOB Functions with Modern .WRITE() Syntax

• Rewrite any UPDATETEXT/WRITETEXT calls using the UPDATE ... SET column.WRITE(...) pattern, which is fully compatible with max data types and LOB locators.
• Example replacement:

  UPDATE YourTable 
  SET YourColumn.WRITE(@NewData, @Offset, @Length) 
  WHERE PrimaryKeyColumn = @ID;

• Remove any dependency on TEXTPTR() since .WRITE() does not require a text pointer.

Step 3: Convert Legacy Columns to Modern MAX Types (If Still Using text/ntext/image)

• If you must retain UPDATETEXT, ensure the column is still a true legacy type and not already converted.
• Run ALTER TABLE YourTable ALTER COLUMN YourColumn varchar(max); to modernize the schema if business logic allows.
• Update all client drivers (ODBC/OLE DB) to the latest version supporting SQL Server 2025, since older drivers may still issue locator-based calls incorrectly against max types.

Step 4: Validate SQL Server Compatibility Level

• Open SSMS, right-click the database → Properties → Options, and confirm the Compatibility Level is set appropriately (150 or higher is recommended for SQL Server 2025 feature alignment).
• Run ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160; if the level is outdated and causing legacy function misbehavior.
• Restart the SQL Server service via services.msc on Windows Server 2025 or Windows 11 after applying changes.

Alternative or Verification Steps

• Re-run the original UPDATETEXT/WRITETEXT statement after conversion; if Error 7134 no longer appears and the LOB data updates correctly, the fix is confirmed.
• As an alternative, use DBCC CHECKDB and DBCC CHECKTABLE on the affected table to rule out underlying corruption masking as a type mismatch, then retest using the .WRITE() method exclusively going forward.

Why This Error Happens

Error 7123 (“Invalid text, ntext, or image pointer value”) occurs when an application attempts to use a TEXTPTR value that has expired, been invalidated, or was never valid in SQL Server 2025. This typically happens because the text pointer was obtained in a separate transaction/batch from the READTEXT, WRITETEXT, or UPDATETEXT operation, or the underlying row was modified/deleted after the pointer was generated. It can also surface due to corrupted LOB (Large Object) page chains or legacy application code that hasn’t been updated for modern SQL Server text-handling standards.

Step-by-Step Resolution Guide

Step 1: Validate the Text Pointer Lifecycle

• Confirm the application retrieves the TEXTPTR() value and immediately uses it within the same transaction — pointers are only guaranteed valid for the duration of that transaction.
• Check application code for cached or reused pointers from a previous session or connection.
• Run SELECT TEXTPTR(column_name) FROM table_name WHERE id = X to confirm the pointer generates correctly right before the WRITETEXT/UPDATETEXT call.

Step 2: Check Database Compatibility Level and Legacy Syntax

• Open SQL Server Management Studio (SSMS) and run:
  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB';
• TEXTPTR, READTEXT, WRITETEXT, and UPDATETEXT are deprecated since SQL Server 2012+ and behave inconsistently on SQL Server 2025 engines running in compatibility level 160+.
• Migrate legacy text/ntext/image columns to varchar(max), nvarchar(max), or varbinary(max), and replace TEXTPTR logic with .WRITE() clause syntax:
  UPDATE table_name SET column_name .WRITE(@NewValue, @Offset, @Length) WHERE id = X;

Step 3: Check for LOB Page Corruption

• Run a consistency check on the affected database:
  DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for LOB_DATA allocation errors (often listed under Msg 8928 or 8944).
• If corruption is found, restore the affected table/database from the last known good backup, or run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS only as a last resort in a maintenance window.

Step 4: Restart the Session and Clear Cached Plans

• On Windows Server 2025, open SQL Server Configuration Manager and restart the SQL Server (MSSQLSERVER) service to clear any orphaned text pointer references held in memory.
• Alternatively, run DBCC FREEPROCCACHE; to purge cached execution plans that may be referencing stale LOB pointers.
• Re-establish the client connection pool in the application (recycle IIS app pool or restart the service account session) to prevent reuse of invalidated pointers.

Alternative or Verification Steps

• Verify the Fix: Re-run the original READTEXT/WRITETEXT/UPDATETEXT operation inside a single explicit transaction (BEGIN TRAN → TEXTPTR → UPDATETEXT → COMMIT) and confirm Error 7123 no longer appears in the SQL Server Error Log or Windows Event Viewer.
• Alternative Fix: If legacy code cannot be changed immediately, wrap the TEXTPTR logic in a stored procedure with SET XACT_ABORT ON and ensure WITH (TABLOCKX) locking hints are applied to prevent concurrent modification of the row between pointer retrieval and use.

Troubleshooting Guide: Error 7124 in Microsoft SQL Server 2025

Why This Error Happens

Error 7124 occurs when a READTEXT statement references an offset and length combination that extends beyond the actual size of the stored text, ntext, or image data. This typically happens when application code calculates byte offsets incorrectly, especially after the underlying data has been updated or truncated by another process. It can also surface when legacy code written for deprecated data types (text/ntext/image) fails to account for data shrinkage during concurrent modifications.

Step-by-Step Resolution Guide

Step 1: Validate the Actual Data Length Before Reading

• Run SELECT DATALENGTH(column_name) FROM table_name WHERE primary_key = value; to confirm the real size of the data.
• Compare this value against the offset and length parameters your application is passing into READTEXT.
• Adjust the application logic so the offset + length never exceeds the DATALENGTH result.

Step 2: Review and Correct the READTEXT Syntax

• Open the stored procedure or script issuing the READTEXT call, typically located in your SSMS query files or application data-access layer (e.g., C:\inetpub\wwwroot\App\DAL\TextHandler.cs on Windows Server 2025 IIS deployments).
• Ensure the syntax follows: READTEXT table.column text_pointer offset size HOLDLOCK;
• Recalculate the size parameter dynamically using DATALENGTH() instead of hardcoding byte values.

Step 3: Migrate Away from Deprecated LOB Data Types

• Since text, ntext, and image are deprecated in SQL Server 2025, convert affected columns to varchar(max), nvarchar(max), or varbinary(max).
• Use ALTER TABLE table_name ALTER COLUMN column_name VARBINARY(MAX); to perform the migration.
• Replace READTEXT calls with SUBSTRING() or .Read() methods in ADO.NET, which handle boundary checks more gracefully.

Step 4: Check for Concurrent Data Modifications

• Verify no other session is truncating or updating the text value between the time the text pointer is obtained and when READTEXT executes.
• Use sp_whoisactive or the Activity Monitor in SSMS to identify blocking or conflicting transactions.
• Wrap the READTEXT operation in a transaction with HOLDLOCK to prevent the underlying data from changing mid-read.

Alternative or Verification Steps

• Re-run the original query after applying the offset/length correction and confirm the error no longer appears in the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• As a long-term alternative, refactor all legacy READTEXT/WRITETEXT usage to modern MAX data type functions, which eliminates this error class entirely since offset validation is handled automatically by SQL Server 2025.

Troubleshooting Guide: SQL Server Error 7118 – LOB Self-Assignment Restriction

Why This Error Happens

This error occurs when a Transact-SQL statement attempts to perform a partial update (such as .WRITE()) on a large object (LOB) column—like VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX)—while referencing the same column as both the source and target in a self-referential manner. SQL Server 2025 restricts this operation because partial LOB updates require a distinct source value separate from the destination pointer to maintain data integrity during the write operation. This typically happens in poorly constructed UPDATE statements, triggers, or ORM-generated queries that attempt in-place string concatenation or modification using .WRITE() syntax on the same column being read.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Check the SQL Server Error Log or Extended Events session for the exact statement throwing Error 7118.
• Look specifically for UPDATE statements using the .WRITE(expression, @Offset, @Length) clause.
• Common culprit pattern: UPDATE TableName SET LOBColumn.WRITE(LOBColumn + 'text', NULL, 0).

Step 2: Rewrite the Update Statement to Avoid Self-Reference

• Replace self-referential .WRITE() calls with a standard full-value replacement instead of a partial update.
• Example fix: Change SET Col.WRITE(Col + @NewData, NULL, 0) to SET Col = Col + @NewData.
• If partial updates are required for performance (large text appends), stage the new value in a temporary variable first:
• DECLARE @Temp NVARCHAR(MAX) = (SELECT Col FROM TableName WHERE ID = @ID);
• UPDATE TableName SET Col.WRITE(@Temp + @NewData, NULL, 0) WHERE ID = @ID;

Step 3: Audit ORM and Application-Layer Code

• If using Entity Framework, Dapper, or a custom data access layer, inspect generated SQL for automatic “append” or “patch” logic on MAX data types.
• Update the ORM mapping configuration to use full-value replacement for LOB columns rather than incremental write operations.
• On Windows Server 2025 hosts, check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for repeated trigger-based occurrences if the issue stems from an AFTER UPDATE trigger modifying the same row.

Step 4: Patch Cumulative Update Level

• Verify your instance is running the latest Cumulative Update (CU) for SQL Server 2025, as early-release builds had stricter LOB validation logic that sometimes misfires on legitimate self-concatenation patterns.
• Run SELECT @@VERSION; in SSMS to confirm the build number against Microsoft’s latest CU release notes before assuming it’s purely a query-design issue.

Alternative or Verification Steps

• Re-run the original UPDATE statement after rewriting it with the temporary variable pattern; confirm no Error 7118 appears and check @@ROWCOUNT to validate the expected number of rows were modified.
• As an alternative, if the update originates from a stored procedure, use SET STATISTICS IO ON and review the execution plan to confirm the LOB column is only referenced once per write operation.

Troubleshooting Guide: Error 7119 – LOB Growth Exceeds Maximum Allowed Size

Why This Error Happens

Error 7119 occurs when a Large Object (LOB) data type—such as VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, or legacy TEXT/IMAGE columns—attempts to grow beyond the maximum size limit enforced by SQL Server (typically 2 GB per LOB value). This is often triggered by application logic that concatenates large strings in a loop, bulk data imports without proper chunking, or corrupted LOB page chains that miscalculate the actual stored size. In rarer cases, this can also surface due to improper use of .WRITE() clause operations that incrementally append data past the engine’s internal ceiling.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Process

• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp and session ID tied to Error 7119.
• Run the following to find active sessions manipulating large objects:

SELECT session_id, text 
FROM sys.dm_exec_requests 
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE '%.WRITE(%' OR text LIKE '%MAX%';

• Correlate this with application logs (IIS, .NET, or custom ETL tools) to find the source of the oversized write operation.

Step 2: Audit and Refactor the Data Insertion Logic

• Inspect application code for loops that use string concatenation (e.g., +=) on NVARCHAR(MAX) variables instead of using .WRITE() for incremental updates.
• Replace bulk concatenation with chunked UPDATE ... SET column.WRITE(@chunk, @offset, @length) statements to avoid building the entire object in memory before the write.
• If using SSIS or bulk import tools, verify the BatchSize and MaxInsertCommitSize properties are not set to 0 (unlimited), as this can cause buffer overflows during LOB processing.

Step 3: Check for LOB Page Corruption

• Run a consistency check to rule out corrupted LOB chains that might be falsely reporting size errors:

DBCC CHECKTABLE ('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS;

• If corruption is found, restore the specific table from the last known good backup using RESTORE DATABASE ... WITH PARTIAL or export/reimport the clean data into a new table.

Step 4: Validate Server and Database Collation Settings

• Navigate to SQL Server Configuration Manager > SQL Server Services to ensure the instance is running the latest Cumulative Update (CU) for SQL Server 2025, as early builds occasionally mishandle UTF-8 LOB encoding, inflating byte counts prematurely.
• Update via Windows Update or the SQL Server Installation Center under Windows 11/Server 2025 Settings > Apps > Microsoft SQL Server 2025 Setup.

Alternative or Verification Steps

• Verify the fix by re-running the original query/import job and monitoring sys.dm_db_file_space_usage to ensure the LOB data stays within the 2GB-per-value limit during the transaction.
• If the error persists despite code changes, use Extended Events (xEvents) to trace page_allocated events specifically filtered for the LOB allocation unit to pinpoint the exact byte threshold being breached in real-time.

Troubleshooting Guide: SQL Server Error 7122 – “Invalid Text…”

Why This Error Happens

Error 7122 typically occurs when SQL Server’s internal text pointer structure becomes corrupted or references an invalid text, ntext, or image page within the database. This often results from improper shutdowns, storage-level corruption, or disk I/O failures that disrupt the linkage between data pages and their associated large object (LOB) storage. In SQL Server 2025, this can also surface when extended page structures are mismanaged during replication or after a failed in-place upgrade from a previous version.

Step-by-Step Resolution Guide

Step 1: Run a Full Consistency Check on the Database

• Open SQL Server Management Studio (SSMS) or use sqlcmd connected to your instance.
• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for allocation errors or specific page IDs referencing text/image corruption.

Step 2: Isolate the Corrupted Table or Column

• Identify the specific table using the error log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Run DBCC CHECKTABLE ('SchemaName.TableName') WITH ALL_ERRORMSGS; on the suspected table.
• Note the Page ID and Object ID returned in the error message for repair targeting.

Step 3: Repair the Database Using Emergency Mode (If Backup is Unavailable)

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); Note: this may delete corrupted LOB data to restore structural integrity.
• Revert back to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Restore From a Clean Backup (Preferred Method)

• Navigate to your backup directory (e.g., D:\SQLBackups\) and locate the last known good .bak file prior to the corruption timestamp.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourFile.bak' WITH REPLACE;
• Verify the Windows Server 2025 Volume Shadow Copy Service (VSS) logs to ensure no underlying disk snapshot errors coincided with the backup window.

Alternative or Verification Steps

• After running the repair or restore, execute DBCC CHECKDB one final time to confirm the “0 allocation errors and 0 consistency errors” message appears.
• If the error persists specifically on SSD/NVMe storage, check the Windows Event Viewer under Windows Logs > System for Event ID 153 or 129, which indicate a hardware-level I/O timeout that may be the root cause of the SQL corruption.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 7109

Why This Error Happens

Error 7109 occurs when SQL Server detects that a database’s metadata is inconsistent, typically because the “isreadonly” or allocation page flags conflict with the actual state of the filegroup structure. This is most often triggered by abrupt shutdowns, storage-level corruption, or a failed transaction log replay during recovery. In some cases, it also surfaces when a database was moved or restored improperly, leaving the boot page out of sync with the primary filegroup.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption.
• Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for correlating entries around the time the error occurred.

Step 2: Place Database in Emergency Mode for Repair

• Execute ALTER DATABASE YourDatabaseName SET EMERGENCY; to allow read-only access despite the inconsistency.
• Set single-user mode with ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;.
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); only if a recent backup is unavailable, as this step may discard corrupted pages.

Step 3: Restore from a Verified Backup (Preferred Method)

• Locate the most recent .bak file, typically stored under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'PathToBackup.bak' WITH REPLACE, RECOVERY; to overwrite the corrupted database.
• Confirm the storage subsystem (SAN/NAS/local disk) isn’t reporting I/O errors via Windows Event Viewer under Windows Logs > System.

Step 4: Reset Database to Multi-User Mode

• Once repairs or restoration are complete, run ALTER DATABASE YourDatabaseName SET MULTI_USER;.
• Confirm the database status in SSMS shows “Online” rather than “Recovery Pending” or “Suspect.”

Alternative or Verification Steps

• Run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm the result returns “ONLINE” with no pending errors in the log.
• If corruption recurs immediately after repair, run a full hardware diagnostic (chkdsk C: /f /r on Windows Server 2025) to rule out underlying disk failure before re-attaching the database.

Why This Error Happens

Error 7116 occurs when SQL Server’s Storage Engine attempts to read a LOB (Large Object Binary) data page—used for types like VARCHAR(MAX), VARBINARY(MAX), XML, or TEXT/IMAGE—and the requested offset pointer exceeds the actual data length stored on disk. This is almost always a symptom of physical database corruption, often caused by an underlying disk I/O failure, a faulty storage controller, or an unexpected server shutdown that interrupted a write operation. In rarer cases, it can be triggered by bugs in older third-party backup/restore tools that mishandle LOB pointers during a database migration.

Step-by-Step Resolution Guide

Step 1: Verify the Extent of Corruption

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to get a full report of the corruption.
• Look specifically for allocation errors tied to the Object ID and Page ID mentioned in the error log, as this confirms if the corruption is isolated to a single table or spread across the database.

Step 2: Check the Windows Event Viewer for Hardware Faults

• Open Event Viewer on Windows Server 2025 (Run eventvwr.msc).
• Navigate to Windows Logs > System and filter for Event ID 7, 9, 11, 15, or 51 (Disk/NTFS warnings).
• If hardware errors appear, pause the SQL Server service immediately to prevent further writes to the corrupted disk sector and contact your SAN/Storage administrator.

Step 3: Restore from the Last Known Good Backup

• Since LOB corruption is a physical issue, the safest fix is restoring the database rather than repairing it.
• Use RESTORE DATABASE [YourDatabaseName] FROM DISK = 'E:\Backups\LastGood.bak' WITH REPLACE, RECOVERY;.
• Apply any subsequent transaction log backups to minimize data loss up to the point of failure.

Step 4: Attempt a Repair (If No Backup is Available)

• If a clean backup does not exist, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS).
• Warning: This command may deallocate the corrupted LOB pages entirely, resulting in permanent loss of that specific data (e.g., text or image column data) to bring the database back online.
• Ensure the database is set to SINGLE_USER mode before running this repair option.

Alternative or Verification Steps

• Verification: After the restore or repair, immediately re-run DBCC CHECKDB to ensure the “0 allocation errors and 0 consistency errors” message is returned.
• Alternative: If the corruption persists only on a specific table, use SELECT * INTO NewTable FROM OldTable to salvage readable rows (excluding the corrupted LOB column) into a new, healthy table before dropping the old one.

Troubleshooting Guide: Error 7117 – Error Reading LOB Data from TDS

Why This Error Happens

Error 7117 typically occurs when SQL Server encounters corrupted, truncated, or improperly streamed Large Object (LOB) data—such as VARCHAR(MAX), VARBINARY(MAX), XML, or legacy TEXT/IMAGE columns—while transmitting it over the Tabular Data Stream (TDS) protocol. This is often caused by network interruptions during large data transfers, outdated or mismatched client drivers (e.g., ODBC, OLE DB) that mishandle MAX-type buffering, or underlying page-level corruption in the database affecting LOB_DATA allocation units. In some cases, aggressive network packet size configurations or firewall/antivirus interference on Windows Server 2025 can fragment TDS packets mid-stream, triggering this read failure.

Step-by-Step Resolution Guide

Step 1: Verify Database Integrity for LOB Corruption

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Specifically review output for errors related to LOBDATA, ROWOVERFLOW_DATA, or allocation unit IDs tied to MAX-type columns.
• If corruption is confirmed, restore the affected table/database from the last known good backup before proceeding.

Step 2: Update and Reconfigure Client Connectivity Drivers

• Navigate to Control Panel > Programs and Features on Windows Server 2025 / Windows 11 and check the installed version of the Microsoft ODBC Driver or OLE DB Driver for SQL Server.
• Download and install the latest driver from the official Microsoft Download Center to ensure compatibility with SQL Server 2025 TDS protocol updates.
• In your connection string or DSN configuration, explicitly set Packet Size=4096 (or test with 8192) to prevent buffer mismatches during LOB transmission.

Step 3: Adjust Network Packet Size and Firewall Rules

• Open SQL Server Configuration Manager, go to SQL Server Network Configuration > Protocols for MSSQLSERVER, and ensure TCP/IP is enabled with no conflicting port bindings (default 1433).
• Check Windows Defender Firewall settings via wf.msc and confirm no rules are throttling or fragmenting packets on the SQL Server port.
• Temporarily disable third-party antivirus/network inspection tools to rule out deep packet inspection interference with large TDS streams.

Step 4: Review and Adjust Server-Level Network Packet Size Setting

• In SSMS, right-click the server instance, select Properties > Connections, and check the Network Packet Size value (default is 4096 bytes).
• If large LOB transfers are failing, try lowering this value incrementally (e.g., to 4096 from a custom higher setting) via sp_configure 'network packet size', 4096; RECONFIGURE;
• Restart the SQL Server service through Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER for changes to take effect.

Alternative or Verification Steps

• Re-run the original query or application process that triggered Error 7117 and monitor the SQL Server Error Log (via SSMS Management > SQL Server Logs) to confirm the error no longer appears.
• If the issue persists, use SQL Server Profiler or Extended Events to capture the exact TDS packet failure point, and consider testing the connection from a different client machine to isolate whether the root cause is server-side corruption or a local Windows 11 network/driver misconfiguration.

Troubleshooting Guide: SQL Server Error 7107

Why This Error Happens

Error 7107 (“You can have only 1…”) occurs when SQL Server detects a conflicting configuration involving a single-instance limitation, most commonly related to full-text catalog definitions, sparse column sets, or identity column assignments on a single table. This typically happens when a schema migration script or an ORM tool attempts to define a second instance of an object that SQL Server 2025 restricts to one per table, such as a second IDENTITY column, a second column set, or a duplicate full-text index path. The root cause is almost always a malformed or outdated T-SQL script that wasn’t updated to reflect SQL Server’s object constraints.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Object Definition

• Open SQL Server Management Studio (SSMS) and run sp_help 'YourTableName' to inspect existing column properties.
• Check specifically for existing IDENTITY columns, computed column sets, or full-text indexes using SELECT * FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID('YourTableName');
• Review the error message context to confirm which object type is triggering the “only 1” restriction.

Step 2: Correct the Table Schema or Script

• If the issue involves IDENTITY columns, remove the duplicate definition from your CREATE TABLE or ALTER TABLE script.
• For full-text catalog conflicts, drop the existing index first using DROP FULLTEXT INDEX ON YourTableName; before attempting to recreate it.
• Edit the migration or deployment script located in your project’s /Scripts/Schema/ directory to remove redundant column set or identity declarations.

Step 3: Reapply the Corrected Schema Change

• Execute the revised script in a non-production environment first to confirm the error no longer appears.
• Run ALTER TABLE YourTableName ADD CONSTRAINT only after confirming no duplicate constraints exist in sys.objects.
• Restart the SQL Server service via services.msc on Windows Server 2025 if schema cache inconsistencies persist.

Alternative or Verification Steps

• Verify the fix by re-running the original query or deployment script and confirming Error 7107 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, use DBCC CHECKDB('YourDatabaseName') to rule out underlying catalog corruption as an alternative root cause.

Troubleshooting Guide: Error 7108 in Microsoft SQL Server 2025

Why This Error Happens

Error 7108 (“Database ID %d must have at least one file allocated for each distinct segment”) occurs when a database is missing a required file allocation, typically affecting the PRIMARY filegroup or a log segment. This usually happens after an incomplete database restore, a corrupted or manually edited system catalog, or when files were detached/deleted without properly updating the database’s internal file allocation map. On Windows Server 2025 or Windows 11 hosts, this can also surface after abrupt shutdowns, storage disconnections, or antivirus/backup software interfering with live .mdf/.ldf files.

Step-by-Step Resolution Guide

Step 1: Verify Current Database File Status

• Open SQL Server Management Studio (SSMS) or run a query against sys.master_files to check file states:

  SELECT database_id, file_id, name, physical_name, state_desc 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');

• Confirm whether any file shows OFFLINE, RESTORING, or is simply missing from the expected count.
• Cross-check physical file existence in the storage path, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.

Step 2: Run Consistency Checks and Attempt Repair

• Set the database to EMERGENCY mode to allow read access for diagnostics:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;

• Run DBCC CHECKDB to assess the extent of corruption:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If segment allocation errors are confirmed, set the database to SINGLE_USER mode and attempt a repair:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

Step 3: Restore from a Known Good Backup

• If repair fails or data loss is unacceptable, restore the database from the last verified full backup plus transaction logs:

  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'E:\Backups\YourDatabaseName_Full.bak' 
  WITH NORECOVERY;
  RESTORE LOG YourDatabaseName 
  FROM DISK = 'E:\Backups\YourDatabaseName_Log.trn' 
  WITH RECOVERY;

• Ensure the backup files are accessible and the SQL Server service account has read permissions on the backup directory.

Step 4: Rebuild Missing Filegroup Reference

• If only a secondary filegroup file is missing (not PRIMARY), add a new file to replace the missing one:

  ALTER DATABASE YourDatabaseName 
  ADD FILE (NAME = 'NewFileName', FILENAME = 'D:\SQLData\NewFileName.ndf') 
  TO FILEGROUP YourFileGroupName;

• Bring the database back online once the file structure is validated:

  ALTER DATABASE YourDatabaseName SET ONLINE;
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS; again after remediation to confirm zero allocation errors are returned.
• If corruption recurs frequently, check Windows Event Viewer (Windows Logs > System) for underlying disk I/O errors and run chkdsk /f on the volume hosting your SQL data files during a maintenance window.

Microsoft SQL Server 2025 — Error 7105 Troubleshooting Guide

Why This Error Happens

Error 7105 (“The Database ID %d, Page %S_PGID, Row %d is marked with the deleted bit set”) occurs when SQL Server’s storage engine encounters a corrupted page where a row is referenced by an index or metadata structure, but the actual data row has already been marked as deleted. This typically results from physical corruption caused by faulty disk I/O, improper shutdowns, storage controller issues, or memory corruption that occurred before the page was written to disk. It indicates a mismatch between the database’s logical structure (indexes/allocation units) and the physical data pages on disk.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify all affected objects.
• Note the exact Database ID, Page ID (PageId), and Object ID returned alongside Error 7105.
• Cross-reference the Page ID using DBCC PAGE (DatabaseName, FileId, PageId, 3) to inspect the header and confirm the deleted bit status.

Step 2: Check Hardware and I/O Subsystem Health

• Review the Windows Server 2025 Event Viewer under Windows Logs > System for Disk, NTFS, or Storport warnings around the time of corruption.
• Run chkdsk D:\ /f /r (replace D:\ with your data drive) in an elevated PowerShell/Command Prompt to repair underlying filesystem errors.
• Verify SQL Server’s I/O stack integrity by running DBCC CHECKDB with PHYSICAL_ONLY to rule out ongoing I/O-layer issues versus logical corruption.

Step 3: Restore from a Clean Backup (Preferred Fix)

• Navigate to your backup repository (e.g., D:\SQLBackups\ or your configured Ola Hallengren maintenance job output).
• Restore the most recent full backup predating the corruption using SQL Server Management Studio (SSMS) or:
  RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\SQLBackups\YourDatabase_Full.bak' WITH REPLACE, RECOVERY;
• Apply any subsequent differential/transaction log backups in sequence to minimize data loss.

Step 4: Run Repair as a Last Resort

• If no valid backup exists, set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this may delete the corrupted rows entirely.
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation — a clean result with zero allocation/consistency errors confirms resolution.
• If corruption recurs on the same disk, migrate the database files to a different physical/virtual disk array and enable CHECKSUM page verification (ALTER DATABASE YourDatabaseName SET PAGE_VERIFY CHECKSUM;) to catch future I/O errors earlier.

Troubleshooting Guide: Error 7106 – Read-Only Text Pointer Update Failure

Why This Error Happens

Error 7106 occurs when an application attempts to use the WRITETEXT or UPDATETEXT statement on a text pointer that was originally obtained using a SELECT statement without specifying the READPTR option correctly, or when the pointer was generated for a read-only operation. This typically stems from legacy application code interacting with deprecated LOB (Large Object) data types—such as TEXT, NTEXT, or IMAGE—in a way that conflicts with SQL Server 2025’s stricter handling of in-row data pointers. Additionally, this can be triggered if the underlying transaction isolation level does not permit write access to the pointer at the time of the update.

Step-by-Step Resolution Guide

Step 1: Verify the Text Pointer Retrieval Method

• Check the application source code (stored procedures or application layer) for the TEXTPTR function usage.
• Ensure the SELECT statement retrieving the pointer does not include the HOLDLOCK or read-only hints that restrict modification.
• Correct syntax should resemble: SELECT @ptrval = TEXTPTR(column_name) FROM table_name WHERE primary_key = @id;

Step 2: Check Transaction Isolation Levels

• Open SQL Server Management Studio (SSMS) and connect to the Windows Server 2025 instance.
• Verify that the session running the UPDATETEXT command is not set to READ UNCOMMITTED or SNAPSHOT isolation, as these can sometimes restrict pointer validity for writes.
• Run the following command to check current session settings: DBCC USEROPTIONS;

Step 3: Migrate Away from Deprecated LOB Types

• Since TEXT, NTEXT, and IMAGE are deprecated and heavily restricted in SQL Server 2025, convert the column to VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX).
• Use the following T-SQL command to alter the table schema:

ALTER TABLE YourTableName ALTER COLUMN YourColumnName VARBINARY(MAX);

• This resolves the error permanently as MAX data types utilize different, more flexible update mechanisms (.WRITE() clause) that do not rely on legacy text pointers.

Step 4: Update SQL Server Native Client Drivers

• Navigate to Control Panel > Programs and Features on the Windows Server 2025 machine.
• Ensure the ODBC or OLE DB drivers used by the connecting application are updated to the latest version compatible with SQL Server 2025, as older drivers may mismanage pointer read/write permissions.

Alternative or Verification Steps

• Verification: Run a test UPDATETEXT command immediately after fetching a fresh TEXTPTR within the same batch/transaction to confirm the pointer remains valid and writable.
• Alternative Fix: If schema migration (Step 3) is not immediately feasible due to application dependency constraints, wrap the UPDATETEXT operation in an explicit transaction (BEGIN TRAN... COMMIT) to lock the row and force a read-write context for the pointer.

Troubleshooting Guide: SQL Server Error 7102 – Text Manager Internal Error

Why This Error Happens

Error 7102 occurs when the SQL Server Text Manager encounters corrupted or inconsistent data pages while processing text, ntext, image, or large object (LOB) data types within a table. This is typically caused by physical database corruption resulting from disk I/O failures, improper shutdowns, or storage subsystem issues on the underlying Windows Server 2025 volume. In some cases, it can also stem from a corrupted buffer cache or a failing SSD/HDD sector where the LOB data pages reside.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of Corruption

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following command against the suspected database:

  DBCC CHECKTABLE ('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS;

• Review the output for specific page IDs and allocation errors related to LOBDATA or TEXTMIXED pages.

Step 2: Run a Full Database Consistency Check

• Execute a full integrity check to determine if the corruption is isolated to one table or spans the database:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Check the Windows Event Viewer (Event Viewer > Windows Logs > System) for underlying Disk or NTFS errors (Event ID 7 or 153) that might correlate with the SQL error timestamp.

Step 3: Restore from a Clean Backup (Preferred Method)

• If corruption is confirmed, the safest fix is to restore the database from the last known good backup taken before the corruption occurred.
• Use the following syntax to restore while overwriting the corrupted database:

  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = 'C:\Backups\YourDatabase.bak' 
  WITH REPLACE, RECOVERY;

• Ensure the backup file path matches the correct volume on your Windows Server 2025 storage configuration.

Step 4: Repair Using DBCC CHECKDB (If No Backup Exists)

• As a last resort, if no valid backup exists, place the database in single-user mode to attempt a repair:

  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

• Note: REPAIR_ALLOW_DATA_LOSS may delete corrupted rows containing the problematic text/image data to restore table consistency.

Alternative or Verification Steps

• Verify the Fix: Re-run DBCC CHECKTABLE on the specific table after the restore or repair; a clean result with “0 allocation errors and 0 consistency errors” confirms the fix was successful.
• Hardware Check: If corruption recurs frequently, run the Windows Server 2025 built-in chkdsk /f /r on the volume hosting the .mdf/.ndf files to rule out underlying physical disk failures.

Troubleshooting Guide: SQL Server Error 7104

Why This Error Happens

Error 7104 occurs when a DBCC command, a system function, or a dynamic SQL statement references an offset or length parameter using an invalid data type, typically when a bigint, varchar, or other unsupported type is passed where SQL Server strictly expects an int or smallint. This is commonly triggered by custom scripts, third-party monitoring tools, or legacy stored procedures that were not updated for compatibility with SQL Server 2025’s stricter type-checking engine. It can also surface after an in-place upgrade from an older SQL Server version if cached execution plans or extended stored procedures pass implicit data type conversions that the new engine no longer tolerates.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Script

• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact timestamp of the 7104 error.
• Cross-reference the timestamp with Extended Events or SQL Profiler traces to capture the exact T-SQL statement that triggered the fault.
• Pay close attention to any DBCC PAGE, DBCC IND, or system functions like sys.fn_PhysLocFormatter being called with incorrect variable declarations.

Step 2: Correct the Data Type Declarations

• Open the identified stored procedure or script in SSMS (SQL Server Management Studio) 21 or Azure Data Studio.
• Locate variables used for byte offsets or lengths (often named @Offset, @Length, or @Size) and explicitly cast them using CAST(@Variable AS INT) or CAST(@Variable AS SMALLINT).
• Save the script and remove any implicit conversions from bigint or decimal that may have been introduced during a copy-paste from an older codebase.

Step 3: Clear the Procedure Cache and Update Statistics

• Since cached execution plans might be enforcing the old, invalid casting logic, flush the specific plan using DBCC FREEPROCCACHE (plan_handle) to avoid a full server-wide cache clear.
• Run sp_recompile 'YourStoredProcedureName' to force the SQL Server 2025 engine to generate a new, compliant execution plan on the next run.
• Restart the SQL Server service via services.msc on Windows Server 2025 if the error persists after recompilation to ensure no stale metadata remains in memory.

Step 4: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and verify you are running the latest Cumulative Update for SQL Server 2025, as early builds had stricter parsing bugs for smallint boundaries.
• Download and install the update, then verify the build number using SELECT @@VERSION; in a new query window.

Alternative or Verification Steps

• Verification: Re-run the original query or scheduled job that failed; if the fix was successful, the query will execute without returning Error 7104, and the Application Event Viewer (Event ID source: MSSQLSERVER) will show no new critical warnings.
• Alternative: If the error stems from a third-party backup or monitoring tool, check the vendor’s website for a “SQL Server 2025 compatibility patch,” as the tool may need an update to align with the new strict type-checking enforced in this version.

Troubleshooting Guide: SQL Server Error 7009 (OPENXML Namespace Prefix Conflict)

Why This Error Happens

This error occurs when an XML document passed to sp_xml_preparedocument contains metaproperty attributes (such as xmlns:mp) that are declared more than once with conflicting prefixes within the same document scope. SQL Server’s internal XML parser strictly enforces a single, unique namespace prefix for metaproperties to maintain an unambiguous internal Document Object Model (DOM) tree. This is typically caused by malformed XML input from an application layer or a Transact-SQL script that manually injects duplicate xmlns declarations into the document string before parsing.

Step-by-Step Resolution Guide

Step 1: Audit the Source XML String

• Locate the exact T-SQL batch or stored procedure calling sp_xml_preparedocument.
• Print the XML variable content (e.g., using PRINT @xmlDoc or SELECT @xmlDoc) before the parsing call to inspect it manually.
• Search specifically for multiple instances of xmlns:mp= or other metaproperty-specific namespace declarations within the root or child nodes.

Step 2: Sanitize and Consolidate Namespace Declarations

• Edit the XML generation logic (whether in the application code or T-SQL) to ensure the metaproperty namespace is declared exactly once, ideally at the root element level.
• Remove any redundant or nested xmlns:mp attributes from child elements if they duplicate the root declaration.
• If using a C# or .NET data layer on Windows Server 2025 to generate this XML, verify the XmlDocument or XDocument serialization settings are not duplicating namespace manager prefixes.

Step 3: Update the Parsing Call with Explicit Flags

• Modify the sp_xml_preparedocument call to explicitly define the XPath namespaces using the second parameter if your document requires multiple contexts.
• Example syntax correction:
• EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc, '<root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop"/>'
• Ensure this namespace URI matches exactly across all document fragments you intend to query via OPENXML.

Step 4: Clear and Rebuild the Document Handle

• Since internal document handles can sometimes retain cached state in connection pooling scenarios on Windows Server 2025, ensure sp_xml_removedocument is called immediately after every use to clear the handle from memory.
• Restart the specific session or connection pool to flush any potentially corrupted internal DOM representations before re-testing the corrected XML.

Alternative or Verification Steps

• Verification: Re-run the corrected stored procedure and use SELECT * FROM OPENXML(@hDoc, '/root/row', 2) to confirm the rowset generates without throwing Error 7009.
• Alternative: If legacy OPENXML namespace handling continues to be unstable, migrate the logic to use the native XML data type methods (.nodes(), .value()) instead, as these are generally more robust against namespace prefix conflicts in modern SQL Server 2025 environments.

Troubleshooting Guide: Error 7101 – “You need an active user transaction in order to use text pointers…”

Why This Error Happens

This error occurs when an application attempts to use a text pointer (obtained via the TEXTPTR function) to manipulate text, ntext, or image data outside the scope of an active, explicit transaction. SQL Server 2025 requires that any operation using UPDATETEXT, WRITETEXT, or READTEXT against a table with the “text in row” option set to ON be wrapped in a BEGIN TRANSACTION/COMMIT TRANSACTION block, because the pointer’s validity is tied to the transaction’s lifetime. If the connection has IMPLICIT_TRANSACTIONS set to OFF and no explicit transaction is started, the text pointer becomes invalid immediately after retrieval, triggering this error.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Stored Procedure

• Check the SQL Server error log or Extended Events session for the exact statement raising Error 7101.
• Look specifically for calls to TEXTPTR(), UPDATETEXT, or WRITETEXT in application code, ORM-generated SQL, or legacy stored procedures.
• Run SELECT * FROM sys.dm_exec_requests WHERE last_error = 7101 if the session is still active to correlate with session ID.

Step 2: Wrap the Text Pointer Operation in an Explicit Transaction

• Modify the T-SQL script or stored procedure to include BEGIN TRANSACTION before declaring the pointer and COMMIT TRANSACTION after the UPDATETEXT/WRITETEXT call.
• Example corrected syntax:

  BEGIN TRANSACTION;
  DECLARE @ptrval binary(16);
  SELECT @ptrval = TEXTPTR(YourColumn) FROM YourTable WHERE ID = 1;
  UPDATETEXT YourTable.YourColumn @ptrval 0 0 'New Data';
  COMMIT TRANSACTION;

• Ensure there is no conditional logic that could skip the COMMIT or ROLLBACK, which would leave the transaction open.

Step 3: Verify Session-Level Transaction Settings

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run DBCC USEROPTIONS; to check if IMPLICIT_TRANSACTIONS is OFF for the session.
• If your application relies on implicit transactions, execute SET IMPLICIT_TRANSACTIONS ON; at the start of the connection/session rather than relying on default driver behavior.

Step 4: Evaluate the “Text in Row” Table Option

• If text pointer usage is non-essential, consider migrating the column to VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX).
• Run sp_tableoption 'YourTable', 'text in row', 'OFF'; if legacy text pointer functions are no longer required by the application.
• Note: MAX data types do not require TEXTPTR and avoid this error entirely, aligning with Microsoft’s deprecation of ntext, text, and image types.

Step 5: Audit Connection Pooling and Driver Configuration

• On Windows Server 2025, check the ODBC Data Source Administrator (odbcad32.exe) or the application’s connection string for transaction isolation settings.
• Ensure the driver (e.g., Microsoft OLE DB Driver 19 for SQL Server or ODBC Driver 18) is not resetting the session state between pooled connections, which can prematurely terminate transactions.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query/stored procedure. If no Error 7101 appears in SSMS or the Application Event Log, the transaction scope is correctly applied.
• Alternative Solution: If modifying legacy code is not feasible, use SET XACT_ABORT ON; alongside the explicit transaction to ensure automatic rollback on error, preventing orphaned transactions that could cause subsequent pointer invalidation.

Troubleshooting Guide: SQL Server Error 7007 — OPENXML Encountered Unknown Metaproperty

Why This Error Happens

This error occurs when the OPENXML function processes an XML document containing a metaproperty attribute (such as those prefixed with internal XML Document Object Model markers) that the SQL Server XML parser does not recognize or support in the current engine version. It is frequently triggered by XML documents prepared using an incompatible or outdated version of MSXML, or by handles generated via sp_xml_preparedocument that reference a corrupted or malformed in-memory XML tree. Additionally, this can surface when a legacy application passes XML formatted for an older SQL Server XML parsing engine into SQL Server 2025, which has stricter metaproperty validation.

Step-by-Step Resolution Guide

Step 1: Validate the XML Document Source

• Confirm the XML string being passed to sp_xml_preparedocument is well-formed and does not contain non-standard or proprietary metaproperty attributes.
• Check the application or ETL process generating the XML for legacy MSXML 3.0/4.0 SDK dependencies.
• Run a manual test using EXEC sp_xml_preparedocument @hDoc OUTPUT, '<YourXML/>' in SQL Server Management Studio (SSMS) to isolate the failure.

Step 2: Update MSXML and SQL Native Client Components

• On Windows Server 2025 / Windows 11, navigate to Control Panel > Programs and Features and verify the installed version of MSXML Core Services.
• Download and install the latest MSXML 6.0 Service Pack from Microsoft’s official redistributable packages if an outdated version is detected.
• Restart the SQL Server service via services.msc or run NET STOP MSSQLSERVER && NET START MSSQLSERVER in an elevated PowerShell terminal.

Step 3: Replace OPENXML with Native XML Data Type Methods

• Since SQL Server 2025 fully deprecates legacy XML parsing behaviors in favor of the native xml data type, refactor queries to use nodes(), value(), and query() methods instead of OPENXML.
• Example replacement: SELECT T.c.value('.', 'VARCHAR(100)') FROM @xmlData.nodes('/Root/Row') AS T(c).
• Update stored procedures in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ related scripts to remove deprecated OPENXML calls.

Step 4: Clear Cached XML Document Handles

• Ensure every sp_xml_preparedocument call has a corresponding sp_xml_removedocument @hDoc to release memory and prevent corrupted handle reuse.
• Restart the SQL Server instance to clear any lingering in-memory XML DOM trees causing handle conflicts.

Alternative or Verification Steps

• Verify the fix by re-running the original query or stored procedure in SSMS and confirming Error 7007 no longer appears in the Messages tab or the SQL Server Error Log (ERRORLOG file located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\).
• If the error persists, bypass OPENXML entirely by converting the input parameter to the native XML data type at the application layer before passing it to SQL Server, eliminating dependency on the legacy parser.

Troubleshooting Guide: Error 7008 – OPENXML EDGETABLE Incompatible with XMLTEXT OVERFLOW Flag

Why This Error Happens

This error occurs when the OPENXML function attempts to process an XML document using the internal edge table representation while the XMLTEXT overflow flag is simultaneously set, creating a conflict in how SQL Server allocates memory for large object (LOB) pointers. This typically happens when the XML document exceeds the internal node size threshold, forcing SQL Server to split text representation across overflow pages that the edge table structure cannot reference correctly. It can also be triggered by a corrupted MSXML parser handle left open from a previous sp_xml_preparedocument call that was never cleared with sp_xml_removedocument.

Step-by-Step Resolution Guide

Step 1: Clear Orphaned XML Document Handles

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run EXEC sp_xml_removedocument @hdoc; for any handle currently in memory to release locked overflow pointers.
• Restart the session or connection pool to ensure no stale handles from previous OPENXML calls remain active.

Step 2: Adjust Internal XML Node Size Configuration

• Navigate to SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Locate the instance under SQL Server Services, right-click, and select Properties > Startup Parameters.
• Add or modify the trace flag -T8626 to force SQL Server to disable automatic edge-table compression for oversized XML text nodes.
• Restart the SQL Server service for the flag to take effect.

Step 3: Rebuild the XML Schema Collection

• In SSMS, run sp_help '[SchemaCollectionName]' to confirm the schema linked to the failing query.
• Drop and recreate the XML schema collection using DROP XML SCHEMA COLLECTION followed by CREATE XML SCHEMA COLLECTION to eliminate any corrupted overflow metadata tied to the old schema object.
• Re-run the original OPENXML query to confirm the EDGETABLE structure now initializes without conflict.

Step 4: Increase Memory Allocation for XML Workloads

• Open Windows Server Manager and verify available physical memory isn’t constrained below SQL Server’s min server memory setting.
• In SSMS, right-click the instance, go to Properties > Memory, and increase the Maximum Server Memory value if the server is defaulting too low for large XML overflow operations.
• Apply changes and restart the SQL Server service.

Alternative or Verification Steps

• Verify the fix by executing a test query using OPENXML on a known large XML payload (over 2MB) and confirm Error 7008 no longer appears in the SSMS output or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists, bypass OPENXML entirely and migrate the query logic to use the .nodes() and .value() XML data type methods, which do not rely on the legacy edge table/overflow architecture.

Troubleshooting Guide: Error 7004 — “OPENXML does not support retrieving schema from remote tables”

Why This Error Happens

This error occurs when a query uses OPENXML in conjunction with sp_xml_preparedocument to retrieve schema information (via OPENXML combined with OPENQUERY, linked server syntax, or four-part naming) from a table that resides on a remote or linked server rather than the local SQL Server 2025 instance. SQL Server’s OPENXML rowset provider is architecturally limited to parsing XML documents and mapping schema locally — it cannot resolve metadata for tables that are not physically local. This typically surfaces when a stored procedure or dynamically generated T-SQL statement references a linked server object inside an OPENXML-based WITH schema clause (explicit or implicit), or when application code (such as an ORM or ETL tool) auto-generates XML-based retrieval syntax against a distributed data source.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Procedure

• Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 / Windows 11 instance and check the SQL Server error log (Management > SQL Server Logs) for the exact statement or stored procedure name tied to Error 7004.
• Run the following to capture the offending session in real time:

  SELECT text FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
  WHERE r.session_id = <blocked_session_id>;

• Confirm the query references a linked server (check for four-part names like [LinkedServerName].[DB].[schema].[table]) combined with OPENXML.

Step 2: Rewrite the Query to Localize the Data First

• Modify the T-SQL to pull the remote table data into a local temporary table or table variable before applying OPENXML parsing logic.
• Example pattern:

  SELECT * INTO #LocalStaging
  FROM OPENQUERY([LinkedServerName], 'SELECT * FROM RemoteTable');

  -- Now apply OPENXML/sp_xml_preparedocument against local data only

• Avoid wrapping OPENXML schema resolution directly around any linked server call or distributed query.

Step 3: Validate Linked Server Configuration

• In SSMS, navigate to Server Objects > Linked Servers > [YourLinkedServer] > Properties and confirm the provider settings allow basic data retrieval (not schema introspection via XML).
• Check Server Objects > Linked Servers > Providers and ensure Nested Queries and Allow InProcess are set appropriately for the OLE DB provider in use.
• Run EXEC sp_testlinkedserver 'LinkedServerName'; to confirm connectivity is functioning independently of the XML parsing issue.

Step 4: Update Application or ETL Logic

• If the error originates from an ORM, SSIS package, or third-party ETL tool, locate the auto-generated query template and disable any “remote schema inference” or “auto-detect XML schema” feature.
• Replace dynamic schema detection with a statically defined schema (explicit column list) to bypass the need for OPENXML to query remote metadata at all.

Alternative or Verification Steps

• Verify the fix: Re-run the original stored procedure or query after localizing the data; confirm no Error 7004 appears in SSMS > Management > SQL Server Logs and that the result set returns correctly.
• Alternative approach: If rewriting isn’t feasible, replace OPENXML entirely with the native XML data type and .nodes() / .value() methods, which support remote-sourced data staged into local variables without triggering schema-retrieval restrictions.

Troubleshooting Guide: SQL Server Error 7005 — “OPENXML requires a metaproperty namespace…”

Why This Error Happens

This error occurs when a query uses sp_xml_preparedocument or OPENXML with the namespace prefix mp explicitly declared for a different URI, while SQL Server internally reserves mp for its own metaproperty namespace (urn:schemas-microsoft-com:xml-metaprop). This creates a namespace collision, since SQL Server cannot distinguish between your custom-declared mp prefix and the one it needs internally to process metaproperties like @mp:id or @mp:parentid. The conflict is purely a T-SQL query design issue rather than a server configuration or permissions problem, meaning it will occur consistently regardless of the Windows Server 2025 or Windows 11 host environment.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Namespace Declaration

• Open the stored procedure, function, or ad-hoc script that calls sp_xml_preparedocument.
• Search for any WITH XMLNAMESPACES clause or inline XML namespace declaration that defines mp as a prefix (e.g., xmlns:mp="http://your-custom-uri").
• Confirm this is the source by temporarily commenting out the OPENXML call and running the XML parsing portion alone.

Step 2: Rename the Conflicting Prefix

• Change your custom namespace prefix from mp to a non-reserved alias, such as cmp or custns.
• Update every reference throughout the query, for example:
• WITH XMLNAMESPACES ('http://your-custom-uri' AS cmp)
• Update XPath references like @cmp:id instead of @mp:id
• Save the script and re-run it in SSMS (SQL Server Management Studio) connected to your SQL Server 2025 instance.

Step 3: Explicitly Declare the Metaproperty Namespace (If Required)

• If your query intentionally needs both custom metadata and SQL Server’s metaproperties, declare the mp namespace explicitly at the top of your query:
• WITH XMLNAMESPACES ('urn:schemas-microsoft-com:xml-metaprop' AS mp, 'http://your-custom-uri' AS cmp)
• Ensure the metaproperty namespace URI is typed exactly as shown — any typo will cause OPENXML to fail silently or throw a different parsing error.

Step 4: Clean Up XML Handles

• Add EXEC sp_xml_removedocument @hdoc; after every OPENXML operation to prevent memory leaks and handle conflicts on subsequent calls.
• Verify no orphaned XML document handles exist by restarting the SQL Server service on Windows Server 2025 if the issue persists across multiple sessions:
• Open Services.msc → locate SQL Server (MSSQLSERVER) → Restart

Alternative or Verification Steps

• Re-run the corrected query in SSMS and confirm Error 7005 no longer appears in the Messages tab; check the SQL Server Error Log (via EXEC sp_readerrorlog;) to confirm no residual namespace warnings.
• As an alternative, migrate the query logic to use XML data type methods (.nodes(), .value()) instead of OPENXML, as this modern approach avoids manual namespace/prefix management entirely.

Troubleshooting Guide: SQL Server Error 7006 – OPENXML Namespace Prefix Issue

Why This Error Happens

This error occurs when the XML document being processed by OPENXML contains namespace prefixes that SQL Server cannot correctly resolve using the metaproperty flags specified in the sp_xml_preparedocument call. It typically arises when the namespace URI mapping passed into sp_xml_preparedocument doesn’t match the actual prefixes used within the XML document, or when the XML schema uses default namespaces that conflict with how OPENXML parses metaproperties (like @mp:prefix). This is a parsing logic issue rather than a server configuration failure, meaning it usually stems from the T-SQL code itself or the structure of the incoming XML data.

Step-by-Step Resolution Guide

Step 1: Isolate the Problematic XML Input

• Extract the exact XML string being passed into the sp_xml_preparedocument stored procedure.
• Check if the XML utilizes multiple namespaces or a default namespace (xmlns="...") without an explicit prefix.
• Validate the XML structure using SQL Server Management Studio (SSMS) 2025 by querying it as an xml data type first to rule out malformed tags.

Step 2: Modify the sp_xml_preparedocument Call

• Locate the specific stored procedure or script on the Windows Server 2025 instance where the error is being thrown.
• Remove the second parameter (the namespace declaration) from the sp_xml_preparedocument call if it is not strictly required for your XPath queries.
• Example change: Update EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc, '<root xmlns:mp="urn:schemas-microsoft-com:mapping-schema"/>' to EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDoc to bypass the explicit namespace lookup.

Step 3: Update OPENXML Column Mapping Flags

• If removing the namespace parameter breaks data retrieval, adjust the flags in your OPENXML WITH clause instead.
• Change the metaproperty schema flag from 1 (Attribute-centric) or 2 (Element-centric) to 0 if you are only retrieving metaproperties and do not need the overlay mapping.
• Ensure the OPENXML call does not reference mp:prefix or mp:namespaceuri unless the namespace parameter in Step 2 is correctly restored and mapped.

Step 4: Refactor to Native XML Data Type (Recommended)

• Since OPENXML is a legacy feature, consider migrating the logic to use the native XML data type methods (.nodes(), .value()) available in SQL Server 2025.
• Use WITH XMLNAMESPACES at the top of the query to explicitly define the prefixes, which offers better compatibility with Windows Server 2025 collation updates than the older sp_xml_preparedocument method.

Alternative or Verification Steps

• Verify the Fix: Run the modified stored procedure using SET STATISTICS XML ON to confirm the output correctly parses the OPENXML rowset without throwing Error 7006 or returning NULL values in prefixed columns.
• Alternative Fix: If the error persists due to a corrupted document handle, ensure you are explicitly calling sp_xml_removedocument after every use to clear the internal MSXML parser memory cache before re-running the script.

Troubleshooting Guide: Error 7002 — “OPENXML XPath Must Be of a String Data Type”

Why This Error Happens

This error occurs when the XPath pattern parameter passed to the sp_xml_preparedocument system stored procedure or an OPENXML rowset function is not an explicit varchar, nvarchar, or char data type. SQL Server 2025’s query parser strictly validates that the XPath expression is a literal string or a variable explicitly cast to a string type before it attempts to traverse the XML DOM. This commonly happens when developers pass a column value, a numeric type, or an unconverted variable directly into the XPath argument, causing the parser to reject the query before execution.

Step-by-Step Resolution Guide

Step 1: Identify the Offending OPENXML Statement

• Review the stored procedure or ad-hoc query that triggered Error 7002 in SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
• Locate the OPENXML(@hDoc, 'XPath_Pattern', flag) call and inspect the second parameter (the XPath row pattern).
• Check if the XPath pattern is being passed as a variable, column reference, or concatenated expression rather than a hardcoded string.

Step 2: Explicitly Cast the XPath Parameter to a String Type

• Modify the query to wrap the XPath variable in an explicit CAST or CONVERT function, for example: CAST(@XPathVar AS NVARCHAR(4000)).
• If the XPath is being dynamically built, ensure the final concatenated result is stored in a properly declared NVARCHAR or VARCHAR variable before being passed into OPENXML.
• Avoid passing NULL, INT, or XML-typed variables directly as the XPath argument—only the row pattern string is accepted.

Step 3: Validate the XML Document Handle and Re-run the Query

• Confirm that sp_xml_preparedocument executed successfully and returned a valid integer handle (@hDoc) before the OPENXML call.
• Re-run the corrected query in a test environment on Windows Server 2025 to confirm the parser no longer throws Error 7002.
• Check SQL Server Error Logs (via EXEC sp_readerrorlog or the Event Viewer under Applications and Services Logs > MSSQLSERVER) to confirm no residual XML parsing errors remain.

Step 4: Clean Up XML Document Handles to Prevent Memory Leaks

• After successful execution, always call EXEC sp_xml_removedocument @hDoc; to release the internal memory tree used by the XML parser.
• Add this cleanup step inside a TRY...CATCH block or at the end of the stored procedure to ensure it runs even if the OPENXML query fails.

Alternative or Verification Steps

• Verify the Fix: Run the query with SET STATISTICS XML ON or check the Messages tab in SSMS to confirm the query returns rows without throwing Error 7002.
• Alternative Solution: If dynamic XPath generation is required, consider migrating the logic to use the native XML data type methods (.nodes(), .value(), .query()) instead of the legacy OPENXML function, as these methods handle type coercion more gracefully in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 7003 — “Only one OPENXML column can be of type %ls”

Why This Error Happens

This error occurs when a query using OPENXML attempts to define more than one column with the text, ntext, or image data type (including their modern equivalents like varchar(max), nvarchar(max), or varbinary(max) when mapped via the overflow metaproperty) within the same rowset schema. SQL Server’s OPENXML provider restricts the result set to only a single large-object (LOB) type column per call due to how the internal XML document is parsed and streamed into memory. This is typically caused by a schema definition error in the WITH clause of the OPENXML statement, or legacy T-SQL code that was written without accounting for this specific LOB restriction.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and locate the stored procedure or script triggering Error 7003.
• Check the Activity Monitor or the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to pinpoint the exact batch ID associated with the failure.
• Isolate the OPENXML statement within the script, specifically focusing on the WITH (SchemaDeclaration) clause.

Step 2: Audit the Schema Declaration

• Review the column definitions in the WITH clause of the OPENXML function.
• Identify every instance where text, ntext, image, varchar(max), or nvarchar(max) is used.
• Confirm if multiple columns share these LOB (Large Object) data type designations, as this is the direct trigger for Error 7003.

Step 3: Modify the Data Types

• Change all but one of the conflicting LOB columns to a standard, bounded data type such as varchar(4000) or nvarchar(4000), provided the data fits within these constraints.
• If the data genuinely exceeds 4,000 characters for multiple columns, refactor the query to use sp_xml_preparedocument combined with separate SELECT statements using .value() or .nodes() XQuery methods instead of relying solely on OPENXML.
• Save the modified script and recompile the stored procedure using EXEC sp_recompile 'YourProcedureName';.

Step 4: Update Legacy Compatibility Settings

• Navigate to SQL Server Configuration Manager on your Windows Server 2025 or Windows 11 host.
• Verify the Database Compatibility Level by running SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';.
• If running in a legacy compatibility mode (e.g., 100 or lower), consider testing the query under a modern compatibility level (160 for SQL Server 2025) to ensure the XML parser behaves as expected, though the single-LOB-column restriction remains a hard constraint in OPENXML.

Alternative or Verification Steps

• Verify the Fix: Re-run the stored procedure or ad-hoc query in SSMS. A successful execution without Error 7003 confirms the schema declaration no longer contains duplicate LOB-type columns.
• Alternative Solution: If multiple large text/XML fields must be extracted simultaneously, bypass OPENXML entirely and use the native XML data type methods (nodes(), value(), and query()) via a CROSS APPLY, as this method does not impose the single-column LOB restriction found in the older OPENXML rowset provider.

Troubleshooting Guide: SQL Server Error 7000 — OPENXML Document Handle Parameter Type Mismatch

Why This Error Happens

This error occurs when the document handle variable passed to the OPENXML function or sp_xml_preparedocument procedure is declared as a data type other than int, such as varchar, bigint, or nvarchar. SQL Server strictly requires the handle returned by sp_xml_preparedocument to be stored in and referenced as an int variable, since internally it maps to a memory pointer managed by the MSXML parser. This commonly happens after code migration, copy-paste errors from older T-SQL scripts, or when developers assume the handle is a string/GUID-based identifier.

Step-by-Step Resolution Guide

Step 1: Locate the Declaration of the Document Handle Variable

• Open the stored procedure, function, or script that calls OPENXML.
• Search for the variable declaration immediately preceding the sp_xml_preparedocument call.
• Confirm whether it is declared as something other than int, for example:
  DECLARE @hDoc VARCHAR(50) (incorrect)

Step 2: Correct the Variable Data Type

• Change the declaration to use the int data type explicitly:
  DECLARE @hDoc INT
• Ensure the OUTPUT parameter in the EXEC sp_xml_preparedocument call matches this corrected type:
  EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDocument
• Save the script and re-run it in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance.

Step 3: Validate the Full OPENXML Syntax Block

• Confirm the full block follows the correct pattern, for example:

  DECLARE @hDoc INT
  EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmlDocument
  SELECT * FROM OPENXML(@hDoc, '/root/row', 2)
  WITH (Column1 INT, Column2 VARCHAR(100))
  EXEC sp_xml_removedocument @hDoc

• Check that sp_xml_removedocument @hDoc is called afterward to release the internal memory tree and prevent resource leaks.

Step 4: Clear Cached Execution Plans (If Error Persists)

• On Windows Server 2025, open SSMS and run:
  DBCC FREEPROCCACHE
• This clears any stale compiled execution plan that may still reference the old, incorrect parameter type.
• Re-execute the corrected stored procedure to force recompilation.

Step 5: Review Linked Application Code

• If the stored procedure is called from an external application (e.g., a .NET service running on Windows Server 2025 or Windows 11), check how the handle parameter is defined in the application’s data access layer (e.g., SqlParameter type should be SqlDbType.Int, not SqlDbType.VarChar).
• Update and redeploy the application code if a type mismatch is found there.

Alternative or Verification Steps

• Run the corrected script in isolation using SSMS “Include Actual Execution Plan” to confirm it compiles and executes without Error 7000.
• If the error persists despite correct typing, check SQL Server 2025 compatibility level (SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB') and ensure it is set to a level that supports legacy OPENXML syntax, or consider migrating to native OPENJSON/XML data type methods (.nodes(), .value()) as a modern alternative.

Troubleshooting Guide: SQL Server Error 7001 – OPENXML Flags Parameter Type Mismatch

Why This Error Happens

This error occurs when the flags parameter passed to the sp_xml_preparedocument or OPENXML function is implicitly or explicitly cast as a data type other than int, such as varchar, smallint, or bit in an incompatible context. SQL Server 2025’s stricter type-checking engine rejects ambiguous implicit conversions that older compatibility levels may have silently allowed. This is frequently triggered after a database compatibility level upgrade or when legacy T-SQL scripts written for SQL Server 2017 or earlier are executed without modification on the new engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and locate the stored procedure or script referencing OPENXML.
• Search specifically for the third parameter in the OPENXML(@idoc, '/root/row', flags) syntax.
• Check if the flags variable was declared using DECLARE @flags varchar(10) or similar non-integer types.

Step 2: Correct the Variable Declaration

• Modify the variable declaration to strictly use the int data type.
• Example fix: Change DECLARE @flags TINYINT = '2' to DECLARE @flags INT = 2.
• Ensure no single quotes are wrapping the numeric flag value (e.g., use 2, not '2'), as string literals can trigger strict parsing errors in the new engine.

Step 3: Check Database Compatibility Level

• Open a new query window and run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';.
• If the level is set below 160 (SQL Server 2022) or the new 2025 standard, strict type enforcement might behave differently than expected.
• Update the compatibility level if necessary using: ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170; (Note: Test this in a staging environment first, as it may expose other legacy syntax errors).

Step 4: Clear and Rebuild the XML Handle Cache

• Since OPENXML relies on an internal memory representation of the XML document via sp_xml_preparedocument, ensure the handle is being passed correctly.
• Run EXEC sp_xml_removedocument @idoc; to clear any corrupted memory pointers before re-running the PREPAREDOCUMENT command.
• Restart the SQL Server service via services.msc on Windows Server 2025 if the memory handle remains locked or unresponsive.

Alternative or Verification Steps

• Verification: Re-run the specific stored procedure using SQL Server Profiler or Extended Events to confirm Error 7001 no longer appears in the event log.
• Alternative Solution: If the error persists due to deep legacy code dependencies, consider migrating the logic away from OPENXML entirely to the modern OPENJSON or native XML data type methods (.nodes() and .value()), which offer better type safety and are fully optimized for the Windows Server 2025 environment.

Troubleshooting Guide: SQL Server Error 6998 – “Invalid Type Definition: Content Model Too Complicated”

Why This Error Happens

This error occurs when SQL Server’s XML parser attempts to validate or compile an XML Schema Collection (XSD) that exceeds the internal complexity limits of the schema processor (MSXML/System.Xml). The root cause is typically an XML schema with an excessive number of enumeration values, deeply nested xs:choice or xs:sequence groups, or a content model that generates too many Deterministic Finite Automaton (DFA) states during compilation. This commonly surfaces when importing large third-party XSD files (such as FpML, HL7, or FIXML schemas) into a CREATE XML SCHEMA COLLECTION statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate recently added or modified XML schema collections:
• SELECT name, xml_collection_id, create_date FROM sys.xml_schema_collections ORDER BY create_date DESC;
• Check the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for the exact %s schema name referenced in Error 6998.
• Export the schema definition to a local .xsd file for inspection using SQL Server Management Studio (SSMS) 21.

Step 2: Simplify the XML Content Model

• Open the exported .xsd file in Visual Studio 2022/2025 or XML Notepad.
• Reduce the number of xs:enumeration facets in any single xs:simpleType restriction; consider splitting large enumeration lists into a reference lookup table instead of inline XML validation.
• Flatten deeply nested xs:choice blocks — SQL Server’s XML compiler struggles when choice groups exceed roughly 200–300 combined child elements per content model.
• Replace maxOccurs="unbounded" combined with complex nested choices with simpler xs:sequence structures where business logic allows.

Step 3: Re-register the Modified Schema Collection

• Drop the existing broken collection (if partially created) using:
• DROP XML SCHEMA COLLECTION [dbo].[YourSchemaName];
• Re-import the simplified schema using:
• CREATE XML SCHEMA COLLECTION [dbo].[YourSchemaName] AS N'<simplified XSD content>';
• Monitor the command execution; it should complete without throwing Error 6998.

Step 4: Apply Latest SQL Server 2025 Cumulative Update

• Open PowerShell as Administrator and check your current build number:
• SELECT @@VERSION;
• Navigate to the Microsoft Update Catalog and download the latest Cumulative Update (CU) for SQL Server 2025, as early builds have known XML parser DFA-state limits that were patched in later CUs.
• Install the update and restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Verify the fix: Re-run the original INSERT, CREATE, or validation query that triggered Error 6998 against a column of type xml bound to the schema collection — it should now process without error.
• Alternative workaround: If the schema cannot be simplified due to compliance requirements (e.g., regulatory XSDs), store the data as untyped xml or nvarchar(max) instead of binding it to a strict XML SCHEMA COLLECTION, and perform validation at the application layer instead.

Why This Error Happens

Error 6999 occurs when SQL Server attempts to parse an XML Schema Collection (XSD) that contains a fixed or default value constraint applied to an element or attribute of type QName. The SQL Server XML engine’s schema validator does not support this specific W3C XSD feature, regardless of version, because QName resolution requires namespace context that cannot be reliably fixed at the schema definition level. This typically surfaces when importing third-party XSDs, migrating databases from older systems, or deploying a CLR/XML-heavy application that includes schemas authored outside of SQL Server’s supported subset.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Component

• Open the XSD file referenced in the CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement that triggered the error.
• Search the file for type="xs:QName" or type="QName" definitions.
• Check each of these elements/attributes for a fixed="..." or default="..." attribute in the same tag.

Step 2: Remove or Refactor the Value Constraint

• Manually strip the fixed or default attribute from the QName-typed element/attribute definition.
• Example — change:

  <xs:element name="DocType" type="xs:QName" fixed="ns:Invoice"/>

to:

  <xs:element name="DocType" type="xs:QName"/>

• If the fixed value is business-critical, enforce it at the application layer or via a CHECK constraint after the XML is shredded into relational columns, rather than inside the XSD.

Step 3: Re-import the Corrected Schema Collection

• Drop the broken schema collection if it partially loaded:

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Re-run the creation script with the sanitized XSD:

  CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName
  AS N'<xs:schema>...corrected content...</xs:schema>';

• Restart the SQL Server service only if the schema collection is tied to a startup stored procedure or Resource Governor configuration that cached the old definition.

Step 4: Validate Dependent Objects

• Check any XML columns, stored procedures, or CLR assemblies bound to the schema collection using:

  SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollectionName';

• Recompile any stored procedures that reference the typed XML column to ensure they pick up the corrected schema.

Alternative or Verification Steps

• Run a test INSERT against a typed XML column referencing the new schema collection to confirm no 6999 error is thrown and that validation behaves as expected.
• If editing the XSD isn’t feasible (e.g., vendor-locked schema), import the XML column as untyped (xml without (CONTENT schema_collection)) and perform validation logic at the application or T-SQL trigger level instead.

Why This Error Happens

Error 6996 occurs when SQL Server attempts to register or validate an XML Schema Collection (XSD) that contains a logically invalid simple type definition. Specifically, the schema defines mutually exclusive boundary facets—such as both minInclusive and minExclusive, or both maxInclusive and maxExclusive—on the same restriction element, which violates the W3C XML Schema specification. This is almost always caused by a malformed or hand-edited XSD file being imported via CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION, rather than a SQL Server configuration or permissions issue.

Step-by-Step Resolution Guide

Step 1: Locate the Offending XML Schema Collection

• Run the following query to identify the schema collection and the type causing the failure:

  SELECT name, xml_schema_namespace, create_date 
  FROM sys.xml_schema_collections 
  WHERE schema_id = SCHEMA_ID('your_schema_name');

• Check SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for the exact type name referenced in the %s placeholder of Error 6996.

Step 2: Extract and Inspect the Raw XSD Content

• Export the schema collection content to a file for manual review:

  SELECT CAST(xml_schema_namespace('your_schema_name', 'YourCollectionName') AS NVARCHAR(MAX));

• Open the exported XSD in a validating editor (Visual Studio 2022/2025 or an online XSD validator) and search for <xs:restriction> blocks.
• Look specifically for duplicate or conflicting facet pairs like:

  <xs:minInclusive value="0"/>
  <xs:minExclusive value="0"/>

Step 3: Correct the Conflicting Facets in the Schema

• Edit the XSD source file to retain only one boundary facet per restriction (either inclusive OR exclusive, never both for the same bound).
• Example fix — remove the redundant exclusive facet:

  <xs:restriction base="xs:decimal">
    <xs:minInclusive value="0"/>
    <xs:maxInclusive value="100"/>
  </xs:restriction>

• Save the corrected file as valid UTF-8 encoded XML.

Step 4: Drop and Re-register the Schema Collection

• Since XML Schema Collections cannot be directly overwritten with conflicting type changes, drop the old one first:

  DROP XML SCHEMA COLLECTION your_schema_name.YourCollectionName;

• Re-import the corrected schema:

  CREATE XML SCHEMA COLLECTION your_schema_name.YourCollectionName
  AS N'<path or inline corrected XSD content>';

• If other tables/columns reference this collection, ensure you reapply ALTER TABLE ... ALTER COLUMN bindings afterward.

Step 5: Validate Dependent Objects

• Re-run any stored procedures, SSIS packages, or application connection strings that reference the affected XML column or schema collection to confirm they compile without error.

Alternative or Verification Steps

• Confirm resolution by executing SELECT * FROM sys.xml_schema_collections WHERE name = 'YourCollectionName'; and verifying no errors are thrown when querying bound XML columns.
• If the source XSD is generated by a third-party tool or ORM (e.g., an older .NET XSD generator), update that tool or manually post-process its output to strip duplicate facets before every deployment to prevent recurrence.

Troubleshooting Guide: SQL Server Error 6997 – Invalid Element Definition (Fixed Value / Nillable Conflict)

Why This Error Happens

This error occurs when SQL Server attempts to parse or validate an XML Schema Collection (XSD) where an element is incorrectly defined with both a fixed attribute and nillable="true". According to W3C XML Schema specifications, these two attributes are mutually exclusive because a nillable element must be able to represent a null value, while a fixed value forces a constant—creating a logical contradiction that the SQL Server XML parser rejects. This typically surfaces after a schema migration, a corrupted schema collection import, or when third-party software (ORM tools, SSIS packages) generates malformed XSD definitions during an upgrade to SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections that may contain the invalid element structure:

SELECT name, xml_collection_id, schema_namespace 
FROM sys.xml_schema_collections 
WHERE schema_namespace IS NOT NULL;

• Cross-reference the %s placeholder in the error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to identify the exact element name causing the conflict.

Step 2: Export and Inspect the Raw XSD Definition

• Extract the schema using SQL Server Management Studio (SSMS) or via T-SQL:

SELECT xml_schema_namespace('dbo','YourSchemaCollectionName');

• Open the output in a text editor and search for any <xs:element> tag containing both fixed="value" and nillable="true" attributes simultaneously.

Step 3: Correct the Schema Definition

• Remove the nillable="true" attribute if the element is intended to always carry the fixed constant value.
• Alternatively, remove the fixed="..." attribute if the business logic requires the element to accept null values.
• Save the corrected .xsd file to a local path, e.g., C:\SQLFixes\CorrectedSchema.xsd.

Step 4: Drop and Recreate the Schema Collection

• Since XML Schema Collections are immutable once created, you must drop the broken collection first:

DROP XML SCHEMA COLLECTION YourSchemaCollectionName;

• Re-import the fixed schema using CREATE XML SCHEMA COLLECTION, pointing to the corrected local file or inline XML definition.

Step 5: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart to clear any cached schema validation errors from memory.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query or application process that triggered Error 6997. Check the Windows Event Viewer (Application Log) to confirm Event ID 6997 no longer appears during the next scheduled job execution.
• Alternative Solution: If dropping the schema collection is not feasible due to dependencies, use ALTER XML SCHEMA COLLECTION to add a corrected namespace component instead of performing a full drop, provided no conflicting element names exist in the current version.

Why This Error Happens

This error occurs when SQL Server attempts to register or alter an XML Schema Collection where a derived complex type uses xs:anyAttribute or xs:any wildcards that are broader or incompatible with the wildcard defined in its base type. Per W3C XML Schema validation rules, a restricting type’s wildcard (including its namespace and processContents attributes) must be a proper subset of the base type’s wildcard—SQL Server’s internal XSD parser strictly enforces this during schema collection creation. This typically stems from a malformed or manually edited XSD file, a third-party schema import that wasn’t validated against W3C constraints, or an application generating schemas programmatically without proper inheritance checks.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate recently modified or problematic schema collections:

  SELECT name, xml_schema_namespace, create_date, modify_date 
  FROM sys.xml_schema_collections 
  ORDER BY modify_date DESC;

• Extract the full schema definition for inspection:

  SELECT xml_schema_namespace(N'dbo', N'YourSchemaCollectionName');

• Note the specific complex type referenced in the error message (%s).

Step 2: Validate the XSD Against W3C Wildcard Restriction Rules

• Open the extracted schema in an XML editor (Visual Studio, Oxygen XML, or VS Code with XML extension).
• Locate the <xs:complexType> definition using <xs:restriction base="..."> and check its <xs:anyAttribute namespace="..."> declaration.
• Confirm the namespace attribute value (e.g., ##any, ##other, or a list of URIs) in the derived type is equal to or narrower than the base type’s wildcard—never broader.
• Verify processContents (e.g., strict, lax, skip) in the derived type is equally or more restrictive than the base type’s setting.

Step 3: Correct the Schema Definition

• Edit the XSD file to narrow the namespace attribute on the restricted type’s wildcard so it falls within the base type’s allowed namespace set.
• If the base type uses namespace="##other", ensure the derived type does not use namespace="##any".
• Save the corrected XSD file locally (e.g., C:\SQLSchemas\corrected_schema.xsd).

Step 4: Re-register the Schema Collection

• Drop the old faulty collection (if it exists and is not in use):

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Re-create it using the corrected file:

  CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName
  AS N'<paste corrected XSD content here>';

• Check the SQL Server error log (via SSMS > Management > SQL Server Logs, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to confirm no residual Error 6994 entries appear.

Step 5: Update Dependent Application Code

• If the schema is generated dynamically by an application (e.g., via XSD.exe, a .NET XmlSchema object, or an ORM), update the code generation logic to enforce W3C wildcard subset rules before submission to SQL Server.
• Re-deploy and re-test the application’s XML insert/validation workflow.

Alternative or Verification Steps

• Verify the fix by inserting a test XML document that exercises the previously failing complex type: SELECT CAST('<test>...</test>' AS XML) validated against the new schema collection — it should succeed without error.
• If re-authoring the XSD is not feasible, as a workaround, remove the <xs:restriction> inheritance chain and flatten the complex type definition (duplicating the base type’s wildcard rules directly) to avoid the restriction validation entirely.

Troubleshooting Guide: SQL Server Error 6995 – Invalid XML Schema Type Definition

Why This Error Happens

This error occurs because SQL Server’s XML Schema Collection (XSC) implementation explicitly disallows the use of the built-in ID and IDREF XML Schema types (or any custom types derived from them) when defining element types or extending complex types. This restriction exists because ID and IDREF are identity-constraint types reserved for internal XML uniqueness validation, and permitting them as general-purpose element types would conflict with SQL Server’s internal XML indexing and validation engine. The error is almost always triggered by a malformed or improperly migrated .xsd file being registered via CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the specific schema collection causing the failure: SELECT name, schema_id FROM sys.xml_schema_collections WHERE name = '%s';
• Check the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to capture the exact timestamp and batch that triggered Error 6995.
• Open the source .xsd file in a text editor or Visual Studio to inspect the type declarations.

Step 2: Remove or Refactor Invalid Type References

• Search the XSD file for any <xs:element> or <xs:complexType> definitions that use type="xs:ID" or type="xs:IDREF".
• Replace these references with a safe substitute type, such as xs:string or xs:token, which preserves data format without violating SQL Server’s identity-constraint restrictions.
• If the schema uses a custom simple type derived by restriction from ID/IDREF (e.g., <xs:simpleType name="CustomID"><xs:restriction base="xs:ID">), change the base attribute to xs:string instead.
• Save the corrected .xsd file to a working directory, such as C:\SQLSchemas\Corrected\.

Step 3: Re-register the Corrected Schema Collection

• Drop the existing broken schema collection if it was partially created: DROP XML SCHEMA COLLECTION [SchemaName];
• Re-import the corrected schema using T-SQL: CREATE XML SCHEMA COLLECTION [SchemaName] AS 'C:\SQLSchemas\Corrected\schema.xsd';
• Confirm the command completes without raising Error 6995 in SQL Server Management Studio (SSMS) or Azure Data Studio.

Step 4: Validate Dependent Objects

• Check any tables or stored procedures referencing the XML Schema Collection using sp_depends or the sys.sql_expression_dependencies catalog view.
• Recompile affected stored procedures with EXEC sp_recompile 'dbo.YourProcedureName'; to ensure they bind correctly to the updated schema.

Alternative or Verification Steps

• Verify the fix by inserting a sample XML document that conforms to the corrected schema into a typed XML column and confirming no validation errors are thrown.
• If modifying the .xsd source is not feasible (e.g., third-party schema), consider storing the XML as untyped (XML data type without a schema collection reference) and enforcing ID/IDREF-like uniqueness constraints at the application layer instead.

Why This Error Happens

This error occurs when SQL Server attempts to parse or register an XML Schema Collection (XSC) that contains a complex type definition with ambiguous content. Specifically, the XSD defines two elements sharing an identical name but mapped to different data types within the same content model, which violates the W3C XML Schema ambiguity rules that the SQL Server XML parser enforces strictly. This is typically caused by a malformed schema file, a corrupted schema import, or a developer merging two conflicting namespaces into a single collection without resolving type overlaps.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Schema Collection

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to locate the problematic collection: SELECT name, xml_schema_namespace FROM sys.xml_schema_collections;
• Cross-reference the schema name found in the Error 6992 log details located in the Windows Event Viewer under Applications and Services Logs > SQL Server.

Step 2: Extract and Validate the XSD File

• Export the specific schema using SELECT xml_schema_namespace('SchemaName', 'TypeName'); to view the raw XSD content.
• Copy this output into a standalone .xsd file and validate it using an external tool, such as the XML Schema Validator in Visual Studio 2025 or the xmllint --schema command via Windows Subsystem for Linux (WSL).
• Look specifically for two <xs:element> tags with the same name attribute but different type attributes nested inside the same <xs:sequence> or <xs:choice> block.

Step 3: Correct the Type Mismatch

• Edit the source XSD file to rename the conflicting duplicate element (e.g., change the second instance from OrderID to OrderID_Alt) if they are logically distinct fields.
• Alternatively, if the elements represent the same logical data, standardize both elements to use a single, unified data type (e.g., change both to xs:string).
• Save the corrected file to a local directory, such as C:\SQLSchemas\Corrected\.

Step 4: Drop and Re-Register the Schema Collection

• Remove the broken collection using DROP XML SCHEMA COLLECTION [SchemaName]; (ensure no active tables depend on it first).
• Register the fixed schema using the CREATE XML SCHEMA COLLECTION command, pointing to the content of your corrected file.
• Restart the SQL Server (MSSQLSERVER) service via the Windows Services console (services.msc) to clear any cached schema validation errors.

Alternative or Verification Steps

• Verify the Fix: Attempt to insert or validate an XML document against the updated schema using SELECT CAST('<YourXML/>' AS XML).query('.') to ensure no Error 6992 is thrown during parsing.
• Alternative Fix: If the schema originates from a third-party vendor and cannot be edited, import the XML data as untyped XML (without a schema collection binding) to bypass strict validation while you await a vendor patch.

Troubleshooting Guide: SQL Server Error 6993 – Attribute Value Constraint Mismatch

Why This Error Happens

Error 6993 occurs when SQL Server 2025 parses an XML Schema Collection (XSD) where an attribute reference redefines a fixed or default value constraint that conflicts with the value declared in the original attribute definition. This typically happens after importing a third-party or legacy XML schema into SQL Server without validating it against W3C XML Schema consistency rules. It can also surface after an in-place upgrade from older SQL Server versions, where legacy XML Schema Collections that were previously tolerated are now strictly validated by the updated XML parser engine in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate recently modified or problematic schema collections:
• SELECT name, xml_schema_namespace, create_date, modify_date FROM sys.xml_schema_collections ORDER BY modify_date DESC;
• Cross-reference the timestamp with the SQL Server error log entry for Error 6993 to isolate the exact collection and attribute name reported in the %s placeholder.

Step 2: Export and Inspect the Raw XSD Definition

• Extract the schema content using:
• SELECT xml_schema_namespace(N'dbo', N'YourSchemaCollectionName');
• Open the exported XSD in a text editor or Visual Studio XML Editor and search for the flagged attribute.
• Check both the <xs:attribute> declaration and any <xs:attribute ref="..."> usage to compare their fixed or default values — they must match exactly in value and type.

Step 3: Correct the Value Constraint Mismatch

• Edit the XSD source file so the fixed/default value on the attribute reference matches the base declaration, or remove the redundant constraint from the reference entirely.
• Save the corrected schema as a new .xsd file (e.g., C:\SQLSchemas\corrected_schema.xsd).
• Drop the broken collection and recreate it:
• DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<paste corrected XSD content here>';

Step 4: Clear Cached Query Plans Referencing the Schema

• Since the old (invalid) schema may be cached in execution plans, flush the relevant plan cache:
• DBCC FREEPROCCACHE;
• Restart the SQL Server 2025 service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025 to ensure the XML parser reloads cleanly.

Alternative or Verification Steps

• Validate the fix by re-running any stored procedures or application calls that insert/validate XML typed columns against this schema — no Error 6993 should appear in ERRORLOG (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• If the schema is from a third-party vendor and cannot be edited, use an external validator like xmllint --schema or Visual Studio’s “Validate XML” feature to confirm W3C compliance before re-importing it into SQL Server.

Why This Error Happens

Error 6990 occurs when SQL Server’s XML Schema Collection validator encounters an XSD (XML Schema Definition) that contains an illegal restriction on a model group (sequence, choice, or all) during schema derivation. This typically happens when a CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement imports a schema where a derived complex type attempts to restrict a base type’s particle (sequence/choice/all) in a way that violates W3C XSD 1.0/1.1 structural constraints. It can also surface after an in-place upgrade to SQL Server 2025 if legacy schema collections were registered under a more permissive parser in older engine builds and are now being re-validated against the stricter schema processor.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate schema collections in the affected database:

SELECT name, xml_schema_namespace, schema_id 
FROM sys.xml_schema_collections;

• Cross-reference the %s type name reported in the error message with the <xs:complexType> or <xs:element> definitions inside the collection to find the exact faulting type.
• Export the raw XSD using SELECT xml_schema_namespace('schema_name', 'collection_name') for offline inspection.

Step 2: Validate the XSD Independently

• Copy the exported schema into a .xsd file on your Windows Server 2025 / Windows 11 workstation.
• Use an external validator (e.g., xmllint --schema from libxml2, or Visual Studio’s XML Schema Explorer) to confirm whether the restriction is structurally invalid per W3C spec — SQL Server 2025 enforces stricter model group compatibility rules than prior versions.
• Pay special attention to <xs:restriction base="..."> blocks nested under <xs:complexContent> where the child <xs:sequence> or <xs:choice> does not form a valid subset of the base type’s particle.

Step 3: Correct the Schema Definition

• Edit the XSD so the restricting complex type’s model group matches one of the following valid patterns against its base type: identical particle order with a subset of occurrence constraints, or explicit <xs:choice> to <xs:sequence> mapping allowed under UPA (Unique Particle Attribution) rules.
• Remove or rewrite any minOccurs/maxOccurs combinations on the restricted group that exceed the bounds defined in the base type.
• Save the corrected file and re-import it:

ALTER XML SCHEMA COLLECTION dbo.YourCollectionName
ADD N'<corrected XSD content here>';

Step 4: Rebuild Dependent Objects

• Identify and recompile any tables, procedures, or XML-typed columns that reference the modified schema collection:

EXEC sp_refreshsqlmodule 'dbo.YourStoredProcedure';

• Restart the SQL Server service via services.msc on Windows Server 2025 only if the schema cache appears stale after the update.

Alternative or Verification Steps

• Confirm the fix by re-running the original CREATE/ALTER XML SCHEMA COLLECTION statement and checking that it completes without Error 6990, then insert a sample XML document that conforms to the restricted type to confirm validation passes.
• If editing the XSD is not feasible, as a workaround, drop the restrictive <xs:complexContent><xs:restriction> block entirely and redefine the type using <xs:extension> instead, which avoids triggering SQL Server’s model group restriction checks.

Why This Error Happens

Error 6991 occurs when SQL Server 2025 attempts to parse or validate an XML Schema Collection (XSD) where a derived complex type uses a restriction on a base type that has empty content, but the derived type illegally declares child elements or attributes. This is a strict XSD validation rule enforced by the SQL Server XML schema processor, and it is almost always caused by malformed or hand-edited XSD files being registered via CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION. It can also surface after an in-place upgrade to SQL Server 2025 on Windows Server 2025, since the updated XML parser enforces W3C XSD compliance more rigorously than legacy versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the schema collection causing the error:

SELECT name, xml_schema_namespace, schema_id 
FROM sys.xml_schema_collections 
WHERE name = 'YourSchemaCollectionName';

• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the exact collection and batch that triggered Error 6991.

Step 2: Export and Inspect the XSD Definition

• Extract the current schema using:

SELECT xml_schema_namespace(N'dbo', N'YourSchemaCollectionName');

• Open the extracted XSD in Visual Studio 2022/2025 or an XML validator and search for <xs:restriction base="..."> elements.
• Confirm whether the base complexType is defined with <xs:complexContent> and no children (empty content model), while the restricting type adds <xs:sequence>, <xs:choice>, or attributes — this combination is illegal per W3C XSD rules.

Step 3: Correct the Schema Definition

• Change the base type to use mixed="true" or define it with an actual content model (xs:sequence/xs:all) if child elements are required downstream.
• Alternatively, remove the illegal child/attribute declarations from the restricting type so it matches the empty content of the base type.
• Save the corrected .xsd file to a known path, e.g., C:\SQLSchemas\CorrectedSchema.xsd.

Step 4: Drop and Recreate the Schema Collection

• Since ALTER XML SCHEMA COLLECTION only allows additive changes, drop the broken collection first:

DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Recreate it using the corrected file:

CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS 
'<content of CorrectedSchema.xsd>';

Step 5: Restart SQL Server Service (If Cached)

• If the error persists due to a cached execution plan referencing the old schema, restart the SQL Server service via PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

• Confirm the service restarts cleanly in Services.msc on Windows Server 2025/Windows 11 before re-running dependent queries.

Alternative or Verification Steps

• Verify the fix by re-running any stored procedures or XML typed columns that reference the schema collection; the batch should now execute without raising Error 6991.
• If the issue persists, use xmllint --noout --schema CorrectedSchema.xsd testfile.xml (via WSL or a standalone XML tool) to independently validate the XSD outside of SQL Server before re-importing it.

Troubleshooting Guide: SQL Server Error 6988 – Invalid XML Schema Restriction

Why This Error Happens

This error occurs when SQL Server attempts to parse or register an XML Schema Collection (XSCH) that contains an invalid complex type restriction. Specifically, the XSD definition tries to restrict a choice particle using an element group or sequence rather than a direct element particle, which violates the W3C XML Schema structural rules enforced by the SQL Server XML parser. This is typically caused by a malformed or hand-edited .xsd file, a corrupted schema collection in the database, or a third-party application generating non-compliant XSD during an ALTER XML SCHEMA COLLECTION or CREATE XML SCHEMA COLLECTION operation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the schema collection referenced in the error:

SELECT name, xml_schema_namespace, create_date 
FROM sys.xml_schema_collections 
WHERE xml_schema_namespace LIKE '%YourNamespace%';

• Note the %s type name from Error 6988 in the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025).
• Export the current schema using SELECT xml_schema_namespace(...) or SSMS’s “Script Table/Schema as” feature to inspect the raw XSD.

Step 2: Validate the XSD File Manually

• Open the exported .xsd file in Visual Studio 2022/2025 or an XML validator (e.g., xmllint --noout --schema base.xsd target.xsd).
• Look specifically for a <xs:complexType> containing a <xs:restriction> where the base type uses <xs:choice>, but the restricted child elements are wrapped in <xs:group> or <xs:sequence> instead of individual <xs:element> tags.
• Correct the structure so that every particle under the restricted choice is defined as:

<xs:choice>
  <xs:element name="Field1" type="xs:string"/>
  <xs:element name="Field2" type="xs:int"/>
</xs:choice>

Step 3: Drop and Recreate the Schema Collection

• Back up the database before making changes: BACKUP DATABASE [YourDB] TO DISK = 'C:\Backups\YourDB.bak';
• Drop the invalid collection: DROP XML SCHEMA COLLECTION YourSchemaCollectionName;
• Recreate it using the corrected XSD:

CREATE XML SCHEMA COLLECTION YourSchemaCollectionName AS 
'<the corrected XSD content>';

Step 4: Check for Application-Level Schema Generation Issues

• If the XSD is auto-generated by an ORM (e.g., Entity Framework, BizTalk, or a legacy .NET serializer), update the generating library to the latest patch, as this is a known issue with outdated XSD serializers producing non-compliant choice restrictions.
• Regenerate the XML payload/schema after the update and re-test the insert/import operation.

Alternative or Verification Steps

• Re-run the original operation (INSERT, bulk load, or ALTER XML SCHEMA COLLECTION) that triggered Error 6988; if it completes without error, the fix is confirmed.
• As a workaround, use xs:sequence instead of xs:choice in the base type definition if you control the schema source and strict choice semantics aren’t required.

Troubleshooting Guide: SQL Server Error 6989 – Invalid XML Schema Restriction

Why This Error Happens

Error 6989 occurs when SQL Server’s XML Schema Collection (XSC) parser encounters a malformed XSD definition where a sequence particle is being restricted by an invalid type, such as choice or all. This is typically caused by a corrupted or manually edited XSD file being registered into SQL Server, or by a third-party application generating non-compliant schema definitions during an ALTER XML SCHEMA COLLECTION operation. In some cases, this surfaces after an in-place upgrade to SQL Server 2025 because the underlying XML parser engine enforces stricter W3C XSD compliance rules than previous versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate XML Schema Collections in the affected database:
• SELECT name, xml_schema_namespace, schema_id FROM sys.xml_schema_collections WHERE name != 'sys';
• Cross-reference the error log timestamp in Event Viewer (Windows Logs > Application, Event ID 6989) with recent schema modification activity to isolate which collection triggered the fault.
• Export the schema definition for manual inspection:
• SELECT CONVERT(varchar(max), xml_schema_namespace) AS SchemaXML FROM sys.xml_schema_collections WHERE name = 'YourSchemaName';

Step 2: Correct the XSD Restriction Syntax

• Open the exported XSD content in a validation-capable editor (Visual Studio 2025 or VS Code with the XML extension).
• Locate the <xs:restriction> element nested inside a <xs:complexContent> or <xs:simpleContent> block.
• Verify that the restricted base type uses only <xs:element> or <xs:sequence> child particles—remove or replace any <xs:choice> or <xs:all> particles used improperly within the restriction.
• Save the corrected file as FixedSchema.xsd in a local working directory, e.g., C:\SQLFixes\Schemas\.

Step 3: Drop and Re-register the Schema Collection

• Since schema collections cannot be directly edited once bound to columns or procedures, first identify dependencies:
• SELECT referencing_entity_name FROM sys.dm_sql_referencing_entities('dbo.YourSchemaName', 'OBJECT');
• Temporarily alter dependent columns/tables to remove the schema binding, then drop the collection:
• DROP XML SCHEMA COLLECTION YourSchemaName;
• Re-create it using the corrected XSD file:
• CREATE XML SCHEMA COLLECTION YourSchemaName AS N'<paste corrected XSD content here>';

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server (MSSQLSERVER) service (or the named instance) to clear any cached execution plans referencing the old schema.
• Alternatively, run via PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Re-run the original ALTER/CREATE XML SCHEMA COLLECTION statement that triggered Error 6989; if it completes without output, the fix is confirmed.
• If re-registration fails due to active dependencies, use sp_help 'YourSchemaName' to generate a full dependency tree and script out/restore objects in the correct order before retrying Step 3.

Troubleshooting Guide: SQL Server Error 6986 – Invalid XML Schema Restriction

Why This Error Happens

This error occurs when SQL Server attempts to parse or register an XML Schema Collection (XSD) where a derived complex type uses restriction, but the wildcard (xs:any) occurrence range in the model group does not properly narrow the occurrence range of the wildcard in the base type. Essentially, the XML schema violates W3C XSD validation rules for restriction derivation, often because the schema was authored or auto-generated incorrectly, or was corrupted during a migration or import process. This is a schema-definition issue rather than a SQL Server engine bug, meaning SQL Server is correctly rejecting a structurally invalid schema.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections in the affected database:
• SELECT name, schema_id, xml_schema_namespace FROM sys.xml_schema_collections;
• Export the schema causing the error using SQL Server Management Studio (SSMS) by scripting out the CREATE XML SCHEMA COLLECTION statement.
• Note the exact complex type referenced in the error message (replace %s with the actual type name shown in your error log).

Step 2: Validate the XSD Against W3C Restriction Rules

• Open the exported .xsd file in Visual Studio 2025 or an XML editor with schema validation support.
• Check the <xs:any> wildcard declarations in both the base type and the restricted (derived) type.
• Confirm that minOccurs and maxOccurs in the derived type are equal to or more restrictive than those in the base type (e.g., base maxOccurs="unbounded" can be restricted to maxOccurs="5", but not expanded).
• Verify the processContents and namespace attributes are compatible (the derived wildcard must not loosen constraints set by the base wildcard).

Step 3: Correct and Re-import the Schema

• Edit the XSD file to align the model group’s effective total range with the base wildcard’s occurrence range.
• Drop the invalid schema collection (if partially registered) using:
• DROP XML SCHEMA COLLECTION [SchemaCollectionName];
• Re-create the schema collection with the corrected file:
• CREATE XML SCHEMA COLLECTION [SchemaCollectionName] AS N'<paste corrected XSD content here>';

Step 4: Check for Third-Party or Legacy Schema Sources

• If the schema was generated by a third-party ORM, BizTalk mapper, or legacy SQL Server 2014/2016 export tool, regenerate it using an updated version compatible with SQL Server 2025.
• On Windows Server 2025, ensure the .NET Framework 4.8.1 or .NET 8 XML libraries used by any schema-generation tooling are fully patched via Windows Update.

Step 5: Restart SQL Server Service and Clear Plan Cache

• Open SQL Server Configuration Manager and restart the SQL Server (MSSQLSERVER) service to clear any cached invalid schema metadata.
• Run DBCC FREEPROCCACHE; to ensure no stale execution plans reference the old schema collection.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement — it should complete without Error 6986, and you can confirm success via SELECT * FROM sys.xml_schema_collections WHERE name = 'SchemaCollectionName';.
• If the error persists, bypass strict typed-XML validation temporarily by storing the data in an untyped xml column (without a schema collection binding) until the schema source can be fully corrected.

Why This Error Happens

Error 6987 occurs when SQL Server attempts to parse or validate an XML Schema Collection (XSD) that contains a malformed complexType definition. Specifically, the XSD specification only allows an <xs:all> particle to be restricted by another <xs:all> particle when using <xs:restriction> in a derivation — if the schema uses <xs:sequence> or <xs:choice> to restrict a base type defined with <xs:all>, SQL Server’s internal XML schema validator rejects it. This typically happens when a third-party application, an ORM-generated schema, or a manually authored XSD is registered into a CREATE XML SCHEMA COLLECTION statement without strict adherence to W3C XSD derivation rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate schema collections in the affected database: SELECT name, schema_collection_id FROM sys.xml_schema_collections;
• Use SELECT xml_schema_namespace(N'dbo', N'YourSchemaCollectionName'); to extract the raw XSD content for inspection.
• Check SQL Server Error Logs (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm the exact schema collection name tied to the 6987 event.

Step 2: Correct the XSD Derivation Structure

• Open the extracted XSD in an XML editor and locate the complexType using <xs:restriction base="...">.
• Verify if the base type’s content model uses <xs:all> — if so, the restricting type must also use <xs:all>, not <xs:sequence> or <xs:choice>.
• Rewrite the restricting complexType to match particle types (e.g., change <xs:sequence> to <xs:all>) or refactor the base type to avoid <xs:all> entirely by switching to <xs:sequence> with minOccurs="0" on all elements.
• Validate the corrected XSD using an external validator (e.g., xmllint --schema base.xsd test.xml --noout on Windows via WSL, or Visual Studio’s built-in XML Schema Designer) before reapplying.

Step 3: Drop and Recreate the Schema Collection

• Drop the broken collection: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Recreate it with the corrected XSD: CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<your corrected XSD content>';
• If the schema collection is used by an XML-typed column, ensure no active data violates the new schema constraints before reapplying it (ALTER TABLE ... ALTER COLUMN may require a temporary type change).

Step 4: Update the Source Application (If Applicable)

• If the XSD originates from an ORM (e.g., Entity Framework) or a third-party vendor schema, check for a patch or updated schema package compatible with SQL Server 2025’s stricter XSD 1.1 validation engine.
• Apply any pending Cumulative Update (CU) for SQL Server 2025, as early builds have had XSD parser regressions tied to xs:all particle handling — verify via SELECT @@VERSION;.

Alternative or Verification Steps

• Verify the fix by re-running CREATE XML SCHEMA COLLECTION and confirming no error is thrown; then insert a sample XML document into a column typed with that schema to confirm successful validation.
• If modifying the XSD isn’t feasible (e.g., vendor-locked schema), as a workaround, store the data as plain NVARCHAR(MAX) instead of a typed XML column with schema validation, bypassing SQL Server’s schema collection enforcement entirely.

Troubleshooting Guide: SQL Server Error 6984 – Invalid Restriction for XML Schema Type

Why This Error Happens

This error occurs when an XML Schema Collection (XSC) registered in SQL Server contains a complex type that attempts to restrict a base type, but the elements defined in that restriction fall outside the namespaces permitted by the base type’s wildcard (xs:any) declaration. This typically happens due to a malformed or manually edited XSD file, a corrupted XML schema collection during migration, or a mismatch between namespace declarations when importing third-party schemas into SQL Server 2025. It can also surface after an in-place upgrade from an older SQL Server version if the XML parser’s stricter W3C XML Schema validation rules reject previously tolerated namespace inconsistencies.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections in the affected database:
• SELECT name, xml_collection_id FROM sys.xml_schema_collections WHERE name != 'sys';
• Check SQL Server error logs (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the specific type name referenced alongside Error 6984.
• Note the exact complex type and namespace mentioned in the error message output (%s placeholder).

Step 2: Extract and Inspect the XSD Definition

• Export the schema collection content using:
• SELECT xml_schema_namespace('dbo', 'YourSchemaCollectionName');
• Open the resulting XML in a validator (Visual Studio 2025 XML Editor or xmllint on Windows Server 2025 via WSL) to inspect the <xs:complexContent> and <xs:restriction base="..."> elements.
• Confirm whether the restricted element’s namespace matches the <xs:any namespace="..."> wildcard defined in the parent (base) type.

Step 3: Correct the Namespace Mismatch

• Edit the XSD source file to ensure the restricted element belongs to a namespace explicitly allowed by the base type’s wildcard (e.g., change namespace="##other" to namespace="##targetNamespace" or list the required URI explicitly).
• If the schema was supplied by a third party, request a corrected version or manually align the targetNamespace attribute across both base and derived type definitions.
• Save the corrected XSD file to a working directory, e.g., C:\SQLSchemas\corrected_schema.xsd.

Step 4: Drop and Recreate the Schema Collection

• Drop the existing faulty collection (ensure no dependent XML columns are actively referencing it, or alter them temporarily):
• DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Recreate it using the corrected file:
• CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<paste corrected XSD content here>';
• Reattach the schema collection to any XML-typed columns that were temporarily modified.

Step 5: Validate Application-Side XML Payloads

• If the schema collection itself is valid, check incoming XML payloads from the application layer for namespace prefixes that don’t align with the registered schema.
• Update application connection strings or XML serialization settings (e.g., in .NET XmlSerializer namespace mappings) to ensure outgoing documents conform to the corrected schema.

Alternative or Verification Steps

• Verify the fix by re-running the original INSERT/UPDATE or CREATE XML SCHEMA COLLECTION statement that previously triggered Error 6984 — it should now complete without error.
• As an alternative, use ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollectionName ADD N'<additional corrected schema fragment>'; to patch only the specific namespace/type definitions rather than dropping and recreating the entire collection.

Troubleshooting Guide: Error 6985 – Invalid Restriction for Type in SQL Server 2025

Why This Error Happens

Error 6985 occurs when SQL Server’s XML Schema Collection validation engine encounters a derived complex type whose wildcard (<xs:any>) declaration is broader or incompatible with the wildcard defined in its base type. This typically happens during schema collection registration or alteration (CREATE XML SCHEMA COLLECTION / ALTER XML SCHEMA COLLECTION) when the processContents or namespace attributes on the restricted type’s wildcard do not form a valid subset of the base type’s wildcard rules. It can also surface after an in-place upgrade to SQL Server 2025 if legacy XSD schemas were registered under looser validation rules in earlier engine versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the schema collection referenced in the error message:

SELECT name, schema_id, xml_schema_namespace 
FROM sys.xml_schema_collections 
WHERE name = '<CollectionNameFromError>';

• Extract the full XSD definition using xml_schema_namespace() to inspect the <xs:complexType> and <xs:restriction> blocks.
• Pinpoint the exact complex type named in the %s placeholder of the error text.

Step 2: Correct the Wildcard Definition in the XSD

• Open the XSD source file (commonly stored in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Schemas\ or your application’s source repo).
• Locate the base type’s <xs:any> element and note its namespace and processContents attributes (e.g., namespace="##any" or processContents="lax").
• Modify the restricted (derived) type’s <xs:any> so its namespace list is a strict subset (e.g., change ##any to a specific targetNamespace) and its processContents is equal to or stricter (e.g., strict instead of skip).
• Save the corrected XSD file with UTF-8 encoding to avoid parser errors on re-import.

Step 3: Drop and Recreate the Schema Collection

• Since ALTER XML SCHEMA COLLECTION can only add components (not modify wildcards), drop the collection first:

DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Recreate it using the corrected XSD:

CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS 
N'<your corrected XSD content here>';

• If the collection is bound to existing XML-typed columns, you may need to temporarily drop the column’s schema binding via ALTER TABLE ... ALTER COLUMN before dropping, then rebind afterward.

Step 4: Clear Cached Query Plans Referencing the Schema

• After recreating the collection, flush any stale plans that referenced the old schema definition:

DBCC FREEPROCCACHE;

• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 if the error persists in SSMS IntelliSense or Management Studio sessions.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE/ALTER XML SCHEMA COLLECTION statement or the application workflow that triggered Error 6985 — it should complete without throwing the restriction error.
• If dropping the schema collection isn’t feasible due to dependencies, use sp_help or query sys.xml_schema_components to export the full schema, fix wildcards offline in an XML editor (like Visual Studio’s XSD Designer), and re-import as a net-new collection with a versioned name (e.g., YourSchemaCollection_v2).

Troubleshooting Guide: Error 6982 – Invalid Restriction for Type in SQL Server 2025

Why This Error Happens

Error 6982 occurs when SQL Server 2025 parses an XML Schema Collection (XSC) in which a derived complex type attempts to restrict a base type, but one or more elements in that restriction are not pinned to a fixed value matching the base type’s definition. This is typically caused by a malformed or manually-edited XSD file imported into SQL Server, or by a schema migration/upgrade process that failed to properly carry over fixed attribute constraints from the original XML Schema Collection. It can also surface after an in-place upgrade to SQL Server 2025 if legacy XML schema collections created in older engine versions used looser validation rules that are no longer permitted.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate the schema collection throwing the error:

  SELECT name, schema_id, xml_schema_namespace 
  FROM sys.xml_schema_collections 
  WHERE name = '<YourSchemaCollectionName>';

• Extract the full XSD definition using xml_schema_namespace() to inspect the raw schema:

  SELECT xml_schema_namespace('dbo', '<YourSchemaCollectionName>');

• Review the output for <xs:restriction> blocks nested under <xs:complexContent>.

Step 2: Correct the Fixed Value Mismatch in the XSD

• Open the extracted XSD in a text editor (Notepad++ or VS Code on Windows 11/Windows Server 2025) and locate the derived type causing the conflict.
• Ensure every restricted element includes a fixed attribute whose value exactly matches the corresponding element in the base (parent) type, for example:

  <xs:element name="Status" type="xs:string" fixed="Active"/>

• Confirm there are no trailing whitespace differences or case-sensitivity mismatches between the base and derived fixed values, as SQL Server 2025 enforces strict byte-for-byte comparison.
• Save the corrected XSD file to a known path, e.g., C:\SQLSchemas\CorrectedSchema.xsd.

Step 3: Drop and Recreate the XML Schema Collection

• Drop the broken collection (ensure no dependent tables/columns are actively referencing it first):

  DROP XML SCHEMA COLLECTION dbo.<YourSchemaCollectionName>;

• Recreate it using the corrected XSD file:

  CREATE XML SCHEMA COLLECTION dbo.<YourSchemaCollectionName>
  AS 'C:\SQLSchemas\CorrectedSchema.xsd';

• If dependencies exist, script them out first using SSMS (Generate Scripts wizard) before dropping, then reapply after recreation.

Step 4: Validate Application/ORM-Generated Schemas

• If the schema was auto-generated by an ORM (Entity Framework, Dapper XML mapping, etc.), check the source code or mapping templates for incorrect xsd:restriction generation logic.
• Patch the ORM schema generator or update the NuGet/driver package to the latest version compatible with SQL Server 2025 (minimum build 17.x of Microsoft.Data.SqlClient is recommended).

Alternative or Verification Steps

• Re-run the original CREATE XML SCHEMA COLLECTION or ALTER statement that failed — a successful execution with no Error 6982 confirms the fix.
• As a quick alternative, validate the XSD externally before importing by using xmllint --schema base.xsd derived.xsd --noout on a Linux subsystem (WSL2) or an XML validation tool, ensuring compliance with W3C XSD restriction rules prior to loading it into SQL Server.

Troubleshooting Guide: SQL Server Error 6983 – Invalid Restriction for Type

Why This Error Happens

This error occurs when SQL Server processes an XML Schema Collection where a derived complex type attempts to apply a block attribute (such as restriction, extension, or #all) that is less restrictive than the block value defined on its base type. SQL Server’s XML schema validation engine enforces strict inheritance rules per the W3C XML Schema specification, and any violation of this blocking hierarchy causes the schema registration or alteration (CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION) to fail. This is typically caused by a manually edited or auto-generated XSD file that doesn’t properly inherit or match the block constraints from its parent type, often introduced during application upgrades or third-party schema imports.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate recently modified or added XML schema collections in the affected database:
• SELECT name, xml_collection_id, create_date, modify_date FROM sys.xml_schema_collections ORDER BY modify_date DESC;
• Cross-reference the schema collection name with the application deployment logs to find the exact XSD source file.

Step 2: Inspect and Correct the XSD Block Attribute

• Open the source .xsd file in a text editor or Visual Studio XML editor.
• Locate the complexType definitions involved in the inheritance chain (look for xs:restriction or xs:extension elements).
• Verify that the block attribute on the derived type is equal to or more restrictive than the base type’s block attribute (valid values: extension, restriction, #all, or empty).
• Adjust the derived type’s block value to match or tighten the base type’s restriction, then save the file.

Step 3: Drop and Recreate the XML Schema Collection

• Since XML Schema Collections cannot be directly edited, drop the broken collection first:
• DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Re-import the corrected schema using:
• CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<paste corrected XSD content here>';
• Confirm no dependent tables, columns, or stored procedures are blocking the drop operation (check sys.xml_schema_collections dependencies first if an error appears).

Step 4: Validate the XSD Externally Before Re-Import

• Use an external XML validator (such as Visual Studio, Oxygen XML, or the xmllint --schema command) to pre-validate the corrected XSD file independently of SQL Server.
• This isolates whether the issue is purely in SQL Server’s parser or in the XSD file structure itself, saving time on repeated failed imports.

Alternative or Verification Steps

• After recreating the schema collection, run SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollectionName'; to confirm it registered successfully without errors.
• If the error persists despite correct block values, check for nested or imported XSDs (xs:import / xs:include) that may carry conflicting block definitions from an external namespace — consolidate these into a single validated file before re-importing.

Why This Error Happens

Error 6979 occurs when SQL Server attempts to register or alter an XML Schema Collection where a derived (restricted) complex type does not properly narrow the base type it inherits from. This typically happens when a schema import contains an xs:restriction element whose child elements use the same name as the base type but specify a type that is equal to, or less restrictive than, the parent’s declared type. SQL Server’s internal XSD validator strictly enforces W3C XML Schema derivation rules, so even schemas that pass validation in third-party tools (like Visual Studio or xmllint) can fail here if SQL Server’s parser interprets the type hierarchy differently, often due to an outdated or corrupted XML Schema Collection cached in the database.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Schema Collection

• Run the following query to identify all XML Schema Collections in the affected database:
• SELECT name, xml_schema_namespace, create_date FROM sys.xml_schema_collections;
• Cross-reference the %s placeholder in the error message with the type name reported in the SQL Server Error Log (Management Studio > SQL Server Logs) to pinpoint the exact collection causing the failure.

Step 2: Validate the XSD Restriction Logic

• Export the raw XSD content using SELECT xml_schema_namespace('schema_name', 'collection_name');.
• Manually inspect the <xs:restriction base="..."> block; ensure every child <xs:element> has a type attribute that is a valid subtype (narrower) of the corresponding element in the base type—matching names alone is insufficient.
• Correct any mismatched types (e.g., changing xs:string to a stricter xs:token or a custom simple type with enforced facets) to satisfy W3C derivation-by-restriction rules.

Step 3: Drop and Re-Create the Schema Collection

• Since ALTER XML SCHEMA COLLECTION only allows additive changes, you must drop the broken collection first:
• DROP XML SCHEMA COLLECTION [SchemaName];
• Re-import the corrected XSD file:
• CREATE XML SCHEMA COLLECTION [SchemaName] AS N'<corrected_xsd_content>';
• If the collection is referenced by existing tables or columns, you must temporarily alter those columns to a standard XML type (unconstrained) before dropping, then reapply the schema binding afterward.

Step 4: Clear the Metadata Cache

• In Windows Server 2025/Windows 11 environments, residual plan cache can sometimes reference the old schema object ID, leading to intermittent occurrences of 6979 even after a fix.
• Execute DBCC FREEPROCCACHE; and DBCC FREESYSTEMCACHE('ALL'); to force SQL Server to reload the schema definitions cleanly.

Alternative or Verification Steps

• Verify: Re-run the original CREATE or ALTER statement that triggered the error; if successful, confirm integrity by querying sys.xml_schema_collections to ensure the create_date timestamp has updated.
• Alternative: If the source schema is third-party and cannot be edited, bypass strict typing by mapping the column to an untyped XML data type instead of binding it to the Schema Collection, sacrificing validation for compatibility.

Why This Error Happens

This error occurs when SQL Server validates an XML Schema Collection and finds that a complex type declared as a restriction of a base type has loosened the occurrence constraints (minOccurs/maxOccurs) on one of its particles instead of tightening them. XSD specification rules require that a restricted type can only narrow the cardinality of inherited elements, never widen it — for example, changing minOccurs="1" in the base type to minOccurs="0" in the derived type is illegal. This typically happens after a schema migration, an ORM-generated XSD, or a manually edited .xsd file is registered into a CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection and Type

• Run the following query to locate the schema collection referenced in the error message:

SELECT name, schema_id, xml_collection_id 
FROM sys.xml_schema_collections 
WHERE name = 'YourSchemaCollectionName';

• Extract the full XSD using xml_schema_namespace() to inspect the exact type referenced in the %s placeholder of Error 6980:

SELECT xml_schema_namespace('dbo','YourSchemaCollectionName');

Step 2: Compare Base and Derived Type Particles

• Open the exported XSD in Visual Studio, VS Code, or any XML editor that supports XSD validation.
• Locate the <xs:complexType> definitions involved — one marked with <xs:restriction base="...">.
• Check every <xs:element> or <xs:sequence> particle for minOccurs and maxOccurs values.
• Confirm the derived type’s occurrence range is equal to or narrower than the base type (e.g., base minOccurs="0" maxOccurs="unbounded" → derived must be minOccurs="0" or higher, maxOccurs equal or lower).

Step 3: Correct the XSD Definition

• Edit the derived complex type so its particle occurrence constraints are restrictive, not permissive, relative to the base type.
• Example fix:

<!-- Before (invalid) -->
<xs:element name="OrderLine" minOccurs="0" maxOccurs="unbounded"/>

<!-- After (valid, matches or narrows base type range) -->
<xs:element name="OrderLine" minOccurs="1" maxOccurs="10"/>

• Save the corrected .xsd file.

Step 4: Drop and Recreate the XML Schema Collection

• SQL Server does not allow in-place alteration that changes occurrence constraints on existing types, so drop and recreate the collection:

DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
GO
CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<Insert corrected XSD here>';
GO

• If the collection is referenced by existing XML-typed columns, you may need to script and temporarily drop those column constraints first, then reapply them after recreation.

Step 5: Redeploy and Rebind Dependent Objects

• Re-link any typed XML columns, stored procedures, or CLR objects that reference the schema collection.
• Run sp_refreshsqlmodule on dependent stored procedures/functions to ensure no stale metadata remains:

EXEC sp_refreshsqlmodule 'dbo.YourStoredProcedure';

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE/ALTER XML SCHEMA COLLECTION statement — it should complete without Error 6980, and you can confirm success via SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaCollectionName';.
• If editing the XSD directly is not feasible (e.g., third-party schema), use an XML tool like xmllint --schema or the .NET XmlSchemaSet validator offline first to catch restriction violations before attempting to load the schema into SQL Server 2025.

Troubleshooting Guide: SQL Server Error 6981 – Invalid Restriction for Type

Why This Error Happens

This error occurs when SQL Server parses an XML Schema Collection (XSD) that contains a type derivation by restriction where a child element is marked nillable="false" while the corresponding element in the base type is nillable="true". According to XML Schema specification rules, a restricted type cannot tighten the nillable constraint of its base type since this would violate substitution group compatibility. This is typically caused by a manually edited or auto-generated XSD file that doesn’t correctly inherit the nillable attribute from its parent schema definition, often occurring after migrating schemas from older SQL Server versions or third-party schema generation tools into SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Locate the Offending XML Schema Collection

• Run the following query to identify the schema collection causing the error:
• SELECT name, xml_schema_namespace, schema_collection_id FROM sys.xml_schema_collections WHERE name LIKE '%YourSchemaName%';
• Export the full schema definition using SELECT xml_schema_namespace(N'dbo', N'YourSchemaCollectionName'); to inspect it in a text editor.

Step 2: Identify and Correct the Nillable Mismatch

• Open the exported XSD content and locate the <xs:restriction> block referenced in the error message.
• Search for any child <xs:element> tag with nillable="false" whose corresponding element in the <xs:extension> or base type definition has nillable="true".
• Update the restricted element to match the base type by setting nillable="true" explicitly, ensuring consistency across the inheritance chain.
• Save the corrected XSD file as UTF-8 encoded .xsd to avoid encoding-related parsing errors in Windows Server 2025 environments.

Step 3: Drop and Recreate the XML Schema Collection

• Back up the current schema binding references using sp_help 'YourSchemaCollectionName' before making changes.
• Drop the old collection: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Re-import the corrected schema: CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<paste corrected XSD content here>';
• Rebind any XML-typed columns or variables that referenced the old collection, as dropping it may require re-establishing dependencies.

Step 4: Validate Schema Compatibility with SQL Server 2025 Engine

• Confirm your SQL Server 2025 instance is on the latest Cumulative Update via SQL Server Configuration Manager or SELECT @@VERSION;, since early builds had stricter XSD parsing behavior for nillable inheritance.
• If using Windows 11 for local development/testing, ensure the SQL Server Native Client or ODBC driver is updated to the version matching SQL Server 2025 to prevent client-side schema caching issues.

Alternative or Verification Steps

• Re-run the original CREATE XML SCHEMA COLLECTION or ALTER statement that triggered Error 6981 — if no error is returned and sys.xml_schema_collections shows the updated entry, the fix is confirmed.
• If the error persists despite corrections, use the XML Notepad tool or an online XSD validator to run a full schema validation pass, as there may be a secondary nested restriction further down the schema causing the same violation.

Troubleshooting Guide: SQL Server Error 6977 – Invalid Member Type in Union Type

Why This Error Happens

This error occurs when a CLR (Common Language Runtime) user-defined type (UDT) or user-defined aggregate contains a union definition that references a complex type, such as a class, array, or another struct, instead of a primitive value type. SQL Server’s CLR integration strictly requires that union members be simple, fixed-size value types (like int, byte, or double) because unions rely on overlapping memory layouts that complex reference types cannot safely support. This typically surfaces during the registration or execution of a custom assembly (CREATE ASSEMBLY / CREATE TYPE) that was either miscompiled, corrupted, or written with an invalid [StructLayout(LayoutKind.Explicit)] union containing managed object references.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Assembly and Type

• Check the SQL Server error log (%ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact assembly name and type referenced in the error message.
• Run the following query to locate the problematic CLR type:

  SELECT * FROM sys.assembly_types WHERE assembly_id = (SELECT assembly_id FROM sys.assemblies WHERE name = 'YourAssemblyName');

• Note the exact union type and member name reported in the %s placeholders of Error 6977.

Step 2: Inspect and Correct the Source Code

• Open the .cs or .vb source project for the CLR UDT in Visual Studio 2025.
• Locate the [StructLayout(LayoutKind.Explicit)] struct definition causing the conflict.
• Ensure every field marked with [FieldOffset(x)] uses only blittable value types (e.g., int, float, byte, bool) — remove any string, object, class, or array references from the union.
• Recompile the project targeting .NET Framework 4.8.1 or the CLR version supported by SQL Server 2025 (do not target .NET Core/.NET 8+ runtimes for SQL CLR assemblies).

Step 3: Redeploy the Corrected Assembly

• Drop the broken assembly and type:

  DROP TYPE IF EXISTS dbo.YourUnionType;
  DROP ASSEMBLY IF EXISTS YourAssemblyName;

• Re-register the corrected DLL:

  CREATE ASSEMBLY YourAssemblyName
  FROM 'C:\SQLCLR\YourAssembly.dll'
  WITH PERMISSION_SET = SAFE;

• Recreate the user-defined type referencing the fixed assembly.

Step 4: Validate CLR Integration Settings

• Confirm CLR integration is enabled on the instance:

  EXEC sp_configure 'clr enabled', 1;
  RECONFIGURE;

• Verify the SQL Server service account has read access to the folder containing the DLL (right-click the file in C:\SQLCLR\ → Properties → Security tab) to rule out permission-based load failures masking as structural errors.

Alternative or Verification Steps

• After redeployment, run SELECT * FROM sys.assemblies WHERE name = 'YourAssemblyName'; and confirm no errors are returned when querying or instantiating the type in a test SELECT statement.
• If the error persists, rebuild the assembly from scratch using a standard class (not a struct/union) for the data model, since SQL Server CLR has limited and strict support for explicit memory layouts — switching to sequential layout with simple properties avoids Error 6977 entirely.

Troubleshooting Guide: Error 6978 – “Invalid item type for list type”

Why This Error Happens

This error occurs when SQL Server’s internal metadata engine encounters a nested list structure within a system-defined list type, typically triggered during execution of extended stored procedures, Service Broker message type validation, or CLR integration routines that build hierarchical list objects. The root cause is almost always a malformed or corrupted definition passed to a function expecting a flat (non-nested) list type, often resulting from a botched extension update, a third-party module injecting improperly structured metadata, or corruption in the sys.table_types or related system catalog views after an in-place upgrade to SQL Server 2025. In rarer cases, this surfaces when Query Store or Intelligent Query Processing (IQP) features attempt to serialize plan metadata that includes an improperly nested table-valued parameter (TVP) definition.

Step-by-Step Resolution Guide

Step 1: Identify the Triggering Query or Process

• Check the SQL Server Error Log (Log.trc or via sp_readerrorlog) for the full stack trace accompanying Error 6978.
• Correlate the timestamp with Extended Events session system_health to identify the exact batch, stored procedure, or CLR assembly call that triggered the error.
• Run the following to isolate recent sessions:

  SELECT * FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
  WHERE object_name = 'error_reported' AND event_data LIKE '%6978%';

Step 2: Validate and Repair User-Defined Table Types

• Enumerate all user-defined table types (UDTTs) to check for nested or nullable list-of-list definitions:

  SELECT tt.name, c.name AS column_name, c.system_type_id
  FROM sys.table_types tt
  JOIN sys.columns c ON tt.type_table_object_id = c.object_id;

• Drop and recreate any UDTT that references another table type as a column (not supported in SQL Server 2025).
• If the UDTT was introduced via a recent deployment script or ORM migration (e.g., Entity Framework, Dapper), roll back to the previous schema version and redeploy manually.

Step 3: Clear and Rebuild the Plan Cache / Query Store

• Nested or corrupted list metadata can persist in cached execution plans, causing the error to recur even after the source object is fixed.
• Run DBCC FREEPROCCACHE to clear the plan cache (test in non-production first due to performance impact).
• If Query Store is enabled, clear it as well:

  ALTER DATABASE [YourDB] SET QUERY_STORE CLEAR;

Step 4: Check for Corrupted System Metadata

• Run DBCC CHECKDB with the DATA_PURITY option on the affected database to rule out underlying catalog corruption:

  DBCC CHECKDB ('YourDB') WITH DATA_PURITY, NO_INFOMSGS;

• If corruption is found in system tables, restore from the last known good backup rather than attempting repair on system catalogs.

Step 5: Update SQL Server 2025 to the Latest Cumulative Update

• Confirm the current build with SELECT @@VERSION; and compare against the latest Cumulative Update (CU) release notes from Microsoft.
• Apply the latest CU, as this specific error has been linked to a regression in early SQL Server 2025 builds related to TVP metadata handling on Windows Server 2025 hosts.

Alternative or Verification Steps

• After applying fixes, re-run the originally failing query or stored procedure in isolation (outside of any wrapping transaction) to confirm Error 6978 no longer appears in sys.dm_exec_requests or the error log.
• If the issue persists after schema and cache remediation, enable trace flag 3604 and 3605 temporarily to capture verbose metadata diagnostics to the error log for escalation to Microsoft Support with a CSS case reference.

Troubleshooting Guide: SQL Server Error 6975 – Invalid Attribute Redefinition in Type

Why This Error Happens

Error 6975 occurs when SQL Server attempts to register or validate an XML Schema Collection (XSCHEMA) where a derived complex type redefines an inherited attribute with a different type, default value, or fixed value than its base type declares. This typically happens during CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION operations when the XSD file has been manually edited or merged from multiple sources with conflicting attribute definitions. SQL Server’s XML parser strictly enforces W3C XSD inheritance rules, so even a minor mismatch (e.g., fixed="A" in the base type vs. fixed="B" in the derived type) will trigger this failure and abort the schema registration.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Schema and Type

• Open SQL Server Management Studio (SSMS) and run the failing CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement to capture the exact error text, which includes the attribute name and type name (%s placeholders in the message).
• Locate the source .xsd file(s) referenced in the T-SQL script, typically stored in your project’s schema folder (e.g., C:\SQLSchemas\).
• Use a text editor or XML IDE (Visual Studio 2022+ or VS Code with XML extension) to open the file and search for the reported type name using Ctrl+F.

Step 2: Correct the Attribute Definition Mismatch

• Compare the base type (<xs:complexType> with <xs:extension> or <xs:restriction>) against the derived type that redefines the attribute.
• Ensure the fixed, default, and type values for the attribute are identical in both the base and derived definitions — SQL Server does not allow derived types to override these values.
• If the redefinition is intentional (e.g., a legitimate business rule change), remove the derived attribute declaration entirely and let it inherit directly from the base type instead of restating it.
• Save the corrected .xsd file, ensuring UTF-8 encoding without BOM corruption, which can also cause parser errors on Windows Server 2025.

Step 3: Re-register the XML Schema Collection

• Drop the broken schema collection if a partial registration exists:
• DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Re-run the corrected CREATE XML SCHEMA COLLECTION statement, pointing to the fixed .xsd file via OPENROWSET(BULK...) or inline XML.
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 if the schema cache appears stale after repeated failed attempts.

Step 4: Validate XSD Compliance Independently

• Run the corrected XSD through an external validator (e.g., xmllint --noout --schema yourfile.xsd from Windows Subsystem for Linux, or Visual Studio’s built-in XML Schema Explorer) before re-importing into SQL Server.
• This isolates whether the issue is purely SQL Server’s stricter parser or a genuine W3C XSD violation.

Alternative or Verification Steps

• Verify the fix by querying sys.xml_schema_collections to confirm the schema collection registered successfully without errors, then insert a test XML document that uses the previously conflicting type to confirm validation passes.
• If the error persists despite matching attribute values, check for duplicate namespace imports or circular xs:include/xs:import references in the schema set, as these can cause SQL Server to misread inherited attribute definitions.

Troubleshooting Guide: SQL Server Error 6976 – Invalid Attribute Redefinition in XML Schema Collections

Why This Error Happens

This error occurs when SQL Server attempts to parse or register an XML Schema Collection (XSD) in which a complexType uses derivation by extension (<xsd:extension>) but redefines an attribute that was already declared in the base type. According to the XML Schema specification, extension can only add new attributes or elements—it cannot override or redefine existing ones; redefinition is only permitted through restriction. This typically surfaces when importing third-party XML schemas, migrating XML Schema Collections from an older SQL Server instance, or when an application dynamically generates XSD definitions with duplicate attribute names across a base and derived type.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection and Type

• Run the following query to locate the XML Schema Collection and inspect its definition:

  SELECT name, xml_schema_namespace(SCHEMA_NAME(schema_id), name) AS schema_xml
  FROM sys.xml_schema_collections
  WHERE name = '<YourSchemaCollectionName>';

• Note the %s and %s values from Error 6976 in SQL Server logs (Event Viewer or ERRORLOG) — these indicate the exact attribute and type name causing the conflict.
• Open the extracted XSD content in a text editor or Visual Studio to locate the <xsd:complexType> block referenced.

Step 2: Correct the XML Schema Definition

• Locate the <xsd:extension base="..."> element in the derived complexType.
• Remove or rename the duplicate <xsd:attribute name="..."> declaration that conflicts with the base type’s attribute.
• If the intent was to change the attribute’s type or constraints, switch the derivation model from <xsd:extension> to <xsd:restriction>, which legally supports attribute redefinition.
• Validate the corrected XSD using a standalone validator (e.g., xmllint --schema on Windows Server 2025 via WSL, or Visual Studio’s XML Schema Explorer) before reimporting.

Step 3: Drop and Recreate the XML Schema Collection

• Back up the current (broken) schema collection definition first:

  SELECT xml_schema_namespace(SCHEMA_NAME(schema_id), name)
  FROM sys.xml_schema_collections
  WHERE name = '<YourSchemaCollectionName>';

• Drop the collection (ensure no dependent tables/columns reference it, or alter those first):

  DROP XML SCHEMA COLLECTION [<YourSchemaCollectionName>];

• Recreate it using the corrected XSD:

  CREATE XML SCHEMA COLLECTION [<YourSchemaCollectionName>]
  AS N'<corrected XSD content here>';

Step 4: Check Application or ETL Source for Root Cause

• If the XSD is auto-generated (e.g., by a .NET XmlSerializer, BizTalk, or an ETL tool like SSIS), review the source class or mapping definitions for inherited attribute collisions.
• Update the source generator or mapping file so derived types only extend with new attributes, not conflicting ones with the same name as the base type.

Alternative or Verification Steps

• Verify the fix by re-running the CREATE XML SCHEMA COLLECTION statement and confirming it completes without Error 6976; then test inserting/validating a sample XML document against the schema using CAST(@xmlvar AS XML) with the WITH SCHEMA clause.
• If you cannot modify the source XSD (e.g., third-party vendor schema), use SQL Server’s loose XML typing (store as untyped XML data type without a schema collection) as a temporary workaround until the vendor supplies a corrected schema.

Troubleshooting Guide: SQL Server Error 6973 — Invalid Attribute Redefinition

Why This Error Happens

Error 6973 occurs when SQL Server 2025 processes an XML Schema Collection (XSD) where a derived complex type attempts to redefine an attribute that was marked as “required” in its base type, but the derived type declares it as optional or omits the “use” constraint entirely. This is a schema validation conflict enforced by the XML Schema Definition (XSD) engine within SQL Server’s XML data type infrastructure, not a server configuration fault. It typically surfaces after a schema collection is altered via ALTER XML SCHEMA COLLECTION or during an in-place upgrade where legacy schema collections (migrated from SQL Server 2019/2022) contain inheritance definitions that violate the stricter W3C XSD 1.1 compliance checks introduced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the schema collection referenced in the error and extract its XML definition:

SELECT name, xml_schema_namespace, SCHEMA_NAME(schema_id) AS owner_schema
FROM sys.xml_schema_collections
WHERE name = '<CollectionNameFromErrorLog>';

• Export the full XSD using SELECT XML_SCHEMA_NAMESPACE('dbo','YourCollectionName') to inspect the <xs:complexType> and <xs:extension base="..."> blocks.

Step 2: Correct the Attribute “use” Declaration

• Open the exported XSD in a text editor (Visual Studio Code or XML Notepad on Windows 11/Server 2025).
• Locate the base type’s attribute definition, e.g., <xs:attribute name="OrderID" use="required"/>.
• Ensure the derived type (via xs:extension or xs:restriction) also declares use="required" — do NOT set it to use="optional" or omit the use attribute, as omission defaults to optional and triggers the conflict.
• Save the corrected XSD file to C:\SQLSchemas\Corrected\ (or equivalent path on your Windows Server 2025 instance).

Step 3: Drop and Recreate the Schema Collection

• Since XML Schema Collections cannot be directly edited once a conflicting type exists, drop the broken collection (ensure no dependent columns/tables reference it first):

DROP XML SCHEMA COLLECTION dbo.YourCollectionName;

• Recreate it using the corrected file:

CREATE XML SCHEMA COLLECTION dbo.YourCollectionName
AS N'C:\SQLSchemas\Corrected\schema_fixed.xsd';

Step 4: Apply SQL Server 2025 Cumulative Update

• Check your build version with SELECT @@VERSION; and compare against the latest Cumulative Update (CU) listed in the Microsoft Update Catalog for SQL Server 2025.
• If running RTM or an early CU, apply the latest patch via Windows Update or manually through Setup.exe /Action=Patch /AllInstances — this resolves a known XSD-inheritance validation bug in the initial release build.

Alternative or Verification Steps

• Verify the fix by re-running the original ALTER/CREATE statement that triggered Error 6973; successful execution with no output confirms resolution.
• If the error persists after schema correction, check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for a secondary Event ID pointing to a corrupted .xsd cache, and restart the SQL Server service to clear the in-memory schema compilation cache.

Troubleshooting Guide: SQL Server Error 6974 — Invalid Redefinition of Attribute in Derived Type

Why This Error Happens

This error occurs when SQL Server 2025 parses an XML Schema Collection (XSCHEMA) containing a complex type that improperly overrides a restricted attribute inherited from its base type. Specifically, the XSD defines an attribute in a derived complexType without marking it prohibited, even though the base type explicitly prohibits that same attribute. This typically results from a corrupted or manually edited XSD file, a schema migration from an older SQL Server version with looser XML validation rules, or third-party application schemas that were never updated for stricter XML Schema compliance enforced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections in the affected database:
• SELECT name, schema_id, create_date FROM sys.xml_schema_collections WHERE name NOT IN ('sys', 'INFORMATION_SCHEMA');
• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact schema name and type referenced in the Error 6974 message.
• Note the specific %s placeholders in the error text — these identify the attribute name and the derived type causing the conflict.

Step 2: Extract and Inspect the XSD Definition

• Export the schema using: SELECT xml_schema_namespace('dbo','YourSchemaCollectionName');
• Open the resulting XML in a validator (Visual Studio XML Editor or xmllint --schema on Windows Subsystem for Linux) to locate the complexType with derivedBy="restriction".
• Confirm whether the base type attribute uses use="prohibited" while the derived type redeclares the same attribute with use="optional" or use="required".

Step 3: Correct the Attribute Definition in the XSD

• Edit the derived complexType block so the conflicting attribute also specifies use="prohibited", matching the base type.
• Alternatively, remove the redundant attribute declaration entirely from the derived type if inheritance should pass through unchanged.
• Save the corrected XSD file to a working directory, e.g., C:\SQLSchemas\Fixed\schema_v2.xsd.

Step 4: Drop and Recreate the XML Schema Collection

• Drop the broken collection: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Recreate it using the corrected file:
• CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS 'C:\SQLSchemas\Fixed\schema_v2.xsd';
• Confirm no dependent tables or columns were orphaned during the drop by checking sys.xml_schema_collections again.

Step 5: Restart Dependent Services and Validate

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator).
• Re-run any stored procedures or application inserts that previously triggered Error 6974 to confirm the schema now validates correctly.

Alternative or Verification Steps

• Validate the fix by inserting a test XML document that previously failed against the schema-bound column; a successful INSERT with no 6974 error confirms resolution.
• If the error persists, use sp_help_xml_schema_collection to audit all nested imported schemas, as the conflict may originate in an xs:imported external XSD rather than the primary file.

Troubleshooting Guide: SQL Server Error 6971 – Invalid Type Definition

Why This Error Happens

Error 6971 occurs when a CLR (Common Language Runtime) user-defined type or a custom XML schema collection type defines an attribute that conflicts with restrictions imposed by its base type. This typically happens after a schema migration, an in-place upgrade to SQL Server 2025, or when a deployment script attempts to register a UDT assembly that was compiled against an incompatible .NET runtime version. Corrupted or partially updated system metadata in the sys.assembly_types catalog view can also trigger this validation failure during type-binding operations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Type and Attribute

• Run the following query to extract the exact type name referenced in the error message:

SELECT name, is_user_defined, is_assembly_type 
FROM sys.types 
WHERE name = '%s';

• Cross-reference the attribute name from the error message against the CLR source code or XSD schema file used to define the type.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the full stack trace preceding Error 6971.

Step 2: Validate the Base Type Compatibility

• Open the assembly source (.cs or .vb file) and confirm the base class does not restrict inherited attributes (e.g., [Serializable] conflicts with SqlUserDefinedType in certain CLR hosting modes).
• For XML Schema Collections, validate the XSD against the base complex type using:

xmllint --schema base_schema.xsd derived_schema.xsd --noout

• Remove or modify the non-conforming attribute so it aligns with the restrictions of the parent/base type definition.

Step 3: Drop and Re-register the Assembly/Type

• Drop the invalid type and its dependent assembly:

DROP TYPE dbo.YourTypeName;
DROP ASSEMBLY YourAssemblyName;

• Recompile the corrected assembly (ensure target framework matches SQL Server 2025’s supported CLR version, typically .NET 8/9 integration).
• Re-register the assembly and type:

CREATE ASSEMBLY YourAssemblyName FROM 'C:\SQLAssemblies\YourAssembly.dll' 
WITH PERMISSION_SET = SAFE;
CREATE TYPE dbo.YourTypeName EXTERNAL NAME YourAssemblyName.[YourClassName];

Step 4: Clear Cached Metadata and Restart Services

• Flush the procedure cache to remove any stale type-binding plans:

DBCC FREEPROCCACHE;

• Restart the SQL Server service via services.msc or PowerShell:

Restart-Service -Name "MSSQLSERVER" -Force

Alternative or Verification Steps

• Verify the fix by querying sys.assembly_types and confirming the type loads without error: SELECT * FROM sys.assembly_types WHERE name = 'YourTypeName';
• If the error persists, drop the entire CLR assembly dependency chain and perform a clean redeployment using SqlPackage.exe with the /p:IncludeCompositeObjects=true flag to ensure all base/derived type relationships are rebuilt correctly.

Troubleshooting Guide: SQL Server Error 6972 – Invalid Attribute Redefinition in XML Schema Collections

Why This Error Happens

Error 6972 occurs when a SQL Server XML Schema Collection (XSC) contains a derived complex type that redefines an inherited attribute with a type that is not a valid restriction of the base type’s attribute. This typically happens when XML schemas are imported with inconsistent or manually edited XSD definitions, or when an application registers a schema collection that violates W3C XML Schema inheritance rules. Corrupted or partially updated schema collection metadata in the database (often after a failed migration or upgrade to SQL Server 2025) can also trigger this validation failure.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate the schema collection referenced in the error message:
• SELECT name, schema_collection_id FROM sys.xml_schema_collections WHERE name = '%s';
• Check the SQL Server error log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the full stack trace, including the attribute name and type involved.
• Note the exact complex type and attribute mentioned in the error text.

Step 2: Extract and Validate the XSD Definition

• Export the schema XML using:
• SELECT xml_schema_namespace('YourSchemaName','YourSchemaCollectionName');
• Open the output in Visual Studio 2022+ or an XML editor with schema validation support.
• Manually inspect the <xs:complexType> and <xs:extension base="..."> sections for the attribute in question — confirm the derived attribute’s type is a valid restriction (same type or narrower, e.g., xs:int restricting to xs:positiveInteger) of the base type’s attribute.
• Correct any mismatched types (e.g., xs:string incorrectly overriding a base xs:decimal attribute).

Step 3: Drop and Recreate the Schema Collection

• Script out all dependent XML columns, parameters, and stored procedures using SSMS “Generate Scripts” to avoid dependency errors.
• Drop the corrupted collection:
• DROP XML SCHEMA COLLECTION [YourSchemaCollectionName];
• Recreate it using the corrected XSD:
• CREATE XML SCHEMA COLLECTION [YourSchemaCollectionName] AS N'<corrected XSD content>';
• Reapply the dependent objects (columns/procedures) that were scripted out in the prior step.

Step 4: Clear Cached Metadata and Restart Services

• Clear the procedure cache to remove any stale compiled plans referencing the old schema:
• DBCC FREEPROCCACHE;
• Restart the SQL Server service via services.msc or PowerShell:
• Restart-Service -Name 'MSSQLSERVER' -Force
• On Windows Server 2025, also verify the SQL Server Launchpad service isn’t holding a stale lock on schema-related extended objects if using CLR integration.

Alternative or Verification Steps

• Verify the fix by re-running the original INSERT/UPDATE/validation operation that triggered Error 6972, and confirm no entries related to Event ID 6972 appear in Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the error persists after recreating the schema, use xml_schema_namespace to compare the new collection against a known-good backup copy of the schema (from a pre-production environment) to rule out a deeper application-layer XSD generation bug.

Troubleshooting Guide: SQL Server Error 6969 – ID/IDREF Validation Memory Exhaustion

Why This Error Happens

Error 6969 occurs when SQL Server’s XML validation engine attempts to resolve ID and IDREF attributes against an XML Schema Collection, but the internal memory structures required to track these cross-references exceed available workspace memory. This typically happens with large XML documents containing excessive ID/IDREF attribute pairs, or when IDREF elements are positioned before the ID elements they reference, forcing the parser to hold the entire validation graph in memory simultaneously. Fragmented memory allocation on the host system, combined with default SQL Server memory configuration limits, can also accelerate this failure on otherwise capable hardware.

Step-by-Step Resolution Guide

Step 1: Reduce ID/IDREF Attribute Density in the Source XML

• Open the offending XML document and audit the schema for unnecessary ID and IDREF attributes.
• Remove or consolidate redundant identifier attributes that aren’t strictly required for referential integrity.
• If the XML is generated programmatically, modify the export logic to use fewer cross-referenced nodes per batch.

Step 2: Rearrange Element Order in the XML Document

• Restructure the XML so that elements with IDREF attributes appear after the elements containing the corresponding ID attributes.
• This allows SQL Server’s validator to resolve references sequentially instead of holding unresolved references in memory.
• Use an XSLT transformation or a pre-processing script to reorder nodes before importing into SQL Server.

Step 3: Increase SQL Server Memory Allocation

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Navigate to Server Properties > Memory.
• Increase the Maximum Server Memory (MB) value to allow more headroom for XML validation operations.
• Restart the SQL Server service via services.msc or run:

  net stop MSSQLSERVER && net start MSSQLSERVER

Step 4: Validate XML in Smaller Batches

• Instead of validating one massive XML document against the schema collection, split the file into logical chunks.
• Use OPENROWSET(BULK...) with smaller file segments or import via SSIS with batch sizing enabled.
• Re-run the ALTER TABLE ... ADD CONSTRAINT or schema validation command (CREATE XML SCHEMA COLLECTION) against each chunk independently.

Step 5: Check for Windows Server Memory Pressure

• On Windows Server 2025 / Windows 11, open Task Manager > Performance > Memory to confirm the OS isn’t throttling SQL Server due to system-wide memory pressure.
• Adjust the Virtual Memory / Page File settings via sysdm.cpl > Advanced > Performance Settings > Advanced > Virtual Memory if physical RAM is constrained.

Alternative or Verification Steps

• After applying the fixes, re-run the original CREATE XML SCHEMA COLLECTION or XML.modify()/validation query and confirm Error 6969 no longer appears in the SQL Server Error Log (SQL Server Logs > Current in SSMS).
• As an alternative, disable strict ID/IDREF validation temporarily by validating against a trimmed-down schema (without ID/IDREF constraints) to confirm the root cause before re-enabling full validation incrementally.

Why This Error Happens

This error occurs when SQL Server attempts to register or validate an XML Schema Collection (XSD) that violates the W3C XML Schema specification. Specifically, the XSD defines a complex type containing two or more attributes that are derived from the xs:ID type (such as xs:ID itself or a user-derived simple type restricted from ID). Since XML Schema validation rules strictly prohibit more than one ID-derived attribute per type (to avoid ambiguity in unique identifier resolution), the SQL Server XML parser rejects the schema during CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION operations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate recently modified or newly created XML Schema Collections in the affected database:
• SELECT name, schema_id, create_date, modify_date FROM sys.xml_schema_collections ORDER BY modify_date DESC;
• Cross-reference the %s type name reported in the error message (visible in SQL Server Error Log or Event Viewer under Event ID 6970) with the schema collection’s complex type definitions.

Step 2: Extract and Inspect the XSD Definition

• Export the current schema using: SELECT xml_schema_namespace('dbo','YourSchemaCollectionName');
• Open the resulting XML in a text editor or Visual Studio XML editor.
• Search for the complex type matching the error, and check all <xs:attribute> elements for type="xs:ID" or any custom simpleType that restricts from xs:ID.

Step 3: Modify the XSD to Remove Duplicate ID-Derived Attributes

• Keep only one attribute of type xs:ID (or an ID-derived type) per complex type definition.
• Convert any redundant ID-type attributes to xs:string, xs:IDREF, or xs:token, depending on their intended use (unique key vs. reference).
• Save the corrected XSD file to a local path, e.g., C:\SQLSchemas\CorrectedSchema.xsd.

Step 4: Re-register the Corrected Schema Collection

• Drop the invalid collection if it exists: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
• Re-create it using the corrected XSD:
• CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS 'C:\SQLSchemas\CorrectedSchema.xsd';
• Confirm no errors are returned in SQL Server Management Studio (SSMS) or Azure Data Studio.

Step 5: Restart Dependent Services (If Applicable)

• If the schema collection is used by SSIS packages or CLR-based XML validation routines on Windows Server 2025, restart the SQL Server Agent service via services.msc to clear cached schema references.
• Also restart the SQL Server (MSSQLSERVER) service if the schema is loaded at instance startup via a startup procedure.

Alternative or Verification Steps

• Verify the fix by re-running any stored procedures or application code that previously triggered Error 6970, and confirm successful XML validation against the updated schema.
• If the error persists, use xmllint --noout --schema CorrectedSchema.xsd testfile.xml (via WSL or a standalone XML validator) outside of SQL Server to confirm the XSD itself is W3C-compliant before re-importing it.

Troubleshooting Guide: SQL Server Error 6967 — Invalid Type Definition for XML Schema Collection

Why This Error Happens

This error occurs when registering or altering an XML Schema Collection in SQL Server, where a complexType definition redefines the mixed attribute inconsistently between a base type and its derived type. SQL Server’s XML schema validation engine strictly enforces W3C XSD rules, which state that unless the derivation method is restriction, the derived complexType must inherit the exact same mixed content setting (true/false) as its base type. This typically stems from a malformed or hand-edited XSD file, a schema migrated from a third-party system with looser validation, or an application that generates XML schemas dynamically without accounting for SQL Server’s strict XSD compliance.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection and Type

• Run the following query to locate the schema collection referenced in the error message: SELECT name, xml_schema_namespace, schema_id FROM sys.xml_schema_collections WHERE name = '<CollectionName>';
• Extract the full schema XML using SELECT xml_schema_namespace(N'dbo', N'<CollectionName>'); to inspect the raw XSD content.
• Note the exact type name mentioned in the %s placeholder of Error 6967 — this identifies which complexType has the mixed mismatch.

Step 2: Correct the Mixed Attribute Mismatch in the XSD

• Open the extracted XSD in a text editor or Visual Studio XML editor on your Windows 11 / Windows Server 2025 machine.
• Locate the base <xs:complexType> definition and check its mixed attribute value (e.g., mixed="true").
• Locate the derived <xs:complexType> that uses <xs:extension base="..."> and ensure its mixed attribute matches the base type exactly.
• If the derivation method is <xs:restriction> instead of <xs:extension>, the mixed values are permitted to differ — confirm you are using the correct derivation method for your intended design.
• Save the corrected XSD file to a known path, e.g., C:\SQLSchemas\CorrectedSchema.xsd.

Step 3: Drop and Re-register the XML Schema Collection

• Since SQL Server does not allow in-place editing of schema collections with structural changes, drop the existing collection: DROP XML SCHEMA COLLECTION dbo.<CollectionName>;
• Re-create it using the corrected file: CREATE XML SCHEMA COLLECTION dbo.<CollectionName> AS 'C:\SQLSchemas\CorrectedSchema.xsd'; (load the file contents into a variable first via PowerShell or sqlcmd if using osql/sqlcmd import).
• If other tables or XML columns reference this schema collection, re-validate those columns after re-registration using ALTER TABLE <TableName> WITH CHECK CHECK CONSTRAINT ALL;

Step 4: Validate the Source Application’s Schema Generation Logic

• If the XSD is generated dynamically (e.g., from Entity Framework, BizTalk, or a custom ETL tool), update the schema generation templates to enforce consistent mixed values across base and derived types.
• Re-test schema generation in a staging environment before deploying to production SQL Server 2025 instances.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE XML SCHEMA COLLECTION or ALTER statement that triggered Error 6967 — it should now complete without error.
• As a quick alternative, validate the XSD independently using an external tool (e.g., xmllint --schema or Visual Studio’s XML Schema Explorer) before importing it into SQL Server, catching mixed attribute mismatches earlier in the development pipeline.

Troubleshooting Guide: SQL Server Error 6968 — Invalid Type Definition (Complex Types Cannot Restrict Simple Types)

Why This Error Happens

This error occurs when SQL Server’s XML schema collection parser encounters an XSD definition where a complexType attempts to restrict a simpleType, which violates XML Schema Definition (XSD) rules enforced by the SQL Server XML engine. This typically happens due to a malformed or hand-edited XML Schema Collection, a corrupted schema imported during a database migration, or an application sending an incompatible schema definition to a column of type xml. In SQL Server 2025, stricter schema validation during the CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION operations surfaces this error more aggressively than in prior versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the schema collection referenced in the error message:

SELECT name, xml_schema_namespace, create_date
FROM sys.xml_schema_collections
WHERE name = '%s';

• Note the %s placeholder corresponds to the actual type name reported in the Error 6968 message in SQL Server Error Logs or Event Viewer (Event ID 6968 under Applications and Services Logs > MSSQLSERVER).
• Open a PowerShell session on the Windows Server 2025 / Windows 11 host and check the SQL Server error log path:

Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 50

Step 2: Export and Validate the XSD Definition

• Extract the raw XML schema using:

SELECT CAST(xml_schema_namespace('dbo', 'YourSchemaCollectionName') AS NVARCHAR(MAX));

• Save the output to a .xsd file and validate it using an external XSD validator (e.g., xmllint --noout --schema base.xsd target.xsd on a WSL instance, or Visual Studio’s XML Schema Explorer).
• Look specifically for <xs:complexType> nodes containing <xs:restriction base="xs:simpleType"> — this combination is illegal per W3C XSD spec and must be corrected to use <xs:simpleType> with <xs:restriction> instead.

Step 3: Correct and Re-import the Schema Collection

• Drop the corrupted collection (after confirming no dependent columns/tables rely on it):

DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Fix the XSD file by changing the invalid structure:

<!-- Invalid -->
<xs:complexType name="BadType">
  <xs:restriction base="xs:string"/>
</xs:complexType>

<!-- Corrected -->
<xs:simpleType name="GoodType">
  <xs:restriction base="xs:string"/>
</xs:simpleType>

• Re-import the corrected schema:

CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<paste corrected XSD here>';

Step 4: Restart the SQL Server Service (If Cached)

• If the error persists due to plan cache or metadata caching, restart the SQL Server 2025 service via Services.msc or PowerShell:

Restart-Service -Name 'MSSQLSERVER' -Force

• Confirm the service returns to a “Running” state before retesting the schema import.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE/ALTER XML SCHEMA COLLECTION statement or application operation that triggered Error 6968 — it should complete without errors, and SELECT * FROM sys.xml_schema_collections should list the updated collection with a current timestamp.
• If re-importing fails due to legacy dependencies, use sp_help on the parent table/column to identify and temporarily detach XML-typed columns, apply the schema fix, then reattach and re-validate using ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ALL.

Why This Error Happens

Error 6965 occurs when SQL Server attempts to validate an XML instance against an XML Schema Collection (XSC) bound to a column, variable, or parameter, but the XML content does not conform to the expected schema structure. This typically happens due to a mismatch in element ordering, a missing required element, or an unexpected element name caused by application-side changes to the XML payload that were never synchronized with the schema definition. It can also surface after a schema collection was altered (via ALTER XML SCHEMA COLLECTION) without updating the data or client application generating the XML.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate the schema bound to the affected column:
• SELECT xsc.name, xsc.xml_schema_namespace_id FROM sys.xml_schema_collections xsc;
• Cross-reference the column using sys.columns where xml_collection_id is not null.
• Note the exact namespace and root element names expected by the schema.

Step 2: Extract and Compare the XML Payload

• Capture the actual XML being inserted/updated using SQL Server Profiler or Extended Events (filter on Error 6965).
• Open the captured XML and compare element names/order against the .xsd definition stored in the schema collection.
• Use SELECT CAST(xml_schema_namespace('schema_name', 'collection_name') AS NVARCHAR(MAX)); to export the schema definition for side-by-side comparison.
• Look specifically for case-sensitivity mismatches, missing mandatory nodes, or elements out of sequence (XSD sequence type requires strict ordering).

Step 3: Correct the Schema or the Source XML

• If the application logic changed the XML format intentionally, update the schema collection:
• ALTER XML SCHEMA COLLECTION [SchemaName] ADD '<xsd:schema>...</xsd:schema>';
• If the schema is correct and the source data is malformed, fix the XML generation logic in the application layer (e.g., .NET XmlSerializer attribute ordering, or SSIS XML Source mapping).
• Restart the SQL Server service via services.msc on Windows Server 2025 only if schema cache corruption is suspected (rare, but clears stale metadata).

Step 4: Validate Permissions and Collection Integrity

• Ensure the executing login has REFERENCES permission on the XML Schema Collection:
• GRANT REFERENCES ON XML SCHEMA COLLECTION::[SchemaName] TO [UserName];
• Confirm the collection itself isn’t partially corrupted by dropping and recreating it in a test environment using the original .xsd file.

Alternative or Verification Steps

• Re-run the original INSERT/UPDATE statement that triggered Error 6965; a successful execution with no validation error confirms the fix.
• As a quick workaround, temporarily remove the XML(SchemaName) type constraint on the column and insert as a loosely-typed XML field to isolate whether the issue is schema-based or data corruption-based.

Troubleshooting Guide: SQL Server Error 6966 – XML Schema Facet Round-Tripping Warning

Why This Error Happens

This warning occurs when an XML Schema Collection registered in SQL Server 2025 defines a simple type with a restrictive facet (such as xsd:totalDigits, xsd:fractionDigits, or xsd:pattern) that SQL Server cannot guarantee to preserve exactly when converting data back and forth between its internal XML representation and the original typed instance. Essentially, SQL Server’s XML parser flags that serializing and then deserializing (round-tripping) the data may cause a loss of fidelity—such as truncated precision or reformatted values—because the facet constraint is tighter than what the internal type system can natively enforce. This is typically triggered during CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION operations where the imported XSD contains these restrictive derivations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema and Facet

• Run the operation that triggers Error 6966 and capture the full error text, including the %s placeholders, which will name the specific type and facet (e.g., xsd:decimal restricted by xsd:totalDigits).
• Query existing collections using SELECT name, xml_schema_namespace FROM sys.xml_schema_collections; to locate the collection in question.
• Export the raw XSD using SQL Server Management Studio (SSMS 21) by right-clicking the schema collection under Database > Programmability > Types > XML Schema Collections and selecting Script Collection As.

Step 2: Relax or Remove the Restrictive Facet

• Open the exported .xsd file in a text editor or Visual Studio 2022/2025.
• Locate the <xsd:restriction> block referencing the facet named in the error (commonly totalDigits, fractionDigits, maxLength, or pattern).
• Either widen the facet value (e.g., increase totalDigits from 5 to 38 to match SQL Server’s native decimal precision) or remove the restrictive facet entirely if business logic allows.
• Save the modified schema file to a working directory such as C:\SQLSchemas\Revised\.

Step 3: Drop and Recreate the XML Schema Collection

• Since ALTER XML SCHEMA COLLECTION only allows additive changes, you must drop the existing collection first: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollection;
• Recreate it using the corrected XSD: CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollection AS N'<paste corrected XSD content here>';
• If other tables or columns reference this collection, ensure you script out dependent column definitions first, as SQL Server will block the DROP if dependencies exist.

Step 4: Rebind Dependent Columns and Objects

• Re-link any xml-typed columns that were bound to the old schema collection using ALTER TABLE YourTable ALTER COLUMN YourXmlColumn XML (dbo.YourSchemaCollection);
• Recompile any stored procedures or functions that validate XML against this collection by running EXEC sp_recompile 'YourProcedureName';
• Check SQL Server Error Logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the warning no longer appears on restart or revalidation.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE/ALTER XML SCHEMA COLLECTION statement or the application workflow that triggered Error 6966; the warning should no longer appear in the SSMS Messages tab or the SQL Server Error Log.
• If modifying the facet is not an option due to strict external schema compliance (e.g., industry-mandated XSDs), consider storing the data as nvarchar(max) with application-level validation instead of a typed XML column, bypassing SQL Server’s internal round-trip constraint checks entirely.

Troubleshooting Guide: SQL Server Error 6963 – “Default’ or ‘Fixed’ Value is Longer Than Allowed”

Why This Error Happens

This error occurs when a column, variable, or parameter definition (DEFAULT or FIXED constraint) specifies a literal value that exceeds the maximum length allowed by its defined data type, such as char(n), binary(n), or varchar(n). It is typically triggered during a CREATE TABLE, ALTER TABLE, or schema migration script where the metadata limit for a column was set too small for the hardcoded default value. This can also surface after a database restore or upgrade to SQL Server 2025 if legacy T-SQL scripts contain outdated length constraints that conflict with newer schema validation rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Object

• Open SQL Server Management Studio (SSMS) and check the Messages tab for the specific object name referenced in the Error 6963 stack trace.
• Run the following query to locate columns with default constraints:

  SELECT t.name AS TableName, c.name AS ColumnName, dc.definition, c.max_length
  FROM sys.default_constraints dc
  JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
  JOIN sys.tables t ON dc.parent_object_id = t.object_id;

• Cross-reference the definition column length against the max_length value to find the mismatch.

Step 2: Correct the Column Definition or Default Value

• Navigate to the specific script file (commonly located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Install\ for system scripts, or your custom deployment folder).
• Either shorten the default value to fit within the existing constraint:

  ALTER TABLE YourTable
  ADD CONSTRAINT DF_YourColumn DEFAULT ('ShortVal') FOR YourColumn;

• Or increase the column size to accommodate the required default value:

  ALTER TABLE YourTable
  ALTER COLUMN YourColumn VARCHAR(100);

Step 3: Clear Cached Execution Plans and Validate Schema

• Since SQL Server 2025 aggressively caches schema metadata, flush the procedure cache to ensure the updated schema is recognized:

  DBCC FREEPROCCACHE;

• Re-run the CREATE or ALTER script that originally triggered the error to confirm the metadata now aligns.

Step 4: Check for Collation Conflicts (Windows Server 2025 Specific)

• If using char or nchar types, verify the server collation hasn’t changed during the Windows Server 2025 upgrade, as Unicode (nchar) defaults require double the byte length of standard ANSI (char) defaults.
• Open SQL Server Configuration Manager and check Advanced Properties under the instance properties to confirm the collation setting matches your script’s data type expectations.

Alternative or Verification Steps

• Verification: Execute sp_help 'YourTable' in SSMS to confirm the column length and default constraints now match without throwing Error 6963.
• Alternative Fix: If the error persists in a vendor-supplied script, use SQLCMD with the -I flag to enable QUOTED_IDENTIFIER and ANSI_NULLS settings, as mismatched session settings can sometimes cause SQL Server to miscalculate byte-length validation for default constraints.

Troubleshooting Guide: SQL Server Error 6964 – “Facet Value Is Longer Than Allowed”

Why This Error Happens

This error occurs when a Policy-Based Management (PBM) facet property—such as a database name, file path, or object name—exceeds the maximum character length defined by the facet’s underlying schema in SQL Server 2025. It is frequently triggered during automated policy evaluation when a DBA imports a policy from a different environment where naming conventions allow longer strings than the target server’s facet definition permits. Corrupted or outdated PBM system tables in the msdb database can also cause the facet metadata to report an incorrect or overly restrictive length constraint.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Policy and Facet

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Navigate to Management > Policy Management > Policies.
• Right-click the failing policy and select View History to locate the exact facet property name (e.g., Name, FileGroupName) causing the overflow.
• Cross-reference the property length against Microsoft’s documented facet schema limits for SQL Server 2025.

Step 2: Shorten or Correct the Offending Value

• Rename the database, table, file, or object that exceeds the allowed length (typically 128 characters for most identifiers, but some facets like FileGroupPath have stricter limits).
• Use the following T-SQL to rename a database if that is the source:
• ALTER DATABASE [OldLongDatabaseName] MODIFY NAME = [ShortName];
• If the issue originates from a file path facet, move the file to a shorter directory path (e.g., D:\SQLData\ instead of a deeply nested folder structure).

Step 3: Repair or Re-import the Policy Definition

• Export the current policy to XML via Policies > Export Policy.
• Manually inspect the XML for the <MaxLength> tag under the problematic facet and correct any mismatched values if the policy was imported from a different SQL Server version.
• Re-import the corrected policy using Import Policy in SSMS, ensuring the target server category matches Windows Server 2025 compatibility settings.

Step 4: Rebuild PBM System Tables (If Corruption Is Suspected)

• Run the following query against msdb to check for orphaned or corrupted policy metadata:
• SELECT * FROM msdb.dbo.syspolicy_policies WHERE is_enabled = 1;
• If inconsistencies are found, disable and drop the affected policy, then recreate it manually through the Facets interface rather than importing XML.

Alternative or Verification Steps

• Verify the fix by re-running Evaluate Policies manually under Policy Management; the policy should complete without throwing Error 6964.
• As an alternative, if renaming objects is not feasible, create a custom condition with a relaxed @Name.Length expression using ExecuteSql() in the Advanced Edit mode of the Condition Editor to bypass the default facet restriction.

Troubleshooting Guide: SQL Server Error 6961 – XML Type System Limit Reached

Why This Error Happens

This error occurs because SQL Server internally tracks every distinct XML schema collection and XML-typed column/variable signature used across the database, and this internal metadata catalog has a hard architectural limit (typically around 2,000-7,000 depending on internal object complexity). When a database design excessively reuses xml data types with many different schema collections, or when applications dynamically generate numerous ad-hoc XML type bindings (common in heavily parameterized ORMs or dynamic SQL), the internal counter reaches its ceiling. This is not a memory or disk issue—it is a hard-coded metadata limit in the SQL Server relational engine itself.

Step-by-Step Resolution Guide

Step 1: Identify Redundant XML Schema Collections

• Run the following query to audit existing XML schema collections in the affected database:

  SELECT xsc.name, xsc.xml_collection_id, s.name AS schema_name
  FROM sys.xml_schema_collections xsc
  JOIN sys.schemas s ON xsc.schema_id = s.schema_id;

• Look for duplicate or near-duplicate collections that could be consolidated (e.g., versioned schemas like InvoiceSchema_v1, InvoiceSchema_v2 that could be merged).
• Check sys.columns and sys.types where system_type_id corresponds to XML to find how many tables/columns are bound to each collection.

Step 2: Consolidate or Remove Unused XML Typed Columns

• For columns no longer referenced by application code, drop the XML schema binding:

  ALTER TABLE dbo.YourTable 
  ALTER COLUMN XmlColumn XML; -- removes schema collection binding

• Drop genuinely unused schema collections entirely:

  DROP XML SCHEMA COLLECTION dbo.OldSchemaCollectionName;

• Audit stored procedures and functions for parameters declared as XML(SchemaCollectionName) and refactor to use untyped XML where strict validation isn’t required.

Step 3: Refactor Dynamic SQL Generating New XML Types

• Review application/ORM logic (especially Entity Framework, Dapper, or custom data layers) that may be dynamically creating new typed XML parameters per call instead of reusing a single collection.
• Standardize on a minimal set of reusable XML schema collections rather than generating one per transaction or per customer/tenant.
• Restart the SQL Server service after major schema cleanup to clear cached metadata:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Increase Headroom via Database Separation (If Consolidation Isn’t Enough)

• If the application legitimately requires a high volume of distinct XML types, consider splitting the workload across multiple databases, since the XML type limit is tracked per-database, not per-instance.
• Use cross-database queries or synonyms to maintain application compatibility while distributing the XML type load.

Alternative or Verification Steps

• Verify the fix by re-running the schema collection audit query from Step 1 and confirming the total count has dropped well below the previous threshold; then re-execute the operation that originally triggered Error 6961.
• As a quick workaround while redesigning, replace strictly-typed XML(SchemaCollection) parameters with untyped XML columns/parameters in non-critical paths — this bypasses the schema-collection counter entirely at the cost of losing XSD validation.

Why This Error Happens

Error 6962 occurs when SQL Server attempts to register or validate an XML Schema Collection (XSD) that contains a restricted element type (such as elements typed as xs:ID, xs:IDREF, or certain complex content types) with a default or fixed attribute value assigned. The SQL Server XML parser strictly enforces W3C XML Schema constraints, and per W3C specification, elements derived from these specific types are prohibited from carrying default or fixed constraints because it creates ambiguous validation states. This typically surfaces when importing third-party XSDs, migrating schema collections from older SQL Server versions (2019/2022) to SQL Server 2025, or when a schema file has been manually edited and corrupted.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Element

• Run the failing CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement and capture the full error text; the %s placeholder will usually resolve to the specific element name (e.g., elementName).
• Open the source .xsd file in a text editor or Visual Studio 2022/2025 XML editor and search for that element name using Ctrl+F.
• Check if the element’s type attribute is set to xs:ID, xs:IDREF, xs:IDREFS, or a complex type derived by restriction/extension from one of these.

Step 2: Remove or Relocate the Default/Fixed Constraint

• Delete the default="value" or fixed="value" attribute directly from the offending <xs:element> or <xs:attribute> tag.
• If the constraint logic is required by your application, move the default-value handling into application-layer code (e.g., C#/.NET data layer) rather than the schema definition.
• Save the corrected XSD file to a known path, such as C:\SQLSchemas\corrected_schema.xsd.

Step 3: Re-import the Corrected Schema Collection

• Open SQL Server Management Studio (SSMS 21) connected to your SQL Server 2025 instance.
• Drop the old faulty collection if it partially exists: DROP XML SCHEMA COLLECTION dbo.MySchemaCollection;
• Import the fixed file using:
  CREATE XML SCHEMA COLLECTION dbo.MySchemaCollection AS N'<paste corrected XSD content here>';
• Alternatively, load it via PowerShell using Invoke-Sqlcmd -InputFile "C:\SQLSchemas\corrected_schema.xsd" if scripting the deployment.

Step 4: Validate Permissions and Compatibility Level

• Confirm the database compatibility level supports the schema features in use: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'; (should be 170 or higher for SQL Server 2025 features).
• Ensure the executing login has ALTER permission on the XML Schema Collection or db_ddladmin role membership, as insufficient permissions can sometimes mask as malformed registration errors in chained scripts.

Alternative or Verification Steps

• Verify the fix: Query sys.xml_schema_collections to confirm the collection registered successfully, then run a test INSERT against a table using that XML column/type to ensure validation passes without Error 6962.
• Alternative: If the XSD originates from a vendor and cannot be edited, use an XSLT transformation step to strip default/fixed attributes programmatically before passing the content to the CREATE XML SCHEMA COLLECTION command.

Troubleshooting Guide: SQL Server Error 6958 – Invalid XML Schema Collection Definition

Why This Error Happens

Error 6958 occurs when SQL Server attempts to parse an XML Schema Collection where an <xs:all> group is improperly nested as a child or parent of another model group (such as <xs:sequence> or <xs:choice>). This violates the W3C XML Schema specification, which restricts all groups to appearing only at the root level of a complex type definition, not within composite structures. This typically happens due to a malformed or manually edited XSD file being registered, or a schema collection migrated from an older system (or third-party tool) that generated non-compliant XML structures.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the problematic collection causing the registration failure:
• SELECT name, xml_schema_namespace, create_date FROM sys.xml_schema_collections WHERE schema_id = SCHEMA_ID('dbo');
• Check the SQL Server Error Log (via xp_readerrorlog or Event Viewer on Windows Server 2025) to confirm the exact type name referenced in the %s placeholder of the error.

Step 2: Extract and Validate the XSD Content

• Export the raw schema content using: SELECT CONVERT(XML, xml_schema_namespace('dbo', 'YourSchemaName'));
• Open the extracted XML in Visual Studio 2022 or VS Code with an XML extension to visually inspect the complex type structure.
• Look specifically for instances where <xs:all> is nested inside a <xs:sequence>, <xs:choice>, or another <xs:all> tag—this is strictly illegal in XSD 1.0/1.1.

Step 3: Correct the Model Group Structure

• Rewrite the complex type definition so that the <xs:all> element exists only as the direct child of <xs:complexType> or <xs:complexContent>.
• If you need ordered elements alongside unordered ones, refactor the <xs:all> into a <xs:sequence> entirely, ensuring all child elements have minOccurs="0" or maxOccurs="1" to mimic unordered behavior if required.
• Save the corrected file to a local path, for example: C:\SQLSchemas\Corrected_Schema.xsd.

Step 4: Drop and Re-register the Schema Collection

• Drop the broken collection (ensure no tables are actively referencing it): DROP XML SCHEMA COLLECTION dbo.YourSchemaName;
• Re-create it using the sanitized file: CREATE XML SCHEMA COLLECTION dbo.YourSchemaName AS N'<paste corrected XSD content here>';
• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 if the schema cache does not refresh immediately.

Alternative or Verification Steps

• Verification: Re-run the original CREATE TABLE or ALTER TABLE statement that triggered the error; if it completes without throwing Error 6958, the schema collection is now valid.
• Alternative: If editing the raw XSD is too complex, use the XML Schema Explorer in SQL Server Management Studio (SSMS) 21 to visually drag-and-drop the all group back to the root complex type level, bypassing manual text editing errors.

Troubleshooting Guide: SQL Server Error 6959 – “Invalid Definition”

Why This Error Happens

Error 6959 in Microsoft SQL Server 2025 typically occurs when the XML schema collection or full-text catalog definition referenced by a database object has become malformed, corrupted, or references a schema component that no longer exists. This is commonly triggered after an in-place upgrade to SQL Server 2025 where legacy XML schema definitions use deprecated syntax or namespace structures incompatible with the new engine validation rules. It can also surface when a full-text index definition points to a language resource or stoplist that was removed or improperly migrated during a Windows Server 2025 OS upgrade.

Step-by-Step Resolution Guide

Step 1: Validate the XML Schema Collection Integrity

• Open SQL Server Management Studio (SSMS) 21 and connect to the affected instance.
• Run SELECT xml_collection_id, name, definition FROM sys.xml_schema_collections WHERE name = 'YourSchemaName'; to inspect the stored definition.
• Check for truncated or null values in the definition column, which indicates corruption during migration.

Step 2: Rebuild the Affected Schema or Index

• If the XML schema collection is corrupted, drop and recreate it using DROP XML SCHEMA COLLECTION YourSchemaName; followed by a fresh CREATE XML SCHEMA COLLECTION statement using a validated .xsd file.
• For full-text related 6959 errors, navigate to Object Explorer > Databases > YourDB > Storage > Full Text Catalogs, right-click the catalog, and select Rebuild.
• Ensure the source .xsd or catalog definition file is UTF-8 encoded without a Byte Order Mark (BOM), as SQL Server 2025 is stricter about encoding validation.

Step 3: Update Compatibility Level and Patch Level

• Confirm the database compatibility level is set correctly by running ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 170;.
• Apply the latest Cumulative Update (CU) for SQL Server 2025 via Microsoft Update or the standalone CU installer, since early CUs had known issues with definition parsing on Windows Server 2025 hosts.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in an elevated PowerShell prompt.

Step 4: Clear and Rebuild the Resource Cache

• Open an elevated PowerShell terminal and run DBCC FREESYSTEMCACHE('ALL'); within an SSMS query window to clear any stale object definitions in memory.
• If the error persists, restart the SQL Server Launchpad and SQL Full-text Filter Daemon Launcher services from the Services console (services.msc).

Alternative or Verification Steps

• To verify the fix, re-run the original query or operation that triggered Error 6959 and confirm it executes without exception; also check SELECT * FROM sys.messages WHERE message_id = 6959; to ensure the definition now resolves correctly against sys.dm_fts_index_keywords or sys.xml_schema_collections.
• If the issue persists, restore the specific database object (schema or full-text catalog) from a pre-upgrade backup and re-apply only the schema changes manually to bypass any corrupted migration artifacts.

Troubleshooting Guide: SQL Server Error 6960 — Decimal/Numeric Precision Out of Range

Why This Error Happens

Error 6960 occurs when a query, stored procedure, or application attempts to define or cast a decimal/numeric value with a fractionDigits (scale) value exceeding 10, or a total precision exceeding 28 digits for the non-fractional portion, which violates the internal CLR or XML schema component boundaries used by SQL Server 2025. This is typically triggered by an application layer (often via Entity Framework, SSIS, or a CLR-based component) that dynamically generates a schema or metadata definition with incorrect precision/scale values. It can also surface after a migration from an older SQL Server version where legacy scale definitions were not validated against SQL Server 2025’s stricter component range checks.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Object

• Open SQL Server Management Studio (SSMS) and check the Activity Monitor or Extended Events session to capture the exact statement raising Error 6960.
• Run the following to check recent error logs:

  EXEC sp_readerrorlog 0, 1, 'Error: 6960';

• Note the specific column, variable, or XML schema component referenced in the error message (%s placeholder).

Step 2: Correct the Decimal/Numeric Definition

• Locate the CREATE TABLE, CAST, or CONVERT statement using an invalid definition (e.g., DECIMAL(38,12)).
• Adjust the scale (fractional digits) to a maximum of 10 and ensure the non-fractional part does not exceed 28 digits total precision minus scale.
• Example fix:

  -- Invalid: DECIMAL(38,15)
  -- Corrected:
  ALTER TABLE dbo.Transactions
  ALTER COLUMN Amount DECIMAL(28,10);

Step 3: Update Application/ORM Mapping Layer

• If using Entity Framework Core or SSIS, inspect the model’s HasPrecision() fluent API calls or .dtsx package data flow metadata.
• Correct any mapping exceeding HasPrecision(28, 10) in your DbContext configuration:

  modelBuilder.Entity<Transaction>()
      .Property(t => t.Amount)
      .HasPrecision(28, 10);

• Redeploy the application or SSIS package after regenerating migrations.

Step 4: Validate Server and Database Compatibility Level

• On Windows Server 2025, confirm the database compatibility level supports SQL Server 2025 schema validation rules:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

• If outdated, update it:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Step 5: Restart SQL Server Service and Re-run Validation

• Restart the SQL Server (MSSQLSERVER) service via Services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Re-execute the original query or application process to confirm the error no longer appears.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no related schema corruption remains after the precision fix.
• If the error persists in a third-party application, check for vendor-specific patches addressing SQL Server 2025 decimal precision compatibility, as some older client drivers (ODBC/OLE DB) may need an update to correctly negotiate new precision boundaries.

Troubleshooting Guide: SQL Server Error 6957 – “Invalid Element Definition”

Why This Error Happens

Error 6957 typically occurs when SQL Server’s XML Schema Collection or Policy-Based Management (PBM) engine encounters an XML document containing an element definition that does not conform to the expected schema structure. This is often triggered by corrupted XML configuration files, a mismatched SQL Server component version after an in-place upgrade to SQL Server 2025, or manually edited XML files (such as Extended Events sessions or Resource Governor configurations) that contain malformed or deprecated tags. In some cases, this error surfaces when Windows Server 2025 security policies block the SQL Server service account from reading the full schema file, causing the parser to interpret the truncated file as invalid.

Step-by-Step Resolution Guide

Step 1: Validate the XML Source File

• Locate the specific XML file referenced in the error log, usually found in the SQL Server installation path (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\schemas\sqlserver\).
• Open the file using a validating editor (like Visual Studio Code with an XML extension) to check for unclosed tags or invalid characters.
• Run Test-Xml -Path "C:\path\to\file.xml" in PowerShell to confirm if the file is well-formed.

Step 2: Repair or Replace Corrupted Schema Files

• If the file is corrupted, do not attempt to manually patch it; instead, replace it with a clean version from the installation media.
• Navigate to the SQL Server 2025 setup media and extract the original .xsd or .xml definition files to the Binn directory.
• Restart the SQL Server (MSSQLSERVER) service via services.msc to force the engine to reload the clean schema.

Step 3: Verify Service Account Permissions

• Open File Explorer and navigate to the SQL Server installation directory.
• Right-click the Binn folder, select Properties > Security, and ensure the NT SERVICE\MSSQLSERVER account has Read & Execute permissions.
• Check Windows Server 2025 Group Policy settings to ensure no local security policy is restricting file system access for the service account.

Step 4: Rebuild the Extended Events Session (If Applicable)

• If the error relates to a custom monitoring session, drop the specific session using DROP EVENT SESSION [SessionName] ON SERVER;.
• Recreate the session using the native SQL Server Management Studio (SSMS) 2025 GUI wizard rather than copy-pasting legacy T-SQL scripts to avoid syntax incompatibilities.

Alternative or Verification Steps

• Verification: Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm Error 6957 no longer appears after a service restart.
• Alternative: If the error persists, run DBCC CHECKDB to rule out underlying database corruption that might be affecting metadata-based XML storage, or consider a full repair installation via the SQL Server 2025 Installation Center.

Why This Error Happens

Error 6955 occurs in SQL Server Analysis Services (SSAS) when the metadata for a dimension attribute is malformed, typically because the KeyColumns, NameColumn, or ValueColumn bindings reference a data source column that no longer exists, has an incompatible data type, or has been duplicated across attributes. This often happens after a schema change in the underlying relational database that was not propagated to the SSAS project, or after a corrupted deployment/XMLA script introduced invalid attribute XML. It can also surface when a dimension is restored or migrated from an older SQL Server version into SQL Server 2025 and the attribute definitions are incompatible with the newer engine’s validation rules.

Step-by-Step Resolution Guide

Step 1: Identify the Faulty Attribute and Dimension

• Open the SSAS error log or SQL Server Profiler trace to capture the full error text, which includes the specific attribute name (%s) and the dimension it belongs to.
• In SQL Server Management Studio (SSMS), connect to the Analysis Services instance and locate the affected dimension under Databases > [YourDB] > Dimensions.
• Cross-reference the attribute name against the source table schema in SQL Server 2025 to confirm the column still exists and matches the expected data type.

Step 2: Repair the Attribute Binding in SQL Server Data Tools (SSDT)

• Open the Analysis Services project in SSDT (Visual Studio) that corresponds to the deployed database.
• Navigate to the Dimension Designer, select the problematic attribute, and inspect the KeyColumns, NameColumn, and ValueColumn properties in the Properties pane.
• Remove any reference to a non-existent or renamed column, then remap it to the correct source column using the Data Source View (DSV).
• Rebuild the DSV (Refresh option) to sync table/column metadata if the underlying SQL Server table structure changed.

Step 3: Clear Corrupted Metadata and Redeploy

• Delete the corrupted dimension object from the SSAS database via SSMS (right-click dimension > Delete) to remove any residual invalid XML definitions stored in the OLAP store.
• From SSDT, redeploy the corrected project using Build > Deploy, pointing to the correct target server (localhost or the named SSAS instance on Windows Server 2025).
• If deployment fails, validate the project XML manually by opening the .dim file in a text editor and checking the <Attributes> node for duplicate AttributeID entries or malformed <KeyColumns> tags.

Step 4: Reprocess the Dimension and Cube

• In SSMS, right-click the redeployed dimension and select Process using the Process Full option to rebuild all attribute hierarchies from scratch.
• Once the dimension processes successfully, reprocess any dependent cubes (Process Full at the cube level) to ensure measure groups relink to the corrected dimension attributes.

Alternative or Verification Steps

• Run a test query against the dimension using MDX (SELECT {[Attribute].MEMBERS} ON 0 FROM [Cube]) in SSMS to confirm the attribute now returns valid members without errors.
• If the issue persists after redeployment, restore the SSAS database from a known-good backup taken before the schema change, then manually reapply only the valid schema updates through SSDT rather than a direct XMLA alter script.

Troubleshooting Guide: SQL Server Error 6956 – “Invalid Type Definition for Type ‘%s'”

Why This Error Happens

Error 6956 typically occurs when SQL Server 2025 encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) or XML schema collection definition during assembly loading or type registration. This often results from a mismatched assembly version, incomplete deployment of a custom type’s DLL, or database metadata that references a type definition no longer present in the associated assembly. It can also surface after an in-place upgrade to SQL Server 2025 if legacy CLR assemblies are incompatible with the new runtime host.

Step-by-Step Resolution Guide

Step 1: Verify the CLR Assembly and Type Registration

• Run SELECT * FROM sys.assemblies; to confirm the assembly tied to the affected type is still listed and enabled.
• Cross-check SELECT * FROM sys.assembly_types; to ensure the type definition matches the assembly’s current version.
• Look for orphaned entries where the assembly exists but the type metadata is inconsistent.

Step 2: Re-deploy or Re-register the Affected Assembly

• Drop the problematic type and assembly using DROP TYPE [TypeName]; followed by DROP ASSEMBLY [AssemblyName];.
• Re-import the correct, updated DLL using CREATE ASSEMBLY [AssemblyName] FROM 'C:\SQLAssemblies\YourAssembly.dll' WITH PERMISSION_SET = SAFE;.
• Re-create the type definition with CREATE TYPE [TypeName] EXTERNAL NAME [AssemblyName].[ClassName];.

Step 3: Check SQL Server CLR Configuration

• Confirm CLR integration is enabled by running sp_configure 'clr enabled'; — it should return a value of 1.
• If disabled, enable it via EXEC sp_configure 'clr enabled', 1; RECONFIGURE;.
• Restart the SQL Server service through Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025.

Step 4: Validate Compatibility Level and Database Integrity

• Check the database compatibility level with SELECT name, compatibility_level FROM sys.databases; and ensure it aligns with SQL Server 2025 standards (160 or higher).
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption affecting system metadata tables that store type definitions.

Step 5: Rebuild the Type from a Clean Script (If Corruption Persists)

• Script out all dependent objects (tables, functions, stored procedures) using SQL Server Management Studio’s Generate Scripts wizard.
• Fully remove the type and assembly, restart the SQL Server instance, then reapply the clean script to rebuild the type from scratch.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.types WHERE name = 'YourTypeName'; and confirming it returns without error, then test the affected stored procedure or function that previously failed.
• If the issue persists, restore the database from a pre-upgrade backup to a test instance and compare the sys.assembly_types metadata against production to identify the exact point of corruption.

Microsoft SQL Server 2025 Troubleshooting Guide: Error 6953

Why This Error Happens

Error 6953 (“Invalid type definition for type ‘%s'”) typically occurs when SQL Server encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type or table-valued type definition in the system catalog. This can result from an incomplete assembly deployment, a mismatch between the assembly binary and its metadata after an in-place upgrade to SQL Server 2025, or corruption in the underlying system tables (sys.assembly_types). It may also surface when a database is migrated from an older SQL Server version without properly re-registering CLR assemblies on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify the Affected Type and Assembly Status

• Run the following query to identify the problematic type: SELECT name, assembly_id, is_table_type FROM sys.assembly_types WHERE name = '<type_name>';
• Cross-check the associated assembly using SELECT * FROM sys.assemblies WHERE assembly_id = <id>;
• Confirm whether the assembly binary exists and is accessible at its original deployment path (check sys.assembly_files).

Step 2: Rebuild or Re-register the CLR Assembly

• Open SQL Server Management Studio (SSMS) 2025 as Administrator.
• Drop the corrupted type and dependent objects: DROP TYPE dbo.<type_name>;
• Drop and re-add the assembly: DROP ASSEMBLY <assembly_name>; ALTER DATABASE <db_name> SET TRUSTWORTHY ON; CREATE ASSEMBLY <assembly_name> FROM 'C:\SQLAssemblies\<file>.dll' WITH PERMISSION_SET = SAFE;
• Recreate the user-defined type referencing the restored assembly.

Step 3: Validate System Catalog Integrity

• Run DBCC CHECKDB ('<database_name>') WITH DATA_PURITY; to rule out catalog or page-level corruption.
• If corruption is confirmed, restore the affected database from the most recent verified backup taken before the upgrade.
• Ensure the SQL Server 2025 service account has Read/Execute permissions on the assembly’s source folder under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\.

Step 4: Update SQL Server 2025 Cumulative Update

• Check your current build via SELECT @@VERSION;
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, since known catalog metadata bugs are often patched in CUs.
• Reboot Windows Server 2025 / Windows 11 and restart the SQL Server service after installation.

Alternative or Verification Steps

• After applying fixes, re-run the original query or stored procedure that triggered Error 6953 to confirm the type resolves correctly without exceptions.
• If the error persists, export the schema and data to a new database using the SQL Server Import/Export Wizard, bypassing the corrupted type definition entirely.

Troubleshooting Guide: SQL Server Error 6954 – “Invalid Group Definition for Group ‘%s'”

Why This Error Happens

Error 6954 typically occurs when SQL Server’s Resource Governor encounters a corrupted, malformed, or incompletely defined workload group configuration in the system metadata. This can result from an interrupted ALTER RESOURCE GOVERNOR RECONFIGURE statement, manual edits to system catalogs, or a failed upgrade/migration process that left orphaned group definitions. It may also occur when a classifier function references a workload group that no longer exists or has inconsistent pool associations.

Step-by-Step Resolution Guide

Step 1: Verify Current Resource Governor Configuration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to inspect existing workload groups:

  SELECT * FROM sys.resource_governor_workload_groups;

• Cross-check the group name referenced in the error message (%s) against this result set to confirm if it exists or is malformed.

Step 2: Disable and Reset Resource Governor

• Temporarily disable Resource Governor to isolate the faulty group definition:

  ALTER RESOURCE GOVERNOR DISABLE;

• Drop the problematic workload group (replace GroupName with the actual name from the error):

  DROP WORKLOAD GROUP [GroupName];

• If the drop fails due to dependencies, first remove any classifier function association in master database by altering or dropping the function via ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);.

Step 3: Recreate the Workload Group Definition

• Manually redefine the group with correct syntax and pool association:

  CREATE WORKLOAD GROUP [GroupName] 
  USING [PoolName];

• Ensure the referenced resource pool (PoolName) exists by checking sys.resource_governor_resource_pools.
• Apply the configuration changes:

  ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Check for Metadata Corruption

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ (adjust version folder for SQL Server 2025) and review the ERRORLOG for related corruption warnings.
• Run DBCC CHECKDB against the master database to rule out deeper system catalog corruption:

  DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS;

Step 5: Re-enable Resource Governor

• Once the group is recreated and configuration is validated, re-enable Resource Governor:

  ALTER RESOURCE GOVERNOR RECONFIGURE;
  ALTER RESOURCE GOVERNOR ENABLE;

• Restart the SQL Server service via Services.msc or SQL Server Configuration Manager if changes do not take effect immediately on Windows Server 2025.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_resource_governor_workload_groups to confirm the group now appears with correct statistics and no error flags.
• If the issue persists after recreation, restore the master database from a recent backup taken prior to the corruption event, as this often indicates deeper system metadata damage beyond user-level repair.

Troubleshooting Guide: SQL Server Error 6950 – “Invalid Type Definition for Type ‘%s'”

Why This Error Happens

Error 6950 typically occurs when SQL Server encounters a corrupted or incompatible CLR (Common Language Runtime) user-defined type (UDT) assembly, often following an in-place upgrade, a restore from an older database version, or a mismatch between the .NET runtime version and the assembly binary. This can also happen if the assembly metadata in sys.assemblies references a type structure that no longer matches the compiled DLL due to partial patching or file corruption. In Windows Server 2025 environments, this is frequently triggered by CLR strict security changes introduced in newer SQL Server builds.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to confirm CLR is enabled: sp_configure 'clr enabled';
• If disabled, enable it using: EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

Step 2: Re-register the Problematic Assembly

• Identify the affected assembly by querying sys.assemblies and cross-referencing the type name from the error message.
• Drop the corrupted assembly using: DROP ASSEMBLY [YourAssemblyName];
• Re-add the assembly from a verified source DLL using: CREATE ASSEMBLY [YourAssemblyName] FROM 'C:\Path\To\Assembly.dll' WITH PERMISSION_SET = SAFE;
• Ensure the DLL file is not blocked by Windows (right-click the file, go to Properties, and click “Unblock” if present).

Step 3: Check CLR Strict Security Settings

• Since SQL Server 2017+, “clr strict security” is enabled by default and may block unsigned assemblies.
• Query the current setting: sp_configure 'clr strict security';
• If you trust the assembly source, sign it with a certificate and create a login mapped to that certificate with UNSAFE ASSEMBLY permission rather than disabling strict security globally.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog or use SQL Server Setup to check for the latest Cumulative Update (CU).
• Apply the update via Setup.exe from an elevated Command Prompt: Setup.exe /q /ACTION=Patch /INSTANCENAME=MSSQLSERVER /IACCEPTSQLSERVERLICENSETERMS
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 5: Rebuild the Type from Source (If Assembly is Unrecoverable)

• If the original DLL source is unavailable, export the schema definition using SSMS’s “Generate Scripts” wizard.
• Drop the broken type definition using DROP TYPE [YourTypeName];
• Recompile the source project in Visual Studio targeting the .NET version compatible with SQL Server 2025, then redeploy the assembly.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.assemblies WHERE name = 'YourAssemblyName'; and ensure the status shows no corruption flags, then execute a query using the specific UDT to confirm it processes without throwing Error 6950.
• If the error persists, restore the specific database (not the whole instance) from a pre-upgrade backup to a temporary instance, extract the working assembly binaries, and migrate them to the production environment.

Troubleshooting Guide: Error 6951 – “Invalid Type Definition for Type ‘%s'” in SQL Server 2025

Why This Error Happens

Error 6951 typically occurs when SQL Server encounters a CLR (Common Language Runtime) user-defined type or system type whose metadata definition has become corrupted, mismatched, or incompatible with the current database engine version. This often happens after an in-place upgrade to SQL Server 2025, a restored database from an older/incompatible version, or when the underlying assembly (.DLL) backing a CLR type is missing, altered, or fails serialization validation. It can also surface due to collation mismatches or incomplete schema replication during a failed deployment.

Step-by-Step Resolution Guide

Step 1: Verify the Scope and Source of the Error

• Check the SQL Server Error Log (via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact type name referenced in the error.
• Run the following query to identify if the type is CLR-based:

  SELECT name, is_assembly_type, system_type_id 
  FROM sys.types 
  WHERE name = '<TypeNameFromError>';

• Confirm whether the issue is isolated to a single database or affects tempdb/system databases (indicating a broader instance-level corruption).

Step 2: Validate and Re-register CLR Assemblies

• If the type is assembly-based, locate the source .DLL file originally used to register the type.
• Drop and re-create the assembly binding using:

  ALTER ASSEMBLY [AssemblyName] 
  FROM 'C:\SQLAssemblies\YourAssembly.dll' 
  WITH PERMISSION_SET = SAFE;

• Ensure clr strict security and clr enabled are properly configured in sp_configure, as Windows Server 2025 enforces stricter execution policies by default.

Step 3: Run Database Consistency Checks

• Execute DBCC CHECKDB against the affected database to rule out physical corruption:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found specifically in system catalog views (sys.types, sys.assembly_types), review the repair recommendations output before applying REPAIR_ALLOW_DATA_LOSS, as this is a last resort.

Step 4: Rebuild or Restore the Problematic Type

• If the type remains invalid, drop and redefine the User-Defined Type (UDT) manually:

  DROP TYPE [dbo].[YourTypeName];
  CREATE TYPE [dbo].[YourTypeName] FROM [BaseDataType];

• For complex CLR types, uninstall and reinstall the assembly rather than altering it, ensuring the .NET Framework/Runtime version matches the requirement for SQL Server 2025 compatibility on Windows 11/Server 2025.

Alternative or Verification Steps

• Verification: Re-run the query or stored procedure that originally triggered Error 6951; also execute SELECT * FROM sys.assemblies WHERE is_user_defined = 1; to confirm the assembly status is “valid” and not “unavailable.”
• Alternative: If the error persists across multiple databases, restore the specific system database (master or model) from a known-good backup taken prior to the corruption event, as this often resolves deep-rooted type definition conflicts faster than manual repair.

Troubleshooting Guide: Error 6952 – “Invalid Type Definition for Type ‘%s'” in Microsoft SQL Server 2025

Why This Error Happens

Error 6952 typically occurs when SQL Server encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) or user-defined aggregate during assembly loading or deserialization. This often results from a mismatch between the assembly version registered in the database’s system catalog and the actual compiled assembly binary, or from file corruption in the underlying .dll after a failed deployment or OS-level update on Windows Server 2025. It can also surface after an in-place upgrade where metadata referencing the old CLR type definitions becomes orphaned or incompatible with the new SQL Server 2025 engine.

Step-by-Step Resolution Guide

Step 1: Verify CLR Integration and Assembly Status

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.assemblies WHERE is_user_defined = 1; to list all custom CLR assemblies.
• Cross-reference the clr_name and assembly_id against the type reported in the error message.
• Check sys.configurations to confirm clr enabled is set to 1 using sp_configure 'clr enabled';.

Step 2: Rebuild and Re-register the Corrupted Assembly

• Locate the original source .dll file for the user-defined type (commonly found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ or your custom deployment path).
• Drop the broken type and assembly using:
• DROP TYPE [dbo].[YourTypeName];
• DROP ASSEMBLY [YourAssemblyName];
• Re-create the assembly by pointing to the verified, uncorrupted binary:
• CREATE ASSEMBLY [YourAssemblyName] FROM 'C:\Path\To\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Re-run the CREATE TYPE statement to bind the type back to the assembly.

Step 3: Check File System and Permissions Integrity

• Verify NTFS permissions on the folder containing the .dll so the SQL Server Service Account (e.g., NT Service\MSSQLSERVER) has at least Read & Execute access.
• Use File Explorer or Get-Acl in PowerShell to confirm no inheritance issues exist after a Windows Server 2025 security policy update.
• Run DBCC CHECKDB on the affected database to rule out underlying page-level corruption that might be feeding bad metadata to the type definition.

Step 4: Restart SQL Server Service and Clear Plan Cache

• Open Services.msc on Windows Server 2025/Windows 11 and restart the SQL Server (MSSQLSERVER) service to force a reload of the CLR hosting environment.
• Execute DBCC FREEPROCCACHE; to ensure no stale execution plans reference the old, invalid type structure.

Alternative or Verification Steps

• To verify the fix, run a query that explicitly uses the previously failing type (e.g., DECLARE @x YourTypeName;) and confirm it executes without throwing Error 6952.
• If the error persists, restore the specific user database from a known-good backup taken prior to the CLR assembly corruption, then re-apply any schema changes manually rather than relying on the corrupted metadata.

Troubleshooting Guide: Error 6948 – “Invalid Type Definition for Type ‘%s'” in SQL Server 2025

Why This Error Happens

Error 6948 occurs when SQL Server encounters a corrupted, mismatched, or incompatible CLR (Common Language Runtime) user-defined type (UDT) or user-defined aggregate during a data operation. This is typically caused by a mismatch between the assembly version registered in the database and the actual implementation loaded into memory, often following an in-place upgrade to SQL Server 2025 or a Windows Server 2025 patch that updated the .NET runtime. It can also occur if system catalog metadata referencing the type has become corrupted due to improper assembly deployment or an interrupted ALTER ASSEMBLY operation.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Type and Assembly

• Run the following query to locate the problematic type: SELECT name, assembly_id FROM sys.types WHERE is_assembly_type = 1;
• Cross-reference the assembly_id against sys.assemblies and sys.assembly_files to confirm the binary is still present and valid.
• Check the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log) for the exact timestamp and object ID associated with the 6948 error.

Step 2: Validate and Re-register the CLR Assembly

• Confirm “clr strict security” and “clr enabled” configurations are correctly set via: EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
• Drop and re-add the assembly reference to force SQL Server to reload the binary definition:
• ALTER ASSEMBLY [YourAssemblyName] FROM 'C:\Path\To\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Ensure the DLL file permissions allow the SQL Server service account (NT Service\MSSQLSERVER) read access on Windows Server 2025.

Step 3: Repair System Catalog Corruption

• If re-registering the assembly does not resolve the metadata mismatch, run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; to check for underlying catalog corruption.
• If corruption is confirmed in system tables related to the type definition, restore the database from the last known good backup taken prior to the error’s first occurrence.
• Avoid using REPAIR_ALLOW_DATA_LOSS on user-defined type corruption unless no valid backup exists, as it may force-drop the type definition entirely.

Step 4: Rebuild the Type in a Test Environment

• If the assembly source code is available, recompile the DLL using the .NET SDK version matched to SQL Server 2025 CLR integration requirements.
• Deploy the newly compiled assembly to a staging instance first to confirm the CREATE TYPE... EXTERNAL NAME command executes without throwing Error 6948.
• Script out dependent objects (tables using the UDT as a column) before dropping and recreating the type in production.

Alternative or Verification Steps

• Verify the fix by querying a table that uses the specific UDT column (e.g., SELECT TOP 10 * FROM dbo.YourTable;) and confirming data serializes/deserializes without throwing the error.
• If the error persists across all repair attempts, the safest alternative is to export the data using the type’s string representation (via ToString() methods) into a staging table, drop the UDT-dependent objects entirely, and migrate the data to a standard SQL data type (e.g., VARBINARY or NVARCHAR) to bypass the CLR dependency.

Troubleshooting Guide: SQL Server Error 6949 – “Invalid Type Definition”

Why This Error Happens

Error 6949 typically occurs when SQL Server 2025 encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) or user-defined aggregate during metadata validation. This often happens after an in-place upgrade from an older SQL Server version where assembly bindings were not properly migrated, or when the underlying .NET assembly referenced by the type has been altered, deleted, or is incompatible with the CLR version hosted by SQL Server 2025. In some cases, this is also triggered by permission restrictions on the assembly’s physical file location on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Type and Assembly

• Run the following query to locate the problematic type: SELECT name, assembly_id FROM sys.types WHERE is_assembly_type = 1;
• Cross-reference the assembly_id with sys.assemblies to find the exact .dll name and version causing the conflict.
• Check the SQL Server Error Log (via SSMS or %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log) for the specific type name reported in the %s placeholder.

Step 2: Validate and Re-register the CLR Assembly

• Ensure CLR integration is enabled by running: sp_configure 'clr enabled', 1; RECONFIGURE;
• Drop and re-add the assembly using ALTER ASSEMBLY [YourAssemblyName] FROM 'C:\Path\To\Assembly.dll' WITH PERMISSION_SET = SAFE;
• Verify the assembly file exists and is accessible by the SQL Server service account; check NTFS permissions on the folder in Windows Server 2025’s File Explorer or via icacls.

Step 3: Rebuild the Type Definition

• If re-registering the assembly does not resolve the issue, drop the dependent columns/tables using the type, then drop the type entirely: DROP TYPE [dbo].[YourTypeName];
• Recreate the type definition using CREATE TYPE [dbo].[YourTypeName] EXTERNAL NAME [YourAssemblyName].[ClassName];
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear any cached metadata.

Step 4: Check for Version Compatibility Issues

• Confirm the target .NET Framework/Core version referenced by the assembly matches what SQL Server 2025 supports (SQL Server 2025 requires CLR strict security and specific .NET runtime compatibility).
• Update the PERMISSION_SET clause if the assembly requires UNSAFE or EXTERNAL_ACCESS due to new strict security policies in SQL Server 2025.
• Re-sign the assembly with a trusted certificate if clr strict security blocks loading, using CREATE CERTIFICATE and ADD SIGNATURE T-SQL commands.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.assembly_types WHERE name = 'YourTypeName'; and confirm no error is thrown; also execute a sample SELECT/INSERT against a table using that type.
• As an alternative, if the assembly is non-critical, consider migrating the custom type logic to a native SQL Server data type or a Scalar UDF to eliminate CLR dependency entirely.

Troubleshooting Guide: Error 6946 – “Invalid Type Definition for Type ‘%s'”

Why This Error Happens

Error 6946 typically occurs when SQL Server encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) or user-defined aggregate stored in the database’s metadata. This often happens after an in-place upgrade to SQL Server 2025, a mismatched .NET assembly version, or corruption in the sys.assemblies and sys.assembly_types system catalog views. It can also arise when a database is restored or attached from an older SQL Server version to a server running on Windows Server 2025 / Windows 11 where the underlying CLR runtime binaries have changed or are missing.

Step-by-Step Resolution Guide

Step 1: Identify the Corrupted Assembly or Type

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to locate the problematic type definition:

  SELECT * FROM sys.assembly_types WHERE assembly_id = (
      SELECT assembly_id FROM sys.assemblies WHERE name = 'YourAssemblyName'
  );

• Cross-reference the %s value from the error message in the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to identify the exact type name.

Step 2: Validate and Rebuild the CLR Assembly

• Confirm that CLR integration is enabled by running:

  sp_configure 'clr enabled', 1;
  RECONFIGURE;

• Drop and re-register the affected assembly using CREATE ASSEMBLY with the WITH PERMISSION_SET = SAFE (or UNSAFE if required) clause, pointing to the correct .dll path, e.g., C:\CLR_Assemblies\MyType.dll.
• Ensure the .NET Framework/Runtime version referenced by the assembly matches what is installed on Windows Server 2025 (verify via dotnet --list-runtimes in PowerShell).

Step 3: Repair System Metadata Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY to check for underlying catalog corruption.
• If corruption is confirmed in system tables, restore the database from the last known good backup rather than attempting REPAIR_ALLOW_DATA_LOSS, as this can affect CLR type bindings.
• Re-deploy the UDT definitions from source control or original deployment scripts after the restore completes.

Step 4: Update SQL Server 2025 Cumulative Update

• Check the currently installed build number via:

  SELECT @@VERSION;

• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as known CLR metadata bugs are frequently patched in CU releases.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after installation.

Alternative or Verification Steps

• To verify the fix, re-run the original query or stored procedure that triggered Error 6946 and confirm the type resolves without exception; also check sys.assembly_types to ensure the type metadata is intact.
• As an alternative, if the assembly is non-critical, consider dropping the user-defined type entirely with DROP TYPE and recreating it using a native SQL Server data type to eliminate CLR dependency.

Troubleshooting Guide: SQL Server Error 6947 — XML Validation: Multiple ID Attributes Found

Why This Error Happens

This error occurs when SQL Server’s XML schema validation engine encounters an XML instance where a single element has more than one attribute marked as type ID within the associated XML Schema Collection (XSC). The XML specification strictly requires that any given element can have, at most, one ID-typed attribute, as these values must be unique identifiers used for referential integrity (like IDREF lookups). This typically results from a malformed or overly permissive XSD being registered with CREATE XML SCHEMA COLLECTION, or from merging two schema components that both define ID attributes on overlapping element definitions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Query the database to locate the schema collection causing the conflict using the following T-SQL: SELECT name, xml_schema_namespace FROM sys.xml_schema_collections;
• Review the specific XML column or variable that triggered the error to trace it back to the exact schema collection name.
• Check the application logs or SSMS error details for the specific line/column reference provided in the %s portion of the error message.

Step 2: Extract and Inspect the XSD Definition

• Run SELECT xml_schema_namespace('SchemaName', 'CollectionName'); to pull the raw schema text into a results pane.
• Save this output as a local .xsd file and open it in Visual Studio 2022/2025 or any XML editor with schema validation support.
• Search specifically for multiple <xs:attribute type="xs:ID"> or use="required" declarations applied to the same complex type or element definition.

Step 3: Correct the Attribute Definitions

• Modify the XSD file to ensure only one attribute per element is declared with type="ID".
• If the conflict arises from combining two namespaces (e.g., via xs:import or xs:include), ensure the imported schemas do not redundantly redefine the same ID attribute on inherited complex types.
• Save the corrected .xsd file to a local path, such as C:\SQLSchemas\Corrected_Schema.xsd.

Step 4: Drop and Recreate the Schema Collection

• Since XML Schema Collections cannot be directly altered to remove conflicting components, you must drop the old one: DROP XML SCHEMA COLLECTION SchemaName.CollectionName;
• Re-import the fixed schema using PowerShell or SSMS: CREATE XML SCHEMA COLLECTION SchemaName.CollectionName AS 'contents_of_corrected_xsd';
• Ensure no active table columns are still bound to the old collection before dropping it, or use ALTER XML SCHEMA COLLECTION to add the corrected complex types if the original collection must remain intact.

Alternative or Verification Steps

• Verification: Attempt to insert or validate a sample XML document against the updated schema using SELECT CAST('<xml_data>' AS XML).query('.') WHERE data.exist('/root') = 1; to confirm the engine accepts the document without throwing Error 6947.
• Alternative: If modifying the XSD source is not feasible (e.g., third-party vendor schema), consider relaxing validation by storing the data as untyped XML (without a schema collection binding) rather than typed XML, bypassing the ID constraint check entirely at the cost of strict validation.

Troubleshooting Guide: SQL Server Error 6944 – “Invalid Type Definition”

Why This Error Happens

Error 6944 typically occurs when SQL Server encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) or user-defined table type during metadata resolution. This often results from an incomplete assembly deployment, a mismatch between the assembly version registered in the database and the actual DLL on disk, or corruption in the system catalog views (sys.assemblies, sys.types) caused by an improper upgrade from a previous SQL Server version. In Windows Server 2025 / Windows 11 environments, this can also be triggered by .NET runtime version conflicts following a Windows Update.

Step-by-Step Resolution Guide

Step 1: Verify the Assembly and Type Registration

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run SELECT * FROM sys.assemblies WHERE name = 'YourAssemblyName'; to confirm the assembly exists and is not marked as “unresolved.”
• Cross-check the associated type using SELECT * FROM sys.types WHERE is_assembly_type = 1;

Step 2: Rebuild or Re-register the CLR Assembly

• Drop the corrupted type and assembly using DROP TYPE [SchemaName].[TypeName]; followed by DROP ASSEMBLY [AssemblyName];
• Re-deploy the assembly with CREATE ASSEMBLY [AssemblyName] FROM 'C:\SQLAssemblies\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Re-create the type with CREATE TYPE [SchemaName].[TypeName] EXTERNAL NAME [AssemblyName].[ClassName];

Step 3: Check .NET Framework and CLR Compatibility

• Confirm that the .NET Framework version used to compile the assembly matches the CLR version supported by SQL Server 2025 (check via SELECT * FROM sys.dm_clr_properties;).
• On Windows Server 2025, validate installed runtimes using dotnet --list-runtimes in PowerShell.
• Reinstall or repair the .NET Framework if a version mismatch is detected.

Step 4: Run DBCC CHECKDB for Catalog Corruption

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out system catalog corruption.
• If corruption is found, restore from the last known good backup rather than attempting automatic repair on system type definitions.

Alternative or Verification Steps

• To verify the fix, re-run the query or stored procedure that originally triggered Error 6944 and confirm the type resolves without exceptions in the SQL Server Error Log.
• If the issue persists, consider migrating the UDT logic to a native SQL Server data type (e.g., replacing CLR UDTs with standard VARCHAR/XML structures) to eliminate CLR dependency entirely.

Troubleshooting Guide: SQL Server Error 6945 — Invalid Facet Value in Type Definition

Why This Error Happens

Error 6945 occurs when SQL Server’s XML Schema Collection validation engine encounters a facet (such as maxLength, minInclusive, pattern, or enumeration) whose assigned value is incompatible with the base data type it restricts. This typically results from a corrupted or manually edited XSD file being registered into a schema collection, or from a mismatch between the facet constraints and the underlying simple type (e.g., applying a numeric facet like totalDigits to a string-based type). It can also surface after an in-place upgrade to SQL Server 2025 on Windows Server 2025/Windows 11, where legacy XML schema collections created in older engine versions fail stricter facet-validation rules enforced by the new engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection and Type

• Run the following query to locate the schema collection referenced in the error message:

  SELECT xsc.name, xsc.xml_schema_namespace, xsc.create_date
  FROM sys.xml_schema_collections xsc
  WHERE xsc.name = 'YourSchemaCollectionName';

• Extract the full XSD definition for manual inspection:

  SELECT CONVERT(XML, xml_schema_namespace) AS SchemaDefinition
  FROM sys.xml_schema_collections
  WHERE name = 'YourSchemaCollectionName';

• Cross-reference the type name from the error message ('%s' placeholder) against the <xs:simpleType> or <xs:complexType> nodes in the exported XML.

Step 2: Validate and Correct the Facet Definition

• Open the exported XSD in Visual Studio 2022+ or an XML editor with schema validation (e.g., Oxygen XML Editor) on your Windows 11 workstation.
• Check for mismatched facet-to-base-type pairings, common examples include:
• <xs:maxLength> applied to xs:decimal or xs:integer (invalid — only valid for string/list types).
• <xs:fractionDigits> or <xs:totalDigits> applied to xs:string (invalid — only valid for numeric types).
• Negative or non-numeric values in <xs:minInclusive>/<xs:maxInclusive> for numeric restrictions.
• Correct the facet value or remove the invalid facet from the <xs:restriction base="..."> block.

Step 3: Re-register the Corrected Schema Collection

• Drop the broken schema collection (ensure no dependent XML columns/variables reference it first):

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Re-create it using the corrected XSD file:

  CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName
  AS N'<paste corrected XSD content here>';

• If the schema is deployed via a DACPAC or SSDT project, update the .xsd source file in the project and redeploy rather than editing the live database directly.

Step 4: Apply Latest Cumulative Update

• Confirm you are on the latest SQL Server 2025 Cumulative Update via SELECT @@VERSION;, since early builds had stricter/buggy facet-parsing logic.
• Download and apply the current CU from the Microsoft Update Catalog if the build number is below the latest GA patch level.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after patching.

Alternative or Verification Steps

• Re-run the original INSERT/UPDATE/CREATE statement that triggered Error 6945; if it completes without exception, validate data integrity by querying the affected XML column with .exist() or .value() methods to confirm schema enforcement works as expected.
• If re-registration still fails, use sqlcmd with the -y 0 flag to capture the full unformatted error output, and compare the XSD against the W3C XML Schema Definition Language Part 2: Datatypes spec to confirm facet-to-type compatibility.

Troubleshooting Guide: SQL Server Error 6942 – “Invalid Type Definition”

Why This Error Happens

Error 6942 typically occurs when SQL Server 2025 encounters a corrupted or incompatible CLR (Common Language Runtime) user-defined type, often following an in-place upgrade or a mismatched assembly version. This can also result from a damaged system catalog entry in sys.types or sys.assembly_types that no longer matches the physical assembly binary stored in the database. Additionally, permission restrictions introduced by Windows Server 2025’s enhanced security baseline can prevent SQL Server from correctly loading or validating the assembly metadata at runtime.

Step-by-Step Resolution Guide

Step 1: Verify the Assembly and Type Metadata Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following query to identify the problematic type: SELECT * FROM sys.assembly_types WHERE name = '%s';
• Cross-reference the assembly_id against sys.assemblies to confirm the binary still exists and is marked as is_visible = 1.
• Check for mismatched clr_name values, which often indicate a version drift after a Windows Server 2025 cumulative update.

Step 2: Rebuild or Re-register the CLR Assembly

• Drop the corrupted type using DROP TYPE [dbo].[YourTypeName]; (only after backing up dependent objects).
• Re-register the assembly manually: CREATE ASSEMBLY YourAssembly FROM 'C:\SQLAssemblies\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Recreate the type: CREATE TYPE [dbo].[YourTypeName] EXTERNAL NAME YourAssembly.[YourClassName];
• Ensure the .dll file path is accessible to the SQL Server service account (check NTFS permissions under C:\SQLAssemblies\).

Step 3: Validate Windows Server 2025 Security Policies

• Open Local Security Policy (secpol.msc) and confirm the SQL Server service account has “Log on as a service” rights.
• Check Windows Defender Application Control (WDAC) or AppLocker policies that may be blocking the CLR assembly execution.
• Review Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating Error 6942 entries with access-denied warnings.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying catalog corruption.
• If corruption is found, restore from the last known good backup rather than attempting a repair on system type definitions.

Alternative or Verification Steps

• To verify the fix, re-run the original query or stored procedure that triggered Error 6942 and confirm the type resolves without exception.
• If the error persists, use sp_helptype 'YourTypeName' to confirm the type is properly registered; if it still shows inconsistencies, consider restoring the model or msdb system databases from a pre-upgrade backup as a last resort.

Troubleshooting Guide: Error 6943 – “Invalid Type Definition for Type ‘%s'” in SQL Server 2025

Why This Error Happens

This error typically occurs when SQL Server encounters a CLR (Common Language Runtime) user-defined type or table type whose metadata definition has become corrupted, mismatched, or incompatible with the current assembly version loaded in the database. It can also arise when a custom data type was created against a .NET assembly that was later altered, dropped, or replaced without properly updating the dependent type definitions. Additionally, permission issues on the CLR assembly or incomplete deployment of a type during a failed migration/upgrade to SQL Server 2025 can trigger this error.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Type and Assembly

• Run the following query to locate the problematic type and its associated assembly:

SELECT name, system_type_id, user_type_id, is_assembly_type 
FROM sys.types 
WHERE name = 'YourTypeName';

• Cross-reference the assembly using sys.assembly_types and sys.assemblies to confirm version consistency.
• Check the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for additional context around the timestamp of the error.

Step 2: Rebuild or Re-register the CLR Assembly

• Drop the corrupted type and dependent objects (views, functions, procedures) referencing it.
• Re-deploy the assembly using CREATE ASSEMBLY with the WITH PERMISSION_SET = SAFE (or appropriate level):

CREATE ASSEMBLY YourAssemblyName
FROM 'C:\SQLAssemblies\YourAssembly.dll'
WITH PERMISSION_SET = SAFE;

• Recreate the user-defined type using CREATE TYPE and bind it explicitly to the refreshed assembly.

Step 3: Validate CLR Integration and Permissions

• Ensure CLR integration is enabled at the instance level:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• Verify the SQL Server service account has read/execute permissions on the folder containing the DLL (e.g., C:\SQLAssemblies\) via Windows File Explorer security settings.
• Restart the SQL Server service through Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to clear cached metadata.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; to rule out underlying metadata or page-level corruption.
• If corruption is found, review repair options (REPAIR_REBUILD) based on the DBCC output severity.

Alternative or Verification Steps

• Confirm resolution by executing a simple SELECT or DECLARE statement using the previously failing type to ensure it initializes without error.
• If the issue persists, restore the affected database from a pre-corruption backup and re-apply only the necessary assembly/type changes in a controlled, scripted transaction.

Why This Error Happens

Error 6940 occurs when SQL Server attempts to parse an XML Schema Collection (XSD) that contains invalid top-level components. SQL Server’s XML schema validation engine strictly enforces that only <element> declarations are permitted at the global (root) scope of a schema—definitions like global <attribute>, <complexType>, or <simpleType> declared outside an element wrapper will trigger this failure. This typically happens due to a malformed or hand-edited XSD file, or when a schema was authored for a different XML parser that allows looser global-scope rules.

Step-by-Step Resolution Guide

Step 1: Locate and Open the Offending XSD File

• Identify the schema collection name referenced in the error message (replaces %s).
• Run the following query to find the collection in your database:

SELECT name, xml_schema_namespace 
FROM sys.xml_schema_collections 
WHERE name = 'YourSchemaCollectionName';

• Export the raw XSD content using SELECT XML_SCHEMA_NAMESPACE('schema_name', 'collection_name'); for inspection.

Step 2: Audit the Global Scope Elements

• Open the exported XSD in a text editor (VS Code or Notepad++ recommended on Windows 11 / Windows Server 2025).
• Check every top-level node directly under <xs:schema>.
• Confirm that only <xs:element> tags exist at this level—any stray <xs:attribute>, <xs:complexType>, or <xs:simpleType> declared globally must be nested inside an <xs:element> definition instead.
• Remove or re-wrap any non-compliant global components.

Step 3: Re-Register the Corrected Schema Collection

• Drop the broken schema collection:

DROP XML SCHEMA COLLECTION YourSchemaCollectionName;

• Re-create it using the corrected XSD file:

CREATE XML SCHEMA COLLECTION YourSchemaCollectionName 
AS N'<paste corrected XSD content here>';

• Ensure the SQL Server service account has read permissions to any network path if the schema is loaded via OPENROWSET(BULK...).

Step 4: Validate Application-Side Schema Generation

• If the XSD is auto-generated by an application (e.g., via .NET’s XmlSchemaInference or a third-party ORM), update the generation library to a version compliant with W3C XSD 1.0/1.1 global-scope rules.
• Re-deploy the corrected schema through your application’s deployment pipeline rather than manual edits, to prevent regression.

Alternative or Verification Steps

• Verify the fix by querying sys.xml_schema_collections again and running a test CAST of sample XML data against the schema using XMLDATA validation to confirm no errors are thrown.
• If re-creation fails again, use an XSD validator tool (e.g., xmllint --schema or Visual Studio’s XML Schema Explorer) independently of SQL Server to confirm the schema is W3C-compliant before re-importing it.

Troubleshooting Guide: Microsoft SQL Server Error 6941 – “Invalid Type Definition”

Why This Error Happens

Error 6941 typically occurs when SQL Server encounters a corrupted or malformed CLR (Common Language Runtime) user-defined type (UDT) definition within the database metadata. This can result from an incomplete assembly deployment, a mismatch between the assembly version registered in SQL Server and the actual DLL on disk, or corruption in the system catalog views (sys.assemblies, sys.types) following an improper upgrade from a previous SQL Server version. In Windows Server 2025 environments, this is also frequently triggered by .NET runtime version conflicts after a Windows Update patches the CLR hosting environment.

Step-by-Step Resolution Guide

Step 1: Verify the CLR Assembly Integrity

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.assemblies WHERE name = 'YourAssemblyName'; to confirm the assembly is registered.
• Check the physical DLL path (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\) to ensure the file has not been deleted, renamed, or replaced with an incompatible version.
• Run SELECT assembly_class, assembly_method FROM sys.assembly_types WHERE assembly_id = <id>; to cross-reference the type definitions against the assembly.

Step 2: Rebuild or Redeploy the User-Defined Type

• Drop the affected type using DROP TYPE dbo.YourTypeName; (ensure no dependent objects exist first by checking sys.sql_dependencies).
• Drop and re-add the assembly: DROP ASSEMBLY YourAssemblyName; CREATE ASSEMBLY YourAssemblyName FROM 'C:\Path\To\YourAssembly.dll' WITH PERMISSION_SET = SAFE;
• Recreate the type definition: CREATE TYPE dbo.YourTypeName EXTERNAL NAME YourAssemblyName.[Namespace.ClassName];

Step 3: Check .NET Runtime Compatibility on Windows Server 2025

• Navigate to Control Panel > Programs > Programs and Features and confirm the correct .NET Framework version (4.8.1 or higher) is installed alongside .NET 9 runtime components required by SQL Server 2025.
• If a mismatch exists, repair the .NET installation via dotnet repair or reinstall the specific runtime from the Microsoft .NET download page.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for underlying catalog corruption.
• If corruption is found in system tables, restore the database from the last known good backup rather than attempting an in-place repair, as REPAIR_ALLOW_DATA_LOSS does not reliably fix CLR type metadata.

Alternative or Verification Steps

• To verify the fix, run the original query or stored procedure that triggered Error 6941 and confirm it executes without exceptions; also re-check sys.assembly_types to ensure the type resolves correctly.
• If the error persists, as an alternative, export the data using bcp or SSIS to a new database with freshly created (non-CLR) native data types, bypassing the corrupted UDT entirely.

Troubleshooting Guide: SQL Server Error 6938 – XML Canonical Form Validation Failure

Why This Error Happens

Error 6938 occurs when SQL Server’s XML validation engine attempts to reconcile a value against an XML Schema Collection (XSD) that contains restrictive facets—such as pattern, enumeration, or range restrictions—applied to data types where they are not reliably supported, particularly floating-point types (float, double, decimal). Because the canonical (lexical) representation of floating-point numbers can vary (e.g., scientific notation, trailing zeros, precision rounding), the XML parser fails to match the stored value against the strict facet rule even though the underlying numeric value is technically correct. This is typically triggered by a malformed or overly strict XSD schema registered in the database, or by an application inserting XML data with a numeric format that doesn’t normalize cleanly under the W3C XML Schema canonicalization rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections in the affected database:

  SELECT name, xml_collection_id FROM sys.xml_schema_collections;

• Use SELECT XML_SCHEMA_NAMESPACE('schema_name', 'collection_name') to extract and inspect the raw XSD content.
• Look specifically for <xs:restriction base="xs:float">, xs:double, or xs:decimal combined with <xs:pattern> or <xs:enumeration> facets.

Step 2: Correct or Relax the Schema Facets

• Modify the XSD source to remove pattern/enumeration facets from floating-point base types; instead, validate ranges at the application layer or use xs:string with pattern validation if format enforcement is required.
• Re-register the corrected schema using:

  ALTER XML SCHEMA COLLECTION [dbo].[YourCollectionName]
  ADD '<xsd corrected schema content here>';

• If the collection cannot be altered due to dependencies, drop and recreate it:

  DROP XML SCHEMA COLLECTION [dbo].[YourCollectionName];
  CREATE XML SCHEMA COLLECTION [dbo].[YourCollectionName] AS N'<corrected XSD>';

Step 3: Normalize the Offending Data Value

• Identify the exact row/value causing the failure by isolating the INSERT/UPDATE batch (binary search on batch size if bulk-loading).
• Convert problematic floating-point values to a canonical string format before insertion (e.g., force two-decimal rounding or remove trailing zeros) using CONVERT(varchar, value, 2) or application-side formatting ("G17" format specifier in .NET is often incompatible—use "R" or explicit rounding instead).
• Re-run the INSERT/UPDATE statement against the typed XML column to confirm the value now validates.

Step 4: Patch and Update SQL Server 2025

• Check your current build with:

  SELECT @@VERSION;

• Apply the latest Cumulative Update (CU) via Windows Update or the SQL Server Update Center, as XML canonicalization edge-case bugs for floating-point facets are frequently addressed in CUs.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after patching.

Alternative or Verification Steps

• Verify the fix by re-inserting the original failing dataset into the typed XML column and confirming no Error 6938 is raised in SQL Server Management Studio (SSMS) or the Application Event Log.
• As a workaround if schema correction isn’t feasible immediately, change the target column to untyped xml (remove the WITH (XML_SCHEMA_COLLECTION) binding) temporarily to unblock data ingestion while the schema is redesigned.

Why This Error Happens

Error 6939 occurs when SQL Server 2025’s XML schema validation engine encounters a schema (XSD) that defines an element as having a fixed value while simultaneously permitting mixed content (text and child elements together). The XML parser rejects this because a fixed-value constraint and element content cannot coexist in the same complex type definition. This typically results from a malformed or manually edited XSD file, a corrupted XML schema collection stored in the database, or a third-party application generating non-compliant schema definitions during an import/export routine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection

• Run the following query to locate schema collections in the affected database:
• SELECT name, schema_id, principal_id FROM sys.xml_schema_collections;
• Cross-reference the element name reported in the error (%ls) with the XSD definitions stored in these collections to pinpoint the exact schema causing the conflict.

Step 2: Extract and Inspect the XSD Definition

• Export the schema collection content using xml_schema_namespace() to view the raw XSD:
• SELECT xml_schema_namespace('SchemaName', 'CollectionName');
• Look for a <xs:complexType> definition where mixed="true" is declared alongside a <xs:element> or <xs:attribute> using fixed="value".
• Remove either the mixed="true" attribute (if element content is required) or remove the child element nodes (if a fixed text value is required), depending on your business logic.

Step 3: Drop and Recreate the Corrupted Schema Collection

• Since XML schema collections in SQL Server 2025 are largely immutable for structural changes, drop the broken collection:
• DROP XML SCHEMA COLLECTION [SchemaName].[CollectionName];
• Recreate it using the corrected XSD file:
• CREATE XML SCHEMA COLLECTION [SchemaName].[CollectionName] AS N'<corrected XSD content here>';
• Ensure any tables or stored procedures that reference this collection as a column type are updated or recompiled.

Step 4: Update the Application Source Generating the XML

• If the XML payload originates from an external application (ETL tool, .NET service, or REST API), check the source code or configuration file generating the schema.
• On Windows Server 2025 / Windows 11, check the application’s config directory (commonly C:\Program Files\<AppName>\Config\schemas\) for the source .xsd file and apply the same fix as Step 2.
• Redeploy the corrected schema file to ensure future XML submissions do not retrigger the validation failure.

Step 5: Clear SQL Server Plan Cache (If Error Persists)

• Occasionally, cached execution plans reference the old invalid schema definition.
• Run DBCC FREEPROCCACHE; in a test environment (avoid on busy production servers due to performance impact) to force SQL Server to reload schema validation logic.

Alternative or Verification Steps

• Verify the Fix: Re-run the original INSERT/UPDATE command or application process that triggered Error 6939. Query sys.xml_schema_collections again to confirm the collection timestamp reflects the recent update.
• Alternative Workaround: If recreating the schema collection is not immediately feasible, temporarily cast the XML data as untyped (XML without schema binding) at the application layer to bypass strict validation while the XSD is corrected offline.

Troubleshooting Guide: SQL Server Error 6936 – XML Validation Invalid Cast

Why This Error Happens

Error 6936 occurs when SQL Server’s XML schema validation engine attempts to cast an element’s value to a data type defined in the XML Schema Collection (XSD), but the actual data format does not match the expected type (e.g., attempting to cast an alphanumeric string to an xs:integer or xs:date type). This typically stems from a mismatch between the application’s data layer and the schema definition, often introduced after a schema collection update or a malformed data insertion attempt. In some cases, this is triggered by regional/locale settings on Windows Server 2025 that alter how numeric or date strings are formatted before being passed to the XML parser.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

• Run the following query to locate the specific XML Schema Collection associated with the column throwing the error:
• SELECT name, xml_schema_namespace FROM sys.xml_schema_collections WHERE schema_id = SCHEMA_ID('dbo');
• Cross-reference the element name mentioned in the error message (%s) with the collection’s complex type definitions to identify the expected data type (%S_MSG).

Step 2: Audit the Source Data Payload

• Extract the exact XML payload that triggered the failure using SQL Server Profiler or Extended Events (filtered for Error Number = 6936).
• Check for common type mismatches, such as:
• Empty strings ("") being passed to numeric or date fields instead of NULL.
• Boolean values formatted as "Yes/No" instead of the XSD-required "true/false" or "1/0".
• Validate the data against the XSD using a standalone tool (like xmllint or Visual Studio’s XML Schema Explorer) before re-inserting it into SQL Server.

Step 3: Update the XML Schema Collection or Application Logic

• If the data source is correct but the schema is outdated, alter the schema collection to accept a broader data type (e.g., changing xs:int to xs:string if dynamic content is required):
• ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollection ADD '<xsd:schema>...</xsd:schema>';
• If the schema is correct, update the application connection string or ORM (Entity Framework/ADO.NET) mapping to ensure it serializes dates and numbers using the InvariantCulture format to prevent locale-based casting conflicts on Windows Server 2025.

Step 4: Clear Cached Execution Plans

• Since XML validation errors can sometimes persist due to cached query plans referencing the old schema structure, clear the specific plan cache:
• DBCC FREEPROCCACHE;
• Restart the “SQL Server (MSSQLSERVER)” service via services.msc to ensure the schema cache is fully refreshed.

Alternative or Verification Steps

• Verification: Re-run the original INSERT/UPDATE transaction that failed; if successful, check the target table using SELECT * FROM YourTable FOR XML AUTO to confirm the data integrity matches the schema constraints.
• Alternative: If modifying the schema collection is not feasible due to production constraints, implement a pre-validation step in the application tier using System.Xml.Schema.XmlSchemaSet in C# to catch type mismatches before the data reaches SQL Server 2025.

Troubleshooting Guide: SQL Server Error 6937 – XML Canonical Form Validation Failure

Why This Error Happens

Error 6937 occurs when SQL Server’s XML parser attempts to validate a typed XML value against an XML Schema Collection (XSD) and finds that the value’s canonical representation does not conform to the restrictions defined in the schema. This typically results from applying pattern facets (regex-like restrictions) to non-string data types, using range restrictions on floating-point values (which can have multiple valid lexical representations for the same value, such as “1.0” vs “1.0E0”), or from a corrupted/malformed XML Schema Collection registered in the database. It can also surface after an in-place upgrade to SQL Server 2025 if legacy XSD collections used constructs that are now more strictly validated by the updated XML engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Schema Collection and Column

• Run the following query to locate XML columns bound to schema collections in the affected database:
• SELECT c.name AS ColumnName, t.name AS TableName, xsc.name AS SchemaCollectionName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id JOIN sys.xml_schema_collections xsc ON c.xml_collection_id = xsc.xml_collection_id;
• Note the exact schema collection name and the table/column throwing the error during INSERT or UPDATE operations.
• Reproduce the error on a single row in a test environment to isolate the exact value causing the canonical form mismatch.

Step 2: Extract and Inspect the XSD for Problematic Facets

• Extract the current schema definition using: SELECT xml_schema_namespace('dbo', 'YourSchemaCollectionName');
• Search the output for <xs:pattern> elements applied to xs:decimal, xs:float, or xs:double types—these are common triggers for 6937.
• Check for <xs:minInclusive> or <xs:maxInclusive> restrictions combined with enumerations on floating-point types, as SQL Server 2025 enforces stricter W3C canonical form compliance than previous versions.
• Flag any float/double fields using scientific notation inconsistently (e.g., mixing “1E2” and “100” in the same dataset).

Step 3: Correct the Schema or Normalize the Data

• If the pattern facet is unnecessary on a numeric type, remove it and rely on xs:minInclusive/xs:maxInclusive alone for range enforcement.
• If the data itself is non-canonical (e.g., “+1.50” instead of “1.5”), normalize the XML data at the application layer or via a CONVERT/CAST pass before insertion using: SELECT CAST(CAST(YourXmlColumn AS NVARCHAR(MAX)) AS XML).query('.') to force re-validation.
• Drop and recreate the XML Schema Collection with the corrected XSD: ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollectionName ADD '<xs:schema>...</xs:schema>';
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) if schema cache issues persist after the ALTER.

Step 4: Clear Cached Query Plans Referencing the Old Schema

• Run DBCC FREEPROCCACHE; to ensure no stale execution plans reference the outdated schema collection metadata.
• If using a named instance, target it specifically: DBCC FREEPROCCACHE WITH NO_INFOMSGS;
• Re-run the original INSERT/UPDATE statement that triggered Error 6937 to confirm resolution.

Alternative or Verification Steps

• Verify the fix by inserting a known-good XML sample containing edge-case floating-point values (e.g., “0.0”, “1E10”, “-0.001”) and confirming no 6937 error is raised.
• If the error persists, bypass strict typed XML validation temporarily by casting the column to untyped XML (without schema binding) to confirm the schema—not the underlying SQL Server installation—is the root cause, then rebuild the XSD from scratch using W3C-compliant canonical examples.

Troubleshooting Guide: SQL Server Error 6934 – XML Validation Element Substitution Failure

Why This Error Happens

Error 6934 occurs when SQL Server’s XML schema validation engine encounters an element that is declared as “abstract” in the XML Schema Collection (XSC) but the XML instance document fails to provide a valid substitution group member in its place. This typically results from a corrupted or incomplete XML Schema Collection registered in the database, a mismatch between the application’s XML payload and the schema version currently bound to the column, or improper namespace declarations that prevent SQL Server from resolving the correct substitution element. In SQL Server 2025 running on Windows Server 2025/Windows 11, this is frequently triggered after an in-place upgrade where cached execution plans reference outdated schema collection metadata.

Step-by-Step Resolution Guide

Step 1: Identify the Affected XML Schema Collection

• Run SELECT name, xml_collection_id FROM sys.xml_schema_collections WHERE schema_id = SCHEMA_ID('dbo'); to list registered collections.
• Cross-reference the failing column using SELECT c.name, t.name AS xml_type FROM sys.columns c JOIN sys.xml_schema_collections t ON c.xml_collection_id = t.xml_collection_id;
• Note the exact collection name tied to the table/column throwing Error 6934.

Step 2: Validate and Repair the XML Schema Collection

• Export the current schema using EXEC sp_help_xml_schema_collection 'dbo.YourSchemaCollectionName'; and inspect it for missing <xs:element> substitution group definitions.
• Drop and recreate the collection with the corrected schema: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName; CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS N'<schema XML content>';
• Ensure the substitutionGroup attribute in the XSD explicitly matches the head element’s name and type definitions.

Step 3: Clear Cached Execution Plans and Metadata

• Execute DBCC FREEPROCCACHE; to remove stale plans referencing the old schema binding.
• Run EXEC sp_refreshsqlmodule 'dbo.YourStoredProcedureName'; for any procedures that insert/validate XML against this column.
• Restart the SQL Server service via services.msc (locate “SQL Server (MSSQLSERVER)”) or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated PowerShell prompt on Windows Server 2025.

Step 4: Re-validate the Source XML Document

• Confirm the incoming XML from the application layer explicitly declares the substitution element (e.g., <SubstituteElementName xsi:type="DerivedType">) rather than relying on the abstract base element.
• Update any XML-generating application code or SSIS packages to include the correct xmlns:xsi namespace reference.

Alternative or Verification Steps

• Verify the fix by re-running the original INSERT/UPDATE statement that triggered Error 6934 and confirm it completes without validation errors.
• If the error persists, as a temporary workaround, remove the XML(CONTENT dbo.YourSchemaCollectionName) typing from the column (via ALTER TABLE ... ALTER COLUMN ... XML) to allow untyped XML while you finalize schema corrections offline.

Troubleshooting Guide: SQL Server Error 6935 – XML ID/IDREF Attribute Length Exceeded

Why This Error Happens

This error occurs when SQL Server’s XML parser validates an XML document against a schema (XSD) and encounters an attribute typed as ID or IDREF whose string value exceeds the internal maximum length limit imposed by the XML engine. This is typically triggered by malformed or auto-generated XML data (such as GUIDs or concatenated keys) being inserted into an xml-typed column that has an associated XML schema collection. It can also surface after upgrading to SQL Server 2025 on Windows Server 2025/Windows 11, where stricter XML validation parsing limits are enforced compared to legacy engine builds.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Data and Column

• Run SELECT name, xml_collection_id FROM sys.columns WHERE xml_collection_id <> 0; to locate all columns bound to an XML Schema Collection.
• Use SELECT * FROM sys.xml_schema_collections; to identify which schema is enforcing the ID/IDREF constraint.
• Export the problematic row’s XML data using SELECT CAST(YourXmlColumn AS NVARCHAR(MAX)) FROM YourTable WHERE PrimaryKey = X; to inspect the attribute values manually.

Step 2: Correct the XML Schema Collection Definition

• Open the XSD source file (often stored in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Schemas\ on Windows Server 2025).
• Locate the attribute defined as xs:ID or xs:IDREF and check if it is unnecessarily strict; consider changing it to xs:string with a custom xs:maxLength restriction if business logic allows.
• Re-register the schema in SQL Server using: ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollection ADD 'file:///C:\Schemas\updated_schema.xsd';

Step 3: Truncate or Regenerate Oversized Identifier Values

• If the ID is being auto-generated by an application (e.g., a GUID chain or hierarchical path key), modify the application logic to shorten the identifier before XML serialization.
• For keys exceeding limits (the XML ID type caps at a finite NCName length), hash long values using HASHBYTES('SHA2_256', @LongID) and convert to a shorter hex string before insertion.

Step 4: Update SQL Server 2025 Cumulative Update

• Open PowerShell as Administrator and check your current build: SELECT @@VERSION;
• Navigate to the Microsoft Update Catalog and install the latest Cumulative Update (CU) for SQL Server 2025, as early builds had a known regression in MSXML6.dll interop validation length checks.
• Restart the SQL Server service via services.msc or Restart-Service -Name 'MSSQLSERVER' in PowerShell.

Alternative or Verification Steps

• Re-run the original INSERT or UPDATE statement that triggered Error 6935; if successful, query sys.dm_os_ring_buffers or the SQL Error Log (via EXEC sp_readerrorlog 0, 1, 'XML Validation') to confirm no recurrence.
• As a workaround if schema modification isn’t feasible, strip the XML schema binding temporarily using ALTER TABLE YourTable ALTER COLUMN YourXmlColumn XML; (without DOCUMENT collection reference) to allow unvalidated inserts while you resolve the underlying data length issue.

Troubleshooting Guide: SQL Server Error 6932 – Invalid XML Schema Definition

Why This Error Happens

This error occurs when SQL Server 2025 attempts to register or process an XML schema collection (XSD) that contains an element or attribute of type ID with a default or fixed value constraint applied to it. Per the W3C XML Schema specification, attributes or elements derived from the ID type cannot have default or fixed values because this would violate uniqueness constraints across the XML document. This typically surfaces during CREATE XML SCHEMA COLLECTION operations or when applications deploy outdated .xsd files that haven’t been validated against strict schema compliance rules enforced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Component

• Locate the exact XSD file or inline schema string referenced in the CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement causing the failure.
• Check the SQL Server error log (via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for the specific element/attribute name referenced by %s in the error message.
• Run the query in SSMS with SET FMTONLY OFF; to ensure the full error context is returned rather than a truncated message.

Step 2: Remove Invalid Value Constraints from the ID Type Element

• Open the .xsd file in a text editor or XML IDE (Visual Studio 2025 recommended for schema validation support).
• Search for any <xs:attribute type="xs:ID"> or <xs:element type="xs:ID"> declarations that also include a default="..." or fixed="..." attribute.
• Remove the default or fixed attribute entirely from these ID-typed declarations, since XML Schema rules prohibit this combination.
• If the value is business-critical, change the underlying type from xs:ID to xs:string or xs:NMTOKEN if global document-wide uniqueness is not strictly required.

Step 3: Validate the Corrected Schema Before Re-Deployment

• Use an external XSD validator (such as xmllint.exe or Visual Studio’s XML Schema Explorer) to confirm the corrected file passes W3C compliance checks before importing it back into SQL Server.
• Run the command: xmllint --schema corrected_schema.xsd --noout test_sample.xml to confirm no structural violations remain.
• Re-attempt the schema registration in SSMS using CREATE XML SCHEMA COLLECTION dbo.YourCollectionName AS 'corrected_schema_content';

Step 4: Clear Cached Schema Collections and Restart the Service

• If SQL Server retains a cached reference to the broken schema collection, drop it first using DROP XML SCHEMA COLLECTION dbo.YourCollectionName;.
• Restart the SQL Server service via Services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated PowerShell window on Windows Server 2025 to clear any in-memory plan cache referencing the old schema.

Alternative or Verification Steps

• Verify the fix by re-running the original CREATE XML SCHEMA COLLECTION statement in SSMS and confirming it completes without raising Error 6932, then query sys.xml_schema_collections to confirm the new collection is registered.
• If the error persists despite editing the XSD, check for nested or imported schemas (via <xs:import> or <xs:include>) that may contain the same ID/value constraint conflict in a separate file not initially inspected.