Troubleshooting Guide: SQL Server Error 9420 — “XML Parsing: Line %d”

Why This Error Happens

Error 9420 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into an XML data type column, passed to an XML method (like .nodes(), .value(), or .query()), or processed through FOR XML/OPENXML operations. This typically results from a missing closing tag, an invalid character encoding (such as a BOM mismatch or illegal Unicode character), a broken namespace declaration, or truncated data caused by network interruption or a buffer/datatype length limit during data transfer. It can also surface in Windows Server 2025 / Windows 11 environments when application-layer serialization (e.g., .NET, PowerShell, or ETL tools) generates XML with inconsistent encoding declarations that SQL Server 2025’s stricter XML engine rejects.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the Malformed XML

• Capture the full error message — it specifies the line number (and often column) where parsing failed.
• Extract the raw XML payload using SELECT CAST(YourXmlColumn AS NVARCHAR(MAX)) or by logging the source string before insertion.
• Run the extracted string through a validator (e.g., SELECT CAST(@xmlString AS XML) in a test query) to reproduce the error in isolation.

Step 2: Validate and Repair the XML Structure

• Check for unclosed tags, mismatched case-sensitive element names, or invalid special characters (&, <, > not properly escaped as &, <, >).
• Verify the XML declaration (<?xml version="1.0" encoding="UTF-8"?>) matches the actual encoding of the byte stream — mismatches commonly trigger 9420 in SQL Server 2025.
• If the XML is generated by an external application, inspect serialization settings (e.g., .NET XmlWriterSettings.Encoding) to ensure UTF-8/UTF-16 consistency.

Step 3: Check for Data Truncation or Buffer Limits

• Confirm the source column or variable isn’t truncating the XML string — use NVARCHAR(MAX) instead of fixed-length types when staging XML before casting.
• Review any network-based data transfer (linked servers, SSIS packages, REST API calls) for timeout or packet-size issues that could cut off the XML mid-stream.
• Increase MAX TEXT REPL SIZE or adjust application connection string parameters (e.g., Packet Size in SSMS/ODBC) if large XML documents are involved.

Step 4: Update SQL Server 2025 and Validate Compatibility Mode

• Apply the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update, since early builds have had stricter XML schema validation that flags previously tolerated XML.
• Check the database compatibility level with SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'; and test against the XML parser behavior if recently upgraded from SQL Server 2019/2022.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER (PowerShell, run as Administrator) after applying updates.

Alternative or Verification Steps

• Verify the fix: Re-run the original INSERT/UPDATE/query operation that failed and confirm no 9420 error is raised; additionally, validate the XML with SELECT CAST(@FixedXml AS XML); to ensure it parses cleanly.
• Alternative workaround: If the XML source cannot be immediately corrected, store the data temporarily as NVARCHAR(MAX) instead of XML type, then sanitize/correct it via a scheduled script before casting to XML for long-term storage.

Why This Error Happens

SQL Server Error 9418 occurs when the XML parser encounters malformed or invalid XML data at a specific line number during a parsing operation (such as OPENXML, sys.xml_schema_collection, or a column insert into an XML-typed column). This typically stems from unescaped special characters (like &, <, >), a missing closing tag, or data truncation that occurred during a transfer or import process, causing the document to violate well-formedness rules. In some cases, encoding mismatches (UTF-8 vs. UTF-16) between the source file and the destination column also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML

• Note the exact line number provided in the error message (e.g., “XML parsing: line 12, unclosed tag”).
• If importing from a file, open the source .xml file in a text editor (VS Code or Notepad++) and navigate directly to that line.
• If the XML is generated by an application or stored procedure, capture the raw XML string using PRINT or SELECT before it is cast to the XML data type to inspect it in plain text.

Step 2: Validate and Correct the XML Structure

• Check for unescaped reserved characters. Replace raw ampersands & with &, less-than signs < with <, and greater-than signs > with >.
• Verify every opening tag <tag> has a corresponding closing tag </tag>; self-closing tags must end in />.
• Use an online XML validator or the xmllint --noout yourfile.xml command (available via WSL or Git Bash on Windows 11/Server 2025) to confirm the file is well-formed before re-importing.
• Confirm the file encoding matches the COLLATION expected by SQL Server; convert to UTF-8 without BOM if necessary using a tool like Notepad++’s “Encoding” menu.

Step 3: Adjust the Import Method or Code

• If using OPENROWSET(BULK...) to import the file, ensure the SINGLE_BLOB option is used correctly for binary-safe XML ingestion:

  INSERT INTO YourTable (XmlColumn)
  SELECT CAST(BulkColumn AS XML)
  FROM OPENROWSET(BULK 'C:\Data\yourfile.xml', SINGLE_BLOB) AS x;

• If the XML is being concatenated dynamically in T-SQL (e.g., via FOR XML PATH), ensure variables being inserted into the string are passed through dbo.fn_XMLEncode() or similar escaping logic before concatenation.
• Increase the MAXERRORS or truncation limits if the issue stems from a BCP or BULK INSERT job cutting off the file mid-tag.

Step 4: Rebuild XML Schema Collections (If Applicable)

• If the error relates to a typed XML column tied to a Schema Collection, drop and recreate the XML SCHEMA COLLECTION to ensure it isn’t corrupted:

  DROP XML SCHEMA COLLECTION YourSchemaName;

• Re-import the schema definition using CREATE XML SCHEMA COLLECTION with a freshly validated .xsd file.

Alternative or Verification Steps

• Verify the Fix: Run a simple SELECT CAST('<test>Data</test>' AS XML); against the specific corrected string or file to confirm SQL Server accepts it without throwing Error 9418.
• Alternative Approach: If the source file is frequently malformed (e.g., generated by a third-party legacy system), bypass strict XML typing by importing the data into an NVARCHAR(MAX) staging column first, then use TRY_CAST to filter out and quarantine corrupt rows before converting valid rows to the XML type.

Why This Error Happens

Error 9419 occurs when SQL Server’s native XML parser encounters malformed or non-well-formed XML data at a specific line within a document being processed by OPENXML, sp_xml_preparedocument, or an XML-typed column/variable. This is typically caused by invalid characters (such as unescaped ampersands or angle brackets), mismatched or unclosed tags, an incorrect or missing encoding declaration, or truncation of the XML string due to varchar/nvarchar length limits during concatenation. It can also surface when a file imported via BULK INSERT or OPENROWSET(BULK...) is corrupted or saved with an unsupported encoding (e.g., UTF-16 without BOM when UTF-8 is expected).

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Character Causing the Failure

• Run the failing query/procedure in SSMS and note the exact line number reported in the error message text.
• Export the source XML to a .xml file and open it in a strict validator (e.g., Visual Studio XML Editor or notepad++ with XML plugin) to pinpoint malformed tags.
• Check for common culprits: raw &, <, > characters that should be escaped as &, <, >.

Step 2: Verify Data Type Length and Truncation

• Confirm that any VARCHAR/NVARCHAR variable holding the XML string before casting to XML is large enough (use VARCHAR(MAX) or NVARCHAR(MAX)).
• Run SELECT DATALENGTH(@xmlVariable) to confirm the full payload was captured and not silently truncated at 4000/8000 characters.
• If data is built via FOR XML PATH, ensure the result is assigned directly to an XML-typed variable rather than a fixed-length string first.

Step 3: Check File Encoding (If Importing External XML)

• On Windows Server 2025 / Windows 11, open the source file in VS Code or Notepad++ and verify the encoding shown in the status bar (should be UTF-8 or UTF-8 with BOM, matching your BULK INSERT codepage settings).
• If using OPENROWSET(BULK 'C:\Data\import.xml', SINGLE_BLOB), re-save the file with the correct encoding and re-run the import.
• Re-generate the export from the source system if corruption is suspected, rather than manually re-encoding a damaged file.

Step 4: Rebuild and Re-test the XML Parsing Logic

• Wrap the parsing call (sp_xml_preparedocument or CAST(... AS XML)) in a TRY...CATCH block to isolate the exact statement failing.
• Temporarily replace dynamic concatenation with a static, known-good XML sample to confirm the parser logic itself works correctly.
• Apply the latest Cumulative Update for SQL Server 2025 via Setup.exe or Microsoft Update, as XML parser edge-case bugs are occasionally patched.

Alternative or Verification Steps

• Verify the fix by re-running the original query/import and confirming no Error 9419 is raised, then validate the resulting XML column with SELECT xmlCol.exist('/root') to confirm it parses correctly.
• As an alternative, use JSON instead of XML for the data interchange format if the source system supports it, bypassing the strict XML well-formedness requirements entirely.

Troubleshooting Guide: SQL Server Error 9417 – XML Parsing Error

Why This Error Happens

Error 9417 occurs when SQL Server’s XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line number during a parsing operation. This typically results from corrupted XML files, mismatched tags, invalid character encoding (such as UTF-8 BOM issues), or truncated data caused by network interruptions during transfer. It can also surface when application code passes improperly escaped special characters (like &, <, >) into an XML column or OPENXML/sp_xml_preparedocument call.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML Data

• Note the exact line number referenced in the error message (e.g., “XML parsing: line 12”).
• Locate the source file, stored procedure, or application payload generating the XML.
• Open the file in a text editor (Notepad++ or VS Code) with line numbers enabled to inspect the problematic area.

Step 2: Validate and Repair the XML Structure

