What is a 'multi-part identifier' and why can't it be bound?

Question

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.

Answer 1

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.

Answer 2

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 [].

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.

Answer 3

What you probably have is a typo. For instance if you havea table name 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 get the message you got.

Answer 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.

Answer 5

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.

Answer 6

DELETE DAO...fnb_debtor_branchmapping FROM fnb_debtor_branchmapping_sync AS a WHERE [DAO...fnb_debtor_branchmapping].debtor_code=a.debtor_code AND [DAO...fnb_debtor_branchmapping].branch_code=a.branch_code

The multi-part identifier "DAO...fnb_debtor_branchmapping.debtor_code" could not be bound.

if any body got a solutino please help me