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


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

1 Answer 1


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, 2019 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? May 30, 2019 at 21:28
  • Thanks @Aaron. I hope the project manager has answers to those questions ;)
    – Paul
    May 31, 2019 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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