none
The multi-part identifier "sys.objects.name" could not be bound

    Question

  • I have run into a strange thing in SQL Server 2008.

     

    I have some code that works fine in SQL Server 2005.

     

    USE master
    SELECT sys.objects.name FROM MyDatabase.sys.objects

     

    In SQL Server 2008 this doesn't work. I get an error message.


    Msg 4104, Level 16, State 1, Line 3
    The multi-part identifier "sys.objects.name" could not be bound.

     

    Is this a changed behavior by design or a bug?

     

    Ola Hallengren
    http://ola.hallengren.com

    Friday, May 16, 2008 5:39 PM

Answers

  • The message for 4014 was written in this rather generic manner intentionally.  In addition to covering the column prefix case, it also needs to cover the case when 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.

     

    The only time you'll get a 107 error in SQL Server 2005/2008 is if an asterisk * is prefixed incorrectly. For example, SELECT O.* from dbo.Orders. 

     

    We have an error message topic for 4104 coming in the next Katmai BOL, but it's available now from the Events and Errors Message Center : http://www.microsoft.com/technet/support/ee/SearchResults.aspx?Type=1&ID=4104&Product=SQL%20Server&Language=1033

     

    Regards,

    Gail

    Friday, May 23, 2008 1:16 AM

All replies

  • works on my instance of SQL 2008 Feb release

    maybe besides the point but you should consider aliasing your tables though, makes your queries a bit shorter, eg SELECT o.name FROM master.sys.objects o
    Monday, May 19, 2008 3:33 PM
  • I'm also on the February CTP.

     

    Microsoft SQL Server code name "Katmai" (CTP) - 10.0.1300.13 (Intel X86)   Feb  8 2008 00:06:52   Copyright (c) 1988-2007 Microsoft Corporation  Developer Edition

     

    I'm only having the problem when I'm doing a cross database query. In this case I'm standing in the master database and is doing a query against the MyDatabase database.

     

    USE master
    SELECT sys.objects.name FROM MyDatabase.sys.objects

     

    (I know about aliases in queries. This is just some existing code that I have that worked well on SQL Server 2005, but not on SQL Server 2008.)

     

    Ola Hallengren
    http://ola.hallengren.com

     

    Monday, May 19, 2008 5:32 PM
  • ah indeed, looks like a bug, this throws an error too for me:
    use tempdb
    SELECT sys.objects.name FROM master.sys.objects

    interestingly this works though, so it's easy to work around the bug if ms doesn't fix it:
    use tempdb
    SELECT o.name FROM master.sys.objects o



    Tuesday, May 20, 2008 7:42 AM
  • Thanks. It feels good that I'm not the only one experiencing this bug.

    Yes, it's easy to work around, if Microsoft doesn't fix it.

     

    Ola Hallengren
    http://ola.hallengren.com

     

    Tuesday, May 20, 2008 3:52 PM
  • This is a bug in SQL 2005, not in SQL 2008.

    Try this:

        select dbo.Orders.OrderID from Northwind.dbo.Orders

    This gives the error message

       Msg 4104, Level 16, State 1, Line 1
      The multi-part identifier "dbo.Orders.OrderID" could not be bound.



    on both SQL 2005 and SQL 2008. The error message on SQL 2000 is clearer:

    Server: Msg 107, Level 16, State 2, Line 1
    The column prefix 'dbo.Orders' does not match with a table name or alias name used in the query.

    That is, you can only use a column prefix that has been defined in a query. For instance, this is
    illegal:

       select Orders.OrderID FROM Orders O

    Once you have defined an alias, you may not use the table name as a prefix. As I recall,this is right
    out of the ANSI standard.

    So when SQL 2005 accepts

       SELECT sys.objects.name FROM master.sys.objects

    it's in error. There are surely some specials around the sys schema, since the definition of the view is
    in mssqlsystem resource, and apparently they got this detail wrong.

     

    Thursday, May 22, 2008 9:07 PM
  • Thursday, May 22, 2008 9:19 PM
  • The message for 4014 was written in this rather generic manner intentionally.  In addition to covering the column prefix case, it also needs to cover the case when 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.

     

    The only time you'll get a 107 error in SQL Server 2005/2008 is if an asterisk * is prefixed incorrectly. For example, SELECT O.* from dbo.Orders. 

     

    We have an error message topic for 4104 coming in the next Katmai BOL, but it's available now from the Events and Errors Message Center : http://www.microsoft.com/technet/support/ee/SearchResults.aspx?Type=1&ID=4104&Product=SQL%20Server&Language=1033

     

    Regards,

    Gail

    Friday, May 23, 2008 1:16 AM