Fix “Msg 207: Invalid Column Name” in SQL Server

Msg 207, Level 16, State 1, Invalid column name 'x'. means SQL Server parsed your query fine and then couldn’t find a column you referenced on the object you referenced it against. It’s one of the most common T-SQL errors there is, and also one of the most misleading, because the column usually does exist, just not where the query is looking for it. This happens constantly when writing queries against system catalog views and DMVs, where two views that sound like they should have the same columns often don’t.

Every example below is a real error this project’s own repo hit while building dba-tools, a working SQL Server toolkit, not invented for this post. They’re grouped by the actual pattern behind the mistake, because the pattern is what generalizes to your own query.

The Diagnostic Move: Find Out What’s Actually There

Before guessing, check what columns the object actually has:

SELECT c.name
FROM sys.all_columns c
JOIN sys.all_objects o ON o.object_id = c.object_id
WHERE o.name = 'the_view_or_table_name'
ORDER BY c.column_id;

For a DMV specifically, sys.dm_exec_describe_first_result_set against a minimal SELECT * FROM <dmv> also works, and Microsoft’s own DMV reference pages list every column per view, worth a direct check when the view name is unfamiliar. The rest of this post is about why the column isn’t where you expected, which the column list alone won’t tell you.

Microsoft’s reference covers MSSQLSERVER_207 and the system dynamic management views in full.

The column list tells you what is there. It does not tell you why the one you wanted is not, and that is what decides the fix. Three causes account for almost all of it, and each one takes a different fix. Find the row that matches what you are looking at.

Cause 1: The Column Is on a Different Object

The most common cause by a distance. Two or three catalog views describe related parts of one thing, and the column you want lives on a different one from the view you queried. It happens two ways: the column belongs to the object’s module or its running state rather than its definition, or it only makes sense for one subtype and therefore lives on the narrower view for that subtype. Either way the fix is the same: find the view that owns it and join.

Real example: Get-DdlTriggers.sql referenced t.execute_as_principal_id on sys.server_triggers. That column doesn’t exist there; DDL triggers don’t carry their own EXECUTE AS principal directly. The EXECUTE AS context lives on the trigger’s underlying module, sys.server_sql_modules, joined on object_id. The fix was joining to the module view and reading m.execute_as_principal_id from there.

Real example: Get-AvailabilityGroupReplicaState.sql referenced synchronization_state_desc on sys.dm_hadr_availability_replica_states (the replica-level DMV). That column is on sys.dm_hadr_database_replica_states (the database-level DMV) instead, a different, more granular view describing per-database sync state within a replica, not the replica’s own state. The fix switched to recovery_health_desc, the actual replica-level equivalent.

Real example: Get-LockEscalationStats.sql referenced ios.lock_escalation_count on sys.dm_db_index_operational_stats. The real column for lock escalation counts on that DMV is index_lock_promotion_count, a naming mismatch (escalation vs. promotion) that reads as if it should exist under the more intuitive name and doesn’t.

Real example: Get-MigrationLoginAudit.sql referenced sp.is_policy_checked and sp.is_expiration_checked directly on sys.server_principals. Those columns only exist on sys.sql_logins, the SQL-authentication-specific view, because password policy and expiration are meaningless for a Windows login. The fix: LEFT JOIN sys.sql_logins sl ON sl.principal_id = sp.principal_id, then read the columns from sl. They come back NULL naturally for any non-SQL login.

The tell is that no amount of checking spelling or adding a cast helps, because the name is right and the object is wrong. Ask which view owns the concept: password policy belongs to a SQL login, not to every principal; an EXECUTE AS context belongs to a module, not to the trigger that wraps it.

Cause 2: The Column Is Here, Under Another Name

The object is right and the column is on it, but it is not spelled the way the obvious guess would spell it. Sometimes that is a plural where the real name is singular or a missing suffix; sometimes it is the same boolean idea carrying a has_ prefix where you expected is_. There is no rule that predicts which, and the fix is always the same: read the real column list and use the real name, aliasing it if the rest of your script depends on the old one.

Real example: Get-LinkedServerScript.sql queried sys.linked_logins.local_login_name and .uses_self_credentials. Neither exists. The real columns are local_principal_id (an ID, requiring a join to sys.server_principals to get the actual name) and uses_self_credential, singular, not plural.

Real example: Get-ProxyAndCredentials.sql referenced ss.subsystem_name on msdb.dbo.syssubsystems. The real column is ss.subsystem, no _name suffix.

Real example: Get-ResourceGovernorConfig.sql joined sys.dm_resource_governor_resource_pools expecting total_request_count and active_request_count. Those columns exist, but only on sys.dm_resource_governor_workload_groups. Request counts are tracked per workload group, not per resource pool, even though a pool sounds like the more natural place for one to live.

