The multi-part identifier “%.*ls” could not be bound.

Details
Product: SQL Server
Event ID: 4104
Source: MSSQLServer
Version: 9.0
Component: SQLEngine
Symbolic Name: ALG_MULTI_ID_BAD
Message: The multi-part identifier “%.*ls” could not be bound.
   
Explanation

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);
    GO

    CREATE TABLE b (a dbo.myudt2);
    GO

    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.

   
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.

Related:

Accelerating SQL Queries that Span Hadoop and Oracle Database

By: Peter Jeffcock

Big Data Product Marketing

It’s hard to deliver “one fast, secure SQL query on all your data”. If you look around you’ll find lots of “SQL on Hadoop” implementations which are unaware of data that’s not on Hadoop. And then you’ll see other solutions that combine the results of two different SQL queries, written in two different dialects, and run mostly independently on two different platforms. That means that while they may work, the person writing the SQL is effectively responsible for optimizing that joint query and implementing the different parts in those two different dialects. Even if you get the different parts right, the end result is more I/O, more data movement and lower performance.

Big Data SQL is different in several ways. (Start with this blog to get the details). From the viewpoint of the user you get one single query, in a modern, fully functional dialect of SQL. The data can be located in multiple places (Hadoop, NoSQL databases and Oracle Database) and software, not a human, does all the planning and optimization to accelerate performance.

Under the covers, one of the key things it tries to do is minimize I/O and minimize data movement so that queries run faster. It does that by trying to push down as much processing as possible to where the data is located. Big Data SQL 3.0 completes that task: now all the processing that can be pushed down, is pushed down. I’ll give an example in the next post.

What this means is cross-platform queries that are as easy to write, and as highly performant, as a query written just for one platform. Big Data SQL 3.0 further improves the “fast” part of “one fast, secure SQL query on all your data”. We’d encourage you to test it against anything else out there, whether it’s a true cross-platform solution or even something that just runs on one platform.

Related:

Maximize SQE usage with DDS-created DB2 for i databases

Applications utilizing SQL experience the best performance when processed by
the DB2 for i SQL Query Engine (SQE).
However, your use of SQE
may be hampered if your database uses keyed logical files containing select/omit criteria.
This article describes how you can use the IGNORE_DERIVED_INDEX query options attribute to
maximize the use of the SQL Query Engine with databases that contain keyed logical files.

Related: