Msg 209, Level 16, State 1, Line 2 Ambiguous colum

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)

Msg 209, Level 16, State 1, Line 2 Ambiguous colum

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-17 : 06:54:00

Msg 209, Level 16, State 1, Line 2
Ambiguous column name

Hi in a select query with inner joins where ine column is not given alias is giving error with some databases and working with some data bases why?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:02:00

at a guess -- your tables / environment are different between databases? Some databases have that column in two tables and some don't?

No way to tell from here.

Either post the query or just expressly alias the desired table and then use proper dot notation. You should be doing this anyway to ensure function if the schema changes in one of the tables anyway.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 07:02:05

It would help the see the query.

This behaves differently in SQL 2000 and SQL 2005 onwards - could that be the issue?

In particular


SELECT [Foo] = Col1, [Bar] = Foo
FROM MyTable AS T
ORDER BY Foo

is OK in SQL 2000, but "Ambiguous" in SQL 2005. Use ORDER BY T.Foo instead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 07:05:00

yeah thats a possibility. another possibility is what Charlie told, having same column available in more than one tables you've used in query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:05:44

quote:

is OK in SQL 2000, but "Ambiguous" in SQL 2005. Use ORDER BY T.Foo instead

It's NOT OK on 2000 - it just works

. Still would be horrendous to debug later. You'd know what it does but not what it was *meant* to do.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-17 : 07:06:10

Got it there are two columns with same name in the particular database from different tables.so i need to use table alias for it.
Where as in other databases only one column.
Thank you.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:09:10

you should alias all the columns anyway in the SELECT and ORDER BY sections. even if you don't *NEED* to. It makes reading the code a lot easier.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-02-17 : 07:09:42

ok thankyou

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:10:25

you are welcome.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 07:18:18

" It's NOT OK on 2000 - it just works . Still would be horrendous to debug later. You'd know what it does but not what it was *meant* to do."

Only happened to me once (where we actually had cross-over names in the SELECT and the DATA - it was a SELECT for an INSERT which deliberated switched "old" and "new" data columns to present the data correctly. It also had an ORDER BY to Sequence the inserts (for an IDENTITY).

When we upgraded to SQL 2008 it fell over

and it took me several hours to write a test rig to prove how SQL 2000 interpreted it so I could simulate that in SQL 2008. All good fun of course ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-17 : 07:51:14

quote:
Originally posted by rajasekhar857

Msg 209, Level 16, State 1, Line 2
Ambiguous column name

Hi in a select query with inner joins where ine column is not given alias is giving error with some databases and working with some data bases why?

Your only aim is to get answers and not reply properly
What is your reply to the last two posts posted here?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139742&whichpage=2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources