3

I have the following code that is generated by an unsupported app

SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID

I can't change this, now it used to work fine on SQL2008R2 or below on DB's in compatability mode 80 but due to infrastructure/licensing changes we require to move to a more modern version of SQL which no longer allows for mode 80 and now generates this error:

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'ID'.

I'm aware it's because of the duplicated reference to the ID column but I can't alter what the app generates as TSQL, is there anything I can do at the DB\server level to have this execute without error?

PS: Yes we are working to replace this app

4

It's not precisely because there are duplicate entries in the SELECT list, it's because the ORDER BY mentions a column that is listed twice in the SELECT list, and SQL Server doesn't know which one you mean (think about the case where you could say SELECT ID, ID = 1-ID..., where which one you mean is important).

The right fix is (in order of my personal preference):

  1. remove the second instance of ID
  2. give the second instance of ID a different alias (like RedundantID)
  3. use ordinal - ORDER BY 1 (would work, but definitely not recommended)

If you can't fix the code in the app, and can't get support from the vendor, I'm not sure there's much else you can do except move back to the copy of the database running on a version that can run in the ancient compatibility level required to support this syntax.

  • Aye, vendor doesn't exist any more. I know the changes you mean there, but I can't make the app change the TSQL it generates in those ways. Which may well mean there is no answer per se. – Paul May 30 at 12:42
  • @Paul Well, if this is part of a migration, did you have a fallback plan (like reverting back to the original server)? What if something else went wrong? Can you delay the migration until the app gets replaced? – Aaron Bertrand May 30 at 21:28
  • Thanks @Aaron. I hope the project manager has answers to those questions ;) – Paul May 31 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.