|Product Name||SQL Server|
|Message Text||Invalid column name '%.*ls'.|
This query error can be caused by one of the following problems.
The column name is misspelled or the column does not exist in any of the specified tables.
The collation of the database is case-sensitive and the case of the column name specified in the query does not match the case of the column defined in the table. For example, when a column is defined in a table as LastName and the database uses a case-sensitive collation, queries that refer to the column as Lastname or lastname will cause error 207 to return because the column name does not match.
A column alias, defined in the SELECT clause, is referenced in another clause such as a WHERE or GROUP BY clause. For example, the following query defines the column alias
Yearin the SELECT clause and refers to it in the GROUP BY clause.
USE AdventureWorks2012; GO SELECT DATEPART(yyyy,OrderDate) AS Year, SUM(TotalDue) AS Total FROM Sales.SalesOrderHeader GROUP BY Year;
Due to the order in which query clauses are logically processed, the example returns error 207. The processing order is as follows:
WITH CUBE or WITH ROLLUP
Because a column alias is not defined until the SELECT clause is processed, the alias name is unknown when the GROUP BY clause is processed.
The MERGE statement raises this error when the <merge_matched> clause references columns in the source table but no rows are returned by the source table in the WHEN NOT MATCHED BY SOURCE clause. The error occurs because the columns in the source table cannot be accessed when no rows are returned to the query. For example, the clause
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1may cause the statement to fail if
Col1in the source table is inaccessible.
Verify the following information and correct the statement as appropriate.
The column name exists in the table and is spelled correctly. The following example queries the sys.columns catalog view to return all column names for a given table.
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('schema_name.table_name');
The case sensitivity of the database collation. The following statement returns the collation of the specified database.
SELECT collation_name FROM sys.databases WHERE name = 'database_name';
The abbreviation CS in the collation name indicates the collation is case-sensitive. For example, Latin1_General_CS_AS is a case-sensitive and accent-sensitive collation. Modify the column name to match the case of the column name as it is defined in the table.
A column alias is referenced incorrectly. Modify the statement by repeating the expression that defines the alias in the appropriate clause or by using a derived table. The following example repeats the expressions that define the
Yearalias in the GROUP BY clause.
USE AdventureWorks2012; GO SELECT DATEPART(yyyy,OrderDate) AS Year ,SUM(TotalDue) AS Total FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy,OrderDate);
The following example uses a derived table to make the alias name available to other clauses in the query. Notice that the alias
Yearis defined in the FROM clause, which is processed first, and so makes the alias available for use in other clauses in the query.
USE AdventureWorks2012; GO SELECT d.Year, SUM(TotalDue) AS Total FROM (SELECT DATEPART(yyyy,OrderDate) AS Year, TotalDue FROM Sales.SalesOrderHeader)AS d GROUP BY Year;
The WHEN NOT MATCHED BY SOURCE clause in the MERGE statement refers to a value that can be accessed. Modify the MERGE statement so that at least one row is returned by the source table in the WHEN NOT MATCHED BY SOURCE clause. For example, you might need to add or revise the search condition specified for the clause. Alternatively, you can modify the clause to specify a value that does not reference the source table. For example,
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = <expression, or other available value>.