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

by

Troubleshooting Guide: SQL Server Error 9980 — “Variable parameters cannot be passed to fulltext predicates: contains”

Why This Error Happens

This error occurs because SQL Server’s full-text search engine (CONTAINS/FREETEXT predicates) requires the search string to be resolved at parse/compile time, not at execution time. When a Transact-SQL variable is passed directly as the search condition inside a CONTAINS predicate, the query optimizer cannot statically validate or optimize the full-text expression, triggering Error 9980. This typically happens in stored procedures, parameterized queries, or dynamically generated T-SQL where developers attempt to pass @variable directly into CONTAINS(column, @variable) instead of using dynamic SQL or parameterized full-text constructs correctly.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Locate the stored procedure, function, or ad-hoc query throwing Error 9980.
  • Check SQL Server Management Studio (SSMS) error details or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the exact line number.
  • Confirm the query uses a pattern like WHERE CONTAINS(column_name, @searchVariable).

Step 2: Convert to Dynamic SQL Execution

  • Rewrite the static query to use sp_executesql so the variable is injected into the SQL string before execution, rather than passed as a native parameter to CONTAINS.
  • Example fix:
  DECLARE @sql NVARCHAR(MAX);
  DECLARE @searchTerm NVARCHAR(100) = '"server*"';
  SET @sql = N'SELECT * FROM Documents WHERE CONTAINS(ContentColumn, @term)';
  EXEC sp_executesql @sql, N'@term NVARCHAR(100)', @term = @searchTerm;
  • Note: sp_executesql does support parameterization for CONTAINS, unlike inline variable substitution — this is the officially supported workaround from Microsoft.

Step 3: Validate Full-Text Catalog and Index Health

  • Run the following to ensure the underlying full-text catalog is not corrupted, which can sometimes mask as predicate errors:
  SELECT * FROM sys.fulltext_catalogs;
  SELECT * FROM sys.dm_fts_index_population;
  • If the catalog shows a “FAILED” or “PAUSED” status, rebuild it:
  ALTER FULLTEXT CATALOG YourCatalogName REBUILD;

Step 4: Update SQL Server 2025 Cumulative Update

  • Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11 and verify the current build number.
  • Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to check the version string.
  • Apply the latest Cumulative Update (CU) from Microsoft, as early 2025 builds had known regressions with full-text predicate parameter binding.

Alternative or Verification Steps

  • Verify the Fix: Re-run the stored procedure or query using sp_executesql with a sample parameter value and confirm Error 9980 no longer appears in SSMS or the SQL Server log.
  • Alternative Workaround: If sp_executesql is not feasible due to application architecture, use CONTAINSTABLE() in a derived table/JOIN instead of embedding CONTAINS directly in the WHERE clause, as CONTAINSTABLE often handles parameterized inputs more flexibly in legacy codebases.