The multi-part identifier "xxx" could not be bound
The last 2 AND ‘s give me the following error messages:
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier «da2.ContractId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
The multi-part identifier «da2.RoomTypeId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier «da2.ContractId» could not be bound.
Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
The multi-part identifier «da2.RoomTypeId» could not be bound.
SQL Server Error 4104: The multi-part identifier could not be bound.
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.
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.
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.
Выборка из двух таблиц
Есть две таблицы (изделия, изготовители), необходимо вывести все данные из первой и одно поле из второй. Во второй (изготовители) таблице перечислены изготовители продуктов, первое поле — счетчик, второе — наименование. В первой таблице (изделия) есть поле «номер изготовителя». Пробую следующий запрос, но ругается ошибкой: The multi-part identifier «изделия.идентификатор» could not be bound.
The multi-part identifier «изделия.номер изготовителя» could not be bound.
The multi-part identifier «изготовители.наименование» could not be bound.
Запрос: «SELECT [изделия].[идентификатор], [изделия].[номер изготовителя], [изготовители].[наименование] FROM [изделия] t1 INNER JOIN [изготовители] t2 ON t1.[номер изготовителя]=t2.[номер изготовителя]»
What is a 'multi-part identifier' and why can't it be bound?
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:
The actual error:
Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "MainDB.dbo.Company.CompanyName" could not be bound.
22 Answers 22
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.