July 22, 2009 at 11:59 am
I am getting the "Ambiguous column name" error on a simple query on one table in our PROD environment. The error does not occur in our DEV environment when I run the same exact query. Why would this happen?
This is my query:
[font="Courier New"]select INSPTYPE,*
from APINSP
where insptype like '%-%'
order by insptype
------------------------*/
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'insptype'.[/font]
July 22, 2009 at 12:06 pm
What are the table definitions between the two environments? Also, are there any differences between how the two systems are set up? Different collations, something?
July 22, 2009 at 12:10 pm
Without more information, I can't tell you why it would work in one environment and not the other... But what I can tell you is that apparently you can't specify a column more than once and do an ORDER BY on it. Didn't know that myself, until I tried it just now.
July 22, 2009 at 12:10 pm
Lynn - The tables are set up the same, as with the collations. I actually built the DEV version from the PROD one recently.
Blake - I don't know why, but the same exact query did work in our DEV environment, even thought I specified a column twice and had an ORDER BY.
July 22, 2009 at 12:13 pm
I see the problem:
select INSPTYPE,* -- right here. You are specifying INSPTYPE explicitly and it is also included in the *, so it is here twice.
from APINSP
where insptype like '%-%'
order by insptype
July 22, 2009 at 12:15 pm
What versions of SQL Server? What compatibility mode on the databases?
July 22, 2009 at 12:15 pm
But is there any reason why this would work in one environment and not the other, when both environments are identical?
Both databases are SQL Server 2005, compatibility (90)
July 22, 2009 at 12:20 pm
Tried something similiar in a Sandbox database. Under compatibility mode 80, query would work. Under compatibility mode 90, I got the error you did.
Something is different, you just haven't given us enough information to help you figure out what.
Add an alias to the explicit column name, and use the alias in the order by clause.
July 22, 2009 at 12:23 pm
Thanks for all of your help. The alias works. As for why I must use the alias in my PROD enivoronment, I'll keep searching.
Thanks, again!
July 22, 2009 at 12:26 pm
Why are you explicitly calling that particular column? Also, you really should explicitly define the columns you want returned even if you want all of them returned.
July 28, 2015 at 9:48 am
try to change compatibility level to 80.
July 28, 2015 at 10:40 am
mrhsham (7/28/2015)
try to change compatibility level to 80.
You do know that this is a 6 year old thread, right? The OP never really provided enough information to really give a good answer.
July 29, 2015 at 9:56 am
I have seen the same type of error and I agree that basically the reason is that the column INSPTYPE is specified explicitly and is also include in the *.
I don’t know that Select * is the best way to code the query in production but one simple way to avoid the error is to change the order by to
Order by 1
July 29, 2015 at 10:47 am
armoredeagle (7/29/2015)
I have seen the same type of error and I agree that basically the reason is that the column INSPTYPE is specified explicitly and is also include in the *.I don’t know that Select * is the best way to code the query in production but one simple way to avoid the error is to change the order by to
Order by 1
And that version of the ORDER BY has been deprecated.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply