SQL Server error 207: Invalid column name
A column the query refers to doesn't exist in any table in scope: a typo, a column from another table, or a SELECT alias used where SQL Server can't see it.
- Error number
207- Severity
- 16
- Category
- Names and objects
What it means
SQL Server binds every column reference to a table in the FROM clause at compile time. If a name doesn't match a column of any table or alias in scope, the statement fails with 207 and names the column.
Common causes
- A typo, or a column that was renamed or dropped.
- Using a
SELECTlist alias inWHERE,GROUP BYorHAVING. Those clauses are processed before the select list, so the alias doesn't exist there yet (it is visible inORDER BY). - A string literal written in double quotes, which SQL Server reads as an identifier when
QUOTED_IDENTIFIERis on:WHERE status = "open". - A column added in the same batch by
ALTER TABLEand used before aGO, because the whole batch is compiled first. - Querying a view whose underlying table changed; refresh it with
sp_refreshview.
How to fix it
-
List the table's real columns.
SELECT name, TYPE_NAME(user_type_id) AS type_name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Orders');
-
Repeat the expression instead of the alias in
WHERE, or wrap the query in a derived table or CTE.SELECT * FROM (SELECT OrderId, Quantity * UnitPrice AS Total FROM dbo.OrderLines) AS t WHERE t.Total > 100;
-
Use single quotes for string values.
SELECT * FROM dbo.Tickets WHERE Status = 'open';
-
Put a
GObetween anALTER TABLE ... ADDand statements that use the new column.
Reproduce it safely
SELECT 1 AS n WHERE n = 1; -- Msg 207: Invalid column name 'n'.
Related errors
208Invalid object name209Ambiguous column name4104Multi-part identifier not bound42703PostgreSQL: column does not exist1054MySQL: Unknown column in field listORA-00904Oracle: invalid identifier
More names and objects errors
2812Could not find stored procedure2714There is already an object named3701Cannot drop, object not found3702Cannot drop database, currently in use5030Database couldn't be exclusively locked8144Too many arguments specified8145Is not a parameter for procedure8146Procedure has no parameters
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.