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

tkv
Starting Member

2 Posts

Posted - 2010-02-16 : 03:32:16

i am getting ambiguous column name 'events' when executing my stored procedure.
alter PROCEDURE [dbo].[spGetEventsByDate]
-- Add the parameters for the stored procedure here
@StartDateTme as DateTime,@EndDateTime as DateTime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @TransactionsDate as Datetime
declare @TransactionsTime as DateTime
declare @SerialNo as bigint
declare @TransactionType as int
declare @CardNum as int
declare @DevId as int
declare @TimeAtt as int
declare @Machine as Int

--DECLARE @Events CURSOR
DECLARE CurEvents CURSOR FOR
select EvenTime,eventime,SerialNum
,DevID,empid,Machine from events
where
EVENTTYPE=0 and EVENTID=0
and eventime>=@StartDateTme and eventime<=@EndDateTime
order by eventime
OPEN CurEvents;

FETCH NEXT FROM CurEvents;
WHILE @@FETCH_STATUS = 0
BEGIN
set @Devid=0;
FETCH NEXT FROM CurEvents
INTO @TransactionsDate,@TransactionsTime,@SerialNo,@DevId,@CardNum,@Machine
if(@DevId=0) RETURN
set @TimeAtt=0
select @TimeAtt= timeatt from reader where panelid=@machine and readerid=@devid

if(@TimeAtt=0) return
else if(@TimeAtt=1) set @TransactionType=7
else if(@TimeAtt=2) set @TransactionType=8
else return

insert into DP_IBA_EVENTS([DATE],[TIME],[SerialNo],[TRANSTYPE],[PIN],[ADDRESS])
VALUES (@TransactionsDate,@TransactionsTime,@SerialNo,@TransactionType,@CardNum,@DevId)
END;

CLOSE CurEvents;
DEALLOCATE CurEvents;

END
GO

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 03:38:46

" i am getting ambiguous column name 'events' when executing my stored procedure."

If you execute the SQL in QA what is the exact message you see?

It is probably referencing a Trigger, or maybe a View, (as there is no "events" column in your SProc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 03:41:59

why do you need cursor for this?
Isnt below enough?


insert into DP_IBA_EVENTS([DATE],[TIME],[SerialNo],[TRANSTYPE],[PIN],[ADDRESS])
select e.EvenTime,e.eventime,e.SerialNum,
case when r.timeatt =1 then 7 
     when r.timeatt=2 then 8
 end
,e.empid,e.DevID
from events e
join reader r
on r.panelid=e.Machine 
and r.readerid=e.DevID
where 
e.EVENTTYPE=0 and e.EVENTID=0
and e.eventime>=@StartDateTme and e.eventime<=@EndDateTime
and r.timeatt	<>0
order by e.eventime

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

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 04:09:19

Maybe because

select @TimeAtt= timeatt from reader where panelid=@machine and readerid=@devid

could return multiple rows and random-value is required?

Personally I would have used TOP 1 and ORDER BY on that, or checked @@ROWCOUNT so that it was either repeatable or error-checked.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-16 : 04:38:17

<<
Personally I would have used TOP 1 and ORDER BY on that
>>

or MIN or MAX of that

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 04:42:23

quote:
Originally posted by Kristen

Maybe because

select @TimeAtt= timeatt from reader where panelid=@machine and readerid=@devid

could return multiple rows and random-value is required?

Personally I would have used TOP 1 and ORDER BY on that, or checked @@ROWCOUNT so that it was either repeatable or error-checked.

I see many people who tend to like cursors based solutions and on most occasions than not they seem to have started their career in oracle and then moved to MSSQL

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

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 05:53:47

Also they have been a programmer using a procedural language, so Cursors "feels right" - it takes a while to get ones head around Set-based processes - after procedural languages (well ... it did for me!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 06:09:58

quote:
Originally posted by Kristen

Also they have been a programmer using a procedural language, so Cursors "feels right" - it takes a while to get ones head around Set-based processes - after procedural languages (well ... it did for me!)

Yeah..thats true too

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-02-16 : 07:13:23

I remember my first steps in SQL...
Without a clue what is possible in SQL I was going to code my own GROUP BY using a cursor because I came from COBOL...

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 07:49:11

We had a NEXT / PREVIOUS record button in our application when I moved from ISAM to SQL (SQL Server 6.5

)

For a table with a multi-part key, to get the NEXT record it did:


SELECT Col1, Col2, ...
FROM MyTable
WHERE KeyField1 > @KeyField1
      OR (KeyField1 = @KeyField1 AND KeyField2 > @KeyField2)
      OR (KeyField1 = @KeyField1 AND KeyField2 = @KeyField2 AND KeyField3 > @KeyField3)
ORDER BY KeyField1, KeyField2, KeyField3

PK / Clustered Index on KeyField1, KeyField2, KeyField3

Th application read the first row from the resultset and then closed the resultset

SQL Server was OK. Oracle took about 30 seconds to do this (I suppose we had about 50,000 rows in the table). Users hated it - "How come the old system was so fast?"

No idea if Oracle had any sort of TOP / LIMIT syntax back then, SQL Server certainly didn't! (Actually it did, SET ROWCOUNT, but I didn't know about that then) we were using generic SQL for all brands of SQL backend ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources