The name of an entity in SQL Server is referred to as its identifier. You use identifiers whenever you reference entities, for example, by specifying column and table names in a query. A multi-part identifier contains one or more qualifiers as a prefix for the identifier. For example, a table identifier may be prefixed with qualifiers such as the database name and schema name in which the table is contained, or a column identifier may be prefixed with qualifiers such as a table name or table alias.
Error 4104 indicates that the specified multi-part identifier could not be mapped to an existing entity. This error can be returned under the following conditions:
The qualifier supplied as a prefix for a column name does not correspond to any table or alias name used in the query.
For example, the following statement uses a table alias (Dept) as a column prefix, but the table alias is not referenced in the FROM clause.
SELECT Dept.Name FROM HumanResources.Department;
In the following statements, a multi-part column identifier TableB.KeyCol is specified in the WHERE clause as part of a JOIN condition between two tables, however, TableB is not explicitly referenced in the query.
DELETE FROM TableA WHERE TableA.KeyCol = TableB.KeyCol;
SELECT ‘X’ FROM TableA WHERE TableB.KeyCol = TableA.KeyCol;
An alias name for the table is supplied in the FROM clause, but the qualifier supplied for a column is the table name. For example, the following statement uses the table name Department as the column prefix; however, the table has an alias (Dept) referenced in the FROM clause.
SELECT Department.Name FROM HumanResources.Department AS Dept;
When an alias is used, the table name cannot be used elsewhere in the statement.
SQL Server is unable to determine if the multi-part identifier refers to a column prefixed by a table or to a property of a CLR user-defined data type (UDT) prefixed by a column. This happens because properties of UDT columns are referenced by using the period separator (.) between the column name and the property name in the same way that a column name is prefixed with a table name. The following example creates two tables, a and b. Table b contains column a, which uses a CLR UDT dbo.myudt2 as its data type. The SELECT statement contains a multi-part identifier a.c2.
CREATE TABLE a (c2 int);
CREATE TABLE b (a dbo.myudt2);
SELECT a.c2 FROM a, b;
Assuming that the UDT myudt2 does not have a property named c2, SQL Server cannot determine whether identifier a.c2 refers to column c2 in table a or to the column a, property c2 in table b.