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

by

Why This Error Happens

Error 4166 (“Invalid PROBMATCH subquery”) occurs when SQL Server’s fuzzy matching engine encounters a malformed or unsupported subquery structure inside a PROBMATCH() function call, typically used in Data Quality Services (DQS) or Master Data Services (MDS) matching policies. This is commonly triggered by schema drift in the reference knowledge base, corrupted matching policy metadata, or a subquery that references columns outside the scope of the matching domain. It can also surface after an in-place upgrade to SQL Server 2025 if legacy matching policies created in earlier versions use deprecated subquery syntax that the new query optimizer no longer silently tolerates.

Step-by-Step Resolution Guide

Step 1: Validate the Matching Policy Metadata

  • Open SQL Server Management Studio (SSMS) and connect to the DQS/MDS instance.
  • Run EXEC dbo.sp_validate_matching_policy @PolicyName = 'YourPolicyName'; to check for orphaned or malformed subquery references.
  • Look specifically for NULL or mismatched domain_id values in the mdm.tbl_MatchingRule table.

Step 2: Rebuild the PROB_MATCH Subquery Definition

  • Navigate to C:\Program Files\Microsoft SQL Server\160\DQS\Data (adjust version path for SQL Server 2025 installs) and locate the policy export XML.
  • Open the matching policy in DQS Client, delete the affected PROB_MATCH clause, and manually recreate it, ensuring all referenced columns exist in the current knowledge base schema.
  • Save and republish the policy using Publish Knowledge Base from the DQS Client menu.

Step 3: Clear Cached Execution Plans

  • Run the following in a new query window to purge stale cached plans that may still reference the broken subquery:
  • DBCC FREEPROCCACHE;
  • DBCC DROPCLEANBUFFERS; (only in non-production environments)
  • Restart the SQL Server DQS Cleansing service via services.msc on Windows Server 2025 / Windows 11.

Step 4: Apply the Latest Cumulative Update

  • Confirm your build number with SELECT @@VERSION;.
  • Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as several known PROB_MATCH parser bugs are patched in post-RTM builds.
  • Reboot the server and re-run the matching job to confirm resolution.

Alternative or Verification Steps

  • Re-execute the original matching job or SSIS Fuzzy Lookup task; if Error 4166 no longer appears in the SQL Server Error Log (Application and Services Logs > SQL Server in Event Viewer), the fix is confirmed.
  • If the error persists, export the matching policy to XML, manually strip all PROB_MATCH subqueries, and rebuild the policy from scratch rather than editing the corrupted version.