Error in stored procedure

TheMSsForum.com: The Microsoft Software Forum

Hi,

I just wanted to ask what is wrong with this stored
procedure. i keep getting the following error message:

ADO error: The column prefix 'dbo' does not match with a
table name or alias name used in the query.


ALTER PROCEDURE dbo.SPSelEquipBooked2
(@Arrival datetime,
@Departure datetime)

AS
SELECT dbo.Equip_Inv_No
FROM dbo.Equipment_Inv
WHERE dbo.equipment_Inv.Equipment_Inv_No
Not In(
SELECT dbo.Equipment_Inv.Equip_Inv_No
FROM dbo.Equipment_Booking INNER JOIN
dbo.Equipment_Inv ON
dbo.Equipment_Booking.Equip_Inv_No =
dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
dbo.Caravan_booking ON
dbo.Equipment_Booking.BookingNo =
dbo.Caravan_booking.BookingNo AND
dbo.Equipment_Booking.Caravan_Inv_No =
dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
= dbo.Booking_Details.BookingNo
WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))


thx in advance
Top

Re: Error in stored procedure by Miha

Miha
Thu Dec 04 15:59:24 CST 2003

Hi Angelina,

Did you try invoking the procedure without prefix?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Angelina" <anonymous@discussions.microsoft.com> wrote in message
news:d33501c3baaf$c9d26d80$a601280a@phx.gbl...
> Hi,
>
> I just wanted to ask what is wrong with this stored
> procedure. i keep getting the following error message:
>
> ADO error: The column prefix 'dbo' does not match with a
> table name or alias name used in the query.
>
>
> ALTER PROCEDURE dbo.SPSelEquipBooked2
> (@Arrival datetime,
> @Departure datetime)
>
> AS
> SELECT dbo.Equip_Inv_No
> FROM dbo.Equipment_Inv
> WHERE dbo.equipment_Inv.Equipment_Inv_No
> Not In(
> SELECT dbo.Equipment_Inv.Equip_Inv_No
> FROM dbo.Equipment_Booking INNER JOIN
> dbo.Equipment_Inv ON
> dbo.Equipment_Booking.Equip_Inv_No =
> dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
> dbo.Caravan_booking ON
> dbo.Equipment_Booking.BookingNo =
> dbo.Caravan_booking.BookingNo AND
> dbo.Equipment_Booking.Caravan_Inv_No =
> dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
> dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
> = dbo.Booking_Details.BookingNo
> WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
> AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
>
>
> thx in advance
>


Top

Re: Error in stored procedure by Angelina

Angelina
Thu Dec 04 18:41:02 CST 2003

Hi Miha,

I have tried this and still i cannot get this to work. :o(



>-----Original Message-----
>Hi Angelina,
>
>Did you try invoking the procedure without prefix?
>
>--
>Miha Markic - RightHand .NET consulting & development
>miha at rthand com
>
>"Angelina" <anonymous@discussions.microsoft.com> wrote
in message
>news:d33501c3baaf$c9d26d80$a601280a@phx.gbl...
>> Hi,
>>
>> I just wanted to ask what is wrong with this stored
>> procedure. i keep getting the following error message:
>>
>> ADO error: The column prefix 'dbo' does not match with
a
>> table name or alias name used in the query.
>>
>>
>> ALTER PROCEDURE dbo.SPSelEquipBooked2
>> (@Arrival datetime,
>> @Departure datetime)
>>
>> AS
>> SELECT dbo.Equip_Inv_No
>> FROM dbo.Equipment_Inv
>> WHERE dbo.equipment_Inv.Equipment_Inv_No
>> Not In(
>> SELECT dbo.Equipment_Inv.Equip_Inv_No
>> FROM dbo.Equipment_Booking INNER JOIN
>> dbo.Equipment_Inv ON
>> dbo.Equipment_Booking.Equip_Inv_No =
>> dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
>> dbo.Caravan_booking ON
>> dbo.Equipment_Booking.BookingNo =
>> dbo.Caravan_booking.BookingNo AND
>> dbo.Equipment_Booking.Caravan_Inv_No =
>> dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
>> dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo
>> = dbo.Booking_Details.BookingNo
>> WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
>> AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
>>
>>
>> thx in advance
>>
>
>
>.
>
Top

Re: Error in stored procedure by bruce

bruce
Thu Dec 04 19:24:56 CST 2003

in your first select

SELECT dbo.Equip_Inv_No

you refer to table dbo, which is not specified in the query, just like the
message say's.





"Angelina" <anonymous@discussions.microsoft.com> wrote in message
news:d33501c3baaf$c9d26d80$a601280a@phx.gbl...
> Hi,
>
> I just wanted to ask what is wrong with this stored
> procedure. i keep getting the following error message:
>
> ADO error: The column prefix 'dbo' does not match with a
> table name or alias name used in the query.
>
>
> ALTER PROCEDURE dbo.SPSelEquipBooked2
> (@Arrival datetime,
> @Departure datetime)
>
> AS
> SELECT dbo.Equip_Inv_No
> FROM dbo.Equipment_Inv
> WHERE dbo.equipment_Inv.Equipment_Inv_No
> Not In(
> SELECT dbo.Equipment_Inv.Equip_Inv_No
> FROM dbo.Equipment_Booking INNER JOIN
> dbo.Equipment_Inv ON
> dbo.Equipment_Booking.Equip_Inv_No =
> dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
> dbo.Caravan_booking ON
> dbo.Equipment_Booking.BookingNo =
> dbo.Caravan_booking.BookingNo AND
> dbo.Equipment_Booking.Caravan_Inv_No =
> dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
> dbo.Booking_Details ON dbo.Caravan_booking.BookingNo
> = dbo.Booking_Details.BookingNo
> WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
> AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
>
>
> thx in advance
>


Top

Re: Error in stored procedure by Miha

Miha
Fri Dec 05 03:32:56 CST 2003

Does the sp work from Sql Query Analyzer?

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

"Angelina" <anonymous@discussions.microsoft.com> wrote in message
news:054301c3bac8$75524fa0$a301280a@phx.gbl...
> Hi Miha,
>
> I have tried this and still i cannot get this to work. :o(
>
>
>
> >-----Original Message-----
> >Hi Angelina,
> >
> >Did you try invoking the procedure without prefix?
> >
> >--
> >Miha Markic - RightHand .NET consulting & development
> >miha at rthand com
> >
> >"Angelina" <anonymous@discussions.microsoft.com> wrote
> in message
> >news:d33501c3baaf$c9d26d80$a601280a@phx.gbl...
> >> Hi,
> >>
> >> I just wanted to ask what is wrong with this stored
> >> procedure. i keep getting the following error message:
> >>
> >> ADO error: The column prefix 'dbo' does not match with
> a
> >> table name or alias name used in the query.
> >>
> >>
> >> ALTER PROCEDURE dbo.SPSelEquipBooked2
> >> (@Arrival datetime,
> >> @Departure datetime)
> >>
> >> AS
> >> SELECT dbo.Equip_Inv_No
> >> FROM dbo.Equipment_Inv
> >> WHERE dbo.equipment_Inv.Equipment_Inv_No
> >> Not In(
> >> SELECT dbo.Equipment_Inv.Equip_Inv_No
> >> FROM dbo.Equipment_Booking INNER JOIN
> >> dbo.Equipment_Inv ON
> >> dbo.Equipment_Booking.Equip_Inv_No =
> >> dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
> >> dbo.Caravan_booking ON
> >> dbo.Equipment_Booking.BookingNo =
> >> dbo.Caravan_booking.BookingNo AND
> >> dbo.Equipment_Booking.Caravan_Inv_No =
> >> dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
> >> dbo.Booking_Details ON
> dbo.Caravan_booking.BookingNo
> >> = dbo.Booking_Details.BookingNo
> >> WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
> >> AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
> >>
> >>
> >> thx in advance
> >>
> >
> >
> >.
> >


Top

Re: Error in stored procedure by Angelina

Angelina
Fri Dec 05 10:49:29 CST 2003

Hi Bruce,

I though you prefix all tables with dbo ?
what is this for anyway?
The bottom half of my query runs properly buy when i add
the top half i get the error message.
What do u suggest i change this sql code to?
I do not have sql server so i cannot run this code in the
sql query analyzer. I am using msde.

thx



>-----Original Message-----
>in your first select
>
> SELECT dbo.Equip_Inv_No
>
>you refer to table dbo, which is not specified in the
query, just like the
>message say's.
>
>
>
>
>
>"Angelina" <anonymous@discussions.microsoft.com> wrote
in message
>news:d33501c3baaf$c9d26d80$a601280a@phx.gbl...
>> Hi,
>>
>> I just wanted to ask what is wrong with this stored
>> procedure. i keep getting the following error message:
>>
>> ADO error: The column prefix 'dbo' does not match with
a
>> table name or alias name used in the query.
>>
>>
>> ALTER PROCEDURE dbo.SPSelEquipBooked2
>> (@Arrival datetime,
>> @Departure datetime)
>>
>> AS
>> SELECT dbo.Equip_Inv_No
>> FROM dbo.Equipment_Inv
>> WHERE dbo.equipment_Inv.Equipment_Inv_No
>> Not In(
>> SELECT dbo.Equipment_Inv.Equip_Inv_No
>> FROM dbo.Equipment_Booking INNER JOIN
>> dbo.Equipment_Inv ON
>> dbo.Equipment_Booking.Equip_Inv_No =
>> dbo.Equipment_Inv.Equip_Inv_No INNER JOIN
>> dbo.Caravan_booking ON
>> dbo.Equipment_Booking.BookingNo =
>> dbo.Caravan_booking.BookingNo AND
>> dbo.Equipment_Booking.Caravan_Inv_No =
>> dbo.Caravan_booking.Caravan_Inv_No INNER JOIN
>> dbo.Booking_Details ON
dbo.Caravan_booking.BookingNo
>> = dbo.Booking_Details.BookingNo
>> WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival)
>> AND (dbo.Booking_Details.Dt_of_arrival <= @Departure))
>>
>>
>> thx in advance
>>
>
>
>.
>
Top