Ambiguous column name

Question

Ambiguous column name

webrunner

SSC-Dedicated

Points: 31862
More actions
March 21, 2014 at 1:09 pm

#305345

Hello experts,
I'm planning an upgrade to SQL Server 2012 and noticed the following.
We have a query that includes a duplicate column name in the SELECT statement. This query happens to work in a db we have that is set to SQL 2000 (80) compatibility level, but the same exact query fails when I set the db to SQL 2008 (100) compatibility level.
The error is (ColumnName is just an example):
[font="Courier New"]Ambiguous column name 'ColumnName'[/font]
Does anyone know when this particular SQL boo-boo stopped being supported? I realize it was never good practice, but I need to trace any such issue back to a deprecated or discontinued feature. My guess is that maybe duplicated column names stopped getting supported in SQL 2005 - is that correct?
This page had a nice compilation of links to various lists of deprecated features, discontinued features, breaking changes, and behavior changes, by version.
http://blog.davidpsmith.com/2011/10/04/sql-server-breaking-changes-and-deprecated-features-2012-2008-r2-2008-2005-2000/[/url]
I didn't see the ambiguous column name listed in any of those, but I will keep searching among them if necessary.
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That has always been like that. It's not new to 2008, it already existed on 2000. The query might have been changed.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

webrunner SSC-Dedicated Points: 31862 More actions · Answer 2

Luis Cazares (3/21/2014)
That has always been like that. It's not new to 2008, it already existed on 2000. The query might have been changed.

Thanks, but it is the same exact query. I copied and pasted it to make sure it is identical. On the production server, where the database is SQL 2000 level, the query runs and returns results. But on the development server, which I switched to SQL 2008 format, the query returns the 'Ambiguous column name' error.

Is there anything else I can check that might be different between the servers that could explain this?

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

x SSC-Insane Points: 23660 More actions · Answer 3

Double check that the 2000 server (edit: database) doesn't has a case sensitive collation and the same named columns aren't just different casewise (and thus actually unique).

Also, you should try to duplicate this with a simple query / test case that you could post and forum members then could possibly duplicate themselves (those with 2000 servers or databases anyways).

webrunner SSC-Dedicated Points: 31862 More actions · Answer 4

patrickmcginnis59 10839 (3/21/2014)
Double check that the 2000 server (edit: database) doesn't has a case sensitive collation and the same named columns aren't just different casewise (and thus actually unique).
Also, you should try to duplicate this with a simple query / test case that you could post and forum members then could possibly duplicate themselves (those with 2000 servers or databases anyways).

Thanks!

The database collation is SQL_Latin1_General_CP1_CI_AS, which is case-insensitive, I believe.

However, when I double-click the Ambiguous column name error in SSMS, the cursor is moved to the ORDER BY clause. This is the first clue for me that it may be the feature described under 'Transact-SQL' at this page:

Behavior Changes to Database Engine Features in SQL Server 2005

http://technet.microsoft.com/en-us/library/ms143359%28SQL.90%29.aspx

in the 'ORDER BY clause' row.

As you advised, though, I will come up with a simple query to replicate the problem myself with an independent query, and then if I do see the problem with that query, post it here to see if others can reproduce it.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

This is the problematic query taken to basics:

SELECT col1, col1 FROM SomeTable ORDER BY col1

It's the order by, it doesn't know which col1 to sort by. Wasn't caught by the parser in SQL 2000, is caught as a valid error in all versions since.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

webrunner SSC-Dedicated Points: 31862 More actions · Answer 6

GilaMonster (3/21/2014)
This is the problematic query taken to basics:
SELECT col1, col1 FROM SomeTable ORDER BY col1
It's the order by, it doesn't know which col1 to sort by. Wasn't caught by the parser in SQL 2000, is caught as a valid error in all versions since.

Great, thanks Gail! I will still work on coming up with an example query to test in my environment, but so far this looks like the smoking gun, as they say.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 7

webrunner (3/21/2014)
GilaMonster (3/21/2014)
This is the problematic query taken to basics:
SELECT col1, col1 FROM SomeTable ORDER BY col1
It's the order by, it doesn't know which col1 to sort by. Wasn't caught by the parser in SQL 2000, is caught as a valid error in all versions since.
Great, thanks Gail! I will still work on coming up with an example query to test in my environment, but so far this looks like the smoking gun, as they say.
Thanks again,
webrunner

Really all you have found is poor code in your environment and the fact that SQL2000 missed it during the parsing phase because of a "bug".

You may well find more poor code. Another good one which will require a code fix are views with TOP 100 PERCENT .... ORDER BY. The ORDER BY will be ignored in versions since SQL 2005. That was a common work around for lazy developers in previous software house.