How to suppress "Ambiguous column name" error? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to suppress "Ambiguous column name" error?

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-03-11 : 03:34:42

For "debugging stuff" I've been in the habit of doing

SELECT MyCol1, MyCol2, *
FROM MyTable

so that I can easily see MyCol1 & MyCol2, but have all the columns available if I want to check something else out.

With the database set to "SQL 2000" compatibility this is fine, but move it up to SQL2005 compatibility and I get:

"Ambiguous column name"

any way around this (yeah, I could alias the column names, but that's a bit of a pain compared to what I used to do ... and I'm gonna have to find&fix all the little code snippets I've got lying around)

It used to annoy me when Oracle wouldn't let me do that ... turns out now that its my fault I suppose

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-11 : 13:08:36

hmm..
works ok for me
ran this in SSMS on sql server 2005 standard version


create database test1
go
use test1
go
create table t1 (col1 int, col2 int, col3 varchar(10))

insert into t1
select 1, 2, 'name 1' union all
select 3, 4, 'name 2' union all
select 5, 6, 'name 3' union all
select 7, 8, 'name 4'

select	col1, col2, * 
from	t1

go
use master
drop database test1

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-11 : 14:26:42

Using Developer Edition and works without errors.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-03-12 : 08:24:36

Damn! That example works for me too ... I'll track down the original issue and see if the ambiguity was more complex than the noddy example I described.

Kristen

pootle_flump

1064 Posts

Posted - 2007-03-12 : 08:54:07

I think you might be ordering by one of the columns you list. Aliasing is the only thing I have found....

Kristen
Test

22859 Posts

Posted - 2007-03-12 : 11:10:30

"I think you might be ordering by one of the columns you list."

Bingo! Amazing that wasn't treated as Ambiguous in SQL 2000 I suppose.

I ain't got a problem sorting out the ones that are needed in the ORDER BY ... its the lazy cheats way of getting a couple of columns to the front of the list, but still see them all, that I'm after for debugging.

Thanks

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources