Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 ambiguous column name
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tkv
Starting Member

Kuwait
2 Posts

Posted - 02/16/2010 :  03:32:16  Show Profile  Reply with Quote
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

United Kingdom
22859 Posts

Posted - 02/16/2010 :  03:38:46  Show Profile  Reply with Quote
" 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)

Edited by - Kristen on 02/16/2010 04:09:41
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/16/2010 :  03:41:59  Show Profile  Reply with Quote
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/

Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/16/2010 :  04:09:19  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 02/16/2010 :  04:38:17  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/16/2010 :  04:42:23  Show Profile  Reply with Quote
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/

Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/16/2010 :  05:53:47  Show Profile  Reply with Quote
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!)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 02/16/2010 :  06:09:58  Show Profile  Reply with Quote
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/

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8781 Posts

Posted - 02/16/2010 :  07:13:23  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Kristen
Test

United Kingdom
22859 Posts

Posted - 02/16/2010 :  07:49:11  Show Profile  Reply with Quote
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 ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000