asked
I have a statement which works in SQL server 2008 but doesn't work in 2012
SELECT supplyType, supplyType FROM ITM_Items GROUP BY supplyType ORDER BY supplyType It gives this error message: Msg 209, Level 16, State 1, Line 4 Ambiguous column name 'supplyType'. I agree that statement is written "weird" but my question is why is working in some version and doesn't in other
It's the ORDER BY statement that's giving you the problem. I would have thought that wouldn't work in 2008 either. It is absolutely ambiguous. Since ORDER BY works off of aliases and is the last piece of processing, it makes sense that you would need to differentiate between the two columns. ORDER BY wouldn't know that they're the same. I tested in 2012 and 2014 and it fails in both. I didn't have a 2008R2 instance available, so I spun one up in Azure. I got the error there too.
Doing a little more research, it looks like you might have a database that's running in 2000 compatibility mode. It worked then. The logical fix was put in with SQL Server 2005. See comments here:
http://connect.microsoft.com/SQLServer/feedback/details/644134/order-by-clause-incorrectly-produces-ambiguous-column-name and here
http://connect.microsoft.com/SQLServer/feedback/details/203628/behaviour-change-2000-2005-ambiguous-column-name-error-using-a-column-twice
Thx for your comments Grant but my concern is why is working in 2008 and doesn't in later versions. I have a database with hundreds store procedures which may have same "approach" of writing statements which is wrong. IF I would now them upfront I can fix one by one by re-writing those parts ( replacing in order by column name with column nbr, using aliases...etc...writing them correctly...but I don't have time to analyse each line of code..I checked the compatibility mode for database and is 90 in all situations...doesn't help no matter what I do in 2012 and above
I'm surprised it works on any version of SQL Server, but clearly it's not going to work going forward. You're going to have to address it in order to upgrade. No way around it. I know people who hit the same issues with the ANSI 89 joins, *= and =* instead of LEFT and RIGHT from ANSI 92. They had to fix their code and I'm pretty sure you're going to have to as well.
Yes..it is working in 2008 if compatibility is for 2000 and doesn't work in 2012 because compatibility can't be bellow 2005 ..now because I can explain why is ok....so if I want to use same database in 2012 and above I have to correct all those....but if I stay in 2008 shoul be ok...thx for your research
15 People are following this question.