Real example: Get-StatisticsHealth.sql referenced is_filtered directly on sys.stats. That column doesn’t exist under that name; the real column is has_filter. The fix was a simple rename: has_filter AS is_filtered in the SELECT list preserved the intended output shape without changing every downstream reference.

There is no shortcut for this class besides checking the column list first. The naming logic that feels obvious from outside a catalog view is frequently not the logic it was written with, and the same view can be inconsistent with itself: has_filter and is_ms_shipped sit side by side on sys.stats.

Cause 3: It Is Not There at All, and Your Guard Cannot Save You

Real example: a script gated on IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) < 13 before selecting total_spills from sys.dm_exec_query_stats, with a friendly “this needs a newer version” message in the other branch. On an instance without that column the friendly message never appeared. The batch failed with Msg 207 instead, and the IF was never evaluated at all.

The reason is binding. SQL Server resolves column references for the whole batch at compile time, before a single statement executes. A runtime IF cannot protect a statically written column reference, because compilation has already failed by the time the branch would be chosen. Missing objects behave differently, which is what makes this so confusing: deferred name resolution means a missing table is only resolved when the statement actually runs, so the identical guard around a missing table appears to work while the same guard around a missing column does not.

You can watch both halves of that on any instance:

-- Msg 207, even though the branch never runs
IF 1 = 0
    SELECT no_such_column FROM sys.databases;
ELSE
    SELECT 'guard worked' AS info;

-- prints 'guard worked': the dynamic batch is not bound unless it runs
IF 1 = 0
    EXEC sys.sp_executesql N'SELECT no_such_column FROM sys.databases;';
ELSE
    SELECT 'guard worked' AS info;

The fix is to detect the column rather than the version, and keep the guarded query in dynamic SQL so it is only bound when it can succeed:

IF COL_LENGTH('sys.dm_exec_query_stats', 'total_spills') IS NULL
BEGIN
    SELECT 'This build does not expose total_spills.' AS info;
END
ELSE
BEGIN
    DECLARE @sql nvarchar(max) = N'
    SELECT TOP (30) qs.total_spills, qs.max_spills, qs.execution_count
    FROM sys.dm_exec_query_stats AS qs
    WHERE qs.total_spills > 0
    ORDER BY qs.total_spills DESC;';

    EXEC sys.sp_executesql @sql;
END;

Feature detection is also more accurate than a version number, and by a wider margin than people expect. total_spills arrived in SQL Server 2016 SP2 and SQL Server 2017 CU3, not in 2016 RTM, so a ProductMajorVersion >= 13 test is wrong at both ends: it lets 2016 RTM and 2016 SP1 through to fail, and it blocks nothing useful. COL_LENGTH asks the only question that matters, which is whether this build has the column.

One more thing worth knowing, because it turns this class of bug into something you catch before shipping. SET NOEXEC ON compiles and binds a batch without executing it, so it raises Msg 207 on a bad column name in seconds. SET PARSEONLY ON does not: it checks syntax only, and SELECT no_such_column FROM sys.databases passes it cleanly. If you validate scripts before release, NOEXEC is the setting you want.

Best Practices

• When a query fails with Msg 207 against a DMV or catalog view you’re not intimately familiar with, check the real column list before guessing at a fix. A guessed fix that happens to compile can still be wrong in a different way.
• Treat views with similar names (sys.dm_hadr_availability_replica_states vs. sys.dm_hadr_database_replica_states, sys.server_principals vs. sys.sql_logins) as genuinely different data, not variants of the same thing.
• When testing a new script against a DMV, run it against a real instance before trusting it. Several of the errors above only surfaced when the script was actually executed against SQL Server, not from reading the T-SQL alone.
• Keep a note of which DMV owns which column once you’ve had to look it up; the same near-miss naming trips people up repeatedly across different scripts touching the same subject area.

The Scripts These Fixes Came From

Every real example above is fixed and live in the repo behind these posts:

• Get Audit Specifications, DDL Triggers, and Proxy Credentials
• High Availability (pillar), includes AG Replica Role and Synchronization State
• Get Lock Escalation, Contention Analysis, and Blocking Chains with Plan
• Get Migration Login Audit and Post-Migration Validation
• Get Trace Flags and Resource Governor Configuration
• Get Statistics Health
• Generate Linked Server Script

Common Questions

Related Scripts

• Get Schema Change History, find out when the column was renamed or dropped, and by whom
• Get Database Inventory, confirm which database and instance you are actually connected to
• Collation and Case Sensitivity, why a column name can resolve on one server and not another