• Check for unclosed tags, mismatched elements, or missing closing brackets near the reported line.
• Verify that special characters (&, <, >, ", ') are properly escaped as &, <, >, etc.
• Run the XML through an online or local XML validator (e.g., xmllint --noout file.xml in PowerShell) to pinpoint syntax errors.
• Confirm the file encoding is UTF-8 without a BOM, since SQL Server can misinterpret BOM markers as invalid characters.

Step 3: Correct the T-SQL Query or Application Code

• If using OPENXML, ensure sp_xml_preparedocument is called correctly and the handle is released with sp_xml_removedocument after use.
• Review any string concatenation in application code (C#, PowerShell, etc.) that builds XML manually, as this is a common source of malformed tags.
• Update the code to use proper XML serialization libraries (like System.Xml.Linq in .NET) instead of manual string building.

Step 4: Check SQL Server and Windows Server Compatibility Settings

• On Windows Server 2025 / Windows 11, confirm the SQL Server 2025 instance has the latest Cumulative Update installed via SQL Server Setup.
• Verify the database compatibility level supports the XML schema version in use by running:

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

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after applying updates.

Alternative or Verification Steps

• Re-run the original query or import job after correcting the XML; confirm no Error 9417 appears in SQL Server Logs (EXEC xp_readerrorlog) or Windows Event Viewer.
• If the error persists, isolate the issue by testing with a minimal, known-valid XML sample to confirm whether the problem lies in the data itself or the SQL Server configuration.

Troubleshooting Guide: SQL Server Error 9415 – “XML Parsing: Line %d”

Why This Error Happens

Error 9415 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into an xml typed column or passed to an XML processing function. This is typically caused by mismatched tags, invalid characters, truncated data from an application/ETL process, or a character encoding mismatch (such as UTF-8 data being interpreted as UTF-16). In some cases, corrupted data pages or a failed transfer (network interruption during BULK INSERT or linked server query) can also introduce malformed XML fragments.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Data

• Locate the exact line number referenced in the error message (line %d) to narrow down the source statement or stored procedure.
• Run the failing query in SQL Server Management Studio (SSMS) in isolation to capture the exact XML payload being parsed.
• Use SELECT TRY_CAST(@xmlVariable AS XML) to test if the data is valid without throwing a hard error.

Step 2: Validate and Repair the XML Structure

• Check for unclosed tags, mismatched namespaces, or invalid special characters (e.g., unescaped &, <, >) in the source data.
• If the data originates from an external file, open it in a text editor (like VS Code on Windows 11) and validate it against an XML schema (XSD) if one exists.
• Ensure the XML declaration encoding (e.g., <?xml version="1.0" encoding="UTF-8"?>) matches the actual byte encoding of the file being imported.

Step 3: Correct the Import/Processing Method

• If using BULK INSERT or OPENROWSET(BULK...) on Windows Server 2025, verify the file path permissions and ensure the SQL Server service account has read access to the directory.
• For application-side issues, update the client code (e.g., C#, Python) to properly serialize the XML object before sending it to the stored procedure, ensuring no truncation occurs due to buffer size limits.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER if the parser appears to be stuck in a corrupted connection state.

Step 4: Check for Database Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page corruption that might be scrambling the XML data at rest.
• Review the SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) for any concurrent I/O warnings that align with the timestamp of the error.

Alternative or Verification Steps

• Verification: Re-run the original INSERT/UPDATE statement with the corrected XML payload; confirm the query returns a success status (Rows affected: 1) without the 9415 exception.
• Alternative: If the data source cannot be fixed immediately, wrap the ingestion logic in a TRY...CATCH block combined with TRY_CONVERT to shunt malformed records into a separate “quarantine” logging table instead of halting the entire batch process.

Troubleshooting Guide: SQL Server Error 9416 — “XML Parsing: Line %d”

Why This Error Happens

Error 9416 occurs when SQL Server’s native XML parser encounters malformed, incomplete, or improperly encoded XML data while attempting to parse a value into the xml data type. This is typically caused by invalid characters, mismatched or unclosed tags, incorrect namespace declarations, or truncated XML content being passed from an application, import file, or OPENXML/FOR XML operation. In some cases, this also surfaces due to encoding mismatches (e.g., UTF-8 vs UTF-16) between the source data and SQL Server’s expected XML schema format.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the Malformed XML

• Note the specific line number (%d) referenced in the error message.
• Locate the source query, stored procedure, SSIS package, or application insert statement generating the XML data.
• Run the offending XML string through a validator (e.g., SELECT CAST(@xmlVar AS XML) in SSMS) to isolate the exact failure point.

Step 2: Validate and Correct the XML Structure

• Check for unclosed or mismatched tags (e.g., <Item> without </Item>).
• Verify all special characters (&, <, >, ", ') are properly escaped using entity references (&, <, etc.).
• Confirm the XML declaration line (if present) matches actual encoding, e.g.:

<?xml version="1.0" encoding="UTF-8"?>

• If using FOR XML PATH or AUTO, test the query output independently before inserting into an xml-typed column.

Step 3: Check Encoding and Data Truncation Issues

• Ensure the source file or connection string is not truncating data mid-tag (common with NVARCHAR(MAX) vs VARCHAR mismatches).
• In SSIS or application code, confirm the XML payload is read using the correct encoding (UTF-8 recommended) via:

Get-Content -Path "C:\Data\input.xml" -Encoding UTF8

• If importing via BULK INSERT or OPENROWSET(BULK...), verify the file path and SINGLE_CLOB option are correctly set on Windows Server 2025:

SELECT CAST(BulkColumn AS XML)
FROM OPENROWSET(BULK 'C:\ImportData\sample.xml', SINGLE_CLOB) AS x;

Step 4: Update SQL Server 2025 Cumulative Updates

• Confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, as early XML parser builds had known issues with namespace resolution.
• Check current build via:

SELECT @@VERSION;

• Download and apply the latest CU from the Microsoft Update Catalog if outdated.

Alternative or Verification Steps

• After correcting the XML, re-run the original query or import job and confirm no Error 9416 is thrown; validate successful insertion with SELECT * FROM YourTable WHERE XMLColumn IS NOT NULL.
• As an alternative, use an external XML schema validation tool (like xmllint or Visual Studio’s XML Editor) to pre-validate payloads before they reach SQL Server, preventing malformed XML from ever reaching the parser.

Troubleshooting Guide: SQL Server Error 9414 – “XML Parsing: Line %d”

Why This Error Happens

Error 9414 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into an xml data type column or processed via FOR XML/OPENXML functions. This is commonly caused by corrupted data during transmission, mismatched character encodings (such as UTF-8 vs. UTF-16 conflicts), or truncated XML documents resulting from buffer size limitations. In some cases, this error also surfaces due to invalid namespace declarations or unescaped special characters (like &, <, >) within the XML payload.

Step-by-Step Resolution Guide

Step 1: Validate the Source XML Data

• Extract the exact XML string causing the failure using the line number referenced in the error message.
• Run the XML through an external validator (e.g., Visual Studio XML Editor or xmllint) to confirm well-formed structure.
• Check for unescaped special characters and ensure all opening tags have corresponding closing tags.

Step 2: Verify Character Encoding Consistency

• Confirm the client application and SQL Server collation settings use matching encodings (UTF-8 is default in SQL Server 2025 for xml types).
• Check the connection string for explicit encoding declarations; mismatches often cause silent truncation.
• Run SELECT SERVERPROPERTY('Collation'); to confirm the instance-level collation aligns with your application’s expected charset.

Step 3: Inspect Buffer and Packet Size Limits

• Open SQL Server Configuration Manager on Windows Server 2025 and navigate to SQL Server Network Configuration > Protocols.
• Increase the Network Packet Size property if large XML payloads are being truncated mid-transfer (default is 4096 bytes).
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell for changes to take effect.

Step 4: Rebuild or Re-Import the Corrupted Dataset

• If the XML originates from a file or external feed, re-export the source data to rule out disk-level corruption.
• Use DBCC CHECKDB on the target database to confirm no underlying page corruption is contributing to parsing failures.
• Re-run the insert/import operation using a known-good XML sample to isolate whether the issue is data-specific or environment-specific.

Alternative or Verification Steps

• Run SELECT CAST(your_xml_column AS NVARCHAR(MAX)) on a known-good row to confirm the parser now processes XML without throwing Error 9414.
• If the issue persists, bypass native XML validation temporarily by casting the data type to NVARCHAR(MAX) first, then validate structure in application code before re-casting to XML.

Troubleshooting Guide: SQL Server Error 9412 – “XML Parsing: Line %d”

Why This Error Happens

Error 9412 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data during an insert, update, or OPENXML/FOR XML operation. This is typically caused by invalid characters, mismatched tags, truncated XML strings due to variable length limits, or encoding mismatches (such as UTF-8 data being interpreted as UTF-16). In SQL Server 2025 running on Windows Server 2025 or Windows 11, this can also surface after collation or locale updates that alter how special characters are validated during parsing.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML

• Note the specific line number (%d) referenced in the error message.
• Run the failing query in SSMS with SET SHOWPLAN_XML OFF and capture the raw XML input using PRINT or SELECT @xmlVariable.
• Use an external validator (like xmllint or Visual Studio’s XML Editor) to confirm whether the tag structure is well-formed.

Step 2: Check Data Type and Truncation Issues

• Confirm the variable or column storing the XML is of type XML or a sufficiently large NVARCHAR(MAX)—not a truncated VARCHAR(255) or similar.
• Run the following to inspect variable declarations:

  DECLARE @x XML;
  SET @x = '<Root><Child>Value</Child></Root>';

• Verify that no intermediate application layer (such as a .NET service or PowerShell script on Windows Server 2025) is truncating the string before insertion.

Step 3: Correct Encoding and Special Character Issues

• Check for unescaped characters like &, <, or > within XML content; replace them with &, <, >.
• If importing from a file, confirm the file encoding matches SQL Server’s expected encoding (UTF-8 vs UTF-16) using:

  Get-Content -Path "C:\Data\input.xml" -Encoding UTF8

• Re-import or re-generate the XML file using proper encoding declarations (<?xml version="1.0" encoding="UTF-8"?>).

Step 4: Rebuild or Re-Cast the XML Column

• If corruption is suspected at the storage level, extract the data into a staging table as NVARCHAR(MAX), clean it, then cast back to XML:

  SELECT CAST(CleanedColumn AS XML) FROM StagingTable;

• This isolates whether the corruption originated in application logic or storage.

Alternative or Verification Steps

• Verify the fix by re-running the original query or stored procedure that triggered Error 9412; confirm no exceptions are thrown and SELECT returns valid XML output.
• As an alternative, use TRY_CAST or TRY_CONVERT in a validation script to catch malformed XML gracefully before it reaches production tables, preventing future line-specific parsing failures.

SQL Server Error 9413: XML Parsing Error Troubleshooting Guide

Why This Error Happens

Error 9413 occurs when SQL Server’s native XML parser encounters malformed, incomplete, or non-well-formed XML data at a specific line number within a column of type xml or an XML variable. This typically happens due to truncated data during insertion, improperly escaped special characters (such as unescaped ampersands or angle brackets), encoding mismatches (e.g., UTF-8 vs. UTF-16 BOM issues), or corruption introduced during ETL/data transfer processes. In Microsoft SQL Server 2025 running on Windows Server 2025 or Windows 11, this is almost always a data integrity issue rather than a server configuration fault.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the Malformed XML

• Note the specific line number (%d) returned in the error message — this points directly to the broken element in the XML string.
• Run a diagnostic query to extract the raw XML value causing the failure:

  SELECT TRY_CONVERT(xml, YourXmlColumn) AS ParsedXML, YourXmlColumn
  FROM YourTable
  WHERE TRY_CONVERT(xml, YourXmlColumn) IS NULL AND YourXmlColumn IS NOT NULL;

• Open the returned raw string in a text editor (VS Code or Notepad++) and jump to the reported line number to visually inspect for broken tags.

Step 2: Fix Encoding and Escaping Issues

• Check for unescaped reserved characters (&, <, >) that are not wrapped in CDATA tags or properly encoded as &, <, >.
• Verify the file or source feed is UTF-8 without BOM or UTF-16, matching what SQL Server expects; mismatched BOM headers often break the parser on line 1.
• If importing via BULK INSERT or OPENROWSET, explicitly declare the codepage:

  BULK INSERT YourTable FROM 'C:\Data\file.xml' WITH (CODEPAGE = '65001', DATAFILETYPE = 'char');

Step 3: Validate Schema Compliance (If Using XML Schema Collections)

• If the column is bound to an XML SCHEMA COLLECTION, confirm the incoming XML matches the expected namespace and element structure exactly.
• Temporarily cast to untyped XML to isolate whether the error is a parsing issue or a schema-validation issue:

  DECLARE @x xml = CAST(@RawString AS xml);

• Update or drop/recreate the schema collection on C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ reference definitions if the XSD itself is outdated.

Step 4: Repair or Regenerate the Source File

• If the XML originates from an external application or Windows Server 2025 scheduled export job, regenerate the file to rule out disk-level truncation or network transfer corruption.
• Check the Windows Event Viewer (Windows Logs > Application) for disk I/O or antivirus interference during file write operations that may have truncated the XML mid-stream.

Alternative or Verification Steps

• Verify the fix: Re-run the original INSERT/UPDATE statement or SELECT ... FOR XML query that triggered Error 9413 — if it completes without exception and TRY_CONVERT returns a non-null result, the XML is now well-formed.
• Alternative workaround: If the source system cannot be immediately fixed, store the data temporarily as NVARCHAR(MAX) instead of XML type, and use TRY_CONVERT with error logging to quarantine bad rows without halting the entire batch load.

Troubleshooting Guide: SQL Server Error 9411 – XML Parsing Error

Why This Error Happens

Error 9411 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line during an insert, update, or query operation involving the xml data type. This is typically caused by missing closing tags, invalid characters (such as unescaped ampersands or angle brackets), mismatched namespaces, or encoding mismatches (e.g., UTF-8 BOM issues) within the source XML document. It can also surface when application-layer code truncates XML data before it reaches SQL Server, resulting in a structurally broken document.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML Data

• Note the exact line number reported in the error message (e.g., “XML parsing: line 12”).
• Run the failing query in SQL Server Management Studio (SSMS) with SET SHOWPLAN_XML OFF to isolate the statement.
• Export the raw XML payload using SELECT CAST(YourColumn AS NVARCHAR(MAX)) to inspect it in a text editor like Notepad++ or VS Code.

Step 2: Validate and Repair the XML Structure

• Use an XML validator (built into VS Code or xmllint on Windows via WSL) to check for unclosed tags, invalid entities, or encoding declarations.
• Check for raw special characters (&, <, >) that must be escaped as &, <, >.
• If the XML originates from an external file, verify the encoding matches the declared <?xml version="1.0" encoding="UTF-8"?> header — remove any stray UTF-8 BOM bytes using PowerShell:

  Get-Content -Path "C:\Data\input.xml" -Encoding UTF8 | Set-Content -Path "C:\Data\fixed.xml" -Encoding UTF8

Step 3: Correct Application or Import Logic

• If data arrives via SSIS, BCP, or an ETL pipeline, inspect the source mapping for truncation at the column length limit (XML columns default to unlimited, but staging tables may truncate at NVARCHAR(4000)).
• Update the application code or stored procedure to validate XML with TRY_CAST or TRY_PARSE before insertion:

  IF (SELECT TRY_CAST(@XmlData AS XML)) IS NULL
      RAISERROR('Invalid XML detected before insert', 16, 1);

• Redeploy the corrected package or script on Windows Server 2025, ensuring the SQL Server service account has read access to any UNC file paths involved.

Step 4: Restart the Affected Service or Session

• If the issue persists due to cached execution plans, clear the plan cache for the affected database:

  DBCC FREEPROCCACHE;

• Restart the SQL Server (MSSQLSERVER) service via services.msc or Restart-Service -Name "MSSQLSERVER" in PowerShell (Run as Administrator).

Alternative or Verification Steps

• Re-run the original INSERT/UPDATE statement with the corrected XML payload and confirm no Error 9411 appears in SQL Server Error Log or Event Viewer > Applications and Services Logs > MSSQLSERVER.
• As an alternative, temporarily store the data in an NVARCHAR(MAX) staging column and use TRY_CAST(... AS XML) to catch bad rows programmatically before committing them to the final xml-typed column.

Troubleshooting Guide: SQL Server Error 9410 – “XML Parsing: Line %d”

Why This Error Happens

Error 9410 occurs when SQL Server’s XML parser encounters malformed, incomplete, or improperly encoded XML data that doesn’t conform to well-formed XML standards. This is typically caused by truncated XML strings, mismatched tags, invalid character encoding (such as non-UTF-8 characters in a UTF-8 expected stream), or corruption introduced during data transmission or file storage. It can also occur when application code constructs XML dynamically and fails to escape special characters like &, <, or > properly.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML Error

• Note the specific line number referenced in the error message (line %d).
• Run the failing query in SSMS (SQL Server Management Studio) with SET SHOWPLAN_XML disabled to isolate the exact XML column or variable causing the issue.
• Use SELECT xml_column, DATALENGTH(xml_column) FROM your_table WHERE id = X to inspect the raw XML content length and structure.

Step 2: Validate and Repair the XML Structure

• Extract the problematic XML data and validate it using an external XML validator or Visual Studio’s XML Editor to check for unclosed tags, invalid nesting, or illegal characters.
• Check for improperly escaped special characters (&, <, >) in dynamically generated XML strings from application code (C#, PowerShell, or T-SQL concatenation).
• If the XML is stored in a file (e.g., accessed via OPENROWSET(BULK...)), open the file in a text editor like Notepad++ on Windows Server 2025 and verify the encoding is UTF-8 without a malformed BOM (Byte Order Mark).

Step 3: Correct Encoding and Import Settings

• In SQL Server Import/Export Wizard or SSIS packages, confirm the Flat File Source or XML Source component is set to match the source file’s actual encoding (UTF-8 vs. UTF-16).
• For OPENROWSET(BULK 'C:\Data\file.xml', SINGLE_CLOB), ensure SINGLE_CLOB is used for ANSI/UTF-8 files and SINGLE_NCLOB is used for Unicode files to prevent parser misinterpretation.
• Rebuild the XML payload programmatically using FOR XML PATH or FOR XML AUTO in T-SQL rather than manual string concatenation to guarantee well-formed output.

Step 4: Apply Latest Cumulative Update for SQL Server 2025

• Navigate to the Microsoft Update Catalog and confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as XML parser edge-case bugs are occasionally patched.
• Check your current build via SELECT @@VERSION; and compare against the latest build number listed on the official SQL Server release notes page.

Alternative or Verification Steps

• Verify the Fix: Re-run the original query or import job; use SELECT CAST(xml_column AS XML) on the corrected data to confirm it casts successfully without throwing Error 9410.
• Alternative Approach: If the source system cannot be fixed immediately, wrap the ingestion logic in a TRY_CAST or TRY_CONVERT function to gracefully handle malformed rows and log them to an error table instead of halting the entire batch process.

Troubleshooting Guide: SQL Server Error 9402 — XML Parsing Failure

Why This Error Happens

Error 9402 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line number within an XML data type column or variable. This is typically caused by corrupted XML content inserted from an external source, mismatched or unclosed tags, invalid character encoding (such as improper UTF-8/UTF-16 handling), or truncated data resulting from a failed transaction or network interruption during data transfer. In some cases, this also surfaces when applications running on Windows Server 2025 or Windows 11 pass XML strings that exceed expected schema constraints or contain illegal special characters.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the Malformed XML

• Run the failing query with SET SHOWPLAN_XML disabled and capture the full error message, noting the exact line number referenced in the error.
• Use SELECT CAST(YourXMLColumn AS NVARCHAR(MAX)) on the suspect row to visually inspect the raw XML content.
• Check the originating application log (Event Viewer > Applications and Services Logs) on Windows Server 2025 for any upstream serialization errors.

Step 2: Validate and Repair the XML Structure

• Open the extracted XML string in a validator (Visual Studio Code with XML extension, or xmllint via Windows Subsystem for Linux) to pinpoint unclosed tags or invalid nesting.
• Check for unescaped special characters such as &, <, > that need to be converted to &, <, >.
• Re-encode the file using UTF-8 without BOM if encoding mismatches are suspected, using PowerShell: Get-Content file.xml -Raw | Out-File -Encoding utf8 file_fixed.xml.

Step 3: Correct the Data Insertion Process

• Review the application or ETL job (SSIS package, API call, or stored procedure) responsible for writing XML into SQL Server 2025.
• Ensure the connection string and client library explicitly define character encoding to prevent truncation during transmission.
• Add server-side validation using XML_SCHEMA_COLLECTION constraints to reject malformed XML before it reaches the table.

Step 4: Clean and Re-Insert the Corrected Data

• Delete or update the corrupted row using the primary key identified in Step 1.
• Re-insert the validated, well-formed XML using a parameterized query to avoid manual string concatenation errors.
• Restart the SQL Server service via Services.msc or Restart-Service MSSQLSERVER if the issue persists due to a cached execution plan.

Alternative or Verification Steps

• Verify the fix by re-running the original query or stored procedure that triggered Error 9402; confirm it executes without exceptions and returns the expected XML structure using SELECT ... FOR XML AUTO.
• If the error recurs intermittently, enable XML schema validation at the application layer (before data reaches SQL Server) and review SQL Server Profiler traces for any truncated payloads during peak network load.

Troubleshooting Guide: SQL Server Error 9403 – XML Parsing Failure

Why This Error Happens

Error 9403 occurs when SQL Server’s native XML parser encounters malformed, incomplete, or improperly encoded XML data during a cast, insert, or query operation against an xml data type column. This is typically caused by truncated data streams, mismatched tags, invalid character encoding (such as non-UTF-8 byte sequences), or XML documents that exceed the nested level limit. In SQL Server 2025 running on Windows Server 2025 or Windows 11, this can also surface after a database migration or ETL job where source data was not validated against a well-formed XML schema before insertion.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the Malformed XML

• Review the full error message, which includes the specific line number (e.g., “XML parsing: line 12, unclosed tag”).
• Run the failing query in SQL Server Management Studio (SSMS) with SET SHOWPLAN_XML OFF to isolate the exact statement.
• Export the offending XML payload to a .xml file and open it in a validator (e.g., Visual Studio or xmllint) to pinpoint the syntax break.

Step 2: Validate and Sanitize the XML Source Data

• Check for unescaped special characters (&, <, >) that need to be converted to entities (&, <, >).
• Confirm the encoding declaration matches the actual file encoding:

  SELECT CAST(column_name AS xml) FROM table_name WHERE id = 1;

• If data is coming from an external ETL process or API, verify the source system is not truncating the payload mid-stream due to timeout or buffer limits.

Step 3: Adjust SQL Server Configuration and Retry the Operation

• Ensure the XML data does not exceed SQL Server’s default nested node limit (128 levels); restructure deeply nested XML if necessary.
• If using OPENROWSET(BULK...) or OPENXML, confirm the file path is correct and accessible under the SQL Server service account permissions on Windows Server 2025:

  C:\SQLData\ImportFiles\

• Re-run the import or query after correcting the XML structure:

  DECLARE @x xml = '<Root><Item>Valid</Item></Root>';
  SELECT @x;

Step 4: Check for Driver or Collation Mismatches

• Verify the client driver (ODBC, OLE DB, or ADO.NET) version is compatible with SQL Server 2025 — outdated drivers on Windows 11 clients can mangle XML payloads in transit.
• Confirm database collation supports the character set used in the XML (especially for non-Latin characters).

Alternative or Verification Steps

• Verify the fix by re-executing the original CAST/CONVERT or INSERT statement and confirming no error is returned; follow up with SELECT *.query('/') to confirm the XML parses and renders correctly.
• If the error persists intermittently, use SQL Server Profiler or Extended Events to capture the raw XML payload at the moment of failure, as the issue may be an upstream application generating invalid XML under specific conditions (e.g., null concatenation or encoding mismatch).

Troubleshooting Guide: SQL Server Error 9401 — “XML Parsing: Line %d”

Why This Error Happens

Error 9401 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data being inserted into an XML-typed column or processed via functions like OPENXML, sys.xml, or TRY_CONVERT. This is typically caused by invalid tags, mismatched namespaces, unescaped special characters, or truncated XML content delivered by an upstream application. In some cases on Windows Server 2025 / Windows 11, corrupted MSXML libraries or a mismatched collation/encoding setting can also cause valid XML to be misread.

Step-by-Step Resolution Guide

Step 1: Identify and Validate the Offending XML Document

• Locate the exact line/column referenced in the error message (line %d) within the source XML payload.
• Extract the XML string using SELECT CAST(YourXmlColumn AS NVARCHAR(MAX)) to inspect it manually.
• Run the extracted XML through a validator (e.g., Visual Studio XML Editor, xmllint, or an online validator) to pinpoint syntax errors like unclosed tags or invalid characters.

Step 2: Check Encoding and Escaping Issues

• Confirm the XML declaration matches the actual encoding (e.g., <?xml version="1.0" encoding="UTF-8"?> vs. actual UTF-16 BOM).
• Ensure special characters (&, <, >) in text nodes are properly escaped as &, <, >.
• If XML is generated by an external application, review the client-side serialization logic (e.g., .NET XmlWriter settings) for incorrect encoding configuration.

Step 3: Repair or Sanitize the Source Data

• If the XML is stored in a staging table, use REPLACE() or TRANSLATE() functions in T-SQL to strip invalid characters before casting to the XML data type.
• For recurring malformed payloads, implement a pre-validation step in the application layer using XmlDocument.LoadXml() (C#) or lxml.etree.fromstring() (Python) to catch errors before they reach SQL Server.
• If using OPENROWSET(BULK...) to import an XML file, verify the file path and confirm the file is not truncated: SELECT * FROM OPENROWSET(BULK 'C:\Data\file.xml', SINGLE_BLOB) AS x;

Step 4: Update SQL Server and MSXML Components

• On Windows Server 2025, check for the latest Cumulative Update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update Catalog, as XML parser bug fixes are periodically shipped in CUs.
• Verify the MSXML6.dll version is current by checking C:\Windows\System32\msxml6.dll properties (Windows 11 and Server 2025 ship MSXML6 by default; reinstalling via sfc /scannow can repair corruption).

Alternative or Verification Steps

• Re-run the original INSERT/CAST statement that triggered Error 9401 after sanitizing the XML; a successful execution with no error confirms the fix.
• As an alternative, use TRY_CONVERT(XML, @YourData) instead of CAST/CONVERT to gracefully return NULL on malformed XML, allowing you to log and isolate bad records without halting the entire batch operation.

Why This Error Happens

This error occurs when a Transact-SQL query uses sql:column() or sql:variable() XQuery functions to map a SQL Server value into an XML construct, but the underlying column or variable is defined using a data type that cannot be represented in the XML type system. Unsupported types typically include sql_variant, CLR user-defined types, xml, text, ntext, image, or large object types used incorrectly within the FOR XML/XQuery context. SQL Server raises Error 9344 because it cannot safely serialize these types into the XML node being constructed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column or Variable

• Review the full error message text (the %s placeholders) to see exactly which SQL type and which column/variable triggered the failure.
• Run the query in SQL Server Management Studio (SSMS) on Windows 11 or Windows Server 2025 and check the “Messages” tab for the specific object name.
• Use sp_help 'YourTableName' or sys.columns / sys.types to confirm the data type of the flagged column.

Step 2: Cast or Convert to a Supported Data Type

• Modify the query to explicitly CAST or CONVERT the unsupported type into a supported one (e.g., VARCHAR, NVARCHAR, INT, DATETIME2) before passing it to sql:column() or sql:variable().
• Example fix:

value('sql:column("CAST(MyVariantColumn AS NVARCHAR(100))")', 'nvarchar(100)')

• For variables, declare a supporting variable with a compatible type:

DECLARE @StrVal NVARCHAR(100) = CAST(@OriginalVariant AS NVARCHAR(100));

Step 3: Update the XML Schema or Query Logic

• If the column is a CLR user-defined type (UDT), extract the scalar property you need using the UDT’s methods, then cast the result to a standard SQL type.
• Avoid passing xml, text, ntext, or image types directly into sql:column()/sql:variable(); convert large object types to varchar(max) or nvarchar(max) first.
• Re-run the query against a test database on your SQL Server 2025 instance to confirm the FOR XML/XQuery statement compiles without Error 9344.

Step 4: Apply Query Changes to Stored Procedures or Views

• If the problematic code resides in a stored procedure, function, or view, update and redeploy the object definition using ALTER PROCEDURE / ALTER VIEW.
• Clear the plan cache for that object with EXEC sp_recompile 'YourObjectName'; to ensure SQL Server uses the corrected logic on next execution.

Alternative or Verification Steps

• Execute the corrected query in SSMS or Azure Data Studio and confirm the XML output is generated without raising Error 9344.
• If the error persists, check for nested function calls that indirectly return unsupported types (e.g., a scalar function returning sql_variant) and apply the same CAST/CONVERT fix at that layer.

Troubleshooting Guide: SQL Server Error 9400 (XML Parsing Failure)

Why This Error Happens

Error 9400 occurs when SQL Server’s internal XML parser encounters malformed, incomplete, or improperly encoded XML data at a specific line number during a query or import operation. This is typically caused by invalid characters (such as unescaped ampersands or angle brackets), mismatched tags, incorrect encoding declarations (UTF-8 vs UTF-16 mismatches), or truncated XML data resulting from a failed network transfer or corrupted source file. In some cases, this also surfaces when application code passes NULL or non-XML data into a column/variable explicitly typed as XML.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Line and Source of the XML

• Note the exact line number reported in the error message (e.g., “XML parsing: line 12”).
• Run the query in SQL Server Management Studio (SSMS) with SET SHOWPLAN_XML OFF and capture the raw XML input being passed to the XML data type.
• If the data is coming from an external file, open it in a text editor (like VS Code) and navigate directly to the reported line to inspect for broken tags or invalid characters.

Step 2: Validate and Correct the XML Encoding

• Check the file’s Byte Order Mark (BOM) and encoding; SQL Server expects UTF-8 or UTF-16 as declared in the XML prolog (e.g., <?xml version="1.0" encoding="UTF-8"?>).
• If encoding mismatches are found, re-save the source file using the correct encoding via PowerShell:
  Get-Content -Path "C:\Data\source.xml" -Encoding UTF8 | Set-Content -Path "C:\Data\source_fixed.xml" -Encoding UTF8
• Replace any unescaped special characters (&, <, >) in the source data with their corresponding XML entities (&, <, >).

Step 3: Test the XML Independently Before Insertion

• In SSMS, isolate the problematic XML string and attempt to cast it directly: SELECT CAST('<root>Your XML Here</root>' AS XML);
• If the cast fails, use TRY_CAST or TRY_CONVERT in your T-SQL script to gracefully handle malformed rows instead of halting the entire batch:
  SELECT TRY_CAST(XMLColumn AS XML) FROM YourTable WHERE ID = 123;

Step 4: Check for Application-Layer Corruption

• If the XML is generated by an application (e.g., a .NET service) running on Windows Server 2025, check the Event Viewer under Windows Logs > Application for any truncation errors occurring before the data was sent to SQL Server.
• Ensure the application’s connection string is not truncating large object (LOB) data due to a mismatched MAX size definition on the XML column.

Alternative or Verification Steps

• Verification: Re-run the original query or stored procedure that threw Error 9400; if successful, immediately check the row count to ensure no data was silently dropped during the TRY_CAST conversion.
• Alternative: If the error persists only in specific environments, run DBCC CHECKDB on the target database to rule out underlying page-level corruption affecting the XML data type columns.

Troubleshooting Guide: SQL Server Error 9343

Why This Error Happens

This error occurs when an XQuery expression uses sql:column() or sql:variable() to inject a typed XML value into a context that expects a different (or no) XML schema collection. SQL Server enforces strict type matching between the XML instance being queried and the value referenced by these functions, since mismatched schema collections could lead to invalid or ambiguous type inference during query execution. This typically surfaces when a column or variable is bound to an XML schema collection (via CREATE XML SCHEMA COLLECTION) that differs from—or is absent on—the XML type of the column the .query(), .value(), or .nodes() method is being called against.

Step-by-Step Resolution Guide

Step 1: Identify the Mismatched XML Schema Collections

• Run sp_help 'YourTableName' or query sys.xml_schema_collections to list schema collections in use.
• Check the XML column definition using: SELECT c.name, t.name AS xml_type, xsc.name AS schema_collection FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.xml_schema_collections xsc ON t.xml_collection_id = xsc.xml_collection_id WHERE c.object_id = OBJECT_ID('YourTableName');
• Compare this against the schema collection bound to the variable or column referenced inside sql:column()/sql:variable().

Step 2: Align the Typed XML Declarations

• Ensure the XML variable declared with DECLARE @xmlVar XML (YourSchemaCollection) uses the exact same schema collection as the context XML column.
• If the two columns legitimately come from different schemas, cast one to untyped XML explicitly: CAST(@xmlVar AS XML) before referencing it inside sql:variable().
• Update the query so both the context instance and the referenced value share a single schema collection reference.

Step 3: Rebuild or Re-register the XML Schema Collection

• If the schema collection appears correct but the error persists, it may be corrupted or outdated after an in-place upgrade to SQL Server 2025.
• Drop and recreate the collection: DROP XML SCHEMA COLLECTION dbo.YourSchemaCollection; CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollection AS N'<schema XML definition>';
• Re-bind affected columns/variables to the refreshed schema collection object.

Step 4: Patch and Restart SQL Server Services

• On Windows Server 2025 / Windows 11, confirm you are on the latest Cumulative Update for SQL Server 2025 via SELECT @@VERSION;.
• Apply any pending CU from the Microsoft Update Catalog, as early 2025 builds had known XQuery type-resolution regressions.
• Restart the service: Open Services.msc > locate SQL Server (MSSQLSERVER) > Restart.

Alternative or Verification Steps

• Re-run the original .query()/.value() statement in SSMS; a successful result set with no Error 9343 confirms the fix.
• As a workaround, strip typing entirely by casting both the context XML and the referenced XML to untyped XML data type before performing the XQuery operation—this bypasses schema collection matching at the cost of compile-time validation.

Troubleshooting Guide: SQL Server Error 9341 – “Syntax error near ‘%ls'”

Why This Error Happens

Error 9341 occurs when SQL Server’s XML or XQuery parser encounters malformed syntax while processing an XML data type method, XQuery expression, or FLWOR statement. This typically stems from improperly escaped special characters, mismatched namespace prefixes, or incompatible query constructs introduced in newer T-SQL language extensions shipped with SQL Server 2025. It can also surface when legacy XML queries written for older SQL Server versions are run against the updated XQuery engine without adjustment for deprecated syntax.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Query Statement

• Review the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to identify the exact statement and line number referenced in the error.
• Run the query independently in SQL Server Management Studio (SSMS) to reproduce the error in isolation.
• Use SET SHOWPLAN_XML ON; temporarily to confirm whether the issue originates from the query itself or from an execution plan XML artifact.

Step 2: Validate XML/XQuery Syntax Manually

• Check for unescaped special characters (<, >, &) inside XML literals; replace them with proper entity references (<, >, &).
• Confirm namespace prefixes declared with WITH XMLNAMESPACES match exactly what is referenced in .value(), .query(), or .nodes() method calls.
• Verify FLWOR expressions (for, let, where, order by, return) follow the updated XQuery grammar supported in SQL Server 2025, as some legacy shorthand syntax has been deprecated.

Step 3: Update Compatibility Level and Rebuild the Query

• Check the database compatibility level with:

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

• If the database is running on an older compatibility level (e.g., 140 or 150), update it to 170 to align with SQL Server 2025 parser rules:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Rewrite the flagged query segment using current XQuery syntax documentation and re-test execution.

Step 4: Apply Latest Cumulative Update

• Open Windows Update or use SQL Server Setup to confirm the instance is patched to the latest Cumulative Update (CU) for SQL Server 2025, as parser bugs are frequently addressed in CUs.
• Download updates from the Microsoft Update Catalog if Windows Server 2025 is not configured for automatic SQL patching.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after installation.

Alternative or Verification Steps

• Re-run the corrected query in SSMS and confirm it executes without raising Error 9341; check sys.dm_exec_query_stats to verify successful plan caching.
• If the error persists after syntax correction, use DBCC FREEPROCCACHE to clear any corrupted cached execution plans that may be referencing the old faulty syntax.

Troubleshooting Guide: SQL Server Error 9342 – XML Instance Insert Restriction

Why This Error Happens

This error occurs when a Transact-SQL statement attempts to insert XML data into a column using OPENXML, a derived table, or an intermediate variable that isn’t directly mapped via sql:column or sql:variable annotations. SQL Server 2025’s XML schema validation engine requires that when XML data is being shredded (decomposed) and inserted directly into a relational table, the source must be explicitly and directly traceable to a single XML instance—any intermediate processing, type casting, or multi-step transformation breaks this direct lineage. This is commonly triggered by poorly structured OPENXML queries, nested subqueries wrapping XML data, or ORM-generated queries (like Entity Framework) that abstract the XML source away from the INSERT statement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Enable SQL Server Profiler or Extended Events on the instance to capture the exact T-SQL batch triggering Error 9342.
• Look specifically for INSERT INTO ... SELECT ... FROM OPENXML(...) patterns or nested CTEs wrapping XML columns.
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the full stack trace tied to Event ID 9342.

Step 2: Refactor the Query to Use Direct sql:column Mapping

• Rewrite the INSERT statement so the XML source is referenced directly in the FROM clause rather than passed through a variable or derived table.
• Example of a fix:

  INSERT INTO dbo.Orders (OrderData)
  SELECT T.c.query('.') 
  FROM OPENXML(@hDoc, '/Root/Order', 2)
  WITH (OrderData xml 'sql:column("OrderData")');

• Avoid wrapping the OPENXML or nodes() call inside a subquery, CTE, or temp table before the final INSERT.

Step 3: Update XML Schema Collection Bindings

• Navigate to SQL Server Management Studio (SSMS) > Databases > [YourDB] > Programmability > Types > XML Schema Collections.
• Verify the target column’s bound XML Schema Collection (XSC) supports the sql:column annotation natively; if not, drop and recreate the schema collection with correct annotations.
• Run sp_help 'YourXMLSchemaCollectionName' to confirm the schema is correctly registered in the database.

Step 4: Patch and Update SQL Server 2025

• Open SQL Server Configuration Manager and confirm the instance is running the latest Cumulative Update (CU) for SQL Server 2025, as XML parsing engine bugs are frequently patched.
• Download updates via Windows Update (for Windows Server 2025/Windows 11 integrated updates) or directly from the Microsoft Update Catalog.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) after applying patches.

Alternative or Verification Steps

• Re-run the original INSERT statement in a test environment (SSMS query window) and confirm Error 9342 no longer appears in the Messages tab.
• If the error persists, bypass the issue entirely by using .nodes() with CROSS APPLY instead of OPENXML, as this method is generally more resilient to the direct-source restriction in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 9340 — Explicit Import of Current Target Namespace Invalid

Why This Error Happens

Error 9340 occurs when an XML Schema Collection (XSD) being registered or altered in SQL Server contains an <xsd:import> element that explicitly references the same targetNamespace as the schema it’s being imported into. SQL Server’s XML Schema Collection engine already resolves same-namespace references implicitly, so an explicit import of that namespace is redundant and flagged as invalid. This typically results from schema files authored or generated by third-party tools (or migrated from an older XML/XSD validation engine) that don’t align with SQL Server’s stricter W3C XML Schema interpretation, or from manually edited XSDs where namespace declarations were copied incorrectly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema Collection

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

  SELECT name, schema_namespace, create_date, modify_date
  FROM sys.xml_schema_collections
  WHERE name = '<YourSchemaCollectionName>';

• Note the schema_namespace value — this is the namespace you’ll check for redundant self-imports.
• Cross-reference this with the application or ETL job (SSIS package, .NET app, etc.) that submitted the CREATE/ALTER XML SCHEMA COLLECTION command.

Step 2: Inspect and Correct the XSD Source File

• Open the raw .xsd file in a text editor or XML IDE (e.g., Visual Studio, Notepad++).
• Locate any <xsd:import namespace="X" schemaLocation="..."> elements where namespace X matches the targetNamespace attribute defined at the top of the same schema document.
• Remove these self-referential <xsd:import> statements entirely — since the namespace is already local to the document, SQL Server will resolve all declarations within it implicitly.
• Example of the invalid pattern to remove:

  <xsd:schema targetNamespace="http://example.com/schema">
    <xsd:import namespace="http://example.com/schema" schemaLocation="self.xsd" />
  </xsd:schema>

Step 3: Re-Register the Cleaned Schema Collection

• Drop the existing invalid or partially loaded schema collection:

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Re-create it using the corrected XSD:

  CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName
  AS N'<contents of cleaned .xsd file>';

• If multiple interdependent schemas are involved (multi-file imports), ensure each file’s targetNamespace is unique and that imports only reference external namespaces, not their own.

Step 4: Validate Application/ORM-Generated Schemas

• If the XSD is auto-generated (e.g., by Entity Framework, SSIS, or a BizTalk schema export), check the generator’s settings or template for namespace import logic.
• Patch or update the generation template so it stops emitting redundant same-namespace imports, preventing recurrence on the next deployment cycle.

Alternative or Verification Steps

• Verify the fix by running SELECT * FROM sys.xml_schema_collections; and confirming the collection registers without error, then execute a sample CAST(@xmlvar AS XML) validation against the schema to confirm enforcement works correctly.
• If the error persists despite removing self-imports, use the xsd.exe (Microsoft XML Schema Definition Tool) or an online W3C XSD validator independently of SQL Server to confirm the schema is structurally valid before re-attempting import into SQL Server 2025.

Troubleshooting Guide: SQL Server Error 9338 — Invalid Namespace Declaration in XML/XQuery Expression

Why This Error Happens

Error 9338 occurs when SQL Server’s XML parser encounters a namespace declaration (xmlns or xmlns:prefix) whose value is dynamically constructed rather than a static string literal. The XQuery and XML standards mandate that namespace URIs be fixed at parse time, so any attempt to concatenate variables, use sql:variable(), or inject column data into the namespace attribute will fail. This is most common in stored procedures or applications that build XML documents using FOR XML PATH with dynamically generated namespace prefixes, or in .XML data type method calls like .query() or .nodes() where T-SQL variables are improperly interpolated into the namespace declaration string.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Procedure

• Capture the exact T-SQL statement using SQL Server Profiler, Extended Events, or the Windows Server 2025 Event Viewer (Applications and Services Logs > Microsoft > SQL Server).
• Search for WITH XMLNAMESPACES, xmlns=, or .query() calls combined with string concatenation (+) or sql:variable()/sql:column().
• Note the exact line number referenced in the Error 9338 message output in SSMS or the application log.

Step 2: Replace Dynamic Namespace Values with Static Literals

• Locate any instance where the namespace is built dynamically, for example:
  WITH XMLNAMESPACES(@NamespaceVar AS ns) — this is invalid.
• Rewrite it using a hardcoded string literal:
  WITH XMLNAMESPACES('http://schemas.example.com/2025/data' AS ns)
• If the namespace URI must vary by environment (dev/test/prod), use dynamic SQL (sp_executesql) to construct the entire query string first, then execute it, rather than parameterizing just the namespace value inline.

Step 3: Validate and Rebuild Dynamic SQL Logic

• If dynamic SQL is required, construct the full query as an NVARCHAR(MAX) string in application code or T-SQL, embedding the literal namespace value before execution:

  DECLARE @sql NVARCHAR(MAX) = N'WITH XMLNAMESPACES(''' + @NamespaceLiteral + N''' AS ns) SELECT ...';
  EXEC sp_executesql @sql;

• Ensure no T-SQL variable reference appears directly inside the XMLNAMESPACES clause itself after the string is built.
• Re-run the corrected procedure in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance to confirm the parser accepts the static literal.

Step 4: Check Application-Layer XML Generation

• If the XML is generated by a .NET application (common on Windows Server 2025 deployments using .NET 9), inspect XmlWriter or LINQ to XML code for improperly interpolated namespace strings passed to stored procedure parameters.
• Ensure any XML payload sent to SQL Server already has namespaces resolved as literals before being passed as an XML typed parameter.

Alternative or Verification Steps

• Re-execute the corrected stored procedure or ad hoc query in SSMS and confirm Error 9338 no longer appears in the Messages pane or Windows Event Viewer.
• As an alternative, use sp_helptext on the affected procedure to export its definition, audit all XMLNAMESPACES and .query()/.nodes() calls across the database, and apply the static-literal fix uniformly to prevent recurrence in related procedures.

Why This Error Happens

Error 9339 occurs when SQL Server attempts to register or parse an XML Schema Collection (XSC) that contains a malformed XSD definition. Specifically, the form attribute (which controls qualified/unqualified namespace usage) is only valid on local element or attribute declarations—not on global ones, since global elements are implicitly namespace-qualified by the schema’s targetNamespace. This typically happens due to a schema authored or exported incorrectly, a corrupted CREATE XML SCHEMA COLLECTION script, or a third-party tool generating non-compliant XSD files during migration from an older SQL Server instance to 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’s %ls location string:

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

• Export the full XSD definition using SELECT xml_schema_namespace(...) or script it out via SSMS (Generate Scripts on the schema collection object).

Step 2: Correct the Malformed XSD Definition

• Open the exported .xsd file in a text/XML editor (VS Code or SSMS Query Editor).
• Search for form="qualified" or form="unqualified" attributes applied directly to top-level <xs:element> or <xs:attribute> nodes (direct children of <xs:schema>).
• Remove the form attribute entirely from these global definitions—it should only appear on nested/local element or attribute declarations inside a <xs:complexType>.

Step 3: Drop and Recreate the Schema Collection

• Drop the broken collection (ensure no dependent XML columns/typed variables are using it first):

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Recreate it using the corrected XSD:

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

• If the collection is tied to replication, SSIS packages, or CLR assemblies, redeploy those dependent objects after recreation.

Step 4: Validate Third-Party or Migration Tooling

• If the XSD was auto-generated (e.g., via dbatools, SSDT, or a legacy export script from SQL Server 2017/2019), check for a tool update—older XSD generators are known to incorrectly emit form attributes on global nodes under stricter XSD 1.1 validation enforced in SQL Server 2025.
• Re-run the export/import process with the patched tool version before re-attempting deployment.

Alternative or Verification Steps

• Validate the corrected schema loads cleanly by running CREATE XML SCHEMA COLLECTION in a test/sandbox database first, confirming no Error 9339 (or related 6xxx-series XML parsing errors) is thrown.
• If the error persists in Visual Studio/SSDT during a DACPAC deployment, open the .sqlproj XSD source file directly and manually strip any form attributes from root-level element/attribute nodes before rebuilding the project.

Troubleshooting Guide: SQL Server Error 9337 — “The XML Schema type ‘NOTATION’ is not supported”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to register or validate an XML schema collection that contains a NOTATION type declaration, which is a legacy XML Schema Definition (XSD) construct that SQL Server’s XML engine does not implement. It typically arises from importing third-party or legacy XSD files (often generated by older DTD-to-XSD conversion tools) into a SQL Server XML Schema Collection via CREATE XML SCHEMA COLLECTION. The root cause is incompatibility between the XSD 1.0/1.1 specification’s full type set and the subset of types that SQL Server’s XML validator supports, not a server misconfiguration or corruption issue.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema File

• Locate the exact .xsd file referenced in the failing CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement.
• Open the file in a text editor or Visual Studio and search for the string NOTATION (commonly found in attribute or element type definitions like <xs:attribute type="NOTATION">).
• Cross-reference the line number reported in the SQL Server error message with the actual XSD content to confirm the exact location.

Step 2: Remove or Replace the NOTATION Type Declaration

• Edit the XSD file and replace any xs:NOTATION type references with a supported type such as xs:string, since NOTATION is rarely used for actual binary data typing in modern schemas.
• If the NOTATION type was tied to an xs:ENTITY or unparsed entity reference (common in older SGML-derived schemas), consider removing that attribute entirely if it is not consumed by downstream applications.
• Save the modified file as a new version (e.g., schema_v2.xsd) to preserve the original for rollback purposes.

Step 3: Re-import the Corrected Schema Collection

• Run the corrected import command in SQL Server Management Studio (SSMS):

  CREATE XML SCHEMA COLLECTION dbo.MySchemaCollection
  AS N'<path or inline content of schema_v2.xsd>';

• If the schema collection already exists, drop it first using DROP XML SCHEMA COLLECTION dbo.MySchemaCollection, provided no columns or tables currently reference it.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm no further validation errors are thrown on import.

Step 4: Validate Downstream XML Column Bindings

• If the schema collection is bound to an existing xml typed column, re-run ALTER TABLE to reassign the schema collection and confirm existing XML data still validates correctly.
• Test with a sample INSERT statement containing data that previously relied on the NOTATION-typed element to ensure no application-level breakage occurs.

Alternative or Verification Steps

• Verify the fix by querying sys.xml_schema_collections and sys.xml_schema_components to confirm the schema registered successfully with zero validation errors.
• If modifying the XSD is not feasible (e.g., it’s a vendor-locked schema), consider stripping XML Schema validation entirely and storing the data as untyped xml column instead, handling validation at the application layer using .NET’s System.Xml.Schema library, which does support NOTATION types.

Troubleshooting Guide: SQL Server Error 9335 – “The XQuery syntax ‘%ls’ is not supported”

Why This Error Happens

This error occurs when a T-SQL query uses XML data type methods (such as .query(), .value(), .nodes(), or .exist()) with XQuery expressions that reference syntax, functions, or axis specifiers not implemented in SQL Server’s XQuery subset. SQL Server only supports a limited profile of the W3C XQuery specification, so advanced constructs like certain FLWOR expressions, specific namespace functions, or unsupported axes will trigger this error. This is almost always a query-authoring issue rather than a server configuration or corruption problem, often introduced after migrating queries from a different XML processing engine or upgrading from an older SQL Server version with looser parsing behavior.

Step-by-Step Resolution Guide

Step 1: Identify the Unsupported XQuery Construct

• Capture the full error message, which includes the specific %ls placeholder showing the exact unsupported syntax fragment.
• Locate the offending stored procedure, function, or ad-hoc query using SQL Server Profiler or Extended Events filtered on Error Number = 9335.
• Check the query against Microsoft’s documented list of supported XQuery functions and language elements for SQL Server 2025.

Step 2: Rewrite the Query Using Supported XQuery Syntax

• Replace unsupported FLWOR clauses (like complex let or group by constructs) with simpler for/where/return expressions supported by SQL Server’s XQuery engine.
• Substitute unsupported functions (e.g., certain string or date functions) with T-SQL equivalents applied outside the .query()/.value() call.
• Avoid unsupported axes such as preceding:: or following-sibling::; restructure the XML shredding logic using .nodes() with explicit CROSS APPLY instead.
• Example fix: Change SELECT xmlcol.query('for $x in //item let $y := $x/price return $y') to SELECT xmlcol.query('for $x in //item return $x/price').

Step 3: Validate XML Schema Collection Compatibility

• If the XML column is typed against an XML Schema Collection, verify the schema doesn’t enforce constructs that force SQL Server to generate unsupported internal XQuery.
• Run SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaName'; to confirm the schema version in use.
• Update or simplify the schema definition if it references deprecated or overly complex type derivations.

Step 4: Apply the Latest Cumulative Update

• Confirm Windows Server 2025 / Windows 11 host has the latest SQL Server 2025 Cumulative Update installed, since XQuery parser improvements are periodically shipped.
• Check current build via SELECT @@VERSION; in SSMS.
• Download and apply the latest CU from the Microsoft Update Catalog if the build is outdated.

Step 5: Test the Query in Isolation

• Run the corrected .query() or .value() expression standalone against a sample XML variable using DECLARE @x XML = '<root><item><price>10</price></item></root>';.
• Confirm no Error 9335 is raised before reintegrating the fix into the full stored procedure or application code.

Alternative or Verification Steps

• Re-execute the original failing stored procedure or report query in SSMS and confirm Error 9335 no longer appears in the Messages tab.
• If the rewrite isn’t feasible, consider shredding the XML in application-tier code (e.g., using .NET’s System.Xml.Linq with full XQuery/XPath support) and passing only the extracted scalar values into SQL Server.

Troubleshooting Guide: SQL Server Error 9336 — “The XML Schema syntax ‘%ls’ is not supported”

Why This Error Happens

This error occurs when SQL Server’s XML parser encounters an element, attribute, or construct within an XML Schema Collection (XSD) that falls outside the subset of the W3C XML Schema specification supported by SQL Server. SQL Server 2025 implements a restricted XML Schema validation engine, meaning advanced constructs such as certain xs:redefine, xs:notation, complex xs:union types, or unsupported facets will be rejected during a CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION operation. It can also surface if the schema file was authored or exported using a different XML validation toolchain (such as .NET’s XmlSchema class or a third-party IDE) that permits syntax SQL Server does not recognize.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Unsupported Syntax

• Re-run the failing CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION statement in SSMS and capture the full error text, since %ls is replaced with the specific unsupported token (e.g., xs:redefine, xs:notation, xs:anyAttribute processContents).
• Open the source .xsd file in a text editor or Visual Studio XML editor and search for the flagged keyword.
• Cross-reference the construct against Microsoft’s documented list of supported XML Schema constructs for SQL Server (found under “XML Schema Collections (SQL Server)” in official docs).

Step 2: Rewrite the Schema to Use Supported Constructs

• Replace unsupported elements like xs:redefine with xs:import plus manual type redefinition, since SQL Server does not support schema redefinition.
• Simplify complex xs:union or xs:list simpleType definitions into equivalent xs:restriction-based types if they trigger the error.
• Remove or replace unsupported facets (e.g., xs:whiteSpace with fixed="true" in unsupported contexts) with SQL Server-compatible equivalents.
• Validate the modified XSD locally using a standalone validator (e.g., xmllint --schema file.xsd --noout) before resubmitting to SQL Server.

Step 3: Recreate the XML Schema Collection in SQL Server

• Drop the old collection if it exists in a broken state:

  DROP XML SCHEMA COLLECTION dbo.MySchemaCollection;

• Import the corrected schema:

  CREATE XML SCHEMA COLLECTION dbo.MySchemaCollection
  AS N'<xsd corrected content here>';

• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation that the import completed without further 9336 errors.

Step 4: Apply Latest Cumulative Update

• Confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, since XML Schema parser bug fixes are periodically shipped via CU.
• Check current build version:

  SELECT @@VERSION;

• Download and apply the latest CU from the Microsoft Update Catalog if your build is behind.

Alternative or Verification Steps

• Verify the fix by querying sys.xml_schema_collections to confirm the schema collection registered successfully, and test an XML column insert/validation against it using a sample document.
• If rewriting the schema is not feasible, consider relaxing validation by storing the column as untyped XML (without a schema collection binding) and enforcing structure via application-layer or CHECK constraint validation instead.

Why This Error Happens

Error 9334 occurs when SQL Server attempts to parse and register an XML Schema Collection (XSD) that contains invalid schema syntax. Specifically, the W3C XML Schema specification prohibits the form attribute from being used on a local <xs:element> or <xs:attribute> definition when that same definition already uses a ref attribute, since ref implies the element’s qualification form is inherited from the referenced global declaration. This typically surfaces when a third-party application, an ORM-generated schema, or a hand-edited XSD file is imported into SQL Server via CREATE XML SCHEMA COLLECTION or ALTER XML SCHEMA COLLECTION, and the source XSD was authored or auto-generated incorrectly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Schema File

• Locate the exact XSD file referenced in the error message under the Location: field (e.g., C:\SchemaFiles\OrderSchema.xsd or a URN inside the registered collection).
• Open the T-SQL script or application log that issued the CREATE XML SCHEMA COLLECTION statement to confirm which file/batch triggered the failure.
• Open the file in a text editor (Notepad++, VS Code) or SQL Server Management Studio’s XML editor to inspect the raw markup.

Step 2: Correct the Invalid XSD Syntax

• Search the file for any <xs:element ref="..." form="qualified"> or <xs:attribute ref="..." form="unqualified"> combinations.
• Remove the form attribute entirely from any element/attribute tag that also carries a ref attribute, since the form is already determined by the global declaration being referenced.
• If you need to control qualification, move the form setting to the global <xs:element name="..."> definition instead, not the local ref usage.
• Validate the corrected XSD locally using a standalone XML validator (e.g., xmllint --schema schema.xsd --noout schema.xsd or Visual Studio’s XML Schema Explorer) before re-importing.

Step 3: Re-Import the Corrected Schema Collection into SQL Server

• Drop the previous failed/partial collection if it was partially created:

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;

• Re-run the corrected script:

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

• Restart the dependent application pool or service on Windows Server 2025 / Windows 11 if it caches schema metadata (IIS, .NET service, etc.).

Step 4: Check for Vendor or Auto-Generated Schema Updates

• If the XSD was auto-generated by a third-party tool (e.g., an ERP connector, WSDL generator, or Entity Framework), check the vendor’s support site or GitHub issues for a patched schema generator — many tools inadvertently emit form + ref together.
• Apply any available hotfix or regenerate the schema using an updated tool version rather than manually patching every file, to prevent recurrence.

Alternative or Verification Steps

• Verify the fix by querying sys.xml_schema_collections to confirm the collection now exists without errors, and run a sample INSERT/SELECT against a column typed with that schema to confirm validation succeeds.
• If manual editing is impractical for large schemas, use an XSD normalization tool (e.g., xsd.exe from the Windows SDK or Liquid XML Studio) to auto-strip invalid form attributes from ref-based definitions before re-importing into SQL Server 2025.

Why This Error Happens

Error 9332 is thrown by the SQL Server Database Engine when it encounters an invalid JSON path expression inside functions like JSON_VALUE, JSON_QUERY, or JSON_MODIFY. This typically happens due to malformed path syntax (missing brackets, incorrect use of lax/strict modifiers, or unescaped special characters), or because a dynamically generated T-SQL string was concatenated incorrectly before execution. In Microsoft SQL Server 2025, this can also surface after an in-place upgrade if legacy JSON path syntax from older T-SQL scripts is no longer compatible with the updated JSON parser.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Query

• Capture the exact statement from SQL Server Management Studio (SSMS) or the application log that triggered Error 9332.
• Run the query manually in a new query window on Windows Server 2025 to reproduce the exact error message, including the %ls placeholder text (it will show the specific invalid token).

Step 2: Validate the JSON Path Syntax

• Check that the path expression starts with $ and uses correct bracket notation for arrays, e.g., $.array[0].key.
• Ensure lax or strict mode keywords (if used) are spelled correctly and placed before the $ symbol, e.g., 'strict $.key'.
• Use ISJSON(@YourJsonVariable) to confirm the underlying JSON data is well-formed before the path is even evaluated.

Step 3: Audit Dynamic SQL Concatenation

• If the query is built dynamically (via sp_executesql or string concatenation), print the final generated string using PRINT @SQL before execution to inspect for missing quotes or truncated variables.
• Verify that any NVARCHAR variables holding the JSON path are sized correctly (e.g., NVARCHAR(4000) or MAX) to prevent silent truncation that corrupts the path syntax.

Step 4: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog and check if a newer Cumulative Update is available for SQL Server 2025 on Windows Server 2025.
• Install the update via the standalone setup wizard, as JSON parser bug fixes are frequently included in CU patches: C:\SQLServer2025_CU\setup.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch.

Alternative or Verification Steps

• After correcting the path syntax, re-run the query in SSMS; the batch should complete without the 9332 error and return the expected JSON-extracted value.
• As an alternative, wrap the JSON function call in a TRY...CATCH block with ERROR_MESSAGE() logging to capture future malformed paths before they reach production reports.

Why This Error Happens

Error 9333 occurs when SQL Server’s internal XQuery/XPath parser encounters a malformed path expression where the abbreviated descendant-or-self axis (//) is immediately followed by an explicit self:: node test. This creates a redundant or ambiguous axis specification that the XML parsing engine cannot resolve, typically surfacing during FOR XML, XQuery(), .nodes(), .value() calls, or XML Index maintenance operations. In most cases, this is triggered by a hand-written or dynamically generated XQuery string embedded in a stored procedure, SSIS package, or application query that was not validated before execution against SQL Server 2025’s stricter XML schema compliance engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Object

• Check the SQL Server Error Log (Default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact statement text accompanying Error 9333.
• Use Extended Events (xel session filtering on error_reported with error_number = 9333) to capture the exact batch text in real time.
• Cross-reference the timestamp with Query Store (sys.query_store_query_text) to pinpoint the stored procedure or ad hoc query.

Step 2: Correct the Malformed XPath/XQuery Expression

• Locate the XML method call (.nodes(), .query(), .value(), or .exist()) containing the //self:: pattern.
• Replace the redundant syntax: instead of //self::node(), use a single valid axis such as //node() or descendant-or-self::node() — never combine the shorthand // with an explicit self:: step.
• Example fix:
• Incorrect: xmlcol.query('//self::Customer')
• Corrected: xmlcol.query('descendant-or-self::Customer') or simply xmlcol.query('//Customer')
• Save and redeploy the corrected stored procedure or script.

Step 3: Rebuild Affected XML Indexes

• If the error originated during XML Index creation or maintenance, drop and recreate the index to force re-validation of all stored XML schemas.
• Run: ALTER INDEX ALL ON dbo.YourTable REBUILD;
• If a Primary XML Index is suspected of corruption from a prior failed build, drop it entirely with DROP INDEX and recreate it using CREATE PRIMARY XML INDEX.

Step 4: Update SQL Server 2025 Cumulative Update

• Confirm your instance is on the latest Cumulative Update via SELECT @@VERSION;.
• Download and apply the latest CU from the Microsoft Update Catalog, as early SQL Server 2025 builds had a known XQuery parser regression affecting combined axis expressions.
• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) after patching.

Alternative or Verification Steps

• Re-run the original query or application workflow and confirm Error 9333 no longer appears in the Error Log or Extended Events session.
• As a quick alternative, wrap suspect dynamic XQuery strings in a validation routine using TRY_CONVERT(XML, @path) before execution to catch malformed axis syntax before it reaches the native parser.

Troubleshooting Guide: SQL Server Error 9331 (“Syntax error near ‘%ls'”)

Why This Error Happens

Error 9331 typically occurs when SQL Server’s parser encounters a malformed or unsupported T-SQL construct, often due to a JSON path expression, dynamic SQL string, or a Transact-SQL statement built using concatenated variables that produce invalid syntax at runtime. This is frequently triggered by mismatched quotes, incorrect use of newer SQL Server 2025 syntax features (such as JSON aggregate functions or vector search clauses), or a corrupted/truncated stored procedure definition caused by an incomplete deployment script. In some cases, encoding mismatches (e.g., Unicode vs. non-Unicode strings) in dynamically generated queries can also introduce invisible characters that the parser cannot interpret.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Statement

• Capture the exact query text from the error message, including the string following “near” — this identifies the token where parsing failed.
• Run the statement in isolation using SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
• Check for unbalanced brackets, missing commas, or unsupported keywords copied from older SQL Server syntax guides.

Step 2: Validate Dynamic SQL and JSON Syntax

• If the query uses sp_executesql or EXEC(), print the fully constructed SQL string to a variable and inspect it manually before execution:

DECLARE @debugSQL NVARCHAR(MAX) = @yourDynamicQuery;
PRINT @debugSQL;

• Confirm JSON functions (JSON_OBJECTAGG, JSON_ARRAYAGG, ISJSON) and any new SQL Server 2025 vector/AI query clauses follow the updated syntax documented in the 2025 Transact-SQL reference, as syntax changed from 2022/2019 versions.
• Replace smart quotes or non-standard Unicode characters (often introduced by copy-pasting from Word or PDFs) with standard ASCII quotes and parentheses.

Step 3: Check for Corrupted or Incomplete Object Definitions

• Navigate to the stored procedure, function, or view in SSMS Object Explorer, right-click, and select Modify to inspect the full definition.
• Run sp_helptext 'YourObjectName' from a query window to confirm the object wasn’t truncated during a prior deployment or script transfer.
• If corruption is suspected, redeploy the object from source control or your original .sql migration script located in your deployment pipeline directory (e.g., C:\Deployments\SQL2025\Scripts\).

Step 4: Review Compatibility Level Settings

• Open a query window and run:

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

• Ensure the database compatibility level is set to 170 (SQL Server 2025) if you intend to use newer syntax features; older compatibility levels may reject new keywords and cause parser errors.
• Update it if necessary:

ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Re-run the corrected query in a test environment and confirm it executes without returning Error 9331, then redeploy the fixed script to production during a maintenance window.
• If the error persists only in application-generated queries (not when run manually), enable SQL Server Profiler or Extended Events to capture the exact runtime-generated statement, as the application layer may be injecting malformed parameters.

SQL Server 2025 Error 9328: “%sType specification expected”

Why This Error Happens

Error 9328 is a parser-level error in SQL Server that occurs when the XML or JSON Query/Value engine encounters a malformed type cast or schema declaration within a query—typically inside an XQuery expression, OPENJSON clause, or a CAST/CONVERT statement targeting XML schema collections. This usually stems from a missing or malformed data type token immediately following a cast as, treat as, or instance of keyword in the query text. It can also surface due to a corrupted or incompletely migrated XML Schema Collection after an upgrade from a prior SQL Server version to SQL Server 2025, where metadata referencing the type library did not resolve correctly.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Query or Stored Procedure

• Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) or Event Viewer on Windows Server 2025 for the full text of Error 9328 to identify which batch, stored procedure, or XML index rebuild triggered it.
• Run SELECT * FROM sys.messages WHERE message_id = 9328; to confirm the exact language template and cross-reference it against your application’s query logs.

Step 2: Inspect and Correct the Type Cast Syntax

• Review the XQuery/XML DML statement for syntax like cast as xs: or treat as and verify a valid XML Schema type (e.g., xs:integer, xs:string) immediately follows the keyword with no missing token.
• For OPENJSON calls, confirm the WITH clause explicitly defines column data types (e.g., WITH (Name varchar(100), Age int)) rather than leaving the type declaration empty or truncated.
• Example fix: change CAST(xmlCol.value('(/Root/Item)[1]', '') AS ...) to CAST(xmlCol.value('(/Root/Item)[1]', 'nvarchar(50)') AS nvarchar(50)).

Step 3: Validate and Rebuild XML Schema Collections

• Run SELECT name, xml_collection_id FROM sys.xml_schema_collections; to list all registered schema collections on the affected database.
• If a schema collection appears incomplete or was migrated improperly, drop and re-import it using CREATE XML SCHEMA COLLECTION with the original .xsd definition file.
• Restart the SQL Server 2025 service via services.msc on Windows Server 2025 (or Restart-Service MSSQLSERVER in PowerShell) after re-registering the schema to clear any cached query plans referencing the old metadata.

Step 4: Update SQL Server 2025 Cumulative Updates

• Open SQL Server Setup or check via SELECT @@VERSION; to confirm your current build number.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, since early SQL Server 2025 builds had known parser edge-cases with nested type specifications in JSON/XML functions.

Alternative or Verification Steps

• After applying the fix, re-run the original query in SSMS with Include Actual Execution Plan enabled; if it compiles and executes without throwing Error 9328, the type specification issue is resolved.
• As an alternative, isolate the problematic expression using SET PARSEONLY ON; before running the statement—this validates syntax without executing it, helping you confirm if the type token is now correctly recognized before rolling the fix into production.

Why This Error Happens

Error 9330 occurs when a T-SQL query attempts to use comparison operators (such as =, <, >, DISTINCT, GROUP BY, ORDER BY, or UNION) on data types that SQL Server does not support for direct comparison, such as XML, TEXT, NTEXT, IMAGE, GEOGRAPHY, GEOMETRY, or certain CLR User-Defined Types (UDTs) that lack an explicit comparison operator overload. This is a query-design limitation rather than a server configuration fault—SQL Server 2025 strictly enforces type comparability rules at the parser level before execution. It commonly surfaces after migrating legacy databases where deprecated types like TEXT/NTEXT were used in JOIN, WHERE, or ORDER BY clauses.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Data Type

• Run the failing query in SQL Server Management Studio (SSMS) 21 or Azure Data Studio and note the exact line number referenced in the error.
• Use sp_help 'YourTableName' or query INFORMATION_SCHEMA.COLUMNS to check if the column is XML, TEXT, NTEXT, IMAGE, GEOGRAPHY, or GEOMETRY.
• Check if a CLR UDT column is involved by running SELECT name, is_user_defined FROM sys.types WHERE is_user_defined = 1;.

Step 2: Rewrite the Query Using Comparable Conversions

• For XML columns, use .value() or .exist() methods instead of direct comparison, e.g., WHERE XmlColumn.value('(/root/node)[1]', 'NVARCHAR(100)') = 'test'.
• For deprecated TEXT/NTEXT/IMAGE types, cast to modern equivalents using CAST(TextColumn AS NVARCHAR(MAX)) before comparing or sorting.
• For GEOGRAPHY/GEOMETRY, replace direct equality checks with spatial methods like .STEquals() or .STDistance().
• For CLR UDTs, implement and call the type’s ToString() or a custom comparison method explicitly in the query.

Step 3: Update Schema for Long-Term Fix (Optional but Recommended)

• Alter the table to migrate deprecated types permanently:
  ALTER TABLE dbo.YourTable ALTER COLUMN OldTextCol NVARCHAR(MAX);
• Rebuild any affected indexes or statistics after the type change using ALTER INDEX ALL ON dbo.YourTable REBUILD;.

Step 4: Validate Query Execution Plan

• Open SSMS, enable Include Actual Execution Plan, and re-run the corrected query.
• Confirm there are no implicit conversion warnings or residual comparison operators flagged on the modified columns.

Alternative or Verification Steps

• Re-execute the original failing stored procedure or query batch; if it completes without raising Error 9330, the fix is verified.
• As an alternative, wrap the incomparable column in a computed column with an explicit conversion (e.g., ALTER TABLE ADD ComputedCol AS CAST(XmlCol AS NVARCHAR(MAX))) and reference that computed column in ORDER BY/JOIN clauses instead of the original type.

Troubleshooting Guide: SQL Server Error 9326 — “Computed comment constructors are not supported”

Why This Error Happens

This error occurs when SQL Server’s XML/XQuery parser encounters a comment constructor expression used in a computed context (such as within a dynamically built XQuery expression) that the engine’s XML processing layer cannot statically validate. SQL Server 2025 enforces stricter XQuery compliance checks, and computed comment nodes (comment { ... } built from variable or expression-driven content) are explicitly disallowed because they cannot be resolved into a static schema-valid node tree. This is typically triggered by application code, ORM-generated queries, or stored procedures that construct XML using FLWOR expressions or comment{} constructors with non-literal content.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Procedure

• Check the SQL Server Error Log and Windows Event Viewer (Applications and Services Logs > Microsoft > SQL Server) for the exact session ID and statement text tied to Error 9326.
• Run the following to capture the live offending batch:

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

• Look specifically for FLWOR, .query(), .nodes(), or XML::comment{} constructs in the returned text.

Step 2: Replace Computed Comment Constructors with Static or Workaround Logic

• Rewrite any dynamic comment { $variable } expressions as literal string concatenation instead, since SQL Server does not support computed comment nodes inside XQuery.
• Example fix — instead of:

  SET @x = @x.query('comment { sql:variable("@val") }')

Use string-based XML assembly:

  SET @x = CAST('<!--' + @val + '-->' AS XML)

• Update any ORM-generated XML mapping templates (e.g., Entity Framework, Hibernate) that auto-generate comment nodes dynamically.

Step 3: Update SQL Server 2025 Cumulative Update

• Open SQL Server Configuration Manager or Windows Update on Windows Server 2025 and confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, as early CU builds had incomplete XQuery validation messaging.
• Download the latest CU from the Microsoft Update Catalog and install via:

  Start-Process "SQLServer2025-KB5XXXXXX-x64.exe" -ArgumentList "/quiet /IAcceptSQLServerLicenseTerms" -Wait

• Restart the SQL Server service (services.msc > SQL Server (MSSQLSERVER) > Restart) after patching.

Step 4: Validate XML Schema Collections

• If the XML column uses a schema collection, verify it does not define elements expecting comment nodes:

  SELECT name, xml_collection_id FROM sys.xml_schema_collections;

• Drop and recreate the schema collection if it references deprecated comment-node typing from older SQL Server versions (pre-2019 compatibility level).

Alternative or Verification Steps

• Re-run the original query/procedure after applying the string-based XML workaround and confirm no Error 9326 appears in sys.dm_os_ring_buffers or the SQL error log.
• If the error persists, set the database compatibility level back one tier (e.g., from 170 to 160) using ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160; as a temporary mitigation while refactoring the XQuery logic.

Why This Error Happens

Error 9327 occurs when SQL Server’s XML parser encounters a malformed XML prolog declaration that is missing a required terminating semicolon within an XQuery or XML data type operation. This typically happens when an application passes XML data with a malformed <?xml version="1.0" encoding="UTF-8"?> declaration, or when a pseudo-attribute within the prolog (such as encoding or standalone) is not properly closed. It can also surface after a corrupted XML column value is inserted via a bulk import process that strips or mangles encoding bytes (such as a BOM mismatch).

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query or Import Source

• Check the SQL Server error log (Log_X.trc or SQL Server Management Studio > Management > SQL Server Logs) for the exact statement or stored procedure that triggered Error 9327.
• Run DBCC INPUTBUFFER(<session_id>) to capture the last statement executed by the session if the error occurred interactively.
• Isolate whether the error originates from an OPENXML, .query(), .value(), or CAST/CONVERT AS XML operation.

Step 2: Validate and Repair the XML Prolog Syntax

• Inspect the raw XML string/document for a prolog resembling <?xml version="1.0" encoding="UTF-8"?>.
• Confirm every pseudo-attribute (version, encoding, standalone) ends in a proper quoted value with no trailing characters before ?>.
• Manually correct malformed declarations, for example changing <?xml version="1.0" encoding="UTF-8" (missing closing ?>) to <?xml version="1.0" encoding="UTF-8"?>.
• If the XML is generated by an external application (e.g., a .NET service or ETL job on Windows Server 2025), update the XML serializer settings to emit a compliant prolog.

Step 3: Re-Validate Source Data Encoding

• Open the source file in a hex editor or VS Code to check for an unexpected Byte Order Mark (BOM) or non-UTF‑8 byte sequence preceding the <?xml tag.
• If importing via BULK INSERT or OPENROWSET(BULK...), explicitly set the codepage:

  BULK INSERT XmlStagingTable
  FROM 'C:\ImportData\source.xml'
  WITH (CODEPAGE = '65001', DATAFILETYPE = 'char');

• Re-run the import and confirm the XML column accepts the value without throwing Error 9327.

Step 4: Update SQL Server Cumulative Updates

• Navigate to Windows Settings > Windows Update (Windows 11) or Server Manager > Updates (Windows Server 2025) to ensure the OS is current, as XML parser libraries can depend on updated system components.
• Apply the latest SQL Server 2025 Cumulative Update (CU) via SQL Server Setup > Maintenance > Edition Upgrade/Update, since XML prolog parsing edge cases are occasionally patched in CUs.

Alternative or Verification Steps

• After applying the fix, re-execute the original query or re-run the import job and confirm no Error 9327 appears in SQL Server Management Studio‘s Messages tab or the SQL Server error log.
• As a quick alternative, strip the XML prolog entirely before insertion (since it’s optional for xml-typed columns) using REPLACE(@xmlString, '<?xml version="1.0" encoding="UTF-8"?>', ''), then cast the remaining content directly to the XML data type.

Microsoft SQL Server 2025 – Error 9325 Troubleshooting Guide

Why This Error Happens

Error 9325 occurs when a T-SQL query attempts to use an XQuery expression that dynamically constructs a processing instruction (e.g., <?target { expression }?>) within an XML data type method such as .query() or .nodes(). SQL Server’s XQuery implementation intentionally supports only a restricted subset of the W3C XQuery specification, and computed processing instruction constructors fall outside that supported subset. This is not a configuration or corruption issue—it is a hard limitation of the SQL Server XML engine, so the query itself must be rewritten to avoid the unsupported syntax.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact T-SQL statement from SQL Server Management Studio (SSMS) or the application log that triggered Error 9325.
• Look specifically for XQuery expressions containing the pattern <?...{...}?> inside .query(), .value(), or .nodes() calls.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full stack/context of the failing statement.

Step 2: Rewrite the XQuery to Remove Computed PI Constructors

• Replace any computed processing instruction syntax (e.g., <?pi-target {sql:variable("@value")}?>) with a static XML element or a pre-formatted string built in T-SQL before insertion.
• Example fix: Instead of generating the PI inside XQuery, construct the string in T-SQL using FOR XML PATH('') or string concatenation, then cast the result to xml afterward.
• Avoid let, computed PI, and computed comment constructors in the same query block—SQL Server flags these together in version 2025.

Step 3: Validate XML Schema Collections (If Applicable)

• If the XML data is governed by an XML SCHEMA COLLECTION, open SSMS and run:
• SELECT xsc.name, xsc.xml_collection_id FROM sys.xml_schema_collections xsc;
• Confirm the schema does not enforce or expect processing instructions that your application is trying to generate dynamically via XQuery.
• Update or drop the restrictive schema collection using ALTER XML SCHEMA COLLECTION if it conflicts with your query logic.

Step 4: Apply the Corrected Query and Test in Isolation

• Run the rewritten query alone in a new SSMS query window (not inside a stored procedure) to confirm Error 9325 no longer appears.
• Use SET SHOWPLAN_XML ON to verify the execution plan compiles without XQuery parse errors.
• Once confirmed, redeploy the corrected logic into the stored procedure, function, or application code.

Step 5: Update Application Data Access Layer

• If the query originates from an ORM (e.g., Entity Framework, Dapper) or a reporting tool, locate the raw SQL template responsible for the XML generation.
• Patch the template to use the Step 2 workaround, then redeploy the application component on Windows Server 2025 / Windows 11.

Alternative or Verification Steps

• Verify the fix by re-executing the original failing stored procedure or application transaction and confirming no Error 9325 appears in ERRORLOG or SSMS output.
• If rewriting XQuery is not feasible short-term, bypass the XML method entirely and generate the processing instruction string in the application layer (C#, PowerShell, etc.) before passing it to SQL Server as a plain nvarchar(max) parameter, then cast to xml only after insertion.

Troubleshooting Guide: SQL Server Error 9323

Why This Error Happens

Error 9323 occurs when Transact-SQL code references a variable name containing a colon character (e.g., @my:variable), which is reserved syntax not supported by the SQL Server Database Engine. This typically happens after migrating scripts from another platform or data tool that permits colons in identifiers, or due to a typo where a colon was mistakenly used instead of an underscore. It can also surface when dynamically generated SQL (built by an ORM, ETL tool, or application layer) concatenates strings incorrectly and inserts a colon into a variable declaration.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Query

• Check the SQL Server error log or SSMS “Messages” tab to locate the exact batch that triggered Error 9323.
• Use sys.dm_exec_query_stats combined with sys.dm_exec_sql_text to pull the full query text if the error originated from an application connection rather than an interactive session.
• Search the retrieved script text for any @ symbol followed by a colon (:) within variable declarations or usages.

Step 2: Correct the Variable Naming Syntax

• Replace any colon characters in variable names with a supported character such as an underscore (_) — for example, change DECLARE @start:date DATETIME to DECLARE @start_date DATETIME.
• If the script was generated by an external tool (SSIS package, Python/C# application, or BI platform), inspect the source code or mapping configuration that builds the dynamic SQL string.
• Re-run the corrected script in a non-production environment (e.g., a local instance or a restored copy of the database) to confirm the parser no longer throws Error 9323.

Step 3: Audit Related Stored Procedures and Jobs

• Run a metadata search across the database using the following query to catch other instances of the same pattern:

SELECT OBJECT_NAME(object_id) AS ObjectName, definition
FROM sys.sql_modules
WHERE definition LIKE '%@%:%';

• Update any stored procedures, functions, or SQL Agent job steps that return matches, following the same naming correction from Step 2.
• Redeploy the corrected objects through your standard change-management or deployment pipeline (e.g., DACPAC, Flyway, or source control migration scripts).

Alternative or Verification Steps

• After applying fixes, execute the corrected stored procedures or scripts manually in SSMS/Azure Data Studio and confirm no Error 9323 appears in the output or SQL Server error log.
• If the error persists despite manual review, enable SQL Server Extended Events (sqlserver.error_reported) filtered on error number 9323 to capture the exact query text and calling application for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 9324 — Mismatched Curly Braces in JSON Text

Why This Error Happens

This error occurs when SQL Server’s JSON parser encounters a closing brace } that does not have a corresponding opening brace { in the JSON expression being processed. It is typically triggered by malformed JSON passed to functions such as ISJSON(), JSON_VALUE(), JSON_QUERY(), JSON_MODIFY(), or OPENJSON(), often due to improperly escaped literal brace characters, truncated JSON strings, or manually concatenated JSON that breaks structural integrity. In some cases, this also surfaces when application code dynamically builds JSON payloads using string concatenation instead of proper serialization, leaving stray braces in the text.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the Malformed JSON

• Locate the exact query or stored procedure throwing Error 9324 by checking SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Run ISJSON(your_json_column) against the suspect data to confirm which rows contain invalid JSON.
• Extract the offending JSON string and inspect it in a text editor or JSON validator (e.g., VS Code with a JSON linter extension) to visually spot unbalanced braces.

Step 2: Correctly Escape Literal Brace Characters

• If the JSON is intentionally meant to contain literal { or } characters as string values (not structural JSON), ensure they are properly escaped or enclosed within quoted string values, e.g., "text": "This is a literal brace: {".
• Avoid using string concatenation (+) to build JSON manually in T-SQL; instead, use JSON_MODIFY() or FOR JSON PATH to generate valid JSON programmatically.
• Example fix in T-SQL:

SELECT JSON_MODIFY('{"name":"test"}', '$.value', 'literal { brace')

Step 3: Validate and Repair the JSON Payload

• Use ISJSON() as a pre-check before inserting or processing JSON data:

IF ISJSON(@jsonInput) = 1
    PRINT 'Valid JSON';
ELSE
    PRINT 'Invalid JSON - check braces';

• If the JSON originates from an external application (e.g., a .NET or Java service), update the client-side serializer (such as System.Text.Json or Newtonsoft.Json) to ensure proper encoding before the payload reaches SQL Server.
• For legacy stored procedures on Windows Server 2025 environments, update any PowerShell or SSIS packages feeding JSON into SQL Server to use ConvertTo-Json instead of manual string building.

Step 4: Restart the Affected Session or Service (If Cached Execution Plan Is Involved)

• Clear the procedure cache if the error persists due to a cached execution plan referencing bad JSON logic:

DBCC FREEPROCCACHE;

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

Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Re-run the original query or application transaction and confirm that ISJSON() returns 1 for all affected rows, and that Error 9324 no longer appears in the SQL Server error log.
• As an alternative, if the JSON source is uncontrollable (e.g., third-party API), wrap the parsing logic in a TRY...CATCH block to gracefully handle malformed JSON without crashing the transaction, and log the raw payload for manual review.

Troubleshooting Guide: SQL Server Error 9322

Why This Error Happens

This error occurs when SQL Server’s XML parser encounters a double hyphen (--) sequence inside an XML comment block, which is invalid per XML specification since -- is reserved exclusively for closing comment tags (-->). This typically happens when application code dynamically generates XML containing SQL comment syntax, T-SQL scripts, or user-submitted data with hyphens that gets embedded into an XML column, variable, or FOR XML/OPENXML operation without proper sanitization. It can also surface after upgrading to SQL Server 2025 if legacy XML-generation logic was never validated against strict XML parsing rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Source

• Locate the exact query, stored procedure, or application call that triggered Error 9322 using SQL Server Profiler or Extended Events (xml_deadlock_report or sql_batch_completed filters).
• Check recent FOR XML, OPENXML, XML.query(), or CAST/CONVERT AS XML operations in the affected code path.
• Review application logs on Windows Server 2025 under C:\inetpub\logs\LogFiles\ or your app’s custom logging directory for the exact payload submitted.

Step 2: Sanitize or Escape the Double-Hyphen Content

• Replace any instance of -- within comment-generating strings with a safe placeholder (e.g., -- or a single hyphen) before inserting into XML.
• In T-SQL, use REPLACE(@InputString, '--', '- -') as a quick mitigation when constructing XML comments dynamically.
• If using SQLCLR or an application layer (C#/.NET on Windows 11 dev machines), apply System.Security.SecurityElement.Escape() or an equivalent XML-safe encoding function prior to insertion.

Step 3: Validate XML Schema and Comment Logic

• Open SQL Server Management Studio (SSMS) 2025 and run the query in isolation with sample data containing -- to reproduce the error in a controlled environment.
• Inspect any stored procedures that build <!-- comment --> blocks programmatically and refactor them to avoid embedding raw user input directly into comment tags.
• Update relevant .xsd schema files (commonly stored under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Schemas\) if custom validation rules need adjusting.

Step 4: Apply and Test the Fix

• Deploy the corrected stored procedure or application patch to a staging instance of SQL Server 2025 first.
• Re-run the original failing transaction or batch job to confirm Error 9322 no longer appears.
• Monitor the Windows Event Viewer (Applications and Services Logs > Microsoft > SQL Server) for recurrence over a 24-hour period.

Alternative or Verification Steps

• Run SELECT CAST('<!-- test -->' AS XML); in SSMS as a baseline sanity check — then test with your sanitized dynamic string to confirm no -- sequences remain before the closing tag.
• If the error persists in third-party or vendor software, apply an input-validation wrapper at the application tier instead of modifying SQL Server internals, since some XML payloads originate outside your direct T-SQL control.

Troubleshooting Guide: SQL Server Error 9320 — XQuery Parent Axis Error on Document Node

Why This Error Happens

Error 9320 occurs when an XQuery expression embedded in a SQL Server operation (such as nodes(), query(), or modify() on an xml data type) attempts to navigate to a parent node from the root document node, which by definition has no parent. This typically happens due to malformed or overly defensive XQuery/XPath expressions (e.g., ../ or parent::*) executed against a context that XQuery’s static type analysis resolves to an empty sequence at compile time. It can also surface after upgrading to SQL Server 2025 because the XQuery engine enforces stricter static typing rules than in prior versions, causing legacy queries that previously failed silently or returned empty results to now throw a hard static 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 9320: EXEC sp_readerrorlog 0, 1, 'Error: 9320';
• Use SQL Server Profiler or Query Store (sys.query_store_query_text) to trace which stored procedure, trigger, or ad-hoc batch contains the XML/XQuery call.
• Look specifically for .query(), .value(), .nodes(), or .modify() method calls on xml-typed columns or variables.

Step 2: Correct the XQuery Expression

• Locate any XPath expressions using parent::node(), ../, or ancestor:: directly on the document root (e.g., /parent::*).
• Rewrite the query to reference a valid starting context node instead of the document node itself — for example, change /Root/parent::node() to /Root or restructure to start from a child element before navigating upward.
• If dynamic SQL constructs the XPath string, add validation logic to strip invalid parent-axis calls when the context path depth is 0.

Step 3: Update Compatibility Level and Validate XML Schema

• Confirm the database compatibility level: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';
• If recently migrated, temporarily test with compatibility level 160 (SQL Server 2022) via ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160; to confirm whether stricter 2025 XQuery static analysis is the trigger.
• Validate the underlying XML schema collection (if xml columns are typed) using SELECT * FROM sys.xml_schema_collections; to ensure no structural mismatch is causing unexpected empty node sets.

Step 4: Patch and Test in a Non-Production Environment

• Apply the latest Cumulative Update (CU) for SQL Server 2025, since XQuery engine fixes are frequently included in servicing releases.
• Deploy the corrected stored procedure or query to a staging instance on Windows Server 2025 / Windows 11 and re-run the original workload or ETL job that triggered the error.
• Monitor via sys.dm_exec_query_stats to confirm the query completes without throwing 9320.

Alternative or Verification Steps

• Re-run the original failing transaction or SSIS package after deployment; confirm no Error 9320 appears in sys.dm_os_ring_buffers or the SQL error log for at least one full execution cycle.
• If the issue persists, isolate the XQuery call into a standalone SELECT statement with a static XML sample to binary-search the exact line causing the empty parent-axis evaluation, then correct or remove the parent-axis navigation entirely.

Why This Error Happens

Error 9321 occurs when a stored XQuery expression (typically executed through the .query(), .value(), or .nodes() methods on an xml data type column) uses the parent:: axis on a node whose static type guarantees it cannot have a parent, such as the document root or an element explicitly typed as a top-level node in an XML Schema Collection. SQL Server’s XQuery engine performs static type analysis at compile time, and if it determines the result of parent:: will always be an empty sequence, it raises this error instead of silently returning nothing. This is most common when working with typed XML columns bound to an XML Schema Collection (XSD), where the schema restricts certain elements to only exist at the root level, or when a query path incorrectly assumes a node is nested when it is actually the outermost element.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XQuery Expression

• Review the SQL Server error log or the failing T-SQL batch to locate the exact .query(), .value(), or .exist() call that references parent::.
• Use sys.dm_exec_sql_text and sys.dm_exec_query_stats to pull the exact statement text if the error surfaced from an application trace.
• Confirm whether the column involved is a typed XML column by running:
  SELECT xml_schema_collection_id FROM sys.columns WHERE object_id = OBJECT_ID('YourTable');

Step 2: Check the XML Schema Collection Definition

• If the column uses a Schema Collection, inspect it with:
  SELECT xsc.name, xsd.xml_schema_namespace FROM sys.xml_schema_collections xsc;
• Determine if the element referenced by parent:: is defined as a global/top-level element in the XSD — this is the root cause of the “statically empty” result.
• If the schema is incorrect or overly restrictive, modify the schema collection using ALTER XML SCHEMA COLLECTION to allow the element to be nested, or remove the top-level-only constraint.

Step 3: Rewrite the XQuery Path Expression

• Replace parent::ElementName with a safer, context-aware path such as ancestor::ElementName or restructure the query to navigate from a known ancestor downward instead of upward.
• Example fix:
  -- Problematic: col.query('//Child/parent::Root')
-- Corrected: col.query('/Root/Child')
• Test the corrected query in isolation using SELECT @xmlvar.query('your/new/path') before deploying it into production procedures.

Step 4: Rebuild and Validate Dependent Objects

• If the XQuery is embedded inside a stored procedure, function, or computed column, recompile the object after the fix:
  EXEC sp_recompile 'YourProcedureName';
• For computed columns using XML methods, drop and recreate the column definition to force SQL Server to re-validate the static type.

Alternative or Verification Steps

• Run the corrected query against a sample dataset using SET SHOWPLAN_XML ON to confirm no XQuery compilation warnings or errors appear.
• If the error persists despite path correction, temporarily cast the column to untyped XML (remove the schema collection binding) to confirm the schema restriction was the root cause, then reapply a corrected schema.

Troubleshooting Guide: SQL Server Error 9319 – Static Simple Type Validation Failure

Why This Error Happens

Error 9319 occurs when SQL Server’s XML parser attempts to validate a value against an XML schema collection, but the value does not conform to the defined simple type (such as an invalid date format, out-of-range integer, or malformed string pattern). This typically happens due to corrupted or improperly formatted XML data being inserted into a column with an associated XML Schema Collection, or due to a mismatch between the application’s data serialization logic and the schema definition deployed on the server. In some cases, this error surfaces after a schema collection update where legacy data no longer satisfies newly enforced constraints (e.g., xs:pattern, xs:minInclusive, or xs:enumeration restrictions).

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Data and Schema Collection

• Run the following query to locate the XML Schema Collection tied to the column throwing the error:

  SELECT name, xml_collection_id FROM sys.xml_schema_collections;

• Extract the exact XML value causing the failure by isolating the INSERT/UPDATE statement from your application logs or SQL Server Profiler trace.
• Cross-reference the simple type definition (e.g., xs:simpleType) in the schema using:

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

Step 2: Validate and Correct the XML Payload

• Manually test the problematic XML fragment against the schema using a local XML validator (Visual Studio’s XML Editor or xmllint --schema works well for Windows Server 2025 environments).
• Check for common mismatches: incorrect date formats (YYYY-MM-DD vs MM-DD-YYYY), leading/trailing whitespace in enumerated values, or numeric values exceeding minInclusive/maxInclusive bounds.
• Correct the data at the source (application code, ETL pipeline, or stored procedure) rather than suppressing the error.

Step 3: Rebuild or Patch the XML Schema Collection if Necessary

• If the schema itself was deployed incorrectly, drop and recreate it:

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

• Ensure the schema file matches the version expected by SQL Server 2025 — check for deprecated XSD syntax that may have been tightened in this release.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to clear any cached schema compilation artifacts.

Step 4: Check for Collation and Encoding Conflicts

• Verify the database collation settings using:

  SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation');

• Ensure the XML data’s character encoding (UTF-8 vs UTF-16) matches what the schema collection expects, as encoding mismatches can trigger false-positive simple type validation failures on Windows 11 client applications feeding data to the server.

Alternative or Verification Steps

• To verify the fix, re-run the original INSERT/UPDATE statement that triggered Error 9319 and confirm it completes without exceptions; additionally, query the target table to ensure the XML column data renders correctly with SELECT CAST(XMLColumn AS NVARCHAR(MAX)).
• If the error persists, as a temporary workaround, remove the XML Schema Collection binding from the column (ALTER TABLE ... ALTER COLUMN XMLColumn XML(DOCUMENT dbo.YourSchemaCollectionName) NULL to unbind) to isolate whether the issue is data-related or schema-enforcement-related, then re-bind once the root cause is resolved.

Troubleshooting Guide: SQL Server Error 9317 – Syntax Error

Why This Error Happens

Error 9317 occurs when SQL Server’s JSON or XML parser encounters malformed syntax within a query construct, typically related to FOR JSON, OPENJSON, JSON_VALUE, or XQuery expressions. This is commonly caused by improperly escaped characters, mismatched brackets/quotes, or incompatible syntax introduced by a recent T-SQL script migration or ORM-generated query. Corrupted stored procedures or incomplete deployment scripts can also leave behind truncated query fragments that trigger this parsing failure.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Query

• Capture the exact query text referenced in the error message (the %ls placeholder will show the problematic token in SQL Server Management Studio or the error log).
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full statement context.
• Run the query in isolation using SQL Server Management Studio (SSMS) to reproduce the error outside of the application layer.

Step 2: Validate JSON/XML Syntax Structure

• Inspect the query for unescaped single quotes, unbalanced curly braces {}, or missing commas in FOR JSON PATH or OPENJSON clauses.
• Use an online or local JSON validator to confirm the payload structure before it’s passed into SQL Server.
• Verify that any dynamic SQL concatenation isn’t introducing broken syntax — use PRINT @sql before EXEC(@sql) to inspect the generated string.

Step 3: Update or Repair Affected Stored Procedures

• Open the stored procedure in SSMS via Object Explorer > Databases > [YourDB] > Programmability > Stored Procedures.
• Re-deploy the procedure using a clean source-controlled version to rule out corruption from a failed deployment.
• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying page-level corruption affecting stored object definitions.

Step 4: Apply Latest Cumulative Update

• Confirm your SQL Server 2025 build number via SELECT @@VERSION;.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early 2025 builds have known JSON parser edge-case bugs.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) after patching.

Alternative or Verification Steps

• Re-run the original query or application transaction that triggered Error 9317; confirm it completes without returning a syntax error in the SSMS Messages tab.
• If the error persists, temporarily simplify the JSON/XML clause (remove nested functions) to confirm basic execution works, then incrementally reintroduce complexity to pinpoint the exact failing token.

Troubleshooting Guide: SQL Server Error 9318

Why This Error Happens

Error 9318 occurs when SQL Server’s JSON parser encounters an invalid character or malformed token while parsing JSON text, typically caused by corrupted data, improper encoding, or manually constructed JSON strings that don’t conform to valid JSON syntax. This often happens when applications pass improperly escaped characters, truncated JSON payloads, or non-UTF-8 encoded data into functions like JSON_VALUE, JSON_QUERY, OPENJSON, or ISJSON. It can also surface when legacy data migrated into JSON-typed columns contains hidden control characters or BOM (Byte Order Mark) artifacts that violate the JSON specification.

Step-by-Step Resolution Guide

Step 1: Identify the Offending JSON Source

• Run SELECT ISJSON(your_column) AS IsValidJson FROM your_table WHERE ISJSON(your_column) = 0; to isolate rows with malformed JSON.
• Check the exact character referenced in the error message (e.g., 0x00 or 0x1F) — these are often non-printable control characters.
• Use SELECT your_column, ASCII(SUBSTRING(your_column, 1, 1)) FROM your_table; to inspect suspicious byte sequences near the reported position.

Step 2: Clean and Re-encode the JSON Input

• Strip invalid control characters using a cleanup script: UPDATE your_table SET your_column = REPLACE(your_column, CHAR(0), '') WHERE your_column LIKE '%' + CHAR(0) + '%';
• Ensure the application layer serializes JSON using UTF-8 without a BOM; check connection string settings for Charset=utf8 or equivalent.
• If data originates from a flat file or external API, re-validate the payload through a JSON linter (e.g., jq or an online validator) before inserting it into SQL Server.

Step 3: Patch and Reconfigure SQL Server Environment

• Confirm SQL Server 2025 is running the latest Cumulative Update via SELECT @@VERSION; and cross-reference with Microsoft’s update catalog, since early JSON parser builds have known encoding edge cases.
• On Windows Server 2025 / Windows 11, verify the regional and system locale settings under Control Panel > Region > Administrative > Change system locale to ensure they match the expected character encoding (UTF-8 preferred).
• Restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) after applying locale or collation changes to ensure the parser reloads with updated settings.

Alternative or Verification Steps

• Re-run the original query or application call that triggered Error 9318 and confirm ISJSON() returns 1 for all affected rows.
• If the error persists, bypass strict parsing temporarily by wrapping the JSON text with OPENJSON(@json) WITH (...) using explicit schema definitions, which can sometimes reveal the exact malformed key/value pair that raw parsing misses.

Troubleshooting Guide: SQL Server Error 9316 – Invalid ‘xmlns’ Usage in Computed Attribute Constructor

Why This Error Happens

This error occurs when an XQuery expression in SQL Server attempts to use the reserved namespace prefix xmlns as part of a computed attribute constructor’s name expression, which violates the XQuery/XML specification. The XQuery parser explicitly disallows binding or referencing xmlns as an attribute name because it is reserved exclusively for namespace declarations, not for use as a regular attribute identifier. This typically surfaces in applications or scripts that dynamically generate XML using FOR XML queries or the native XML data type methods with improperly constructed node names.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Review the SQL Server error log or the client application’s query history to locate the exact T-SQL statement that triggered Error 9316.
• Search specifically for usage of .query(), .modify(), or .nodes() methods on xml typed columns or variables.
• Look for computed attribute constructors formatted like attribute {expression} {value} within the XQuery string.

Step 2: Correct the Attribute Name Expression

• Modify the query to ensure the name expression inside the attribute {} constructor does not resolve to the string xmlns.
• If dynamic naming is required, add a conditional check (e.g., CASE statement in T-SQL) to rename the attribute to a non-reserved value if the input variable equals xmlns.
• Example fix: Replace attribute {$name} {$value} with attribute {if ($name eq 'xmlns') then 'xmlns-attr' else $name} {$value}.

Step 3: Validate XML Schema Compliance

• Check the underlying XML Schema Collection (XSD) bound to the column, if applicable, using sys.xml_schema_collections in SSMS.
• Ensure the schema does not implicitly require an xmlns attribute to be generated via the computed constructor rather than a standard namespace declaration.
• Update the schema or the generating application code to use standard namespace declaration syntax (e.g., <root xmlns="uri">) instead of computed constructors for namespace binding.

Step 4: Restart the Query Execution

• Clear the procedure cache for the specific object using DBCC FREEPROCCACHE (plan_handle) to ensure the old faulty execution plan is purged.
• Re-execute the corrected query in a new session to confirm the parser accepts the syntax.

Alternative or Verification Steps

• To verify the fix, run the corrected query in SQL Server Management Studio (SSMS) and confirm that the XML output renders correctly without throwing Error 9316 in the Messages tab.
• If the error persists despite code changes, use SELECT @@VERSION to confirm you are running the latest Cumulative Update (CU) for SQL Server 2025, as this specific XQuery parsing behavior may be subject to compatibility level settings (ALTER DATABASE ... SET COMPATIBILITY_LEVEL).

Why This Error Happens

Error 9314 occurs when a SQL Server XQuery expression attempts to apply fn:data() or an implicit atomization operation (such as a value comparison, arithmetic operation, or cast) to an XML node that contains complex content—meaning the node has child elements, mixed content, or attributes rather than simple scalar text. SQL Server’s XQuery engine cannot automatically flatten a complex-content node into a single atomic value because doing so would be ambiguous. This is almost always a query design/schema issue rather than a server configuration or permissions problem, often introduced after an application update, a changed XML schema collection, or a migration to SQL Server 2025 where stricter XQuery compliance is enforced.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XQuery Expression

• Capture the exact query from SQL Server Management Studio (SSMS) or Extended Events (sql_batch_completed / rpc_completed) that throws Error 9314.
• Look for .value(), .query(), .exist(), or comparison operators (=, <, >) applied directly against an XML column or node without specifying a leaf-level path.
• Confirm the XML structure in question using:

SELECT xml_column.query('.') FROM YourTable WHERE id = @id;

Step 2: Correct the XPath to Target Leaf (Simple Content) Nodes

• Modify the XQuery path to point to the specific child text node instead of the parent complex element.
• Example fix — change:

SELECT xml_column.value('(/Root/Order)[1]', 'varchar(50)') FROM YourTable;

to:

SELECT xml_column.value('(/Root/Order/OrderID/text())[1]', 'varchar(50)') FROM YourTable;

• If multiple child elements exist, use fn:data() explicitly on the deepest text node rather than letting SQL Server atomize implicitly.

Step 3: Validate the XML Schema Collection (If Typed XML is Used)

• Open SSMS → Object Explorer → Databases → YourDB → Programmability → Types → XML Schema Collections.
• Confirm the schema does not define the queried node as a complexType when your query expects simpleType data.
• If the schema was recently altered, run:

SELECT name, definition FROM sys.xml_schema_collections WHERE name = 'YourSchemaName';

• Update the application or stored procedure logic to align with the actual schema definition.

Step 4: Rebuild Dependent Stored Procedures and Views

• After fixing the XQuery paths, recompile affected objects to clear stale execution plans:

EXEC sp_recompile 'YourStoredProcedureName';

• Deploy the corrected query logic through your standard release pipeline (SSDT/DACPAC) if the fix originates in source-controlled code.

Alternative or Verification Steps

• Re-run the original failing transaction or report in SSMS; Error 9314 should no longer appear, and the .value()/.query() call should return the expected scalar result.
• If the error persists, wrap the XQuery call in an explicit fn:data() function at the correct leaf node level, or cast the complex element to a derived simple-content view using .query() first, then .value() on the result.

Troubleshooting Guide: SQL Server Error 9315

Why This Error Happens

Error 9315 occurs when an XQuery expression in SQL Server attempts to use a dynamic or variable value (such as a column reference, parameter, or function result) as the name for a computed element or attribute constructor. SQL Server’s XML/XQuery engine mandates that the name portion of these constructors be resolved at compile time, meaning only literal, constant string values are permitted. This typically surfaces in applications performing XML data transformations after an upgrade or migration to SQL Server 2025, where legacy XQuery syntax may not have been validated against stricter parsing rules.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and check the Activity Monitor or SQL Server Logs (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to find the exact query triggering Error 9315.
• Look specifically for .query(), .value(), or .nodes() methods combined with element {} or attribute {} constructors in the T-SQL code.
• Use SELECT * FROM sys.dm_exec_query_stats cross-referenced with sys.dm_exec_sql_text to pinpoint the exact batch if the error occurs in a stored procedure.

Step 2: Refactor the XQuery Constructor Syntax

• Locate the dynamic variable being used inside the curly braces, for example: element {@DynamicName} { ... }.
• Since SQL Server requires a constant, you cannot pass a variable directly into the name slot. Refactor the logic to use a CASE statement that maps variables to hardcoded string literals, or restructure the query to use FOR XML PATH instead of native XQuery constructors if the element names must be dynamic.
• Example fix: Replace element {@var}{} with a static definition like element Root {} if the name is static, or pivot to generating the XML string via concatenation if dynamic names are strictly required.

Step 3: Update Compatibility Level and Rebuild Execution Plans

• Since this is running on SQL Server 2025, ensure the database compatibility level is correctly set to support the newer XQuery parser (should be 170 or higher).
• Run the following command to check and set the level:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

• Clear the procedure cache to ensure the old, erroring execution plan isn’t being reused: DBCC FREEPROCCACHE;.

Step 4: Validate XML Schema Collections

• If the query is binding to an XML Schema Collection (XSD), verify that the schema hasn’t been corrupted during the Windows Server 2025 migration.
• Check schema integrity using: SELECT * FROM sys.xml_schema_collections WHERE name = 'YourSchemaName';.
• Re-import the schema collection if it appears truncated or missing definitions using CREATE XML SCHEMA COLLECTION.

Alternative or Verification Steps

• Verification: Execute the modified query in isolation within a new SSMS query window. If the query returns the XML result set without throwing Error 9315, the constant expression fix was successful.
• Alternative: If dynamic naming is a hard business requirement, bypass native XQuery constructors entirely by building the XML output using string concatenation (FOR XML PATH('')) combined with QUOTENAME() to sanitize dynamic tag names before casting the final result back to the XML data type.

Troubleshooting Guide: SQL Server Error 9313

Why This Error Happens

This error occurs when SQL Server 2025 parses an XML attribute constructor within a FOR XML query (or XQuery expression) that contains more than one expression, or an expression combined with literal string values, inside a single attribute value. The SQL Server XML parsing engine requires attribute constructors to resolve to a single, unambiguous expression value rather than a concatenated mix of variables, columns, and strings. This typically surfaces after a migration or upgrade when legacy T-SQL code containing non-compliant XQuery syntax is executed against the newer SQL Server 2025 engine, which enforces stricter XML standards compliance than previous versions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact T-SQL statement that triggered Error 9313 using SQL Server Profiler or Extended Events (xml_deadlock_report won’t help here—use a generic error event filter on Error Number 9313).
• Review the query for FOR XML PATH or FOR XML EXPLICIT clauses, specifically looking at attribute definitions written like @attr = col1 + '-' + col2.
• Check any recently migrated stored procedures located in your database’s Programmability > Stored Procedures node in SQL Server Management Studio (SSMS) 21.

Step 2: Rewrite the Attribute Constructor Using CONCAT()

• Replace mixed string/expression concatenation inside the attribute constructor with the CONCAT() function so only a single resolved expression is passed.
• Example fix: change SELECT col1 + '_' + col2 AS "@AttributeName" FOR XML PATH('Root') to SELECT CONCAT(col1, '_', col2) AS "@AttributeName" FOR XML PATH('Root').
• Alternatively, pre-calculate the concatenated value in a derived table or Common Table Expression (CTE) before passing it into the FOR XML clause.

Step 3: Update Compatibility Level Settings

• Open SSMS and connect to the instance, then navigate to Database Properties > Options > Compatibility Level.
• Confirm the database is not unintentionally running under an older compatibility level (e.g., 150 or 160) that may interact unexpectedly with the new XML parser; set it to 170 for full SQL Server 2025 engine behavior using: ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170;
• Restart the SQL Server service via Services.msc on Windows Server 2025 if the setting does not immediately take effect.

Step 4: Validate XML Schema Collections

• If the attribute constructor is tied to a typed XML column, check the associated XML Schema Collection for outdated constraints using SELECT * FROM sys.xml_schema_collections;.
• Drop and recreate the schema collection if it references deprecated attribute typing rules from earlier SQL Server versions.

Alternative or Verification Steps

• Re-run the corrected query in SSMS and confirm the XML output generates successfully without Error 9313, then check the Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) to confirm no recurrence.
• If the error persists on complex nested queries, isolate the FOR XML clause into a standalone temporary stored procedure to test the CONCAT() fix in isolation before merging it back into the production codebase.

Why This Error Happens

Error 9311 occurs when a Transact-SQL statement attempts to build a sequence, array, or vector construct (such as the SQL Server 2025 ARRAY literal, JSON_ARRAY, or GENERATE_SERIES output) using elements of mixed, incompatible data types. The SQL Server 2025 query engine enforces strict type homogeneity for these new sequence-based constructs, so implicit conversions that worked in older row-based contexts are now rejected. This typically results from application code or migration scripts that concatenate values of different base types (e.g., INT and NVARCHAR, or DECIMAL and DATETIME2) into a single array/sequence expression without explicit casting.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact query text from the error dialog or from sys.dm_exec_query_stats using the associated sql_handle.
• Look specifically for ARRAY[...], JSON_ARRAY(...), GENERATE_SERIES(...), or table-valued constructor syntax in the statement.
• Run the query in SQL Server Management Studio (SSMS) 21 connected to the SQL Server 2025 instance to reproduce the error in isolation.

Step 2: Audit and Normalize Data Types

• Check each element being inserted into the sequence/array using SELECT SQL_VARIANT_PROPERTY(value, 'BaseType') to confirm actual underlying types.
• Explicitly cast every element to a single common type, for example:

  SELECT ARRAY[CAST(col1 AS DECIMAL(18,2)), CAST(col2 AS DECIMAL(18,2))]

• Avoid relying on implicit conversion between numeric, string, and temporal types inside the same array/sequence constructor.

Step 3: Update Application and ORM Layers

• Review any Entity Framework Core, Dapper, or custom data-access code that dynamically builds array parameters for SQL Server 2025.
• Ensure parameter binding passes a single consistent CLR type (e.g., all decimal or all string) per array parameter rather than object[].
• Redeploy the updated application binaries to the Windows Server 2025 / Windows 11 host and restart the application pool or service.

Step 4: Apply Latest Cumulative Update

• Open Windows Update or SQL Server Setup and confirm you are on the latest Cumulative Update (CU) for SQL Server 2025, since early builds had stricter-than-documented type-checking in sequence constructors.
• Path to check installed build: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\Setup\Version.
• Install any pending CU via Microsoft Update Catalog and restart the MSSQLSERVER service.

Alternative or Verification Steps

• Re-run the original query after casting all elements to a uniform type; confirm Error 9311 no longer appears in ERRORLOG (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• If the error persists, temporarily rewrite the sequence construct using a standard UNION ALL of explicitly typed SELECT statements as a stable workaround while the underlying array logic is refactored.

Troubleshooting Guide: SQL Server Error 9312 — XML Schema Element Restrictions

Why This Error Happens

Error 9312 occurs when a query attempts to apply an XQuery method, validation rule, or typed operation (such as xml.value(), xml.query(), or schema validation) against an XML element that is either untyped (simple type) or typed as anyType in its associated XML Schema Collection (XSD). SQL Server’s XML engine requires strongly-typed complex elements to perform certain operations like nilability checks, list validations, or specific casting functions, and it cannot resolve these actions against elements lacking a defined complex structure. This typically stems from an incomplete or overly generic XSD schema being registered in the database, or from application code calling methods incompatible with the schema design.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query and Schema Collection

• Capture the exact T-SQL statement throwing Error 9312 using SQL Server Profiler or Extended Events on the Windows Server 2025 instance.
• Run the following query to locate the XML Schema Collection bound to the column in question:

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

• Note which specific element or attribute is being referenced as anyType.

Step 2: Inspect and Correct the XML Schema Definition

• Extract the current schema using sys.xml_schema_collections or SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025).
• Open the .xsd file and search for the element causing the conflict; look for either a missing type attribute or an explicit declaration of xs:anyType.
• Update the schema to define a specific complex type instead of leaving it generic, for example:

  <xs:element name="OrderDetail" type="tns:OrderDetailType" />

• Avoid using <xs:element name="Data" /> without a type, as this defaults to anyType.

Step 3: Re-register the Updated XML Schema Collection

• Since SQL Server does not allow direct editing of an existing schema collection if it introduces conflicting types, drop and recreate it (ensure no production data depends on the old structure first):

  DROP XML SCHEMA COLLECTION dbo.YourSchemaCollectionName;
  GO
  CREATE XML SCHEMA COLLECTION dbo.YourSchemaCollectionName AS 
  N'<Paste Updated XSD Content Here>';
  GO

• If data exists in a typed XML column referencing this collection, export the data to untyped XML temporarily before dropping the schema, then re-import after recreation.

Step 4: Adjust the Application Query Logic

• If modifying the schema is not feasible (e.g., third-party schema), rewrite the T-SQL query to avoid methods restricted on anyType elements.
• Replace strict methods like .exist() combined with typed casting with a generic CAST AS VARCHAR(MAX) approach before processing, or use loosely typed XQuery functions that support anyType, such as basic nodes() traversal instead of value extraction requiring strict typing.

Alternative or Verification Steps

• Verify the fix: Re-run the original query or application transaction that triggered Error 9312. If the schema was corrected, the query should execute without the “not supported on simple typed or anyType elements” message.
• Alternative workaround: If schema modification is blocked by vendor restrictions, strip the xml_schema_namespace_id binding entirely by casting the column to untyped XML in your SELECT statement (e.g., CAST(XmlColumn AS XML)) to bypass the strict validation layer during read operations.

Troubleshooting Guide: SQL Server Error 9310 – “The ‘with’ clause of ‘replace value of’ cannot contain constructed XML”

Why This Error Happens

This error occurs when an XQuery modify() method using replace value of attempts to set a node’s value using a with clause that contains constructed XML (such as element or attribute constructors) rather than a simple scalar value or expression. SQL Server’s XML data type methods enforce a strict rule that replace value of can only insert singleton atomic values, text nodes, or simple expressions—it cannot accept a newly constructed XML fragment or element. This typically happens due to malformed or overly complex XQuery statements written against xml-typed columns, often introduced during application updates, ORM-generated queries, or manually written stored procedures that were not validated against SQL Server 2025’s XML schema handling engine.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XQuery Statement

• Review SQL Server error logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact query or stored procedure triggering Error 9310.
• Use SQL Server Profiler or Extended Events (xml_deadlock_report session templates can be adapted) to capture the exact T-SQL batch causing the failure in real time.
• Search application code or stored procedures for .modify('replace value of ...') patterns.

Step 2: Correct the XQuery Syntax

• Replace any constructed XML (e.g., <Node>{sql:column("value")}</Node>) inside the with clause with a plain scalar expression.
• Example of incorrect syntax:

  SET @xmlData.modify('replace value of (/Root/Node/text())[1] with <NewNode>Value</NewNode>')

• Example of corrected syntax:

  SET @xmlData.modify('replace value of (/Root/Node/text())[1] with "Value"')

• If you need to replace an entire element structure, use delete followed by insert instead of replace value of.

Step 3: Update Stored Procedures and Application Logic

• Open SQL Server Management Studio (SSMS) 21 (compatible with SQL Server 2025) and locate affected stored procedures via Object Explorer > Databases > [YourDB] > Programmability > Stored Procedures.
• Modify any procedure using ALTER PROCEDURE to replace the faulty XQuery with the corrected syntax from Step 2.
• Recompile the procedure using EXEC sp_recompile 'YourProcedureName'; to clear any cached execution plans referencing the old syntax.

Step 4: Validate XML Schema Collections (If Applicable)

• If the XML column uses a schema collection (XML(SchemaCollectionName)), verify the schema does not enforce complex type structures that conflict with scalar replacement.
• Run SELECT * FROM sys.xml_schema_collections; to review registered schemas tied to the affected table/column.

Step 5: Test in a Non-Production Environment

• Deploy the corrected script to a staging instance of SQL Server 2025 running on Windows Server 2025 or Windows 11.
• Execute the modified stored procedure or ad hoc query to confirm Error 9310 no longer appears.

Alternative or Verification Steps

• Run the corrected query directly in SSMS and confirm it executes without error, then check the resulting XML output using SELECT @xmlData; to ensure the value was updated correctly.
• If replace value of continues to cause issues for structural changes, use the delete and insert XQuery methods combination instead, as they natively support constructed XML fragments.

Troubleshooting Guide: SQL Server Error 9309

Why This Error Happens

Error 9309 occurs when SQL Server’s XQuery/XML processing engine encounters a replace value of statement targeting a node that is typed as xs:anySimpleType, which is an abstract base type that cannot be directly modified. This typically happens when XML data has been cast implicitly to a generic schema type rather than a concrete, specific type (such as xs:string or xs:integer) during an .modify() call on an xml typed column. The root cause is almost always a mismatch between an untyped or loosely-typed XML instance and a strict XML DML (Data Modification Language) operation that requires a concrete node type to perform the value replacement.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML Column and Query

• Locate the specific stored procedure, trigger, or ad-hoc query reported in the SQL Server error log alongside Event ID 9309.
• Run the query in SQL Server Management Studio (SSMS) in isolation to confirm it reproduces the error.
• Check if the column is bound to an XML Schema Collection using: SELECT xml_schema_namespace(schema_name, collection_name) FROM sys.xml_schema_collections;

Step 2: Cast the Target Node to a Concrete Type

• Modify your .modify('replace value of ...') syntax to explicitly cast the node using the sql:column() or sql:variable() function combined with a strict type.
• Update the XQuery expression to force a specific type, for example: replace value of (/root/node/@attr)[1] with cast(@NewValue as xs:string).
• Avoid using generic variable assignments without an explicit CAST or CONVERT in the XML DML with clause.

Step 3: Validate or Rebuild the XML Schema Collection

• If the XML column uses a Typed XML schema, verify the schema collection isn’t allowing xs:anySimpleType as a wildcard for the specific element being modified.
• Use ALTER XML SCHEMA COLLECTION to add a specific type definition if the current schema defaults to the abstract base type.
• Restart the SQL Server service via services.msc on Windows Server 2025 only if schema changes do not take effect immediately due to query plan caching.

Step 4: Clear the Procedure Cache

• If the error persists due to a stale execution plan referencing the old schema, clear the specific plan cache.
• Run the command: DBCC FREEPROCCACHE; (Note: use with caution in production as this clears all cached plans server-wide).
• Re-execute the original query to see if the explicit cast resolved the type mismatch.

Alternative or Verification Steps

• Verification: Re-run the original UPDATE or .modify() statement that triggered Error 9309; if successful, check the data using SELECT ColumnName.query('.') to confirm the value was replaced correctly in the XML structure.
• Alternative: If casting fails, shred the XML into a relational temporary table using OPENXML or nodes(), update the relational data, and then reconstruct the XML using FOR XML PATH to bypass the strict typing constraints of in-place XML DML.

Why This Error Happens

Error 9306 occurs when an XML DML replace value of statement targets a node whose schema-defined type is a union type (i.e., the node can validate against more than one simple type per an associated XML Schema Collection). SQL Server’s XQuery engine cannot guarantee a single deterministic type for the replacement value, so it rejects the operation outright. This typically surfaces in Microsoft SQL Server 2025 when XML columns are bound to a XML SCHEMA COLLECTION containing <xs:union> definitions, and a query or application attempts to directly modify a node value using .modify('replace value of ...').

Step-by-Step Resolution Guide

Step 1: Identify the Offending XML DML Statement

• Enable SQL Server Profiler or Extended Events on the instance to capture the exact T-SQL batch triggering Error 9306.
• Look for .modify('replace value of (/path)[1] with "value"') calls in application code, stored procedures, or ORM-generated queries.
• Cross-reference the targeted node against its bound XML Schema Collection using sys.xml_schema_collections in SQL Server Management Studio (SSMS).

Step 2: Inspect the XML Schema Collection for Union Types

• Run the following query to export the schema definition:

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

• Search the output for <xs:union memberTypes="..."> elements — this confirms the node in question is a union type.
• Confirm the exact element/attribute name causing the conflict.

Step 3: Rewrite the XML DML Operation

• Replace the direct replace value of call with a delete followed by an insert operation, which does not require strict type resolution:

  SET @xmlVar.modify('delete (/Root/Node)[1]');
  SET @xmlVar.modify('insert <Node>NewValue</Node> into (/Root)[1]');

• Alternatively, cast the replacement value explicitly to one of the union’s member types using sql:variable() or xs:string() in the XQuery expression to remove ambiguity.
• Update any stored procedures or application data-access layers (Entity Framework, Dapper, etc.) that generate the original .modify() call.

Step 4: Validate or Modify the XML Schema Collection

• If you control the schema, consider refactoring the <xs:union> definition into a single concrete simple type if business logic allows it, reducing future ambiguity.
• Use ALTER XML SCHEMA COLLECTION to apply additive changes without dropping dependent objects:

  ALTER XML SCHEMA COLLECTION dbo.YourSchemaCollectionName ADD N'<xsd namespace content>';

• Re-test the original .modify() statement against the updated schema in a non-production environment first.

Alternative or Verification Steps

• Re-execute the original failing query/application transaction against a test database restored from a recent backup to confirm Error 9306 no longer appears in the SQL Server Error Log or Windows Event Viewer (Applications and Services Logs > MSSQLSERVER).
• If rewriting the XQuery is not feasible, as a workaround, deserialize the XML into a strongly-typed object in the application layer (C#/.NET), modify the value there, and re-serialize the full XML document back into the column instead of using in-place .modify() DML.

Troubleshooting Guide: SQL Server Error 9308

Why This Error Happens

Error 9308 occurs when an XQuery or XML data type method (such as .value(), .query(), or comparison operators within XML data) receives an argument that isn’t a single numeric primitive type or untypedAtomic. This typically happens when a query attempts to cast or compare an XML node sequence (such as a node-set or complex element) against a function expecting a scalar numeric value. It is almost always a query-authoring issue rather than a server configuration, corruption, or permissions problem.

Step-by-Step Resolution Guide

Step 1: Identify the Offending XQuery Expression

• Review the SQL Server error log or the client-side error message for the exact statement/stored procedure referenced alongside Error 9308.
• Extract the XQuery expression passed to .value(), .exist(), or .query() methods on the XML column in question.
• Example of a problematic pattern:

  SELECT XmlCol.value('(/Root/Item)', 'int') FROM dbo.YourTable;

If /Root/Item returns multiple nodes or a non-numeric node, this will trigger Error 9308.

Step 2: Correct the XPath to Return a Single Atomic Value

• Modify the XPath expression to explicitly target a single node using indexing, e.g., (/Root/Item)[1].
• Ensure the target node actually contains numeric or untyped atomic content (not nested elements or attributes).
• Updated example:

  SELECT XmlCol.value('(/Root/Item)[1]', 'int') FROM dbo.YourTable;

• If the value may be non-numeric or missing, add a TRY_CAST or TRY_CONVERT wrapper to avoid type mismatches:

  SELECT TRY_CAST(XmlCol.value('(/Root/Item)[1]', 'nvarchar(50)') AS INT) FROM dbo.YourTable;

Step 3: Validate the XML Schema and Data Types

• Check if the XML column is schema-bound (typed XML); if so, confirm the schema collection defines the node as a numeric simple type.
• Run SELECT XmlCol.query('/Root/Item') separately to inspect the raw node content before casting.
• If the node unexpectedly contains child elements or mixed content, update the upstream data generation process or application logic feeding the XML column.

Step 4: Test and Deploy the Fix

• Run the corrected query in SQL Server Management Studio (SSMS) or Azure Data Studio against a test environment first.
• Confirm no Error 9308 is raised and the expected scalar value is returned.
• Deploy the corrected stored procedure, view, or ad-hoc query to production during a low-traffic maintenance window.

Alternative or Verification Steps

• Verify the fix by executing the corrected query multiple times against rows known to have varying XML structures (including edge cases with missing or multiple nodes) to confirm consistent, error-free results.
• If the issue persists across many queries, consider adding a computed column or indexed view that pre-extracts and validates the numeric value, reducing repeated inline XQuery evaluation and the risk of type mismatches.

Troubleshooting Guide: Error 9305 – Invalid ‘instance of’ Target Type Syntax

Why This Error Happens

This error occurs when SQL Server 2025’s query parser encounters an instance of clause where the target type reference is malformed or uses an unsupported syntax pattern. The engine strictly requires that type names used in this context be followed by a nullable indicator (?) to explicitly define the comparison behavior against polymorphic or CLR-based types. This typically stems from outdated T-SQL scripts, incompatible ORM-generated queries, or a corrupted query plan cache that is referencing legacy type-checking syntax no longer valid in this release.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) version 21 or later, as it includes the updated IntelliSense for SQL Server 2025 syntax.
• Run the following command to check the current execution context and isolate the error-causing statement from the Recent Activity pane:

SELECT * FROM sys.dm_exec_query_stats WHERE last_execution_time > DATEADD(minute, -10, GETDATE());

• Look specifically for instance of keywords within stored procedures or dynamically generated SQL strings.

Step 2: Correct the Type Syntax

• Locate the specific line in the script containing the instance of clause.
• Modify the type reference to include the mandatory nullable suffix. For example, change:
  WHERE Column1 instance of MyCustomType
  To:
  WHERE Column1 instance of MyCustomType?
• Save the script and clear the local cache in SSMS using Ctrl + Shift + Del before re-executing.

Step 3: Clear the Procedure Cache

• If the error persists despite correcting the script, the old execution plan may be cached. Flush the specific database cache using:

DBCC FLUSHPROCINDB (<database_id>);

• Alternatively, perform a full server-level cache clear (use with caution in production):

DBCC FREEPROCCACHE;

Step 4: Update Database Compatibility Level

• Open a New Query window in SSMS and verify the current compatibility level of the database in question.
• Ensure the database is set to the latest compatibility level supported by SQL Server 2025 (170) to ensure the parser correctly interprets the new nullable-type syntax requirements:

ALTER DATABASE [YourDatabaseName] SET COMPATIBILITY_LEVEL = 170;

Alternative or Verification Steps

• Verification: Re-run the specific query or stored procedure that triggered Error 9305. If the syntax was corrected properly, the query should execute without throwing the parser exception, and results should return as expected.
• Alternative: If the error originates from a third-party application or ORM (like Entity Framework) generating the faulty SQL, check for a hotfix or updated driver package, as the application may need to be updated to generate SQL Server 2025-compliant syntax.

Troubleshooting Guide: SQL Server Error 9303 – “Syntax error near ‘%ls'”

Why This Error Happens

Error 9303 occurs when SQL Server’s XML or query parser encounters malformed or unexpected syntax, most commonly within XML schema collections, XQuery expressions, or dynamic SQL statements constructed with invalid tokens. This typically stems from incorrectly formatted XML data being passed into XML-typed columns or variables, mismatched quotation marks in dynamically generated T-SQL, or corrupted/truncated query strings sent from an application layer. It can also surface after an in-place upgrade to SQL Server 2025 if legacy T-SQL code relies on deprecated syntax patterns no longer parsed the same way.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending Query or XML Fragment

• Enable SQL Server Profiler or Extended Events (xe_query_errors session) to capture the exact statement text triggering Error 9303.
• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full %ls token substitution, which indicates the specific problematic character or keyword.
• If the error originates from an application, enable verbose logging in the app’s data access layer to capture the raw query string before execution.

Step 2: Validate XML Syntax and Schema Collections

• If the error involves XML data types, run the fragment through SELECT CAST('<your_xml_fragment>' AS XML); in SSMS to isolate parser failures.
• Check for unescaped special characters (&, <, >) in XML payloads; ensure they are properly encoded as &, <, >.
• Review any associated XML Schema Collections via sys.xml_schema_collections to confirm they haven’t been altered or corrupted during migration.

Step 3: Audit Dynamic SQL Construction

• Inspect stored procedures or application code using EXEC() or sp_executesql for improper string concatenation, especially missing single quotes around variables.
• Replace manual string concatenation with parameterized queries using sp_executesql and proper @parameter bindings to eliminate quoting errors entirely.
• Rebuild any affected stored procedures using ALTER PROCEDURE after correcting syntax, then recompile with EXEC sp_recompile 'YourProcedureName';.

Step 4: Check for Compatibility Level Issues Post-Upgrade

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database compatibility level.
• If legacy syntax is required temporarily, consider testing with ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160; (or the prior version level) to confirm whether the 2025 parser is the cause.
• Update the offending T-SQL to use current syntax standards rather than permanently downgrading compatibility level.

Step 5: Repair or Reinstall Corrupted Query Cache (If Persistent)

• Clear the plan cache for the affected database using DBCC FREEPROCCACHE; to rule out a cached corrupted execution plan.
• If the issue persists only on one server instance, use the Windows Server 2025 Programs and Features panel to run a Repair on the SQL Server 2025 installation.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after the repair completes.

Alternative or Verification Steps

• After applying fixes, re-run the original query or XML insert operation in SSMS and confirm no Error 9303 appears in the Messages tab or ERRORLOG.
• If the error persists only under specific application load, use SQL Server Extended Events to capture a live trace during peak usage and compare the captured statement against a manually tested version in SSMS to confirm parity.

Troubleshooting Guide: SQL Server Error 9304 – Unsupported XQuery Version

Why This Error Happens

This error occurs when a query, stored procedure, or application explicitly declares an XQuery prolog version other than 1.0 (such as 1.1 or 2.0) when working with the xml data type methods (.query(), .value(), .nodes(), .exist()). SQL Server 2025’s XML engine is built on a fixed XQuery processor that strictly adheres to the W3C XQuery 1.0 specification, and it does not support newer XQuery standards regardless of the host OS (Windows Server 2025 or Windows 11). This typically surfaces after migrating code from another XML processing platform (like a custom .NET XML library or a different RDBMS) that defaulted to a later XQuery spec, or from a copy-pasted XQuery prolog declaration left in legacy T-SQL code.

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 9304.
• Run the following to capture recent XML-related errors if you have Query Store enabled:

  SELECT qt.query_sql_text, rs.last_execution_time
  FROM sys.query_store_query_text qt
  JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_runtime_stats rs ON q.query_id = rs.query_id
  WHERE qt.query_sql_text LIKE '%xquery version%';

• Look specifically for a prolog declaration pattern like: xquery version "1.1"; embedded inside .query('...') or .value('...') calls.

Step 2: Correct the XQuery Version Declaration

• Open the T-SQL script or stored procedure definition identified in Step 1.
• Locate the XQuery prolog (usually the very first line inside the quoted XQuery expression) and change it explicitly to version 1.0:

  SELECT XMLColumn.query('xquery version "1.0"; /root/element')
  FROM YourTable;

• If no version is required for your logic, simply remove the prolog declaration entirely — SQL Server defaults to 1.0 automatically, avoiding future hardcoding issues.

Step 3: Audit Linked Applications and ORM-Generated Queries

• If the query originates from an application (e.g., Entity Framework, a custom .NET XML serializer, or a migrated Java/XQuery library), check the application’s XML-generation layer or mapping templates.
• Search application source code or generated SQL logs for the string xquery version and update the hardcoded version string to "1.0".
• Redeploy the corrected stored procedures or application binaries to your Windows Server 2025 instance and restart the SQL Server service via services.msc or:

  Restart-Service -Name MSSQLSERVER -Force

Step 4: Rebuild Affected Stored Procedures and Clear Plan Cache

• After editing the procedure definitions, recompile them to ensure no stale execution plans reference the old XQuery version string:

  EXEC sp_recompile 'YourStoredProcedureName';

• Optionally clear the plan cache for the database if multiple objects were affected:

  DBCC FREEPROCCACHE;

Alternative or Verification Steps

• Re-run the original query or execute the stored procedure manually in SQL Server Management Studio (SSMS) connected to your SQL Server 2025 instance — it should now return XML results without raising Error 9304.
• If the error persists, use SQL Server Profiler or Extended Events to trace xml_deadlock_report and sql_statement_completed events filtered on the XML column name, confirming no other embedded prolog declarations were missed in dynamic SQL or linked server queries.

Troubleshooting Guide: SQL Server Error 9302 – Invalid Context Item for fn:id()

Why This Error Happens

This error occurs when an XQuery expression invokes the fn:id() function against a context item that is not a node type, typically a scalar value, string literal, or an atomic value derived from an XML data type method. SQL Server’s XQuery engine strictly enforces the XPath/XQuery data model, which requires fn:id() to operate within the context of a node (such as an element or attribute) in order to resolve ID references within the document tree. This commonly happens due to malformed XQuery expressions in application code, corrupted stored procedures, or improperly cast XML variables where the node context is lost before the function call is executed.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Capture the exact T-SQL statement using SQL Server Profiler or Extended Events, filtering for Error Number 9302.
• Look specifically for .query(), .value(), or .nodes() methods on xml typed columns or variables combined with fn:id(...).
• Check recent deployments or ORM-generated queries (Entity Framework, Dapper) that might dynamically construct XML paths.

Step 2: Correct the XQuery Context Binding

• Modify the query to ensure fn:id() is called directly on a node-set rather than a string result.
• Avoid chaining fn:id() immediately after a string() or data() cast, as this strips the node context.
• Example fix: Change data(.)/id("123") to a proper node-based path like //*[@id="123"] if the intent is simple ID matching, since fn:id() requires specific DTD/Schema ID type definitions which SQL Server’s XML type rarely enforces.
• If using a variable, ensure it is explicitly typed as xml and not implicitly converted to nvarchar prior to the query execution.

Step 3: Validate XML Schema Collections (XSCs)

• If the table column uses a Typed XML schema, verify that the schema correctly defines the attribute as ID type.
• Run the following to check associated schema collections:

SELECT name, xml_collection_id 
FROM sys.xml_schema_collections 
WHERE schema_id = SCHEMA_ID('dbo');

• If the schema is missing the ID type definition for the attribute referenced, update the XSD and re-register the schema collection using CREATE XML SCHEMA COLLECTION with the ALTER permission.

Step 4: Rebuild and Test the Query Logic

• Isolate the specific line of code in SSMS (SQL Server Management Studio) and execute it independently against a test dataset.
• Replace the fn:id() logic with standard XPath node selection (e.g., sql:variable() or let clauses in FLWOR expressions) if the ID-specific lookup is not strictly required by business logic.
• Deploy the corrected stored procedure or ad-hoc script to the production environment during a maintenance window.

Alternative or Verification Steps

• Verification: Re-run the original transaction or application workflow that triggered the error; monitor the Windows Event Viewer (Application Log) on the Windows Server 2025 host to confirm Event ID 9302 no longer appears.
• Alternative: If modifying the XQuery is not immediately feasible, wrap the specific XML processing logic in a TRY...CATCH block to gracefully handle the exception and log the malformed XML instance for manual review rather than halting the entire transaction.

Troubleshooting Guide: SQL Server Error 9300

Why This Error Happens

Error 9300 (“%s In this version of the server…”) typically occurs when a deprecated or unsupported feature, function, or syntax is invoked against a SQL Server 2025 instance. This often happens after an in-place upgrade or migration from an older SQL Server version, where legacy T-SQL objects, extended stored procedures, or compatibility-level features are no longer supported. It can also surface when third-party applications or linked server calls rely on APIs that Microsoft has officially removed in this release.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Feature Triggering the Error

• Open SQL Server Management Studio (SSMS) and reproduce the error to capture the full error message text (the “%s” placeholder will show the specific unsupported feature).
• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the full stack and session context.
• Cross-reference the feature name against Microsoft’s official Deprecated Database Engine Features documentation for SQL Server 2025.

Step 2: Check and Adjust Database Compatibility Level

• Run the following query to check the current compatibility level:

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

• If the database was migrated from an older version, update it to a supported level:

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

• Test the failing query or procedure again after changing the compatibility level.

Step 3: Update or Replace Deprecated Code

• Locate stored procedures, triggers, or application code using removed syntax (common culprits: sp_dboption, old BACKUP LOG WITH NO_LOG, deprecated system tables like sysobjects direct writes, or legacy XML/FILESTREAM calls).
• Rewrite the offending code using the modern equivalent recommended in SQL Server 2025 documentation (e.g., replace deprecated system stored procedures with their catalog view equivalents).
• Deploy the updated script to a test/staging environment on Windows Server 2025 before pushing to production.

Step 4: Validate Server and Instance Configuration

• Confirm the SQL Server instance is fully patched: check Help > About in SSMS or run SELECT @@VERSION;.
• Ensure no legacy Database Engine features were manually re-enabled via sp_configure with the “show advanced options” flag, as some deprecated features may still exist but are disabled by default.
• Restart the SQL Server service via SQL Server Configuration Manager after applying any configuration changes.

Alternative or Verification Steps

• Verify the fix by re-running the original query/application call that triggered Error 9300 and confirming it executes without the “%s” message appearing in SSMS or the Error Log.
• If the issue persists, use the Database Engine Tuning Advisor or sys.dm_os_sys_info combined with Extended Events to trace the exact batch/session triggering the deprecated call, then isolate and quarantine that application module for a code-level fix.

Troubleshooting Guide: SQL Server Error 9301 — “In this version of the server…”

Why This Error Happens

Error 9301 typically occurs when a database that was created or last used on a newer build of SQL Server is attached to, restored on, or accessed by an older or incompatible instance — in this case, features or metadata catalogs unsupported by the current SQL Server 2025 build. This is often triggered by internal system catalog corruption, an interrupted upgrade process, or a mismatch between the database’s internal version number and the engine’s supported version range. It can also surface when system metadata becomes inconsistent due to improper shutdowns or incomplete patch/Cumulative Update (CU) installations on Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Build and Database Compatibility Level

• Open SQL Server Management Studio (SSMS) and run SELECT @@VERSION; to confirm the exact build number of your SQL Server 2025 instance.
• Run SELECT name, compatibility_level FROM sys.databases; to check if the affected database’s compatibility level exceeds what your current engine build supports.
• Cross-reference the build number against Microsoft’s official SQL Server 2025 release notes to confirm whether a required Cumulative Update (CU) is missing.

Step 2: Apply the Latest Cumulative Update (CU)

• Navigate to the Microsoft Update Catalog or use Windows Update on Windows Server 2025 to check for pending SQL Server 2025 updates.
• Download and install the latest CU package (e.g., SQLServer2025-KB*****-x64.exe) matching your edition.
• Restart the SQL Server service via SQL Server Configuration Manager after installation:
• net stop MSSQLSERVER
• net start MSSQLSERVER

Step 3: Run Database Consistency Checks (DBCC CHECKDB)

• Open a new query window in SSMS and execute:
• DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
• Review the output for any system catalog corruption flags related to internal version metadata.
• If corruption is confirmed, attempt a targeted repair:
• DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); (only after taking a full backup first)

Step 4: Detach and Re-Attach the Database on a Matching Engine Version

• Take the database offline safely:
• ALTER DATABASE YourDatabaseName SET OFFLINE;
• Copy the .mdf and .ldf files to a SQL Server instance matching the original source version, if available.
• Use sp_detach_db and sp_attach_db procedures to re-register the database cleanly once version alignment is confirmed.

Step 5: Restore from a Compatible Backup

• If the database remains inaccessible, restore from the most recent backup taken on a compatible SQL Server 2025 build:
• RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDatabaseName.bak' WITH REPLACE;
• Confirm the backup’s origin version using RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDatabaseName.bak'; before restoring.

Alternative or Verification Steps

• After applying fixes, run SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Version'); to confirm the internal version number is now supported by your SQL Server 2025 build.
• If the error persists, consider provisioning a clean SQL Server 2025 instance with the latest CU pre-installed, then migrate data using the Import/Export Wizard or Azure Data Studio as an alternative to direct attach/restore operations.

Troubleshooting Guide: SQL Server Error 9245 – Query Notification Errors Suppressed

Why This Error Happens

Error 9245 occurs when SQL Server’s Service Broker subsystem, which handles Query Notifications, encounters a high volume of internal errors (such as malformed messages, dialog failures, or queue overflows) in a short period of time. To prevent error log flooding and performance degradation, SQL Server automatically suppresses repeated logging of these notification errors after a threshold is reached. The underlying root cause is typically a misconfigured or disabled Service Broker, expired or invalid notification subscriptions, orphaned queue messages, or network/certificate issues preventing dialog conversations from completing successfully.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check Service Broker status:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Ensure no application connections are active during this operation to avoid timeouts.

Step 2: Inspect and Clear the Query Notification Queues

• Check the system queues for backlog or poison messages:

SELECT * FROM sys.transmission_queue;
SELECT * FROM sys.conversation_endpoints WHERE far_service = 'SqlQueryNotificationService-%';

• Identify any conversations stuck in CLOSED or DISCONNECTED_INBOUND states and end them manually:

END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

• Restart the SQL Server Service Broker task via SQL Server Configuration Manager if queues remain unresponsive.

Step 3: Validate Permissions and Certificates

• Confirm the service account running SQL Server has SUBSCRIBE QUERY NOTIFICATIONS permission:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourAppLoginName];

• On Windows Server 2025/Windows 11, check that the SQL Server Database Engine service (via services.msc) is running under an account with network access if notifications traverse endpoints.
• If using encrypted Service Broker endpoints, verify certificates haven’t expired under Security > Certificates in SQL Server Management Studio (SSMS).

Step 4: Review and Adjust Error Log Verbosity (Optional)

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to review suppressed error patterns.
• If suppression is masking a deeper issue, enable trace flag 1222 temporarily for deadlock/notification diagnostics:

DBCC TRACEON (1222, -1);

• Disable the trace flag once root cause is identified to avoid unnecessary log growth.

Alternative or Verification Steps

• After applying fixes, monitor sys.dm_broker_queue_monitors and the SQL Server error log for 24-48 hours to confirm Error 9245 no longer recurs.
• If the issue persists, consider rebuilding the Query Notification subscription on the client application side, as a corrupted cache on the application tier can continuously trigger failed dialog requests regardless of server-side fixes.

Troubleshooting Guide: SQL Server Error 9246 – Distributed Exchange Row/Byte Reporting

Why This Error Happens

Error 9246 is actually an informational message, not a true failure condition — it is logged by the SQL Server Parallel Query Execution engine to report the number of rows and bytes moved between Exchange operators in a distributed or parallel query plan. It commonly surfaces (and gets mistaken for an error) when verbose tracing or Extended Events diagnostics are enabled on parallel query plans, or when a linked server/distributed query triggers high-volume data movement across threads. In rare cases, it appears alongside actual performance degradation if the Exchange operator buffers are mismanaged due to memory pressure, outdated statistics, or a misconfigured Max Degree of Parallelism (MAXDOP) setting on Windows Server 2025 / Windows 11 hosts.

Step-by-Step Resolution Guide

Step 1: Confirm the Message is Informational, Not a Blocking Error

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log (Management > SQL Server Logs).
• Confirm Severity Level is 10 (informational) and not 16+ (actual error).
• Cross-reference with Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.

Step 2: Review and Adjust MAXDOP and Parallelism Settings

• Run the following query to check current server-level MAXDOP:

  EXEC sp_configure 'show advanced options', 1;
  RECONFIGURE;
  EXEC sp_configure 'max degree of parallelism';

• Set MAXDOP according to Microsoft’s NUMA/core guidance (typically ≤ 8 on modern multi-core Windows Server 2025 hosts):

  EXEC sp_configure 'max degree of parallelism', 4;
  RECONFIGURE;

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

  Restart-Service -Name MSSQLSERVER -Force

Step 3: Disable Unneeded Verbose Tracing / Extended Events Sessions

• Open Extended Events in SSMS (Management > Extended Events > Sessions).
• Locate and stop any active session capturing exchange_spill or query_parallelism events, as these generate excessive 9246 logging.
• Use T-SQL to stop a session manually:

  ALTER EVENT SESSION [YourSessionName] ON SERVER STATE = STOP;

Step 4: Update Statistics and Rebuild Fragmented Indexes

• Run the following to refresh outdated statistics causing inefficient parallel row distribution:

  EXEC sp_updatestats;

• Rebuild indexes on large tables involved in the distributed query:

  ALTER INDEX ALL ON [YourTableName] REBUILD;

Step 5: Validate Linked Server and Network Configuration (If Distributed Query Involved)

• Confirm linked server connectivity under Server Objects > Linked Servers in SSMS.
• Check for network latency or packet loss between nodes using:

  Test-NetConnection -ComputerName <RemoteServerName> -Port 1433

• Ensure firewall rules on Windows Server 2025 allow SQL Server TCP port 1433 (or custom port) bidirectionally.

Alternative or Verification Steps

• Re-run the original query with Actual Execution Plan enabled in SSMS and confirm the Exchange operator no longer shows abnormal row/byte skew between threads.
• If the message persists with performance issues, temporarily set MAXDOP = 1 on the specific query using a query hint (OPTION (MAXDOP 1)) to isolate whether parallelism — rather than data volume — is the root cause.

Troubleshooting Guide: Error 9244 — Query Notification Cleanup Could Not Access Database Metadata

Why This Error Happens

Error 9244 occurs when SQL Server’s background cleanup task for Service Broker query notifications (Query Notification Cleanup Task) attempts to access system metadata for a database that is not currently in an ONLINE state. This typically happens when a database is in RESTORING, SUSPECT, OFFLINE, or RECOVERY_PENDING state, or when the database was dropped/detached while Service Broker queues still held active subscription references. It can also surface after an incomplete restore operation or a failover event where the metadata cache became stale relative to the actual database state.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or a query window and run:

  SELECT name, state_desc, is_broker_enabled 
  FROM sys.databases;

• Confirm whether the affected database shows ONLINE, RESTORING, SUSPECT, or OFFLINE in the state_desc column.
• Cross-reference the database ID (%d in the error) using:

  SELECT database_id, name FROM sys.databases WHERE database_id = <ID_from_error>;

Step 2: Complete or Repair the Restore Operation

• If the database is stuck in RESTORING, check if a restore sequence was left incomplete (missing WITH RECOVERY clause):

  RESTORE DATABASE [YourDB] WITH RECOVERY;

• If the database is SUSPECT, run DBCC CHECKDB to assess corruption:

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

• Review the SQL Server error log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for corresponding restore failure messages.

Step 3: Disable and Re-enable Service Broker on the Database

• If the database is online but notifications are still failing, Service Broker’s internal GUID may be mismatched. Run:

  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  GO
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;
  GO

• This forces SQL Server to refresh the Broker metadata and clear orphaned notification subscriptions tied to the old database state.

Step 4: Clear Orphaned Notification Queues

• Query the notification subscription table to identify stale entries:

  SELECT * FROM sys.dm_qn_subscriptions;

• If orphaned subscriptions reference the problematic database, restart the SQL Server service to force the Query Notification Cleanup Task to re-initialize against the corrected metadata:
• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click → Restart.

Step 5: Validate Permissions on System Metadata

• Ensure the SQL Server service account has not lost permissions on the master or msdb system databases (common after a Windows Server 2025 security policy update).
• Check via:

  SELECT * FROM fn_my_permissions('master', 'DATABASE');

• Re-grant VIEW SERVER STATE and CONNECT permissions if missing, using SQL Server Configuration Manager to confirm the service account identity under SQL Server Services properties.

Alternative or Verification Steps

• After applying fixes, re-run SELECT name, state_desc FROM sys.databases; and confirm the database reports ONLINE; monitor the SQL Server error log for 30 minutes to confirm Error 9244 no longer recurs.
• If the issue persists, as a fallback, perform a full database backup, then drop and restore the database from a known-good backup file to rebuild clean Service Broker metadata from scratch.

Troubleshooting Guide: SQL Server Error 9242 – Query Notification Delivery Failure (Dialog Endpoint Error)

Why This Error Happens

Error 9242 occurs when Service Broker cannot locate or access the dialog endpoint associated with a query notification subscription, typically because the underlying conversation has been prematurely ended, timed out, or the dialog’s security context has become invalid. This often stems from Service Broker being disabled on the database, the message queue hitting its retention limits, or certificates/permissions used for dialog security becoming corrupted or expired. It can also occur after a database restore or mirroring failover where dialog endpoints reference broker instance IDs that no longer match the current database.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check the broker status:

  SELECT name, is_broker_enabled, service_broker_guid 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• On Windows Server 2025, ensure no active connections are blocking this operation; use WITH ROLLBACK IMMEDIATE if necessary during a maintenance window.

Step 2: Inspect and Clear Orphaned Conversations

• Query the sys.conversation_endpoints view to identify dialogs stuck in an inconsistent state:

  SELECT conversation_handle, state, state_desc, lifetime 
  FROM sys.conversation_endpoints;

• End any conversations showing state_desc as DISCONNECTED_INBOUND or DISCONNECTED_OUTBOUND:

  END CONVERSATION 'conversation_handle_value' WITH CLEANUP;

• Restart the SQL Server Service Broker task via SQL Server Configuration Manager if dialogs remain stuck after cleanup.

Step 3: Validate Certificates and Dialog Security

• Check if the error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) references certificate expiration or master key issues.
• Verify the Service Broker master key is accessible:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• If certificates have expired, regenerate them using CREATE CERTIFICATE and re-establish the Remote Service Binding for the affected dialog.

Step 4: Reset the Query Notification Infrastructure

• Drop and recreate the Service Broker queue used for notifications (commonly SqlQueryNotificationService-<GUID>):

  DROP QUEUE [dbo].[SqlQueryNotificationService-xxxx];

• Restart the application pool (if using ASP.NET SqlDependency) or restart the client application to force re-registration of the notification subscription.
• Ensure the Windows 11/Server 2025 firewall allows internal TCP traffic for Service Broker endpoints (default port 4022) if conversations span instances.

Alternative or Verification Steps

• Verify the Fix: Re-run the application query that triggers the notification and monitor sys.dm_broker_queue_monitors to confirm the queue is actively receiving activation signals without generating new 9242 errors in the SQL ERRORLOG.
• Alternative Fix: If the issue persists after certificate and conversation cleanup, perform a full Service Broker reset by running ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE (Note: this action permanently removes all existing conversations and should only be used in non-production or isolated testing scenarios).

Troubleshooting Guide: Error 9243 — Query Notification Delivery Failure in SQL Server

Why This Error Happens

Error 9243 occurs when SQL Server’s Service Broker cannot deliver a query notification message because the underlying dialog conversation has been terminated, corrupted, or blocked. This typically results from Service Broker being disabled on the database, expired or missing conversation endpoints, or network/certificate issues preventing dialog security from completing the handshake. It can also surface when the SqlDependency or Query Notification feature loses its queue or service due to a database restart, failover, or manual cleanup of Service Broker objects.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check the status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: This command requires exclusive access, so ensure no active connections are blocking it (use WITH ROLLBACK IMMEDIATE if necessary in a maintenance window).

Step 2: Inspect and Clean Up Broken Conversation Endpoints

• Query the current conversation endpoints to identify ones stuck in an error state:

  SELECT * FROM sys.conversation_endpoints WHERE state = 'ER';

• End any faulty dialogs manually to force cleanup:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server Agent and Database Engine services via Services.msc on Windows Server 2025 to flush stale broker sessions.

Step 3: Check Event Viewer and SQL Server Error Log for Root Cause

• Open Event Viewer > Windows Logs > Application and filter for Source: MSSQLSERVER.
• Cross-reference the exact error message embedded in the 9243 event (e.g., “Broker is disabled,” “could not find certificate,” or “dialog security negotiation failed”).
• For certificate-related errors, verify the Service Broker master key and certificates are valid:

  SELECT * FROM sys.certificates;
  SELECT * FROM sys.symmetric_keys;

Step 4: Validate SqlDependency/Query Notification Configuration

• Ensure the application’s connection string and SqlDependency.Start() method correctly reference the target queue and service.
• Confirm the QueryNotificationErrorQueue is present and not disabled:

  SELECT * FROM sys.service_queues WHERE name = 'QueryNotificationErrorQueue_XXX';

• If the queue is disabled or missing, recreate the notification infrastructure by restarting the dependent application or re-registering with SqlDependency.

Step 5: Restart SQL Server Service Broker Task Manually

• In SQL Server Configuration Manager, restart the SQL Server (MSSQLSERVER) service to reinitialize the Service Broker task.
• Confirm Service Broker is now processing messages by checking:

  SELECT * FROM sys.dm_broker_activated_tasks;

Alternative or Verification Steps

• Run SELECT * FROM sys.transmission_queue; — if this returns empty, messages are being delivered successfully with no stuck transmissions.
• If the issue persists after all steps, drop and recreate the Query Notification subscription entirely by disposing of the SqlDependency object in the application and re-issuing the subscribing query, forcing SQL Server to rebuild the broker objects from scratch.

Troubleshooting Guide: SQL Server Error 9240 – Service Broker Dialog Closure Failure

Why This Error Happens

Error 9240 occurs when SQL Server’s Service Broker attempts to close a conversation dialog but encounters an internal broker-level error, often due to a corrupted or inconsistent sys.conversation_endpoints entry in the database. This can result from a mismatched dialog state (e.g., the remote endpoint already terminated the conversation unexpectedly), transport-layer disruptions, or damaged Service Broker metadata caused by improper shutdowns, replication conflicts, or database corruption. In some cases, insufficient permissions on the SEND or message type objects can also prevent clean dialog termination.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Configuration

• Run SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is enabled.
• If is_broker_enabled returns 0, enable it with ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Check for orphaned or stuck conversations using SELECT * FROM sys.conversation_endpoints WHERE state != 'CD';

Step 2: Inspect and Clean Corrupted Conversation Endpoints

• Query sys.transmission_queue to identify messages stuck due to the broker error: SELECT * FROM sys.transmission_queue;
• If a specific conversation handle is causing repeated failures, manually end it using END CONVERSATION 'conversation_handle' WITH CLEANUP;
• Review the transmission_status column for detailed error text tied to the failing dialog.

Step 3: Validate Permissions on Broker Objects

• Confirm the service account running SQL Server 2025 has CONTROL permission on the affected database.
• Check message type and contract permissions using SELECT * FROM sys.service_message_types; and SELECT * FROM sys.service_contracts;
• Grant missing permissions with GRANT SEND ON SERVICE::[YourServiceName] TO [YourLogin]; if applicable.

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying corruption affecting system tables.
• If corruption is detected in Service Broker metadata, restore the database from a known-good backup rather than attempting repair, since Service Broker objects are sensitive to forced repairs.

Step 5: Restart the Service Broker Task

• Disable and re-enable the broker to reset its internal task queue: ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for recurrence of Event ID 9240.

Alternative or Verification Steps

• After applying fixes, generate test traffic between services and confirm dialogs close cleanly by querying sys.conversation_endpoints — the lifetime and state columns should show CD (Closed) without lingering DO (Disconnected Outbound) entries.
• If the error persists, enable Service Broker trace flags (e.g., DBCC TRACEON(1222, -1)) temporarily to capture deeper diagnostic output, or consider failing over to a secondary replica (if using Always On Availability Groups) to isolate whether the issue is instance-specific or database-specific.

Troubleshooting Guide: SQL Server Error 9241 — Service Broker Dialog Could Not Be Closed

Why This Error Happens

This error occurs when Service Broker attempts to close a dialog conversation, but the target database (identified by the database id) is offline, detached, in recovery, or otherwise inaccessible at that moment. This commonly happens during database failover, a database being set to OFFLINE/RESTORING state, or when the database is dropped while active Service Broker dialogs still reference it. Because Service Broker’s internal cleanup process cannot access the system tables within that database, it cannot properly terminate the dialog and logs Error 9241 instead of silently failing.

Step-by-Step Resolution Guide

Step 1: Verify Database Availability and State

• Open SQL Server Management Studio (SSMS) and run SELECT name, state_desc FROM sys.databases; to confirm if the referenced database is ONLINE, OFFLINE, RESTORING, or SUSPECT.
• Cross-reference the database id from the error message using SELECT DB_NAME(<id>); to identify the exact database involved.
• If the database is OFFLINE or RESTORING, wait for any ongoing restore/recovery operations to complete before proceeding.

Step 2: Bring the Database Back Online (If Applicable)

• If the database is OFFLINE, bring it back online with: ALTER DATABASE [YourDBName] SET ONLINE;
• If the database is in SUSPECT mode due to corruption, run DBCC CHECKDB ('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to assess and repair integrity issues.
• Check SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context on why the database went offline.

Step 3: Manually Close Orphaned Service Broker Dialogs

• Once the database is back online, query active dialogs using: SELECT * FROM sys.conversation_endpoints WHERE database_id = <id>;
• Manually end any stuck conversations with: END CONVERSATION '<conversation_handle>' WITH CLEANUP;
• If dialogs persist, restart the Service Broker queue monitor using: ALTER DATABASE [YourDBName] SET ENABLE_BROKER; followed by ALTER DATABASE [YourDBName] SET DISABLE_BROKER; to reset the internal state.

Step 4: Check Windows Server Event Viewer for Correlated Failures

• Navigate to Event Viewer > Windows Logs > Application and filter for Source “MSSQLSERVER” with Event ID 9241.
• Cross-check timestamps against storage or cluster failover events (common in Windows Server 2025 Failover Clustering scenarios) to rule out underlying disk I/O or network partition issues.
• Confirm there are no pending Windows Updates or storage driver issues causing intermittent disk unavailability.

Alternative or Verification Steps

• Run SELECT COUNT(*) FROM sys.conversation_endpoints WHERE database_id = <id> AND state_desc = 'CLOSED'; to confirm no orphaned dialogs remain after remediation.
• If the error persists after bringing the database online, consider rebuilding the Service Broker GUID using ALTER DATABASE [YourDBName] SET NEW_BROKER; (note: this will break existing conversations and should only be used as a last resort in non-production environments first).

Troubleshooting Guide: SQL Server Error 9239 — Internal Query Notifications Error

Why This Error Happens

Error 9239 occurs when SQL Server’s internal Query Notifications subsystem detects a corrupted or inconsistent memory structure within its garbage collection process, typically managed internally by the SQL Server Broker engine. This is often triggered by memory pressure, improper shutdowns of the SQL Server service, or corrupted system-level caches related to the SSBDiagnose and notification queue subsystems. In rare cases, outdated SQL Server 2025 cumulative updates or conflicts with Windows Server 2025 memory management (particularly with Hyper-V dynamic memory allocation) can also surface this inconsistency.

Step-by-Step Resolution Guide

Step 1: Verify Service Health and Restart the Query Notification Subsystem

• Open SQL Server Configuration Manager and confirm the SQL Server (MSSQLSERVER) and SQL Server Agent services are running without pending restarts.
• Execute the following T-SQL command to manually reset the notification broker:

  ALTER DATABASE [YourDatabaseName] SET DISABLE_BROKER;
  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• Restart the SQL Server service via PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Step 2: Apply the Latest Cumulative Update (CU) for SQL Server 2025

• Navigate to the Microsoft Update Catalog and download the latest SQL Server 2025 CU package.
• Run the installer with elevated privileges: Setup.exe /ACTION=Patch /IACCEPTSQLSERVERLICENSETERMS /QUIET
• Confirm the build number matches the latest patch via:

  SELECT @@VERSION;

Step 3: Clear Corrupted Plan Cache and Notification Queues

• Run DBCC FREEPROCCACHE; to clear any corrupted execution plans tied to the broken notification.
• Check the Service Broker queue for orphaned or “poisoned” messages using:

  SELECT * FROM sys.transmission_queue;

• If poisoned messages are found, manually purge the queue using ALTER QUEUE statements to reset the status.

Step 4: Adjust Windows Server 2025 Memory Allocation

• Open Hyper-V Manager (if running on a VM) and disable Dynamic Memory, setting a fixed static RAM allocation instead.
• In SQL Server Management Studio (SSMS), navigate to Server Properties > Memory and ensure “Maximum Server Memory” is set to leave at least 4GB free for the OS to prevent garbage collection conflicts.

Alternative or Verification Steps

• Verify the Fix: Check the SQL Server Error Log (via EXEC sp_readerrorlog;) to confirm Error 9239 no longer appears after running active notification queries for 24 hours.
• Alternative Solution: If the error persists, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out underlying database corruption as the root cause of the garbage collector inconsistency.

Troubleshooting Guide: Error 9237 – “Database is not a valid broker database”

Why This Error Happens

This error occurs when the service_broker_guid stored in the database’s system catalog does not match the GUID expected by the SQL Server instance, typically following a database restore, attach, or copy operation performed without the ENABLE_BROKER, NEW_BROKER, or WITH FORCE_REMOVE options. It can also happen if the database’s Service Broker metadata was corrupted due to an improper file copy (such as copying .mdf/.ldf files while the SQL Server service was still running) or a failed upgrade from a previous version to SQL Server 2025. In some cases, this is triggered when a database is moved between servers and the broker configuration retains a GUID that conflicts with sys.databases.

Step-by-Step Resolution Guide

Step 1: Verify the Current Broker Status and GUID

• Open SQL Server Management Studio (SSMS) or use sqlcmd on Windows Server 2025.
• Run the following query to check the current broker GUID and enabled status:

  SELECT name, service_broker_guid, is_broker_enabled 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Confirm whether is_broker_enabled is 0 (disabled) or if the GUID appears as NULL or mismatched.

Step 2: Set the Database to Single-User Mode

• Service Broker configuration changes require exclusive access to the database.
• Run the following command in a query window:

  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Ensure no active connections (including SSMS Object Explorer) are pointing to this database before proceeding.

Step 3: Reset the Service Broker GUID

• Execute the NEW_BROKER option if you want to disassociate the database from its old broker identity and generate a new GUID (recommended for copied/restored databases):

  ALTER DATABASE [YourDatabaseName] SET NEW_BROKER;

• If you need to retain the original GUID (e.g., for existing broker conversations), use the ENABLE_BROKER option instead:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• If both commands hang or fail due to metadata corruption, use the force option:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER WITH ERROR_BROKER_CONVERSATIONS;

Step 4: Revert to Multi-User Mode

• Once the broker setting has been applied successfully, restore standard access to the database.
• Run:

  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

• Restart the SQL Server (MSSQLSERVER) service via services.msc on Windows Server 2025 to clear any cached broker session states.

Alternative or Verification Steps

• Re-run the verification query from Step 1 to confirm is_broker_enabled now returns 1 and service_broker_guid is populated with a valid, non-null value.
• If the error persists after NEW_BROKER, check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for underlying corruption flags, and consider running DBCC CHECKDB to rule out deeper system table corruption.

Troubleshooting Guide: Error 9238 – Query Notification Subscriptions Blocked Under Application Role Context

Why This Error Happens

This error occurs because SQL Server explicitly disallows the creation of query notification subscriptions while an application role is active in the session context. Application roles replace the security context of the original login, which conflicts with the way SQL Server tracks ownership and permissions for notification subscriptions. As a result, the SERVICE BROKER-based notification system rejects the request until the application role is reverted using sp_unsetapprole.

Step-by-Step Resolution Guide

Step 1: Identify Where the Application Role Is Being Activated

• Review the application or service code (such as a .NET SqlDependency or SqlNotificationRequest implementation) to locate calls to sp_setapprole.
• Check connection pooling logic, since application roles persist for the lifetime of a pooled connection and may leak into subsequent queries.
• Search stored procedures and middleware configuration files for references to sp_setapprole.

Step 2: Revert the Application Role Before Issuing the Notification Request

• Ensure every sp_setapprole call is paired with a corresponding sp_unsetapprole call once the elevated-privilege work is complete.
• Example correct sequence:

  EXEC sp_setapprole 'MyAppRole', 'MyPassword';
  -- perform privileged operations
  EXEC sp_unsetapprole @cookie;

• Store the @cookie output value securely so the role can be properly unset later in the same session.

Step 3: Isolate Query Notification Logic Into a Separate Connection

• Configure your application to open a dedicated SQL connection (outside the connection pool used for the application role) specifically for SqlDependency or SqlNotificationRequest calls.
• In .NET applications, confirm SqlDependency.Start(connectionString) uses a connection string tied to a login that never invokes sp_setapprole.
• Update Web.config or App.config connection strings to split “privileged” and “notification” workloads if they currently share a single connection string.

Step 4: Validate Permissions Without Using Application Roles

• Grant the required permissions (such as SUBSCRIBE QUERY NOTIFICATIONS) directly to the SQL login or Windows Active Directory group instead of relying on an application role for elevation.
• Run the following in SQL Server Management Studio (SSMS) connected to the Windows Server 2025 instance:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [DOMAIN\ServiceAccount];

• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell (run as Administrator) to ensure permission changes propagate.

Alternative or Verification Steps

• To verify the fix, re-run the notification subscription request from a fresh session without calling sp_setapprole, and confirm Error 9238 no longer appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the issue persists, use SQL Server Profiler or Extended Events to trace the session and confirm sp_unsetapprole is firing correctly before the SqlDependency command executes.

Troubleshooting Guide: SQL Server Error 9235

Why This Error Happens

Error 9235 occurs when SQL Server’s Service Broker notification mechanism (typically used with WAITFOR(RECEIVE...) or query notifications via sys.sp_notification) receives a malformed notification options string. This happens because the notification options identifier is syntactically constructed with a key but no corresponding value (e.g., timeout= instead of timeout=5000), often caused by application code dynamically building the options string without validating null or empty variables. In Windows Server 2025 / Windows 11 environments, this is frequently triggered by outdated client drivers (SqlClient or ODBC) that incorrectly serialize notification parameters before sending them to the Database Engine.

Step-by-Step Resolution Guide

Step 1: Identify the Source Query Triggering the Notification

• Open SQL Server Profiler or Extended Events and filter for Error events with Error Number 9235.
• Capture the exact T-SQL statement or stored procedure making the call to sp_notification or using SqlDependency in the application layer.
• Note which option (e.g., service, timeout, status) is reported as missing in the %ls placeholder of the error text.

Step 2: Correct the Notification Options String Syntax

• Open the application code or script responsible for generating the notification options (commonly found in .NET applications using SqlDependency.Start() or raw T-SQL scripts).
• Ensure every option follows the strict key=value; format — for example:

  EXEC sp_notification
      @service_name = N'MyService',
      @options = N'timeout=5000;status=received';

• Remove any trailing semicolons or empty assignments like timeout=; which cause the parser to fail.

Step 3: Update SQL Server Native Client / ODBC Driver

• On Windows Server 2025 / Windows 11, navigate to Control Panel > Programs > Programs and Features and check the installed version of Microsoft ODBC Driver for SQL Server or Microsoft.Data.SqlClient (if using .NET).
• Download and install the latest driver from the official Microsoft Download Center to ensure compatibility with SQL Server 2025’s Service Broker notification schema.
• Restart the application pool or service consuming the SQL connection after the driver update.

Step 4: Validate Service Broker Configuration

• Run the following query to confirm Service Broker is enabled on the target database:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Re-run the original notification request to confirm the options string now parses correctly.

Alternative or Verification Steps

• Re-execute the application transaction that previously failed and confirm no 9235 error appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS).
• If the error persists, manually test the notification call directly in SQL Server Management Studio (SSMS) with a hardcoded, known-good options string to isolate whether the issue lies in application logic or SQL Server configuration.

Troubleshooting Guide: Error 9236 – “Database %.*ls is not a valid local database”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to access a database file (typically an .mdf or .ldf file) that is either corrupted, mismatched with its associated log file, or was created by an incompatible version of SQL Server. It can also surface when a LocalDB instance’s metadata becomes desynchronized from the actual file structure on disk, often due to an improper shutdown or abrupt termination of the SQL Server process. Additionally, this issue is commonly triggered when a database file has been manually copied or moved without detaching it first, leaving the internal file header pointing to an invalid or non-existent local database reference.

Step-by-Step Resolution Guide

Step 1: Verify the LocalDB Instance Status

• Open Command Prompt as Administrator on your Windows Server 2025 / Windows 11 machine.
• Run sqllocaldb info to list all existing LocalDB instances.
• Run sqllocaldb info MSSQLLocalDB (or your specific instance name) to check its current state (Running, Stopped, or Corrupted).
• If the instance shows as corrupted or unresponsive, proceed to the next step.

Step 2: Stop and Delete the Corrupted LocalDB Instance

• Stop the instance using: sqllocaldb stop MSSQLLocalDB -i
• Delete the broken instance registration with: sqllocaldb delete MSSQLLocalDB
• Navigate to C:\Users\<YourUsername>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\ and manually remove the leftover instance folder if it still exists.
• Recreate a fresh instance using: sqllocaldb create MSSQLLocalDB

Step 3: Re-attach the Database Files Correctly

• Open SQL Server Management Studio (SSMS) 2025 and connect to (localdb)\MSSQLLocalDB.
• Use sp_detach_db on the source server (if accessible) before moving files in the future to avoid header corruption.
• Attach the .mdf file manually via: CREATE DATABASE YourDBName ON (FILENAME = 'C:\Path\To\YourDB.mdf') FOR ATTACH;
• If the .ldf log file is missing or mismatched, use FOR ATTACH_REBUILD_LOG instead to force SQL Server to regenerate a new log file.

Step 4: Check File Permissions and Integrity

• Right-click the database folder (commonly located in C:\Users\<YourUsername>\Documents\ or your project’s App_Data folder) and go to Properties > Security.
• Ensure the NT AUTHORITY\NETWORK SERVICE and your local Windows user account have Full Control permissions.
• Run CHKDSK C: /F via an elevated Command Prompt to rule out underlying disk corruption affecting the .mdf/.ldf files.

Alternative or Verification Steps

• To verify the fix, run SELECT name, state_desc FROM sys.databases; in SSMS and confirm the database state reads ONLINE rather than SUSPECT or RECOVERY_PENDING.
• If the issue persists, restore the database from a recent .bak backup file using RESTORE DATABASE YourDBName FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE; as a fallback to bypass the corrupted local file entirely.

Troubleshooting Guide: Error 9233 – “The option ‘Service’ Must Be Specified in the Notification Options Identifier”

Why This Error Happens

This error occurs when SQL Server’s Service Broker attempts to process a notification request (commonly tied to Query Notifications or Event Notifications) but the notification options string is malformed or incomplete. Typically, this happens because the SERVICE clause—which specifies the target service name and broker instance GUID—was omitted or incorrectly formatted when constructing the notification options in application code or a SUBSCRIBE statement. This can also surface after a SQL Server 2025 upgrade if legacy connection strings or cached notification templates from older client libraries (like older SqlDependency implementations) don’t conform to the updated Service Broker syntax validation.

Step-by-Step Resolution Guide

Step 1: Review the Notification Options Syntax in Application Code

• Locate the code generating the SqlNotificationRequest or SUBSCRIBE statement (often in C#/.NET applications using SqlDependency or SqlCommand.Notification).
• Confirm the options string follows the required format: service=ServiceName;local database=DatabaseName.
• Check for typos, missing semicolons, or omitted service= key-value pairs.

Step 2: Verify Service Broker Configuration on the Database

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to confirm Service Broker is enabled:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Ensure the required Service Broker service (e.g., SqlQueryNotificationService-<GUID>) exists under sys.services.

Step 3: Recreate the Notification Service and Queue

• Drop and recreate the notification service tied to the queue if it appears corrupted or missing the correct name binding:

DROP SERVICE [SqlQueryNotificationService];
CREATE SERVICE [SqlQueryNotificationService] ON QUEUE [SqlQueryNotificationQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

• Restart the SQL Server service via Services.msc (Windows Server 2025/Windows 11) or run:

Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Update Client Libraries and Connection Strings

• Ensure the application is using an updated System.Data.SqlClient or Microsoft.Data.SqlClient NuGet package compatible with SQL Server 2025.
• Rebuild the notification options string explicitly in code rather than relying on cached or default values:

SqlDependency.Start(connectionString, "service=MyNotifyService;local database=MyDB");

Alternative or Verification Steps

• To verify the fix, re-run the application’s subscription logic and monitor sys.dm_qn_subscriptions in SSMS—an active row with no errors confirms the notification registered successfully.
• If the error persists, enable Service Broker trace flags (DBCC TRACEON(933, -1)) temporarily to capture detailed diagnostic output for the malformed notification request.

Why This Error Happens

Error 9234 occurs when an application configures SQL Server Query Notifications (via SqlDependency or SERVICE BROKER options) using a malformed notification options string. The service=...; local database=...; broker instance=... syntax only allows one method of identifying the target broker instance — either local database (which lets SQL Server resolve the broker instance automatically) or broker instance=<GUID> (which explicitly specifies it). When both identifiers appear in the same notification options string, SQL Server cannot determine which value takes precedence, so it rejects the request outright. This typically stems from a copy-paste error in application connection strings or a misconfigured SqlDependency.Start() / SqlNotificationRequest call in legacy .NET code that hasn’t been updated for SQL Server 2025 syntax validation.

Step-by-Step Resolution Guide

Step 1: Locate the Notification Options String

• Open the application source code or configuration file (e.g., web.config, app.config, or custom C#/.NET service layer) where SqlDependency or SqlNotificationRequest is implemented.
• Search for strings containing service=, local database=, or broker instance= — these are the usual culprits.
• Example of the broken string:
  service=MyNotifyService;local database=AdventureWorks;broker instance=E4F4B610-...

Step 2: Remove the Conflicting Identifier

• Decide whether your application needs automatic resolution or explicit targeting:
• Use local database=<DBName> if the broker instance is on the same SQL Server instance as the connection.
• Use broker instance=<GUID> only if you are targeting a broker instance on a remote or linked server.
• Edit the string to include only one of the two, for example:
  service=MyNotifyService;local database=AdventureWorks
• Save the configuration file and restart the application pool or service (IIS Manager → Application Pools → Recycle, or net stop/start for Windows Services).

Step 3: Validate Service Broker Configuration

• Open SQL Server Management Studio (SSMS) on your SQL Server 2025 instance.
• Run the following to confirm Service Broker is enabled on the target database:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'AdventureWorks';

• If is_broker_enabled returns 0, enable it:

  ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

Step 4: Re-test the Query Notification Request

• Restart the application or trigger the code path that issues the SqlDependency subscription.
• Monitor SQL Server error logs (Management → SQL Server Logs in SSMS, or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) to confirm Error 9234 no longer appears.

Alternative or Verification Steps

• Run a manual test using sys.dm_qn_subscriptions in SSMS to confirm a new subscription registers successfully without errors after the fix.
• If the error persists despite correcting the string, check for cached connection strings or stale .config transforms in deployment pipelines (e.g., Azure DevOps release artifacts) that may be reintroducing the old notification options syntax.

Why This Error Happens

Error 9232 occurs when SQL Server’s parser encounters an invalid notification options string, typically used in Query Notifications (SqlDependency), Service Broker CREATE EVENT NOTIFICATION statements, or Database Mail profile strings. The options identifier expects a strict key=value;key=value format, and this error fires when a value contains trailing garbage, a missing semicolon, or an unescaped special character between key-value pairs. This is almost always caused by a malformed connection string or notification string generated by application code (ADO.NET SqlDependency, ODBC drivers, or custom T-SQL scripts) rather than by SQL Server itself being corrupted.

Step-by-Step Resolution Guide

Step 1: Identify the Source of the Malformed String

• Check SQL Server error logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact string reported in the %.*ls placeholder.
• If using .NET, enable verbose exception logging in your application to capture the full SqlDependency or SqlNotificationRequest options string before it is sent to the server.
• Confirm whether the error originates from SUBSCRIBE QUERY NOTIFICATION, CREATE EVENT NOTIFICATION, or a Database Mail profile call.

Step 2: Correct the Notification Options Syntax

• Ensure the options string strictly follows the pattern: service=ServiceName;local database=DatabaseName.
• Remove any trailing semicolons, double semicolons (;;), or stray whitespace after the last key-value pair.
• In C#, verify the SqlNotificationRequest.Options property is built using a safe string builder rather than manual string concatenation:

  request.Options = "service=MyNotifyService;local database=MyAppDB";

Step 3: Validate the Query Notification Infrastructure

• Open SQL Server Management Studio (SSMS) and confirm Service Broker is enabled on the target database:

  ALTER DATABASE MyAppDB SET ENABLE_BROKER;

• Verify the notification service and queue exist and are not corrupted:

  SELECT * FROM sys.services;
  SELECT * FROM sys.service_queues;

• Recreate the service and queue if they appear misconfigured or orphaned from a previous app deployment.

Step 4: Update Application Configuration and Restart Services

• Edit the application’s app.config or web.config file to correct the connection string or notification options value.
• Restart the application pool (IIS) or Windows Service hosting the application on Windows Server 2025.
• Restart the SQL Server Agent service via services.msc or net stop SQLSERVERAGENT && net start SQLSERVERAGENT to clear any cached malformed job notification settings.

Step 5: Rebuild Corrupted Alert or Mail Profiles (If Applicable)

• If the error ties to SQL Server Agent alerts, open SSMS, navigate to SQL Server Agent > Alerts, and recreate the affected alert’s notification message manually instead of relying on a scripted insert.
• For Database Mail, re-run sp_send_dbmail with a clean, manually typed profile name to rule out string corruption from copy-paste characters (e.g., smart quotes).

Alternative or Verification Steps

• Re-run the exact query or application action that triggered Error 9232 and confirm no exception is thrown; check sys.dm_qn_subscriptions to confirm the subscription registered successfully.
• As an alternative, bypass the custom options string entirely by using the default SqlDependency.Start(connectionString) overload (without a custom options string) to let ADO.NET auto-generate a valid service/queue pair.

Troubleshooting Guide: SQL Server Error 9230 – Duplicate Notification Option Specified

Why This Error Happens

Error 9230 occurs when a Service Broker or Event Notification statement defines the same option within the CREATE EVENT NOTIFICATION or notification options identifier list more than once. SQL Server’s parser validates the notification option clause for uniqueness, and when it encounters a duplicate token (such as specifying BROKER_INSTANCE or a service name qualifier twice), it halts compilation of the DDL statement. This is typically caused by manually edited T-SQL scripts, copy-paste errors during script generation, or automated deployment tools that append duplicate options when merging configuration templates.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Statement

• Open SQL Server Management Studio (SSMS) and locate the exact script that triggered Error 9230, typically found in deployment logs at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for the string “Option” and “specified multiple times” to pinpoint the line number and object name.
• Cross-reference the script with your source control system (Git, Azure DevOps) to see if the file was recently merged or auto-generated.

Step 2: Review and Correct the Notification Options Syntax

• Open the CREATE EVENT NOTIFICATION or CREATE ROUTE statement in a query window.
• Check for duplicate entries in the WITH clause, for example WITH FAN_IN, FAN_IN or repeated LIFETIME declarations.
• Manually remove the duplicate token, keeping only one instance of each valid option (e.g., FAN_IN, ACTIVATION, BROKER_INSTANCE).
• Save the corrected script as a new .sql file to avoid overwriting your audit trail.

Step 3: Validate the Script Using a Syntax-Only Execution

• Run the corrected statement with SET PARSEONLY ON; before execution to confirm the parser accepts the syntax without creating the object.
• Example:

SET PARSEONLY ON;
GO
CREATE EVENT NOTIFICATION TestNotify
ON SERVER
WITH FAN_IN
FOR DDL_DATABASE_LEVEL_EVENTS
TO SERVICE 'NotifyService', 'current database';
GO
SET PARSEONLY OFF;

• If no errors return, proceed to execute the statement against the target database.

Step 4: Clear and Redeploy via SQL Server Configuration Manager

• If the error originates from an automated deployment pipeline on Windows Server 2025, open SQL Server Configuration Manager and restart the SQL Server (MSSQLSERVER) service to clear any cached execution plans.
• Re-run the deployment pipeline (e.g., DACPAC or SSDT project) after confirming the source .sql file no longer contains duplicate tokens.

Alternative or Verification Steps

• Verify the fix by querying sys.event_notifications and sys.server_event_notifications to confirm the notification object was created successfully without errors.
• If the error persists in an SSDT or DACPAC project, inspect the .sqlproj file for duplicate <Build Include> references pointing to the same notification script, as duplicate file inclusion can cause the statement to be parsed twice.

Troubleshooting Guide: SQL Server Error 9231

Why This Error Happens

Error 9231 occurs when SQL Server’s parser encounters a malformed option string, typically within a WITH clause, extended properties definition, or a backup/restore command, where an equal sign (=) was expected but not found. This is most commonly caused by a syntax error in a Transact-SQL script—such as a missing =, a misplaced comma, or an unquoted string containing special characters—or by corruption in a scripted job, maintenance plan, or SSIS package that generates T-SQL dynamically. In some cases, this error surfaces after an in-place upgrade to SQL Server 2025 when legacy scripts use deprecated or reformatted option syntax that the newer parser no longer accepts.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending T-SQL Statement

• Open SQL Server Management Studio (SSMS) and check the Messages tab for the exact line number and batch referenced alongside Error 9231.
• If the error originates from a SQL Agent Job, navigate to SQL Server Agent > Jobs > [Job Name] > Steps and review the Command field for the failing step.
• Copy the full statement into a new query window for isolated testing.

Step 2: Audit the Syntax for Missing or Malformed Operators

• Inspect WITH (...) clauses (common in BACKUP, RESTORE, CREATE INDEX, or ALTER DATABASE statements) for options missing their = sign, e.g., WITH (COMPRESSION, STATS 10) instead of the correct WITH (COMPRESSION, STATS = 10).
• Check for stray or smart quotes (curly quotes ” “ instead of straight quotes " ") that may have been introduced by copying script text from Word documents or PDF files.
• Verify that multi-value options are comma-separated correctly and that no reserved keyword is used as an unquoted identifier.

Step 3: Regenerate or Repair the Script Source

• If the script originates from a Maintenance Plan, open SQL Server Management Studio > Management > Maintenance Plans, and regenerate the plan via the GUI rather than manually editing the underlying XML/T-SQL.
• For SSIS packages, open the package in Visual Studio/SSDT, locate the Execute SQL Task, and rebuild the parameterized query using the native query builder to avoid manual string concatenation errors.
• Save the corrected script and redeploy it to the SQL Server 2025 instance.

Step 4: Validate Compatibility Level Settings

• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the database compatibility level.
• If recently upgraded from an older SQL Server version, ensure any legacy syntax (such as old-style SET options) has been updated to match SQL Server 2025 standards, as some older abbreviated syntax is no longer tolerated by the parser.

Alternative or Verification Steps

• Re-run the corrected query or job step manually in SSMS; a successful execution without Error 9231 confirms the fix.
• If the error persists, use SQL Server Profiler or Extended Events to capture the exact batch text sent to the server at runtime, as dynamically generated SQL (via sp_executesql or application code) may differ from the static script you reviewed.

Why This Error Happens

Error 9228 occurs when the SQL Server parser encounters an invalid or malformed identifier within the NOTIFICATION OPTIONS clause of a BEGIN DIALOG CONVERSATION or Query Notification subscription statement. This typically happens due to a missing [ ] or " " delimiter around a service name, broker instance, or contract name that contains special characters, reserved keywords, or spaces. It can also surface after an in-place upgrade to SQL Server 2025 if legacy Service Broker scripts were written using syntax conventions that the updated Transact-SQL parser now flags as non-compliant identifiers.

Step-by-Step Resolution Guide

Step 1: Isolate the Offending T-SQL Statement

• Open SQL Server Management Studio (SSMS) and locate the specific stored procedure or application code triggering the error (check sys.dm_exec_requests for the sql_handle if the error is intermittent).
• Run the isolated BEGIN DIALOG CONVERSATION statement manually in a new query window to reproduce Error 9228 outside of the application context.

Step 2: Validate and Correct Identifier Syntax

• Check the NOTIFICATION OPTIONS segment of the query for service names or contract names that are missing square bracket delimiters.
• Ensure any identifier containing a hyphen, space, or reserved word (e.g., Service-Broker) is wrapped correctly, such as [Service-Broker].
• Verify QUOTED_IDENTIFIER is set to ON for the session by running:

SET QUOTED_IDENTIFIER ON;

Step 3: Check Compatibility Level Settings

• Since SQL Server 2025 enforces stricter ANSI SQL compliance, verify the database compatibility level using:

SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDBName';

• If the compatibility level is outdated (e.g., 120 or lower), update it to align with SQL Server 2025 standards to ensure the parser handles notification syntax correctly:

ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170;

Step 4: Rebuild the Service Broker Configuration

• If the identifier is confirmed correct but the error persists, drop and recreate the specific Service Broker SERVICE and CONTRACT objects to clear any metadata corruption.
• Use ALTER SERVICE to reset the specific queue attached to the notification request.

Alternative or Verification Steps

• Verification: Re-run the BEGIN DIALOG CONVERSATION query in SSMS; if the dialog handle returns successfully without a red error message, the identifier syntax has been resolved.
• Alternative: If the error persists despite correct syntax, bypass the issue by using a sp_executesql wrapper with explicit NVARCHAR parameterization for the service name, which forces the engine to treat the string as a literal rather than parsing it as a raw identifier.

Troubleshooting Guide: SQL Server Error 9229 — Invalid Notification Option Name

Why This Error Happens

This error occurs when a CREATE EVENT NOTIFICATION statement contains an invalid or misspelled option name within the WITH clause of the notification definition. SQL Server 2025 strictly validates these options against a fixed internal list, and currently Service is the only accepted keyword for specifying the Service Broker target. This typically results from manually edited T-SQL scripts, copy-pasted syntax from outdated documentation, or deprecated syntax carried over from legacy SQL Server versions.

Step-by-Step Resolution Guide

Step 1: Locate the Offending T-SQL Script

• Identify the specific CREATE EVENT NOTIFICATION or ALTER EVENT NOTIFICATION statement throwing Error 9229.
• Check SQL Server Management Studio (SSMS) query history or the job step in SQL Server Agent that triggered the error.
• Use the following query against the error log to pinpoint the timestamp and session:

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

Step 2: Correct the Notification Option Syntax

• Open the script in SSMS and locate the WITH clause of the notification definition.
• Verify that the only option name used is Service, formatted exactly as shown below:

  CREATE EVENT NOTIFICATION [MyNotification]
  ON SERVER
  WITH FAN_IN
  FOR DDL_EVENTS
  TO SERVICE 'MyService', 'current database';

• Remove any extraneous or deprecated keywords (e.g., QUEUE, BROKER_INSTANCE, or custom option names) that are not part of the supported syntax in SQL Server 2025.
• Save the corrected script to a .sql file in a version-controlled directory, such as C:\SQLScripts\Notifications\.

Step 3: Validate Service Broker Configuration

• Confirm that Service Broker is enabled on the target database, since the Service option depends on it:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If disabled, enable it with:

  ALTER DATABASE [YourDatabaseName] SET ENABLE_BROKER;

• Ensure the specified service (e.g., 'MyService') actually exists via sys.services.

Step 4: Re-Execute and Deploy the Script

• Run the corrected script in SSMS against the target instance.
• If deploying via automation (PowerShell/DevOps pipeline on Windows Server 2025), update the source-controlled script file and re-run the deployment job.
• Restart the SQL Server Agent service only if the notification was tied to a scheduled job:

  Restart-Service -Name "SQLSERVERAGENT"

Alternative or Verification Steps

• Verify the fix by querying sys.event_notifications to confirm the notification object was created successfully without errors.
• If the error persists, check for mismatched SQL Server client tools (e.g., an outdated SSMS or sqlcmd version generating legacy syntax) and update to the version compatible with SQL Server 2025.

Troubleshooting Guide: SQL Server Error 9226

Why This Error Happens

Error 9226 occurs when a string value supplied to a SQL Server Agent notification, alert, or Service Broker identifier exceeds the maximum allowable character length defined by the system catalog schema. This is typically triggered by an overly long operator name, notification category, or custom identifier string being passed programmatically or through a misconfigured maintenance script. It can also surface after a migration from an older SQL Server version where legacy naming conventions exceeded the stricter validation limits enforced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending String

• Open the SQL Server Error Log via SQL Server Management Studio (SSMS) or Event Viewer on Windows Server 2025 to capture the full error text, including the %.*ls placeholder resolved value.
• Run the following query to check lengths of names in msdb notification tables:

  SELECT name, LEN(name) AS NameLength 
  FROM msdb.dbo.sysoperators 
  WHERE LEN(name) > 128;

• Cross-reference the identifier against the specific stored procedure (e.g., sp_add_notification or sp_add_category) that triggered the failure in the Agent job history.

Step 2: Truncate or Rename the Identifier

• Modify the application code or SSIS package generating the string to enforce a hard limit (usually 128 characters for sysname fields).
• Update existing records directly in msdb using sp_rename or an UPDATE statement if the string belongs to a legacy Agent object:

  EXEC msdb.dbo.sp_update_operator 
      @name = N'OldLongNameHere', 
      @new_name = N'ShortName';

• Ensure no leading/trailing whitespace is inflating the character count inadvertently.

Step 3: Validate Agent and Service Broker Configuration

• Restart the SQL Server Agent service via the Services console (services.msc) on Windows Server 2025 to clear cached notification metadata.
• Re-run the specific job or alert that failed to confirm the truncated string passes validation without throwing Error 9226.

Alternative or Verification Steps

• Verification: Check the SQL Server Agent job history (right-click the job in SSMS > View History) to confirm the status reads “Succeeded” rather than “Failed” after the string length correction.
• Alternative: If the error persists due to a third-party monitoring tool, review the tool’s configuration XML or registry settings (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server) to ensure it isn’t injecting a dynamically generated GUID or timestamp that exceeds the character limit.

Troubleshooting Guide: SQL Server Error 9227 – Unmatched Quote in Notification Options Identifier String

Why This Error Happens

Error 9227 occurs when SQL Server parses the @notification_options parameter (commonly used in sp_notify_operator, Service Broker notification configurations, or Database Mail/Agent alert definitions) and encounters a string with an odd number of quote characters. This typically results from a malformed or manually edited T-SQL script, a corrupted job step definition, or improper escaping of single quotes within a dynamically generated identifier string. Third-party monitoring tools or automated deployment scripts that construct notification strings programmatically are frequent culprits, especially after migration to SQL Server 2025 on Windows Server 2025 or Windows 11 environments.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Script or Job

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Navigate to SQL Server Agent > Jobs and review recent job history for failed steps referencing notification options.
• Run the following query to locate jobs using notification strings:

SELECT job_id, name, notify_email_operator_id 
FROM msdb.dbo.sysjobs 
WHERE notify_level_email <> 0;

• Cross-reference the job step command text for unescaped single quotes (').

Step 2: Correct the Quote Escaping in the Identifier String

• Locate the exact string passed to @notification_options or similar parameters in the job step or stored procedure.
• Ensure every literal single quote within the string is escaped by doubling it (e.g., O'Brien must be written as O''Brien).
• Example of a corrected call:

EXEC msdb.dbo.sp_notify_operator 
    @name = N'DBA_Alert', 
    @subject = N'Server O''Reilly Node Alert';

• Save the corrected script and re-deploy it via SSMS or your deployment pipeline.

Step 3: Validate Job Step XML/Command Definitions

• If the job was created via PowerShell or dbatools on Windows Server 2025, re-export the job definition using:

Get-DbaAgentJob -SqlInstance "YourServerName" -Job "JobName" | Export-DbaScript -Path "C:\Scripts\Job_Fixed.sql"

• Open the exported .sql file in a text editor and manually inspect the @command and @notification_options fields for mismatched quotes.
• Re-import the corrected job definition using Invoke-DbaQuery or execute the script directly in SSMS.

Step 4: Clear Cached Plans and Restart the SQL Server Agent Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Restart the SQL Server Agent service to clear any cached malformed job metadata:

Restart-Service -Name "SQLSERVERAGENT" -Force

• Re-run the affected job manually from SSMS to confirm the parser no longer throws Error 9227.

Alternative or Verification Steps

• Verify the fix: Execute the corrected stored procedure or job step manually in SSMS; a successful run with no parser errors confirms resolution.
• Alternative approach: If the error persists, use QUOTENAME() or REPLACE(@string, '''', '''''') functions within the stored procedure to programmatically sanitize any dynamically built notification strings before execution.

Troubleshooting Guide: Error 9224 – Query Notification Delivery Failure

Why This Error Happens

Error 9224 occurs when SQL Server’s Service Broker attempts to deliver a query notification but cannot access the target database, typically because the database is offline, in an inconsistent state, or has had its Service Broker GUID changed or disabled. This commonly happens after a database restore, mirroring failover, or when the ENABLE_BROKER setting was not properly configured during a database copy operation. Permission issues on the notification queue or a corrupted Service Broker configuration in the sys.conversation_endpoints metadata can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Accessibility

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run SELECT name, state_desc, is_broker_enabled FROM sys.databases WHERE database_id = <id>; using the database ID from the error message.
• Confirm the database is ONLINE and not stuck in RESTORING, RECOVERY_PENDING, or SUSPECT mode.
• If offline, bring it online using ALTER DATABASE [YourDB] SET ONLINE;.

Step 2: Enable or Repair Service Broker

• Check if is_broker_enabled returned 0 in the previous query.
• If disabled, run the following in a query window with single-user access:

ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• If the database was restored from another server, the Broker GUID may be mismatched. Fix this with:

ALTER DATABASE [YourDB] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• Note: Use NEW_BROKER only if existing conversations can be safely discarded.

Step 3: Validate Permissions on the Notification Queue

• Navigate to Databases > YourDB > Service Broker > Queues in SSMS Object Explorer.
• Confirm the SqlQueryNotificationService-<GUID> queue exists and is in ACTIVE status.
• Grant necessary permissions to the application login:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourAppLogin];

• Restart the SQL Server service via Services.msc (search “SQL Server (MSSQLSERVER)”) on Windows Server 2025 if permissions were recently changed.

Step 4: Check for Orphaned Conversations

• Query sys.conversation_endpoints to identify stuck or orphaned conversations tied to the failed database ID.
• Clean up stale endpoints with:

END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the Service Broker task if conversations remain unresponsive after cleanup.

Alternative or Verification Steps

• Re-run the application’s notification subscription logic and monitor the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm Error 9224 no longer appears.
• If the issue persists, drop and recreate the Query Notification queue and service using CREATE QUEUE and CREATE SERVICE statements, then re-enable ENABLE_BROKER on the database.

Why This Error Happens

Error 9225 occurs when SQL Server’s Service Broker attempts to process a notification options identifier (typically used in BEGIN DIALOG CONVERSATION or Query Notification subscriptions) that exceeds the maximum character limit defined by the engine. This is usually caused by an application dynamically generating overly long service names, GUIDs, or contract identifiers, or by a concatenation bug in client-side code that builds the notification string. In rare cases, this can also surface after a botched migration script where identifiers were not truncated or validated before insertion into system catalog views.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Identifier String

• Enable SQL Server extended events or check the application/client log that triggered the BEGIN DIALOG or notification subscription call.
• Run the following query to inspect recent Service Broker activity and look for abnormally long identifiers:

  SELECT * FROM sys.transmission_queue;
  SELECT * FROM sys.conversation_endpoints;

• Note the exact string causing the overflow (SQL Server typically reports the offending length in the error output via %d).

Step 2: Correct the Application or Script Generating the Identifier

• Open the application source code or SSIS/T-SQL script responsible for constructing the notification options string (often found in SqlDependency or SqlNotificationRequest usage in .NET apps).
• Ensure identifiers do not exceed 128 characters (standard SQL Server identifier limit) — truncate or hash long dynamic values instead of concatenating full GUIDs and timestamps.
• Example fix in C#:

  string safeId = Guid.NewGuid().ToString("N").Substring(0, 20);
  SqlNotificationRequest request = new SqlNotificationRequest("MyService", safeId, 300);

Step 3: Validate Service Broker Configuration

• On the SQL Server 2025 instance, confirm Service Broker is enabled and not corrupted:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';

• If disabled or inconsistent, re-enable it:

  ALTER DATABASE YourDBName SET ENABLE_BROKER;

• Restart the SQL Server service via Services.msc on Windows Server 2025 to clear any cached broker session states.

Step 4: Apply Latest Cumulative Update

• Check SQL Server Configuration Manager or SELECT @@VERSION; to confirm your build.
• Download and apply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as identifier-length validation bugs are occasionally patched in CUs.
• Reboot the Windows Server 2025 host after installation to ensure all binaries are refreshed.

Alternative or Verification Steps

• Re-run the original query or application notification request that triggered Error 9225 — if no error returns and sys.transmission_queue shows clean processing, the issue is resolved.
• If the error persists, as a workaround, disable and recreate the Service Broker queue/service with shorter, static identifiers instead of dynamically generated ones:

  DROP SERVICE [OldLongServiceName];
  CREATE SERVICE [ShortSvc] ON QUEUE MyQueue ([MyContract]);

Troubleshooting Guide: SQL Server Error 9222 – Outdated Query Notification Table Schema

Why This Error Happens

Error 9222 occurs when the internal system table used to track Query Notification subscriptions becomes outdated relative to the current SQL Server engine version, typically after an in-place upgrade or major build update (such as moving to SQL Server 2025). Because the schema no longer matches what the Service Broker-based notification engine expects, SQL Server automatically drops the stale internal table rather than risk corruption, which interrupts the normal cleanup cycle for expired notifications. This leaves orphaned metadata and prevents new notification subscriptions from being properly garbage-collected until the system is manually reconciled.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of the Issue

• Open SQL Server Error Log via SSMS (Management > SQL Server Logs) or xp_readerrorlog and confirm Error 9222 is isolated to a specific database.
• Run the following to identify affected databases:

  SELECT name, database_id, is_broker_enabled FROM sys.databases;

• Note the database_id associated with the error timestamp for targeted remediation.

Step 2: Disable and Re-enable Service Broker on the Affected Database

• Service Broker underpins Query Notifications, so cycling it forces SQL Server to rebuild internal tracking tables.
• Run the following in a maintenance window (requires exclusive access):

  ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  ALTER DATABASE [YourDB] SET DISABLE_BROKER;
  ALTER DATABASE [YourDB] SET ENABLE_BROKER;
  ALTER DATABASE [YourDB] SET MULTI_USER;

• This regenerates the internal notification tables with the current schema version.

Step 3: Clear Orphaned Notification Subscriptions

• Query the notification queue to check for leftover or stuck entries:

  SELECT * FROM sys.dm_qn_subscriptions;

• If stale subscriptions persist, restart the SQL Server service via Services.msc (Windows Server 2025/Windows 11) to force a full flush of in-memory notification caches:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Apply Latest Cumulative Update

• Confirm you are on the latest SQL Server 2025 Cumulative Update (CU), as early builds have known internal table versioning mismatches tied to this error.
• Download and install via Microsoft Update Catalog or SQL Server Setup, then verify version:

  SELECT @@VERSION;

Step 5: Rebuild Query Notification Infrastructure (If Error Persists)

• Drop and recreate any application-level SqlDependency or Query Notification objects referencing the affected database.
• In application code, call SqlDependency.Stop() followed by SqlDependency.Start() against the connection string to re-register notification queues cleanly.

Alternative or Verification Steps

• Verify the fix by monitoring the SQL Server Error Log for 24–48 hours to confirm Error 9222 does not recur, and check sys.dm_qn_subscriptions to ensure active subscriptions are being cleaned up normally.
• If the error persists after CU installation and Broker cycling, consider exporting data and recreating the database from a known-good backup, as a deeper system catalog corruption may require a full DBCC CHECKDB repair pass.

Troubleshooting Guide: SQL Server Error 9223

Why This Error Happens

Error 9223 occurs when SQL Server’s Service Broker drops active query notification subscriptions tied to a specific security identification number (SID), typically because the associated login or user was dropped, disabled, or had its permissions revoked while subscriptions were still active. This can also happen when the security context becomes invalid due to orphaned SIDs following a database restore, login migration, or Windows account deletion. Additionally, corruption in the SSBQueueNotificationEventSink or Service Broker internal metadata tables can trigger premature subscription cleanup.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Security Identifier

• Query sys.server_principals and sys.database_principals to map the SID referenced in the error message back to a login or user.
• Run: SELECT name, sid FROM sys.server_principals WHERE sid = 0x... (replace with the SID from the error log).
• Check SQL Server Error Log via SSMS or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the exact timestamp and context of the drop.

Step 2: Validate Login and Permission Integrity

• Confirm the login still exists and is enabled: SELECT name, is_disabled FROM sys.sql_logins;
• If the login was dropped intentionally, recreate it using CREATE LOGIN [username] WITH PASSWORD = 'StrongPassword123!'; and remap orphaned users using ALTER USER [username] WITH LOGIN = [username];
• Ensure the login has SUBSCRIBE QUERY NOTIFICATIONS permission granted explicitly: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [username];

Step 3: Rebuild the Query Notification Subscriptions

• Restart the application or service responsible for issuing the query notification requests (e.g., ASP.NET SqlDependency or SqlCacheDependency consumers) so it re-subscribes under a valid security context.
• Flush stale subscriptions manually if needed: EXEC sp_dropqueue 'target_queue_name'; followed by recreating the queue and service via CREATE QUEUE and CREATE SERVICE.

Step 4: Check Service Broker Health

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabase'; to confirm Service Broker is active.
• If disabled, enable it with: ALTER DATABASE YourDatabase SET ENABLE_BROKER;
• Review sys.transmission_queue and sys.conversation_endpoints for stuck or corrupted message chains and clean them up using END CONVERSATION statements where necessary.

Step 5: Apply Latest Cumulative Update

• Verify current build version with SELECT @@VERSION; and compare against the latest SQL Server 2025 Cumulative Update from Microsoft’s official download center.
• Apply the update on Windows Server 2025 using the standard .exe installer, ensuring you restart the SQL Server service afterward via services.msc.

Alternative or Verification Steps

• To verify the fix, re-run the application that generates query notifications and monitor sys.dm_qn_subscriptions to confirm new subscriptions are created and persist without being dropped.
• If the issue persists, enable Service Broker trace flags (such as 9950) temporarily in SQL Server Configuration Manager startup parameters to capture deeper diagnostic logging for the next occurrence.

Troubleshooting Guide: Error 9220 — Query Notification Dialog Closed Due to Unknown Service Broker Error

Why This Error Happens

Error 9220 typically occurs when Service Broker encounters an internal issue processing a conversation on the specified handle, often due to corrupted message payloads, a disrupted or disabled Service Broker configuration at the database level, or transport-layer interruptions between endpoints. This can also surface when query notifications (used by SqlDependency or Query Notifications in client applications) lose their underlying conversation due to database mirroring/Always On failover events, master key issues, or certificate-based routing problems between dialog endpoints. In some cases, insufficient permissions on the service_broker_guid or a mismatch in the broker’s internal queue state can trigger this generic/unknown error.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the broker status:

  SELECT name, is_broker_enabled, service_broker_guid 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: If the database was restored or attached, you may need SET NEW_BROKER instead to reset the GUID.

Step 2: Inspect the Broker Queues for Poison Messages or Errors

• Check the sys.transmission_queue for stuck or failed messages:

  SELECT * FROM sys.transmission_queue;

• Look specifically at the transmission_status column for error details tied to the conversation handle.
• If messages are stuck, clear the queue by restarting the relevant endpoint or ending the conversation:

  END CONVERSATION 'conversation_handle_guid' WITH CLEANUP;

Step 3: Validate Certificates and Master Key Configuration (for Cross-Database/Instance Scenarios)

• Confirm the database master key exists and is open:

  OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• Verify certificates used for Service Broker routing haven’t expired:

  SELECT name, expiry_date FROM sys.certificates;

• On Windows Server 2025/Windows 11, ensure the SQL Server service account has read access to the certificate store path (typically under C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys).

Step 4: Restart the SQL Server Service and Re-establish Notifications

• Restart the SQL Server (MSSQLSERVER) service via PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Re-register your Query Notification subscription (SqlDependency.Start()) from the client application to generate a fresh conversation handle.

Step 5: Enable Service Broker Diagnostic Tracing

• Use Extended Events to capture broker_conversation and broker_queue_disabled events for deeper diagnostics:

  CREATE EVENT SESSION [SB_Diag] ON SERVER 
  ADD EVENT sqlserver.broker_conversation,
  ADD EVENT sqlserver.broker_queue_disabled
  ADD TARGET package0.event_file(SET filename=N'C:\SQLDiag\SB_Diag.xel');
  ALTER EVENT SESSION [SB_Diag] ON SERVER STATE = START;

• Review the output after reproducing the error to pinpoint the exact internal failure point.

Alternative or Verification Steps

• Confirm resolution by re-running your application’s notification subscription and checking sys.dm_broker_connections and sys.conversation_endpoints for a healthy CONNECTED or CONVERSING state without errors.
• If the issue persists, as a fallback, drop and recreate the Service Broker queue, service, and contract objects entirely, then re-enable ENABLE_BROKER to force a clean GUID and conversation state.

Troubleshooting Guide: SQL Server Error 9221 – Query Notification Dialog Endpoint Failure

Why This Error Happens

Error 9221 occurs when Service Broker cannot locate or access the dialog endpoint associated with a query notification subscription, typically because the underlying conversation has expired, been prematurely ended, or was never properly established. This is commonly caused by Service Broker being disabled on the database, expired dialog lifetimes (default timeout reached before the client processed the notification), or corrupted/missing entries in the sys.conversation_endpoints system view. It can also stem from permission issues where the service account lacks rights to SEND on the service broker conversation.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: This may require single-user mode or a brief service restart on Windows Server 2025 if the database has active connections.

Step 2: Inspect and Clean Up Stale Conversation Endpoints

• Query existing dialogs to identify orphaned or expired conversations:

  SELECT * FROM sys.conversation_endpoints WHERE state_desc <> 'CONVERSING';

• End any broken conversations manually:

  END CONVERSATION 'conversation_handle' WITH CLEANUP;

• Restart the SQL Server Broker task via SQL Server Configuration Manager if endpoints remain stuck after cleanup.

Step 3: Validate Query Notification Timeout Settings

• Check the client application’s SqlDependency or SqlNotificationRequest timeout value — default is 5 minutes, which may be too short under heavy load.
• Increase the timeout in your notification subscription code (C#/.NET example):

  SqlNotificationRequest request = new SqlNotificationRequest("MyNotification", "MyService", 3600);

• Ensure the application server’s system clock is synchronized (Windows Time service) to avoid premature expiration calculations.

Step 4: Check Permissions on the Service Broker Service and Queue

• Confirm the executing user/service account has SEND permission on the service:

  GRANT SEND ON SERVICE::[MyNotificationService] TO [DOMAIN\ServiceAccount];

• Verify the queue is not disabled:

  SELECT name, is_receive_enabled, is_enqueue_enabled FROM sys.service_queues;

• Re-enable if needed:

  ALTER QUEUE MyNotificationQueue WITH STATUS = ON;

Step 5: Restart SQL Server Service Broker Component

• Open SQL Server Configuration Manager on Windows Server 2025.
• Restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize dialog handling.
• Confirm no errors appear in the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) related to Broker startup.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_broker_queue_monitors; to confirm the queue monitor is in an “ACTIVE” or idle-notified state rather than stuck.
• If the error persists, drop and recreate the query notification subscription from the client application to force a fresh dialog handshake with Service Broker.

Troubleshooting Guide: SQL Server Error 9219 – Query Notification Subscriptions Cleanup Failed

Why This Error Happens

Error 9219 occurs when SQL Server’s Service Broker is unable to complete the automatic cleanup of expired or orphaned query notification subscriptions in the AspNetSqlCacheForChangeNotification or similar system tables. This is typically caused by the Service Broker being disabled on the database, corrupted conversation queues, or insufficient permissions for the SQL Server service account to modify internal notification tables. In many cases, this error is a secondary symptom triggered by an underlying Service Broker error (such as a damaged queue or disabled broker GUID) that must be resolved first.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check the broker status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 2: Inspect and Clear Orphaned Notification Queues

• Navigate to Object Explorer > Databases > YourDatabase > Service Broker > Queues.
• Look for system queues like SqlQueryNotificationService-xxxx that may be stuck or disabled.
• Run the cleanup procedure manually to force removal of stale subscriptions:

  EXEC sp_cleanup_query_notification_subscriptions;

• If the procedure fails again, 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 specific underlying error referenced by 9219.

Step 3: Validate Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service is running under a valid domain or managed service account.
• Ensure the service account has db_owner or explicit CONTROL permission on the database via:

  GRANT CONTROL ON DATABASE::YourDatabaseName TO [DOMAIN\ServiceAccount];

• Restart the SQL Server service from Services.msc (right-click SQL Server (MSSQLSERVER) > Restart) to reinitialize the Broker task.

Step 4: Rebuild Query Notification Infrastructure (If Corruption Persists)

• If errors continue, disable and re-enable notifications at the database level:

  ALTER DATABASE YourDatabaseName SET DISABLE_BROKER;
  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Re-run your application’s notification setup script (e.g., aspnet_regsql.exe for ASP.NET cache dependencies) to recreate the required queues and services cleanly.

Alternative or Verification Steps

• To verify the fix, re-run EXEC sp_cleanup_query_notification_subscriptions; and confirm it completes without generating Error 9219 in the SQL Server Error Log.
• If the issue persists after rebuilding the broker, restore the database from a pre-corruption backup and re-apply only the Service Broker configuration to isolate whether the corruption exists at the database or instance level.

Troubleshooting Guide: Error 9217 – Failed to Drop Unused Internal Query Notification Table

Why This Error Happens

Error 9217 occurs when SQL Server’s Service Broker attempts to clean up internal query notification tables but cannot complete the drop operation, typically due to the table being held open by an active transaction or an orphaned session lock. This can also result from Service Broker corruption, insufficient permissions on system objects, or disk I/O issues preventing metadata updates during the cleanup routine. In most cases, this is a transient issue tied to Query Notification (used by SqlDependency or SqlCacheDependency) rather than a sign of critical database corruption.

Step-by-Step Resolution Guide

Step 1: Identify Active Sessions Blocking the Internal Table

• Run the following query to check for blocking sessions related to Service Broker or system transactions:

SELECT session_id, blocking_session_id, wait_type, wait_resource 
FROM sys.dm_exec_requests 
WHERE wait_type LIKE '%BROKER%' OR wait_type LIKE '%LCK%';

• Identify any long-running or orphaned sessions using sys.dm_exec_sessions and terminate them if safe using KILL <session_id>.

Step 2: Verify and Repair Service Broker Configuration

• Check if Service Broker is enabled on the affected database:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If disabled unexpectedly or in an inconsistent state, re-enable it safely:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Ensure the Service Broker GUID is valid and not duplicated across restored databases using sys.databases.service_broker_guid.

Step 3: Run Database Consistency Checks

• Execute a full integrity check to rule out underlying corruption in system catalogs:

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

• Review the output for any allocation or consistency errors tied to internal system tables (object IDs starting with negative values, typical of Query Notification tables).

Step 4: Clear Orphaned Query Notification Subscriptions

• Manually clean up stale SqlDependency/Query Notification subscriptions if your application uses SqlDependency.Start():

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

• Restart the SQL Server service to force a clean reinitialization of internal Broker queues:
• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart.

Step 5: Apply Latest Cumulative Update

• Confirm your SQL Server 2025 build number via:

SELECT @@VERSION;

• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as this specific internal cleanup bug is often patched in maintenance releases.

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) for 24–48 hours to confirm Error 9217 does not recur during off-peak cleanup cycles.
• If the error persists, as a workaround, disable and re-enable Service Broker entirely on the database (ALTER DATABASE ... SET DISABLE_BROKER; followed by SET ENABLE_BROKER;) to force SQL Server to rebuild its internal notification table structures from scratch.

Troubleshooting Guide: SQL Server Error 9218 – Query Notification Object Limit Reached

Why This Error Happens

Error 9218 occurs when the internal Query Notification subsystem (part of the Service Broker infrastructure) exceeds its maximum allowable number of active notification objects, typically because applications are subscribing to notifications without properly disposing of SqlDependency or SqlNotificationRequest objects. This commonly results from connection pooling issues, applications not calling SqlDependency.Stop(), or a runaway loop that continuously registers new notification subscriptions. Over time, these orphaned notification objects accumulate in memory until SQL Server’s internal cap (tied to available memory and configured limits) is reached, causing new subscription requests to fail.

Step-by-Step Resolution Guide

Step 1: Identify Applications Generating Excessive Notifications

• Query the sys.dm_qn_subscriptions DMV to see active Query Notification subscriptions and their counts.
• Run: SELECT COUNT(*) AS ActiveSubscriptions, database_id FROM sys.dm_qn_subscriptions GROUP BY database_id;
• Cross-reference the database_id with application connection strings to isolate the offending service or app pool.

Step 2: Clean Up Orphaned Subscriptions and Restart the Dependency Service

• On the application server, ensure every SqlDependency.Start() call has a matching SqlDependency.Stop() call during app shutdown or recycle events.
• For ASP.NET apps on IIS (Windows Server 2025), recycle the affected Application Pool via iisreset /restart or through IIS Manager to force disposal of stale SqlDependency objects.
• If subscriptions remain stuck, manually clear them by disabling and re-enabling notifications on the affected database:
• ALTER DATABASE [YourDB] SET DISABLE_BROKER;
• ALTER DATABASE [YourDB] SET ENABLE_BROKER;

Step 3: Increase Memory Allocation or Adjust Notification Limits

• Verify SQL Server has adequate memory allocated via sp_configure 'max server memory' since Query Notifications consume memory from the Service Broker memory clerk.
• If running on Windows Server 2025 with Hyper-V, confirm the VM isn’t memory-constrained by checking Hyper-V dynamic memory settings against actual workload needs.
• Restart the SQL Server service (net stop MSSQLSERVER then net start MSSQLSERVER) after configuration changes to clear the internal object cache.

Step 4: Audit and Limit Notification Scope in Application Code

• Review application queries used with SqlDependency to ensure they meet the strict requirements for notification eligibility (no SELECT *, two-part table names, deterministic functions only).
• Reduce the polling/subscription frequency in application logic to avoid rapid re-registration of notification requests (a common cause of exponential object growth).

Alternative or Verification Steps

• After applying fixes, re-run SELECT COUNT(*) FROM sys.dm_qn_subscriptions; and confirm the count stabilizes well below previous levels without continuously climbing.
• If the error persists under legitimate high-volume use, consider migrating to SQL Server Service Broker with explicit queues instead of SqlDependency, as it offers more granular control and higher scalability for notification-heavy workloads.

Troubleshooting Guide: SQL Server Error 9215 — Query Notification Delivery Failure

Why This Error Happens

Error 9215 occurs when SQL Server’s Service Broker attempts to deliver a query notification message but fails to encode it properly, typically due to message size exceeding the configured limits or encoding/collation mismatches between the sending and receiving endpoints. This can also happen when the notification payload contains invalid characters that cannot be converted to the target encoding, or when the Service Broker queue responsible for notifications has become corrupted or is in a disabled state. Additionally, network-level interruptions or certificate issues on encrypted Service Broker endpoints can prevent proper message serialization before transmission.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Configuration and Status

• Run SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is active.
• If disabled, enable it using ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Check for orphaned or disabled queues with SELECT * FROM sys.service_queues WHERE is_receive_enabled = 0;

Step 2: Inspect Notification Message Size and Encoding Settings

• Review the query notification subscription to ensure the payload does not exceed the default message size limit (default is often constrained around 8000 bytes for certain encodings).
• Check collation settings on both the database and the SQL Native Client/ODBC driver using SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation');
• Ensure the client application is using a compatible driver version (SQL Server Native Client 11.0 or Microsoft ODBC Driver 18+ recommended for SQL Server 2025 on Windows Server 2025/Windows 11).

Step 3: Clear and Rebuild the Notification Queue

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Execute ALTER QUEUE [QueueName] WITH STATUS = OFF; followed by ALTER QUEUE [QueueName] WITH STATUS = ON; to reset the queue state.
• If corruption is suspected, drop and recreate the queue and associated service using DROP QUEUE [QueueName]; and CREATE QUEUE [QueueName];

Step 4: Check Windows Firewall and Certificate Bindings

• Navigate to Control Panel > Windows Defender Firewall > Advanced Settings on Windows Server 2025 and confirm the Service Broker endpoint port (default 4022) is not blocked.
• Verify certificate validity for encrypted endpoints using SELECT * FROM sys.certificates; and check expiration dates.
• Restart the SQL Server Service Broker endpoint with ALTER ENDPOINT [EndpointName] STATE = STARTED; if it was found stopped.

Step 5: Restart SQL Server Service and Monitor Logs

• Open services.msc, locate “SQL Server (MSSQLSERVER)” or your named instance, and perform a restart.
• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for recurrence of Error 9215.

Alternative or Verification Steps

• To verify the fix, trigger a test query notification subscription and confirm successful delivery by checking sys.dm_qn_subscriptions for an active, non-error status.
• If the issue persists, consider switching the notification delivery service to use a local Service Broker instance only (bypassing external network delivery) by setting the notification service to LOCAL scope, which eliminates encoding issues tied to cross-network transmission.

Troubleshooting Guide: SQL Server Error 9216 — Failed to Delete Expired Query Notification Subscription

Why This Error Happens

This error occurs when SQL Server’s internal background task (the SQL Server Broker-driven cleanup process) attempts to remove an expired Query Notification subscription from the system tables but encounters a locking conflict, a corrupted internal queue, or insufficient permissions on the master or msdb-related system objects. It is commonly triggered when the Service Broker queue associated with query notifications (SqlQueryNotificationService-*) becomes blocked by long-running transactions, orphaned dialogs, or when the subscription metadata in sys.dm_qn_subscriptions references a session that terminated abnormally. In some cases, this is also caused by disk I/O latency or a corrupted system catalog affecting the notification cleanup job that runs periodically.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status and Activation

• Confirm Service Broker is enabled on the database using: SELECT is_broker_enabled FROM sys.databases WHERE name = 'YourDBName';
• If it returns 0, enable it with: ALTER DATABASE YourDBName SET ENABLE_BROKER;
• Check for blocked or orphaned conversations: SELECT * FROM sys.conversation_endpoints WHERE state NOT IN ('CD');

Step 2: Identify and Clear Stale Query Notification Subscriptions

• Run SELECT * FROM sys.dm_qn_subscriptions; to list active subscriptions and identify expired or orphaned entries (check the expiration_time column against current server time).
• If stale entries exist with past expiration timestamps still lingering, manually terminate the owning session: KILL <session_id>; (only if the session is confirmed idle/orphaned).
• Clear any dead-lettered messages in the notification queue: SELECT * FROM QueryNotificationErrorsQueue; (inspect for poison messages causing cleanup failures).

Step 3: Check Permissions on System Objects

• Ensure the SQL Server service account has proper CONTROL permission on the QueryNotificationErrorsQueue and SqlQueryNotificationService-* service/queue objects.
• Run: SELECT * FROM sys.service_queues WHERE name LIKE 'SqlQueryNotificationService%'; to confirm the queue is ENABLED and not in a SUSPENDED state.
• If suspended, re-enable with: ALTER QUEUE [QueueName] WITH STATUS = ON;

Step 4: Rebuild Broker GUID if Corruption Is Suspected

• If errors persist and no permission/locking issue is found, the Service Broker GUID may be corrupted after a restore or failover on Windows Server 2025.
• Run: ALTER DATABASE YourDBName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (⚠️ this drops existing conversations — use only in non-production or after confirming no active dependent processes).
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to force re-initialization of the Broker task.

Step 5: Review SQL Server Error Log and Windows Event Viewer

• Open the SQL Server error log via EXEC sp_readerrorlog 0, 1, 'Query Notification'; to check for correlating 9216 timestamps.
• Cross-reference with Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > SQLServer for I/O or access-denied warnings occurring at the same time.

Alternative or Verification Steps

• To verify the fix, monitor sys.dm_qn_subscriptions and the SQL error log for 24–48 hours to confirm Error 9216 no longer appears during the notification cleanup cycle.
• If the issue persists, as an alternative, disable and re-enable Query Notifications at the application level (dropping and recreating the SqlDependency or SqlNotificationRequest objects) to force a clean subscription state rather than relying on automatic expiration cleanup.

Troubleshooting Guide: SQL Server Error 9213 (Query Notification / Broker Dialog Failure)

Why This Error Happens

Error 9213 occurs when SQL Server’s Service Broker cannot establish a dialog conversation for a query notification subscription, typically because the Service Broker is disabled on the target database or the database GUID has changed (often after a restore, attach, or mirroring failover) without updating the broker configuration. This can also happen if the SET ENABLE_BROKER option was never enabled, the database is in single-user mode, or permissions on the service/queue/contract objects used by the notification subscription have been revoked or corrupted.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Open SQL Server Management Studio (SSMS) or a query window connected to the instance.
• Run the following query to check broker status:

  SELECT name, is_broker_enabled, service_broker_guid 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: this command requires exclusive database access; disconnect other active connections first.

Step 2: Fix Broker GUID Mismatch After Restore or Attach

• If the database was recently restored, attached, or failed over (AlwaysOn/mirroring), the service_broker_guid may no longer match the original.
• Run this to regenerate a new broker ID and resolve orphaned dialog references:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER;

• Alternatively, if you need to preserve existing conversations, use:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 3: Validate Permissions on Broker Service and Queue Objects

• Confirm the login/user account running the notification subscription has SUBSCRIBE QUERY NOTIFICATIONS permission:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [DOMAIN\ServiceAccount];

• Check that the SqlQueryNotificationService queue and service exist and are not disabled:

  SELECT * FROM sys.service_queues WHERE name LIKE '%QueryNotification%';
  SELECT * FROM sys.services WHERE name LIKE '%QueryNotification%';

• If missing, restart the application pool or service that initializes query notifications, as SQL Server auto-creates these objects on first use.

Step 4: Restart SQL Server Service and Clear Stale Connections

• On Windows Server 2025 / Windows 11, open Services.msc and restart the SQL Server (MSSQLSERVER) service to force Service Broker to reinitialize dialog endpoints.
• Alternatively, run from an elevated PowerShell prompt:

  Restart-Service -Name "MSSQLSERVER" -Force

• This clears any stale broker dialog handles left over from a previous crash or failover event.

Alternative or Verification Steps

• Re-run your application’s query notification subscription and confirm no Error 9213 appears in the SQL Server Error Log (Management > SQL Server Logs in SSMS) or Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
• If the issue persists, drop and recreate the Service Broker service/queue objects manually using CREATE SERVICE and CREATE QUEUE, then restart the dependent application to force re-registration of the notification subscription.

Troubleshooting Guide: SQL Server Error 9214 – Query Notification Delivery Failed to Decode Service Broker Error Message

Why This Error Happens

Error 9214 occurs when the Service Broker’s query notification mechanism receives a malformed or unreadable error message, typically caused by internal message corruption or a mismatch in the message format expected by the SqlDependency/SqlNotificationRequest subsystem. This often stems from damaged Service Broker queues or conversation endpoints in the msdb or application database, incorrect collation settings between the message initiator and target, or network packet corruption during transmission. It can also surface when Service Broker is in an inconsistent state after a database restore, mirroring failover, or an abrupt service restart that left orphaned conversations behind.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled and Healthy

• Run SELECT is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm Service Broker is active.
• If is_broker_enabled returns 0, enable it with ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• Check for orphaned conversations using SELECT * FROM sys.conversation_endpoints WHERE state_desc <> 'CONVERSING';

Step 2: Clean Up Corrupted Queues and Conversations

• Identify the query notification queue (usually named like SqlQueryNotificationService-xxxx) with SELECT * FROM sys.service_queues;
• End any stuck or erroring conversations manually: END CONVERSATION 'conversation_handle' WITH CLEANUP;
• If the queue itself is corrupted, drop and recreate the notification service and queue using DROP QUEUE and CREATE QUEUE, then re-register with sp_add_dependency or restart the SqlDependency listener in your application.

Step 3: Check Windows Server 2025 Network and Firewall Configuration

• Navigate to Control Panel > System and Security > Windows Defender Firewall > Advanced Settings and confirm TCP port 4022 (default Service Broker port) is not blocked.
• Verify the certificate or Windows Authentication used for Service Broker endpoint encryption is valid: SELECT * FROM sys.service_broker_endpoints;
• Restart the SQL Server service via services.msc or Restart-Service -Name 'MSSQLSERVER' in PowerShell (run as Administrator) to clear any transient network-layer corruption.

Step 4: Rebuild the Notification Infrastructure in the Application Database

• Run EXEC sp_configure 'clr enabled', 1; RECONFIGURE; if your notification logic depends on CLR integration and it was inadvertently disabled.
• Re-run the setup script for Query Notifications, ensuring SqlDependency.Start(connectionString) is called correctly in the application layer before any command execution.
• Confirm the AqTaskLimit and Broker-related trace flags (e.g., 1222 for deadlock logging related to Broker locks) aren’t causing silent message drops by reviewing the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Alternative or Verification Steps

• To verify the fix, trigger a test query notification subscription and monitor sys.dm_broker_queue_monitors and the SQL Server Error Log to confirm Error 9214 no longer appears during message delivery.
• If the issue persists, as a fallback, fully disable and re-enable Service Broker on the database (ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; followed by SET ENABLE_BROKER;) during a maintenance window, which forces a fresh broker GUID and clears all residual corrupted state.

Troubleshooting Guide: SQL Server Error 9210 – Query Notification Delivery Failure

Why This Error Happens

Error 9210 occurs when SQL Server’s Query Notification feature (built on Service Broker) is unable to deliver a notification message because the underlying Service Broker dialog has encountered an issue. This is typically caused by Service Broker being disabled on the database, expired or corrupted conversation dialogs, certificate/routing mismatches for message encryption, or network/firewall interference between endpoints. In many cases, the root issue is that the SET ENABLE_BROKER option was never configured correctly after a database restore, migration, or upgrade to SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check the broker status:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it using:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: This command requires exclusive database access, so schedule it during a maintenance window.

Step 2: Inspect and Clear Broken or Orphaned Conversations

• Query the sys.conversation_endpoints view to identify dialogs stuck in an error state:

SELECT conversation_handle, far_service, state_desc, lifetime 
FROM sys.conversation_endpoints 
WHERE state_desc != 'CONVERSING';

• End any faulty conversations manually to clear the backlog:

END CONVERSATION 'conversation_handle_value' WITH CLEANUP;

• Check the SQL Server Error Log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025) for correlating Broker errors like 9617 or 9666.

Step 3: Validate Certificates and Master Key Encryption

• If notifications rely on encrypted dialogs, ensure the Service Master Key and Database Master Key are intact and not corrupted:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourStrongPassword';

• Re-create the Service Broker certificate if it has expired:

CREATE CERTIFICATE SSB_Cert WITH SUBJECT = 'Service Broker Certificate', START_DATE = '2025-01-01', EXPIRY_DATE = '2030-01-01';

• Confirm that TCP port 4022 (default Service Broker endpoint port) is not blocked by Windows Defender Firewall on Windows Server 2025.

Step 4: Restart the SQL Server Service to Reset Broker Threads

• Open Services.msc (or use PowerShell) to restart the instance:

Restart-Service -Name 'MSSQLSERVER' -Force

• This clears any hung internal Broker worker threads that may be silently blocking message delivery without throwing a visible deadlock error.

Alternative or Verification Steps

• Verify the Fix: Re-run your Query Notification subscription (e.g., via SqlDependency in .NET or SqlCacheDependency) and monitor the sys.transmission_queue view—it should remain empty if messages are being delivered successfully.
• Alternative Fix: If errors persist, drop and recreate the specific Query Notification Service and Queue (e.g., DROP SERVICE/DROP QUEUE followed by CREATE SERVICE/CREATE QUEUE) to rule out metadata corruption in the specific broker objects rather than the whole database.

Troubleshooting Guide: SQL Server Error 9211

Why This Error Happens

Error 9211 occurs when SQL Server’s Query Notification feature (SQL Broker dependent) cannot verify pending notifications because the target database itself failed to open properly. This is typically caused by the database being in an inaccessible state (SUSPECT, RECOVERY PENDING, or OFFLINE), a disabled or misconfigured Service Broker GUID, or insufficient permissions for the service account attempting to access the database files. In some cases, corrupted system catalog metadata related to Service Broker queues can also trigger this error.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Accessibility

• Open SQL Server Management Studio (SSMS) and run: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Confirm the state is ONLINE. If it shows SUSPECT, RECOVERY_PENDING, or OFFLINE, address the underlying recovery issue first using RESTORE DATABASE or ALTER DATABASE ... SET ONLINE.
• Check the SQL Server error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context on why the database failed to open.

Step 2: Validate Service Broker Configuration

• Run the following query to check if Service Broker is enabled: SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';
• If is_broker_enabled returns 0, enable it using: ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;
• If the database was restored or copied, the broker GUID may conflict. Run: ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (use only if message delivery history is not critical).

Step 3: Check File System and Service Account Permissions

• Navigate to the database file location (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Right-click the .mdf and .ldf files, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out corruption in system tables tied to Service Broker queues.
• If corruption is found, review repair options (REPAIR_REBUILD first, avoiding REPAIR_ALLOW_DATA_LOSS unless necessary) based on the DBCC output.

Alternative or Verification Steps

• To verify the fix, re-run the query notification subscription (e.g., via SqlDependency in your application) and confirm Error 9211 no longer appears in the SQL Server error log or Windows Event Viewer (Application log, source: MSSQLSERVER).
• If the issue persists, as a temporary workaround, disable and re-enable Service Broker entirely using ALTER DATABASE YourDatabaseName SET DISABLE_BROKER; followed by ALTER DATABASE YourDatabaseName SET ENABLE_BROKER; to reset internal broker state.

Troubleshooting Guide: SQL Server Error 9208 – Query Notification Dialog Endpoint Failure

Why This Error Happens

Error 9208 occurs when SQL Server’s Service Broker cannot establish a dialog endpoint for a query notification subscription, typically because the Service Broker is disabled on the database or the SERVICE_BROKER_GUID is mismatched/duplicated after a database restore or copy. This can also happen when the endpoint lacks the necessary SEND permissions on the service, the master key is missing/inaccessible for encrypted dialogs, or the Service Broker endpoint itself is disabled at the instance level. Additionally, orphaned or corrupted conversation routes and services left over from a previous configuration can block new dialog creation.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Run the following query to check the status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: This command requires exclusive access, so ensure no active connections are blocking it during off-peak hours.

Step 2: Check and Repair Service Broker GUID Conflicts

• Query the broker GUID to confirm it is unique and not a leftover copy identifier:

  SELECT name, service_broker_guid FROM sys.databases WHERE name = 'YourDatabaseName';

• If the database was restored or attached from a backup, run:

  ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

• This regenerates a new broker instance ID, resolving endpoint collisions with the source database.

Step 3: Validate Permissions on the Target Service and Queue

• Confirm the user account initiating the subscription has SUBSCRIBE QUERY NOTIFICATIONS permission:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourLoginName];

• Ensure the SqlQueryNotificationService and associated queue (SqlQueryNotificationService-xxxx) exist and have not been manually dropped.
• Grant SEND permission on the service explicitly if custom security is configured:

  GRANT SEND ON SERVICE::[SqlQueryNotificationService] TO [YourLoginName];

Step 4: Inspect the Service Broker Endpoint and Firewall Rules (Windows Server 2025)

• Check if a TCP endpoint for Service Broker is active and not blocked by Windows Defender Firewall:

  SELECT * FROM sys.service_broker_endpoints;

• On Windows Server 2025/Windows 11, verify the firewall allows inbound traffic on the configured Broker port (default 4022) via Windows Defender Firewall with Advanced Security.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after making endpoint changes.

Alternative or Verification Steps

• To verify the fix, re-run the application that triggers the query notification and monitor sys.dm_broker_connections and sys.transmission_queue for active, error-free dialogs.
• If the error persists, drop and recreate the Service Broker service and queue manually using CREATE QUEUE and CREATE SERVICE, then re-issue the notification subscription.

Troubleshooting Guide: SQL Server Error 9209 — Query Notification Dialog Failure

Why This Error Happens

Error 9209 occurs when the Service Broker cannot establish a dialog between the query notification service and the target broker instance, typically because the SqlQueryNotificationService-<GUID> service or route has become invalid, disabled, or was never properly initialized in the target database. This is commonly caused by Service Broker being disabled (is_broker_enabled = 0), a mismatched or stale broker GUID after a database restore/attach, or insufficient permissions on the service or contract required to initiate the conversation. Corrupted or orphaned routes in sys.routes, as well as firewall/port restrictions blocking broker endpoint communication, can also trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Run the following query to check broker status:

  SELECT name, is_broker_enabled, service_broker_guid 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Note: If the database was restored or attached, you may instead need SET NEW_BROKER to regenerate a fresh GUID.

Step 2: Validate the Query Notification Service and Queue Exist

• Check for the auto-generated service and queue:

  SELECT * FROM sys.services WHERE name LIKE 'SqlQueryNotificationService%';
  SELECT * FROM sys.service_queues WHERE name LIKE 'SqlQueryNotificationService%';

• If missing or orphaned, drop and let SQL Server regenerate them automatically on the next SqlDependency.Start() call from the application.
• Confirm the login executing the notification subscription has SUBSCRIBE QUERY NOTIFICATIONS permission:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourAppLogin];

Step 3: Check Service Broker Endpoint and Firewall Configuration

• On Windows Server 2025 / Windows 11, confirm the TCP endpoint for Service Broker (default port 4022) is active:

  SELECT * FROM sys.tcp_endpoints WHERE type_desc = 'SERVICE_BROKER';

• Open the required port in Windows Defender Firewall:

  New-NetFirewallRule -DisplayName "SQL Server Broker" -Direction Inbound -Protocol TCP -LocalPort 4022 -Action Allow

• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) to apply endpoint changes.

Step 4: Clear Orphaned Routes and Restart the Application Dependency

• Remove any stale broker routes pointing to invalid instances:

  SELECT * FROM sys.routes;
  DROP ROUTE [RouteName]; -- only if confirmed orphaned

• Restart the client application or IIS app pool so SqlDependency re-registers a fresh notification subscription and service.

Alternative or Verification Steps

• Re-run the query notification subscription from the application and confirm no 9209 error appears in SQL Server Error Log or Event Viewer (Applications and Services Logs → MSSQLSERVER).
• As an alternative, manually test dialog establishment using BEGIN DIALOG CONVERSATION with the same service name/contract to isolate whether the issue is Service Broker configuration or application-side SqlDependency logic.

Troubleshooting Guide: SQL Server Error 9206 – Query Notification Subscription Cannot Be Deleted

Why This Error Happens

This error occurs when an application or internal process attempts to explicitly remove a Query Notification subscription using sys.sp_dispatcher_end_dialog or similar cleanup logic, but the subscription ID (%ld) has already been removed from the internal notification queue. This typically happens because the notification already “fired” naturally (the underlying data changed or the subscription timed out) before the manual deletion request was processed, creating a race condition. In some cases, this is also triggered by corrupted Service Broker queues or orphaned conversation handles left behind after an application crash or improper connection termination.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Status

• Confirm that Service Broker is enabled for the specific database, as Query Notifications rely entirely on this feature.
• Run the following T-SQL query to check the status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it immediately using ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;.

Step 2: Inspect and Clear Orphaned Conversations

• Check the sys.conversation_endpoints system view to identify dialogs stuck in a CLOSED or DISCONNECTED_INBOUND state that haven’t been garbage collected.
• Execute the following to view problematic endpoints:

  SELECT conversation_handle, state_desc, far_service FROM sys.conversation_endpoints;

• If a high volume of stale endpoints exists, manually end them using END CONVERSATION conversation_handle WITH CLEANUP; to force the removal of the orphaned subscription data.

Step 3: Update Application Logic for Race Conditions

• Modify the application code (likely utilizing SqlDependency or SqlNotificationRequest in .NET) to wrap the unsubscribe logic in a try-catch block specifically handling SQL Exception 9206.
• Since this error is often benign (indicating the resource is already gone), ensure the application logic treats this specific exception as a “soft success” rather than a critical failure requiring a retry loop.

Step 4: Restart the SQL Server Service Broker Task

• If errors persist across multiple databases, restart the SQL Server service to reset the internal Service Broker task manager.
• On Windows Server 2025, open Services.msc, locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.

Alternative or Verification Steps

• Verification: After implementing the fix, monitor the SQL Server Error Log via EXEC sp_readerrorlog; for 24 hours to ensure Error 9206 does not reappear during standard application polling cycles.
• Alternative: If the issue persists due to high-frequency triggers, consider migrating the dependency logic to SignalR or a dedicated Change Tracking mechanism, as Query Notifications are generally recommended only for low-frequency update scenarios in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 9207 – Query Notification Dialog Closed

Why This Error Happens

Error 9207 occurs when the Service Broker dialog used for Query Notifications encounters a transport-level or protocol error, causing the conversation endpoint to terminate unexpectedly. This is commonly triggered by network interruptions between the SQL Server instance and the subscribing client, certificate or authentication mismatches on the dialog security, or message size/format corruption in the notification payload. It can also occur when the SqlDependency/SqlNotificationRequest queue has been dropped or recreated while a conversation was still active, orphaning the handle.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled and Healthy

• Run the following to confirm Broker is active on the database:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

• Check for orphaned conversations in sys.conversation_endpoints and end any stuck in an ERROR or DISCONNECTED state.

Step 2: Inspect and Clear Stale Notification Queues

• Query the system queue for backlog or poison messages:

SELECT * FROM sys.transmission_queue;

• If messages show repeated transmission failures, drop and recreate the notification queue/service tied to SqlDependency:

DROP QUEUE [dbo].[SqlQueryNotificationService-xxxx];

• Restart the application pool or service (IIS/Windows Service) so a fresh queue and conversation handle are generated on next connection.

Step 3: Validate Network and Firewall Configuration on Windows Server 2025 / Windows 11

• Confirm the TCP port used by Service Broker endpoints (if cross-instance) is open:

Get-NetFirewallRule -DisplayName "*SQL Server Broker*"

• Ensure no intermediate proxy, load balancer, or Windows Defender Firewall rule is silently resetting long-lived TCP connections used by the dialog.
• If using encrypted dialogs, verify the certificate used for Service Broker authentication has not expired:

SELECT name, expiry_date FROM sys.certificates;

Step 4: Review SQL Server Error Log for Underlying Detail

• Open SQL Server Configuration Manager or navigate to the log path (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• Search for the full text accompanying Error 9207, which typically includes a secondary error number (e.g., a transport or security error) that pinpoints the exact cause.
• Cross-reference that secondary error code in Microsoft’s documentation to apply a targeted fix (e.g., certificate renewal, permission grant on the queue).

Step 5: Recreate the SqlDependency/SqlNotificationRequest Subscription

• In the application code, ensure SqlDependency.Stop() is called cleanly before SqlDependency.Start() on app restart to avoid orphaned handles.
• Re-register the dependency command after confirming the queue and service exist and are activated:

SELECT * FROM sys.service_queues WHERE name LIKE '%SqlQueryNotificationService%';

Alternative or Verification Steps

• Verify the fix by triggering a test notification subscription and confirming no 9207 entries appear in the SQL error log or Windows Event Viewer (Application log, Source: MSSQLSERVER) over a 24-hour monitoring window.
• If the error persists, as an alternative, disable Query Notifications temporarily and switch to a polling-based or Change Tracking approach while further diagnosing certificate/network instability with Microsoft Support.

Troubleshooting Guide: SQL Server Error 9205 – Query Notification Subscription Permission Denied

Why This Error Happens

This error occurs because the user account attempting to request a Query Notification subscription (typically via SqlDependency or Service Broker) lacks the required SUBSCRIBE QUERY NOTIFICATIONS permission at the database level. SQL Server 2025 enforces this permission explicitly, and it is not granted by default even to users with dbdatareader or dbdatawriter roles. Additionally, Service Broker may be disabled on the target database, which can trigger related permission and subscription failures even if the grant exists.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker is Enabled on the Database

• Open SQL Server Management Studio (SSMS) and connect to your SQL Server 2025 instance.
• Run the following query to check Service Broker status:

SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

Step 2: Grant the Required Subscription Permission

• Connect to the target database in SSMS (not the master database).
• Execute the following T-SQL command, replacing the username and database name as needed:

USE YourDatabaseName;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [DOMAIN\Username];

• If using a SQL Login instead of a Windows account, reference the mapped database user name instead.

Step 3: Confirm Application Connection String and Context

• Ensure the application’s connection string specifies the correct database in the Initial Catalog or Database parameter.
• Verify the application is not attempting the subscription while connected to master or a different context than where the GRANT was applied.
• Restart the application pool (IIS) or application service on Windows Server 2025/Windows 11 to clear any cached connection permissions.

Step 4: Check for Group Policy or Firewall Interference (Windows Server 2025 specific)

• On Windows Server 2025, open Windows Defender Firewall with Advanced Security and confirm the SQL Server Service Broker port (default TCP 4022) is not blocked.
• Navigate to Control Panel\All Control Panel Items\Windows Defender Firewall\Advanced Settings and inspect inbound rules for SQL Server.
• If using domain Group Policy, confirm no GPO is stripping custom database permissions on service restart.

Alternative or Verification Steps

• To verify the fix, run SELECT * FROM sys.database_permissions WHERE permission_name = 'SUBSCRIBE QUERY NOTIFICATIONS'; in the target database and confirm the user/role is listed with state_desc = 'GRANT'.
• If the error persists, check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for Service Broker initialization errors, as a corrupted Service Broker GUID may require running ALTER DATABASE YourDatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; (note: this resets conversation queues).

Troubleshooting Guide: SQL Server Error 9203 – Invalid Query Notification Subscription Service Name

Why This Error Happens

Error 9203 occurs when the SQL Server Service Broker cannot resolve the service name specified in a query notification subscription, typically because the SqlDependency or SqlNotificationRequest object references a malformed or non-existent service name. This often happens when Service Broker is disabled on the target database, the service name string contains invalid characters or improper formatting, or the application’s connection string points to a database where notification services were never properly initialized via SqlDependency.Start(). In some cases, this is also triggered by a mismatch between the service name registered in sys.services and the one the client application is attempting to subscribe to.

Step-by-Step Resolution Guide

Step 1: Verify Service Broker Is Enabled on the Database

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to check status:

  SELECT name, is_broker_enabled FROM sys.databases WHERE name = 'YourDatabaseName';

• If is_broker_enabled returns 0, enable it with:

  ALTER DATABASE YourDatabaseName SET ENABLE_BROKER;

• Note: This command may require exclusive database access; close other active connections first.

Step 2: Validate the Query Notification Service Name

• Check existing services registered in the database:

  SELECT * FROM sys.services;

• Confirm the service name matches exactly what your application code passes to SqlDependency or SqlNotificationRequest.Options.
• Ensure the format follows the pattern: service=ServiceName;local database=DatabaseName without trailing spaces or unsupported special characters.

Step 3: Reinitialize SqlDependency in the Application Layer

• In your .NET application code, confirm SqlDependency.Start(connectionString) is called once at application startup (not per-request).
• If using a custom queue/service, explicitly recreate it:

  CREATE QUEUE YourNotificationQueue;
  CREATE SERVICE [YourServiceName] ON QUEUE YourNotificationQueue 
  ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

• Restart the application pool (IIS) or the Windows Service hosting the application to force re-registration.

Step 4: Check SQL Server and Windows Server Permissions

• Navigate to SQL Server Configuration Manager on Windows Server 2025.
• Confirm the SQL Server service account has SUBSCRIBE QUERY NOTIFICATIONS permission:

  GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourLoginName];

• Restart the SQL Server service (services.msc → SQL Server (MSSQLSERVER) → Restart) to apply permission changes.

Step 5: Review SQL Server Error Logs for Broker-Specific Failures

• Open the SQL Server error log via SSMS: Management → SQL Server Logs.
• Filter for keywords “Broker” or “9203” to identify any additional context, such as certificate or endpoint mismatches in cross-database scenarios.
• Cross-reference with Windows Event Viewer (Event ID 9203 under Application logs) for timestamp correlation.

Alternative or Verification Steps

• Run a test subscription using a minimal T-SQL script with WAITFOR (RECEIVE ...) against the queue to confirm Service Broker is actively listening without throwing 9203.
• If the error persists, drop and recreate the Service Broker service and queue entirely (DROP SERVICE / DROP QUEUE, then recreate), as corrupted metadata in sys.services can sometimes require a full reset rather than a repair.