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

General SQL Server Forums

New to SQL Server Programming

Ambiguous column name

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-02 : 07:45:48

My web app is running fine on the live SQL 2005 server. Our SQLServer administrator has copied the database to a test server - also 2005. Now when my asp.net tries to fill a dataset using a view this error appears. There are two linked tables in the view

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 07:49:17

Change name of the offending column.
That's all advice I can give without seeing the code.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 08:21:07

use actual column names in your view rather than *. and if you're using actual names make sure you given an alias name if you're taking same field from both tables.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-02 : 08:24:44

Why does it work on one server ok, but not on the other ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-02 : 08:26:25

quote:
Originally posted by Pinto

Why does it work on one server ok, but not on the other ?

heck if structure of base tables are same in both servers.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-02 : 08:26:35

Here's my view. BK_RepeatId it doesn't like

SELECT dbo.tblRB_RepeatBookings.BK_RepeatId, dbo.tblRB_RepeatBookings.BK_BookingNo, dbo.tblRB_RepeatBookings.BK_BookingDateRef,
dbo.tblRB_RepeatBookings.BK_DateBooked, dbo.tblRB_RepeatBookings.BK_RoomRef, dbo.tblRB_RepeatBookings.BK_DateRequired,
dbo.tblRB_RepeatBookings.BK_Group, dbo.tblRB_RepeatBookings.BK_BookedBy, dbo.tblRB_RepeatBookings.BK_Telephone,
dbo.tblRB_RepeatBookings.BK_CateringRequired, dbo.tblRB_RepeatBookings.BK_NoAttending, dbo.tblRB_RepeatBookings.BK_SpecialRequirements,
dbo.tblRB_RepeatBookings.BK_TotalCharge, dbo.tblRB_RepeatBookings.BK_Parking, dbo.tblRB_RepeatBookings.BK_Refreshments,
dbo.tblRB_RepeatBookings.BK_P1, dbo.tblRB_RepeatBookings.BK_P2, dbo.tblRB_RepeatBookings.BK_P3, dbo.tblRB_RepeatBookings.BK_P4,
dbo.tblRB_RepeatBookings.BK_P5, dbo.tblRB_RepeatBookings.BK_P6, dbo.tblRB_RepeatBookings.BK_P7, dbo.tblRB_RepeatBookings.BK_P8,
dbo.tblRB_RepeatBookings.BK_P9, dbo.tblRB_RepeatBookings.BK_P10, dbo.tblRB_RepeatBookings.BK_P11, dbo.tblRB_RepeatBookings.BK_P12,
dbo.tblRB_RepeatBookings.BK_P13, dbo.tblRB_RepeatBookings.BK_P14, dbo.tblRB_RepeatBookings.BK_P15, dbo.tblRB_RepeatBookings.BK_P16,
dbo.tblRB_RepeatBookings.BK_P17, dbo.tblRB_RepeatBookings.BK_P18, dbo.tblRB_RepeatBookings.BK_P19, dbo.tblRB_RepeatBookings.BK_P20,
dbo.tblRB_RepeatBookings.BK_P21, dbo.tblRB_RepeatBookings.BK_P22, dbo.tblRB_RepeatBookings.BK_P23, dbo.tblRB_RepeatBookings.BK_P24,
dbo.tblRB_RepeatBookings.BK_P25, dbo.tblRB_RepeatBookings.BK_P26, dbo.tblRB_RepeatBookings.BK_P27, dbo.tblRB_RepeatBookings.BK_P28,
dbo.tblRB_RepeatBookings.BK_Confirmed, dbo.tblRB_RepeatBookings.BK_STime, dbo.tblRB_RepeatBookings.BK_ETime,
dbo.tblRB_RepeatBookings.BK_EnteredBy, dbo.tblRB_RepeatBookings.BK_Cancelled, dbo.tblRB_RepeatBookings.BK_CancelledDate,
dbo.tblRB_RepeatBookings.BK_CancelledBy, dbo.tblRB_RepeatBookings.BK_RoomLayout, dbo.tblRB_RepeatBookings.BK_MeetingTitle,
dbo.tblRB_RepeatBookings.BK_Identifier, dbo.tblRB_RepeatBookings.BK_Include, dbo.tblRB_RepeatBookings.BK_Recharge,
dbo.tblRB_Rooms.RM_Location
FROM dbo.tblRB_Rooms INNER JOIN
dbo.tblRB_RepeatBookings ON dbo.tblRB_Rooms.RM_RoomRef = dbo.tblRB_RepeatBookings.BK_RoomRef

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 08:40:27

