ambiguous column name - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

ambiguous column name

Author

Topic

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-10 : 01:30:26

specific reasons why ambiguous column name pops up as an error in sql server?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 01:36:40

If you join two table and dont specify the alias name, you will get this error
Post the query you used

Madhivanan

Failing to plan is Planning to fail

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-10 : 01:45:39

so how do i do it?
ex.
select q.vc_name from [production].[dbo].[jobfinal] a LEFT JOIN [production].[dbo].[queue] q ON a.lng_queue=q.id

this will actually give an ambiguous column name error y?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-10 : 01:53:11

Just check.. the following things..

the column name VC_Name and id are there in the table Queue..

and lng_queue is there in the table jobfinal???

or post the structure of this 2 tables..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-10 : 01:57:05

Post the entire error messeage
Post the structure of two tables

Madhivanan

Failing to plan is Planning to fail

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-10 : 20:53:57

1st table:
CREATE TABLE [queue] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[vc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lng_location] [bigint] NOT NULL ,
[txt_note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

2nd table:
CREATE TABLE [jobfinal] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[vc_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lng_template] [bigint] NOT NULL ,
[lng_team] [bigint] NOT NULL ,
[lng_duration] [bigint] NOT NULL ,
[lng_store] [bigint] NOT NULL ,
[lng_queue] [bigint] NOT NULL ,
[dte_date] [datetime] NULL
) ON [PRIMARY]
GO

query:
select q.vc_name from [production].[dbo].[jobfinal] a LEFT JOIN [production].[dbo].[queue] q ON a.lng_queue=q.id

error:
java.lang.Exception: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Ambiguous column name 'id'.
beans.SearchList.prepareResults(SearchList.java:374)
org.apache.jsp.schedule_jsp._jspService(schedule_jsp.java:122)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 22:17:18

Works fine in Query Analyser here, so there's nothing wrong with that query.

"]Ambiguous column name 'id'."

You've aliased "id" in this query - is it possible that the error relates to a different query in your source code?

Kristen

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-10 : 22:25:13

yep it does work even with my Toad but when i run it over a webserver such error pops out and that makes my life a bit harder..

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 22:29:54

Use Profiler to have a look at what Java is actually sending to SQL Server? Java might be rearranging your query for you

Kristen

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-10 : 22:38:35

Profiler? whats that?

Kristen
Test

22859 Posts

Posted - 2005-11-10 : 22:42:24

One of the tools that makes up "SQL Client", along with Query Analyser, Enterprise Manager, etc. - captures "events" that hit the SQL box. You can filter to specific database/client PC etc. and the default is to filter to just [give or take a few other useful events!] the "SQL Command" events - so you will see the SQL statements that arrive at the SQL box, as sent by the application.

Kristen

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-11-26 : 01:42:33

did you post the entire query? you might have left out a
where clause perhaps?

--------------------
keeping it simple...

my_aro
Yak Posting Veteran

50 Posts

Posted - 2005-11-28 : 06:12:45

thanks. i have found my mistake actually.. its within my sql template. thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources