Permalink
Cannot retrieve contributors at this time
title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | helpviewer_keywords |
---|---|---|---|---|---|---|---|---|
MSSQLSERVER_4104 |
The specified multi-part identifier could not be mapped to an existing entity. See an explanation of the error and possible resolutions. |
MashaMSFT |
mathoma |
04/04/2017 |
sql |
supportability |
reference |
4104 (Database Engine error) |
MSSQLSERVER_4104
[!INCLUDE SQL Server]
Details
Attribute | Value |
---|---|
Product Name | SQL Server |
Event ID | 4104 |
Event Source | MSSQLSERVER |
Component | SQLEngine |
Symbolic Name | ALG_MULTI_ID_BAD |
Message Text | The multi-part identifier «%.*ls» could not be bound. |
Explanation
The name of an entity in [!INCLUDEssNoVersion] 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.
-
[!INCLUDEssNoVersion] 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
andb
. Tableb
contains columna
, which uses a CLR UDTdbo.myudt2
as its data type. The SELECT statement contains a multi-part identifiera.c2
.CREATE TABLE a (c2 int); GO
CREATE TABLE b (a dbo.myudt2); GO
Assuming that the UDT
myudt2
does not have a property namedc2
, [!INCLUDEssNoVersion] cannot determine whether identifiera.c2
refers to columnc2
in tablea
or to the columna
, propertyc2
in tableb
.
User Action
-
Match the column prefixes against the table names or alias names specified in the FROM clause of the query. If an alias is defined for a table name in the FROM clause, you can only use the alias as a qualifier for columns associated with that table.
The statements above that reference the
HumanResources.Department
table can be corrected as follows:SELECT Dept.Name FROM HumanResources.Department AS Dept; GO
SELECT Department.Name FROM HumanResources.Department; GO
-
Ensure that all tables are specified in the query and that the JOIN conditions between tables are specified correctly. The DELETE statement above can be corrected as follows:
DELETE FROM dbo.TableA WHERE TableA.KeyCol = (SELECT TableB.KeyCol FROM TableB WHERE TableA.KeyCol = TableB.KeyCol); GO
The SELECT statement above for
TableA
can be corrected as follows:SELECT 'X' FROM TableA, TableB WHERE TableB.KeyCol = TableA.KeyCol;
or
SELECT 'X' FROM TableA INNER JOIN TableB ON TableB.KeyCol = TableA.KeyCol;
-
Use unique, clearly defined names for identifiers. Doing so makes your code easier to read and maintain, and it also minimizes the risk of ambiguous references to multiple entities.
See Also
MSSQLSERVER_107
Database Identifiers
If you get an error telling you that the “The multi-part identifier could not be bound.”, it usually means that you’re prefixing one or more columns with either a table that isn’t included in your query, or an alias that you haven’t actually assigned to a table.
Fortunately, the error message shows you which multi-part identifier is causing the problem.
Example
Here’s an example to demonstrate how to get the error.
SELECT * FROM Cats
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Result:
Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "c.CatName" could not be bound.
In this example, I forget to include an alias for the Cats table.
The error will also occur if you try to reference the table name after you’ve already provided an alias.
Like this:
SELECT * FROM Cats c
INNER JOIN Dogs d
ON Cats.CatName = d.DogName;
Result:
Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "Cats.CatName" could not be bound.
So in this case, I correctly assigned the alias, but in the ON
clause I referenced the actual table name instead of the alias.
The Solution
So the solution is to ensure you’ve assigned all aliases that you might reference later in the query, and also to make sure you use those aliases instead of the table name directly.
So if you’re going to use aliases, like this:
SELECT * FROM Cats c
INNER JOIN Dogs d
ON c.CatName = d.DogName;
Or if you choose not to use aliases, like this:
SELECT * FROM Cats
INNER JOIN Dogs
ON Cats.CatName = Dogs.DogName;
I continually get these errors when I try to update tables based on another table. I end up rewriting the query, change the order of joins, change some groupings and then it eventually works, but I just don’t quite get it.
What is a ‘multi-part identifier’?
When is a ‘multi-part identifier’ not able to be bound?
What is it being bound to anyway?
In what cases will this error occur?
What are the best ways to prevent it?
The specific error from SQL Server 2005 is:
The multi-part identifier «…» could not be bound.
Here is an example:
SELECT * FROM [MainDB].[dbo].[Company]
WHERE [MainDB].[dbo].[Company].[CompanyName] = 'StackOverflow'
The actual error:
Msg 4104, Level 16, State 1, Line 2 The multi-part identifier
«MainDB.dbo.Company.CompanyName» could not be bound.
asked Oct 15, 2008 at 21:21
Even MienEven Mien
44k43 gold badges115 silver badges119 bronze badges
A multipart identifier is any description of a field or table that contains multiple parts — for instance MyTable.SomeRow — if it can’t be bound that means there’s something wrong with it — either you’ve got a simple typo, or a confusion between table and column. It can also be caused by using reserved words in your table or field names and not surrounding them with [].
It can also be caused by not including all of the required columns in the target table.
Something like redgate sql prompt is brilliant for avoiding having to manually type these (it even auto-completes joins based on foreign keys), but isn’t free. SQL server 2008 supports intellisense out of the box, although it isn’t quite as complete as the redgate version.
answered Oct 15, 2008 at 21:48
WhiskWhisk
3,2872 gold badges29 silver badges30 bronze badges
2
Actually sometimes when you are updating one table from another table’s data, I think one of the common issues that cause this error, is when you use your table abbreviations incorrectly or when they are not needed. The correct statement is below:
Update Table1
Set SomeField = t2.SomeFieldValue
From Table1 t1
Inner Join Table2 as t2
On t1.ID = t2.ID
Notice that SomeField
column from Table1 doesn’t have the t1
qualifier as t1.SomeField
but is just SomeField
.
If one tries to update it by specifying t1.SomeField
the statement will return the multi-part error that you have noticed.
ΩmegaMan
29.1k10 gold badges99 silver badges121 bronze badges
answered Jun 20, 2011 at 15:58
amadelleamadelle
7515 silver badges2 bronze badges
4
It’s probably a typo. Look for the places in your code where you call [schema].[TableName] (basically anywhere you reference a field) and make sure everything is spelled correctly.
Personally, I try to avoid this by using aliases for all my tables. It helps tremendously when you can shorten a long table name to an acronym of it’s description (i.e. WorkOrderParts -> WOP), and also makes your query more readable.
Edit: As an added bonus, you’ll save TONS of keystrokes when all you have to type is a three or four-letter alias vs. the schema, table, and field names all together.
answered Oct 15, 2008 at 21:57
Binding = your textual representation of a specific column gets mapped to a physical column in some table, in some database, on some server.
Multipart identifier could be: MyDatabase.dbo.MyTable. If you get any of these identifiers wrong, then you have a multipart identifier that cannot be mapped.
The best way to avoid it is to write the query right the first time, or use a plugin for management studio that provides intellisense and thus help you out by avoiding typos.
answered Oct 15, 2008 at 21:29
0
I found that I get these a lot when I try to abbreviate, such as:
Table1 t1, Table2 t2
where t1.ID = t2.ID
Changing it to:
Table1, Table2
where Table1.ID = Table2.ID
Makes the query work and not throw the error.
ElderMael
7,0005 gold badges34 silver badges53 bronze badges
answered May 26, 2011 at 17:59
jo-msojo-mso
591 silver badge1 bronze badge
0
You probably have a typo. For instance, if you have a table named Customer in a database named Sales, you could refer to it as Sales..Customer (although it is better to refer to it including the owner name (dbo is the default owner) like Sales.dbo.Customer.
If you typed Sales…Customer, you might have gotten the message you got.
answered Oct 15, 2008 at 21:51
HLGEMHLGEM
94.3k15 gold badges112 silver badges186 bronze badges
If you are sure that it is not a typo spelling-wise, perhaps it is a typo case-wise.
What collation are you using? Check it.
answered Oct 15, 2008 at 22:02
Pittsburgh DBAPittsburgh DBA
6,6422 gold badges39 silver badges68 bronze badges
When updating tables make sure you do not reference the field your updating via the alias.
I just had the error with the following code
update [page]
set p.pagestatusid = 1
from [page] p
join seed s on s.seedid = p.seedid
where s.providercode = 'agd'
and p.pagestatusid = 0
I had to remove the alias reference in the set statement so it reads like this
update [page]
set pagestatusid = 1
from [page] p
join seed s on s.seedid = p.seedid
where s.providercode = 'agd'
and p.pagestatusid = 0
answered Jun 20, 2012 at 0:48
Adding table alias in front Set field causes this problem in my case.
Right
Update Table1
Set SomeField = t2.SomeFieldValue
From Table1 t1
Inner Join Table2 as t2
On t1.ID = t2.ID
Wrong
Update Table1
Set t1.SomeField = t2.SomeFieldValue
From Table1 t1
Inner Join Table2 as t2
On t1.ID = t2.ID
answered Nov 22, 2018 at 22:56
I had this issue and it turned out to be an incorrect table alias. Correcting this resolved the issue.
answered Dec 15, 2011 at 14:35
Matthew SetterMatthew Setter
2,3571 gold badge19 silver badges17 bronze badges
Mine was putting the schema on the table Alias by mistake:
SELECT * FROM schema.CustomerOrders co
WHERE schema.co.ID = 1 -- oops!
answered Feb 15, 2013 at 20:14
unnknownunnknown
1,6852 gold badges19 silver badges37 bronze badges
I had P.PayeeName AS 'Payer' --,
and the two comment lines threw this error
answered Jun 26, 2017 at 17:19
Andrew DayAndrew Day
56310 silver badges23 bronze badges
I actually forgot to join the table to the others that’s why i got the error
Supposed to be this way:
CREATE VIEW reserved_passangers AS
SELECT dbo.Passenger.PassName, dbo.Passenger.Address1, dbo.Passenger.Phone
FROM dbo.Passenger, dbo.Reservation, dbo.Flight
WHERE (dbo.Passenger.PassNum = dbo.Reservation.PassNum) and
(dbo.Reservation.Flightdate = 'January 15 2004' and Flight.FlightNum =562)
And not this way:
CREATE VIEW reserved_passangers AS
SELECT dbo.Passenger.PassName, dbo.Passenger.Address1, dbo.Passenger.Phone
FROM dbo.Passenger, dbo.Reservation
WHERE (dbo.Passenger.PassNum = dbo.Reservation.PassNum) and
(dbo.Reservation.Flightdate = 'January 15 2004' and Flight.FlightNum = 562)
answered Sep 4, 2017 at 23:17
Error Code
FROM
dbo.Category C LEFT OUTER JOIN
dbo.SubCategory SC ON C.categoryID = SC.CategoryID AND C.IsActive = 'True' LEFT OUTER JOIN
dbo.Module M ON SC.subCategoryID = M.subCategoryID AND SC.IsActive = 'True' LEFT OUTER JOIN
dbo.SubModule SM ON M.ModuleID = SM.ModuleID AND M.IsActive = 'True' AND SM.IsActive = 'True' LEFT OUTER JOIN
dbo.trainer ON dbo.trainer.TopicID =dbo.SubModule.subModuleID
Solution Code
FROM
dbo.Category C LEFT OUTER JOIN
dbo.SubCategory SC ON C.categoryID = SC.CategoryID AND C.IsActive = 'True' LEFT OUTER JOIN
dbo.Module M ON SC.subCategoryID = M.subCategoryID AND SC.IsActive = 'True' LEFT OUTER JOIN
dbo.SubModule SM ON M.ModuleID = SM.ModuleID AND M.IsActive = 'True' AND SM.IsActive = 'True' LEFT OUTER JOIN
dbo.trainer ON dbo.trainer.TopicID = SM.subModuleID
as you can see, in error code, dbo.SubModule
is already defined as SM, but I am using dbo.SubModule
in next line, hence there was an error.
use declared name instead of actual name. Problem solved.
answered Jan 17, 2018 at 6:33
My best advise when having the error is to use [] braquets to sorround table names, the abbreviation of tables causes sometimes errors, (sometime table abbreviations just work fine…weird)
answered Mar 9, 2018 at 19:40
ramnzramnz
6311 gold badge6 silver badges24 bronze badges
I was getting this error and just could not see where the problem was. I double checked all of my aliases and syntax and nothing looked out of place. The query was similar to ones I write all the time.
I decided to just re-write the query (I originally had copied it from a report .rdl file) below, over again, and it ran fine. Looking at the queries now, they look the same to me, but my re-written one works.
Just wanted to say that it might be worth a shot if nothing else works.
answered Feb 5, 2019 at 14:52
clamumclamum
1,22710 silver badges17 bronze badges
When you type the FROM table those errors will disappear.
Type FROM below what your typing then Intellisense will work and multi-part identifier will work.
I faced this problem and solved it but there is a difference between your and mine code. In spite of I think you can understand what is «the multi-part identifier could not be bound»
When I used this code
select * from tbTest where email = sakira@gmail.com
I faced Multi-part identifier problem
but when I use single quotation for email address It solved
select * from tbTest where email = 'sakira@gmail.com'
answered Feb 11, 2020 at 5:34
I had exactly the same issue, and similar to your coding I had missed out the FROM field, once it is added, the query knows what table to read the data from
answered Jun 9, 2021 at 9:19
Mine worked after removing square brackets in a SUBSTRING method. I changed from
SUBSTRING([dbo.table].[column],15,2)
to
SUBSTRING(dbo.table.column,15,2)
answered Jul 7, 2021 at 19:46
abovetempoabovetempo
1402 silver badges8 bronze badges
CTRL+SHIFT+R (refreshing the Intellisense) took care of it for me.
answered Jun 21, 2022 at 14:36
JohnJohn
3135 silver badges9 bronze badges
I was using an alias but that alias I was not using in select
It is important to use an alias when data is coming from another database and use that alias with your select statement with fields
Example
Database_Name.dbo.Table_Name as Alias_Name with(NOLOCK)
ON
Join Condition
in Select Statement Alias_Name.Columns_Name
answered Apr 28 at 7:58
Home > SQL Server Error Messages > Msg 4104 — The multi-part identifier could not be bound. |
||
SQL Server Error Messages — Msg 4104 — The multi-part identifier could not be bound. |
||
To illustrate, given the following table structure: CREATE TABLE [dbo].[Employee] ( [EmployeeID] INT [FullName] VARCHAR(100), [ManagerID] INT ) The following SELECT statement will generate the error: SELECT [Manager].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Manager.FullName" could not be bound. The error is encountered because there is no [Manager] table or alias defined in the FROM clause. Another way of getting the error is when an alias has been assigned to a table referenced in the FROM clause of a statement and the table is used as a prefix of a column instead of using the alias. To illustrate, here’s another way of getting the error: SELECT [Employee].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "Employee.EmployeeID" could not be bound. Since an alias has already been used for the [dbo].[Employee] table, the alias, in this case [Emp], should be used instead of the table name when used as a prefix in the column names. The error can also happen not just as part of the column list in the SELECT statement but can also happen when the referenced column is in the JOIN clause, WHERE clause or the ORDER BY clause. -- As part of the JOIN clause SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Manager].[EmployeeID] -- As part of the ORDER BY clause SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] ORDER BY [Manager].[FullName] Solution / Workaround: This error can easily be avoided by making sure that the table or alias used when qualifying columns in the SELECT statement matches one of the tables or aliases specified in the FROM clause. In the first scenario, simply change the incorrect alias used in the SELECT statement. Changing the [Manager] prefix, which doesn’t exist in the FROM clause, to [Mgr] will solve the issue: SELECT [Mgr].[FullName] AS [ManagerName], [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] INNER JOIN [dbo].[Employee] [Mgr] ON [Emp].[ManagerID] = [Mgr].[EmployeeID] In the second scenario, since an alias has already been assigned to the table, use that alias instead of the table when prefixing columns from then on. Changing the [Employee] table prefix to the [Emp] alias will solve the issue: SELECT [Emp].[EmployeeID], [Emp].[FullName] FROM [dbo].[Employee] [Emp] |
||
Related Articles : | ||
- Remove From My Forums
-
Question
-
Using SSMS 17.3 and 17.4, I get this error message when I try to add a database to an alwaysOn group that has been removed previously. I can perform this action using a previous version of SSMS (ie. 2016) with no issue from the same server.
«An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The multi-part identifier «a.delimiter» cound not be bound. (Microsoft SQL Server, Error: 4104)
Has anyone else experienced this issue and found a solution?
-
Edited by
Wednesday, December 13, 2017 4:57 PM
-
Edited by