Try this

SELECT b.BK_RepeatId, b.BK_BookingNo, b.BK_BookingDateRef,
b.BK_DateBooked, b.BK_RoomRef, b.BK_DateRequired,
b.BK_Group, b.BK_BookedBy, b.BK_Telephone,
b.BK_CateringRequired, b.BK_NoAttending, b.BK_SpecialRequirements,
b.BK_TotalCharge, b.BK_Parking, b.BK_Refreshments,
b.BK_P1, b.BK_P2, b.BK_P3, b.BK_P4,
b.BK_P5, b.BK_P6, b.BK_P7, b.BK_P8,
b.BK_P9, b.BK_P10, b.BK_P11, b.BK_P12,
b.BK_P13, b.BK_P14, b.BK_P15, b.BK_P16,
b.BK_P17, b.BK_P18, b.BK_P19, b.BK_P20,
b.BK_P21, b.BK_P22, b.BK_P23, b.BK_P24,
b.BK_P25, b.BK_P26, b.BK_P27, b.BK_P28,
b.BK_Confirmed, b.BK_STime, b.BK_ETime,
b.BK_EnteredBy, b.BK_Cancelled, b.BK_CancelledDate,
b.BK_CancelledBy, b.BK_RoomLayout, b.BK_MeetingTitle,
b.BK_Identifier, b.BK_Include, b.BK_Recharge,
r.RM_Location
FROM dbo.tblRB_Rooms as r INNER JOIN
dbo.tblRB_RepeatBookings as b ON r.RM_RoomRef = b.BK_RoomRef

Madhivanan

Failing to plan is Planning to fail

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-02 : 08:46:38

The database was copied from live to test and just called another name - I cannot see why it shouldn;t work on the test server.....

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-03 : 04:30:00

I've tried your code madhivanan but get the same error......

Could it be my sp which uses the view ?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[spRB_GetRepeatBookingsbyLoc]

@strLocation nvarchar(1000)
as
Select *,BK_RepeatId,convert(varchar(20),BK_RepeatId) + ' :' +BK_BookedBy + ' :: '
+ BK_Group + '::' +Coalesce(BK_STime,'00:00')+' to ' + Coalesce(BK_ETime,'00:00') +'::'
+Coalesce(BK_Identifier,'No Identifier') as RepeatDesc
from vweRepeatBookings
WHERE BK_Include = 1 and
RM_Location= @strLocation
ORDER BY BK_RepeatId asc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 05:26:31

Yes!
The asterisk (*) will repeat your column name.
And this last query is not the one you posted earlier.

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-03 : 05:48:36

Ok - so what do I change it to ? More to the point, why doesn;t it cause an error on th eother server ??????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 05:52:47

It depends on what you do with the returning resultset from spRB_GetRepeatBookingsbyLoc.

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-03 : 06:58:57

Fill a dropdown list on a web page in asp.net app

Function GetRepeatBookings()

Dim MySQL As String = "spRB_GetRepeatBookingsbyLoc"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strLocation", Me.Session("Location")))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet
myDataAdapter.SelectCommand = Cmd
myDataAdapter.Fill(ddlDataSet, "vweRepeatBookings")

Return ddlDataSet
End Function

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 07:07:37

Now we finally know what is happening! It only took a day

The DataAdapter do not accept duplicate column name.
Edit the "SELECT *" to "SELECT <column name list here>" instead.
And why would you want duplicate column names.

You can return same column twice, but you have to alias one of the columns

SELECT Number, Number AS Num2
FROM master..spt_values
WHERE Type = 'P'

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-09-03 : 07:57:55

But why does it work ok on the other server ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-03 : 09:02:59

I have no idea, nor access to your code.

E 12°55'05.63"
N 56°04'39.26"

Pinto
Aged Yak Warrior

590 Posts