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

by

Troubleshooting Guide: SQL Server Error 2020

Why This Error Happens

Error 2020 occurs when SQL Server’s sys.sql_expression_dependencies mechanism cannot fully resolve all column-level references within a stored procedure, view, or function. This typically happens because the entity references an object (table, view, or column) that does not exist at the time of compilation, was renamed, or was dropped without updating dependent objects. It can also surface due to deferred name resolution, where SQL Server allows creation of objects referencing non-existent tables, causing dependency metadata to be incomplete or stale.

Step-by-Step Resolution Guide

Step 1: Identify the Problematic Entity and Its Dependencies

  • Run SELECT * FROM sys.dm_sql_referenced_entities('schema.object_name', 'OBJECT'); to see what the entity references.
  • Cross-check with sys.sql_expression_dependencies to find broken or ambiguous links.
  • Note any referenced_minor_id = 0, which indicates SQL Server could not resolve specific columns.

Step 2: Validate Underlying Objects and Schema Consistency

  • Confirm that all tables, views, and columns referenced in the entity’s definition actually exist using sys.objects and sys.columns.
  • Check for recent schema changes (renamed columns, dropped tables) using SQL Server Audit or the default trace.
  • If using synonyms or cross-database references, verify the target database and object are accessible and online.

Step 3: Recompile and Refresh Dependency Metadata

  • Execute EXEC sp_refreshsqlmodule 'schema.object_name'; to force SQL Server to recompile and refresh dependency information.
  • For views, run EXEC sp_refreshview 'schema.view_name'; instead.
  • If the object uses dynamic SQL or temp tables, note that these are expected to show incomplete dependencies — this is often a false positive rather than a true error.

Step 4: Rebuild or Correct the Entity Definition

  • Script out the object definition using SSMS (Right-click > Script Object as > CREATE To).
  • Manually correct any typos, outdated column names, or missing schema prefixes.
  • Drop and recreate the object with CREATE OR ALTER to ensure SQL Server re-evaluates all dependencies cleanly.

Alternative or Verification Steps

  • After applying fixes, re-run the original query that triggered Error 2020; if it completes without warning, dependencies have been successfully resolved.
  • As an alternative, use sys.dm_sql_referenced_entities with the 'OBJECT' parameter in a validation script scheduled via SQL Agent to proactively catch broken dependencies before they cause runtime errors.