Home Dashboard Directory Help
Search

Behaviour Change 2000->2005: Ambiguous column name error using a column twice by 67F4F363-0087-4BA0-93CC-980995B3E773


Status: 

Closed
 as Won't Fix Help for as Won't Fix


0
0
Sign in
to vote
Type: Bug
ID: 203628
Opened: 9/15/2006 8:43:17 AM
Access Restriction: Public
1
Workaround(s)
view
1
User(s) can reproduce this bug

Description

Executing:
    SELECT Name,Name FROM sysdatabases order by Name
in master database returns an error:
GERMAN:
Meldung 209, Ebene 16, Status 1, Zeile 1
Mehrdeutiger Spaltenname 'Name'.
ENGLISH:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Name'.
This Statement works in SQL-Server 7.0 and 2000.
I cannot change it, its generated by MS Jet-Engine joining an MS Access Table and a SQL-Server Table.
Forcing SQL-Server 2000 compatibility helps to solve my problem.
But isn't it possible to handle this stament correct in SQL-Server 2005 mode too?
Details
Sign in to post a comment.
Posted by Robert Varga on 12/14/2009 at 5:35 AM
As previously noted, the issue is with the ORDER BY clause, as demonstrated with the following repro:

create table #a
(
    ACol int not null
);

select top 10 ACol, * from #a order by ACol desc
go

drop table #a
GO

Removing the order by results in the above running successfully (9.00.3042.00 (X64). This is via SSMS 2008 sp1, not via access etc. Again, this is not an alias binding issue...
Posted by spasecookee on 10/12/2007 at 3:15 PM
I know it seems silly to select the same column twice, but we have a proc in a legacy ASP application that generates SQL dynamically for a drop down list. There is a list that needs the value and text columns to be the same, and hence the function that generates the drop down list calls the proc with the same column name twice. The proc then executes SELECT columnName, columnName FROM tableName ORDER BY columnName. Fortuately, the function does not rely on the column aliases, and so we can pass in columnName as X for the second parameter, and all is good.
Posted by 67F4F363-0087-4BA0-93CC-980995B3E773 on 9/20/2006 at 1:25 AM
If binding of columns by name was the main goal for this change it is very confusing to allow:
SELECT Name,Name FROM sysdatabases
Consequently it should raise then same error as the sorted version.
It seems that the error is an result of the ORDER BY handling. But its pretty clear what should be done.

The 'wrong' SQL was generated by MS Jet-Engine joining an MS Access Table and a SQL-Server Table. Jet-Engine splits the cross database queries automatically and this error was an result of the SQL-Server query part. I have not much influence to this behaviour.

I like the intention of the change you've made, SQL statements with ambigous column names are not very useful.
But its hard for me to go back to SQL-Server 2000 compatibily mode. Having an option/configurtion entry instead would be nice.
Posted by Microsoft on 9/19/2006 at 1:55 PM
Thank you for the report. This change is by design and was done as part of an overall effort to make binding of column names more predictable and align it with the SQL Standard. Among the other changes were: not to ignoring column prefixes and limiting column references to 2-part names instead of allowing more prefixes. This was done to help uncover queries that were not running as the author intended or had typos or cut/paste errors. In most of the investigated cases, this has proven to be beneficial. As you note, the original behavior is maintained in backwards compat mode, so if you need this query to continue running as-is, you'll need to use the compat mode.

If this is a query you've generated through Access, you can always add column aliases to the select list using SQL View instead of Design View (from the View menu in Access 2003).

Hope that helps
Jerry Weiler
jweiler@microsoft.com
SQL Server Engine Development
Sign in to post a workaround.
Posted by spasecookee on 10/12/2007 at 3:09 PM
If your query looks like this:
SELECT columnA, columnA FROM tableA ORDER BY columnA, alias ONE of the columns:
SELECT columnA, columnA AS redundant FROM tableA ORDER BY columnA

This issue could arise if you had a proc that used dynamic SQL to (for example) generate a HTML <select> list programatically (in legacy code).