Tuesday, March 27, 2012

Cursor is not open

Hi All,
I have a SQL query that open a cursor, the query running well on SQL 2000
query analyser but give me an error message "Cursor is not open" on SQL 2005
management studio.
the query running on the same database, same server (compatibilty level: SQL
2000 (80)).
any help ?
Hi
Can you post the source to be tested?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> I have a SQL query that open a cursor, the query running well on SQL 2000
> query analyser but give me an error message "Cursor is not open" on SQL
> 2005 management studio.
> the query running on the same database, same server (compatibilty level:
> SQL 2000 (80)).
> any help ?
>
|||select ID,AutoID
into #Duplo
from Table1,
(select ID,count(id) as T
from Table1
where StoreID= xx
group by id
having count(id)>1) as M1
where Table1.ID=M1.ID
order by Table1.ID,Table1.AutoID
declare @.PrevID int,@.ID int,@.RecautoID int
DECLARE Table1_Cursor CURSOR dynamic FOR
select ID,AutoID
from #Duplo
for update
OPEN Table1_Cursor
FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.PrevID=@.ID
begin
delete Table1 WHERE AutoID=@.RecAutoID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
else
begin
set @.PrevID=@.ID
FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
end
END
CLOSE Table1_Cursor
DEALLOCATE Table1_Cursor
GO
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
> Hi
> Can you post the source to be tested?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:uUZ4gh8tHHA.4488@.TK2MSFTNGP05.phx.gbl...
>
|||Hi
I could not reproduce the problem. What if you execute first SELECT *INTO
... command and then run a cursor, do you still have an error?
"Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
> select ID,AutoID
> into #Duplo
> from Table1,
> (select ID,count(id) as T
> from Table1
> where StoreID= xx
> group by id
> having count(id)>1) as M1
> where Table1.ID=M1.ID
> order by Table1.ID,Table1.AutoID
> declare @.PrevID int,@.ID int,@.RecautoID int
> DECLARE Table1_Cursor CURSOR dynamic FOR
> select ID,AutoID
> from #Duplo
> for update
> OPEN Table1_Cursor
> FETCH FIRST FROM Table1_Cursor into @.PrevID,@.RecAutoID
> FETCH NEXT FROM Table1_Cursor into @.ID,@.RecAutoID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> if @.PrevID=@.ID
> begin
> delete Table1 WHERE AutoID=@.RecAutoID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> else
> begin
> set @.PrevID=@.ID
> FETCH next FROM Table1_Cursor into @.ID,@.RecAutoID
> end
> END
> CLOSE Table1_Cursor
> DEALLOCATE Table1_Cursor
> GO
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:efTJvq8tHHA.4844@.TK2MSFTNGP04.phx.gbl...
>
|||do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or what
?
my problem is why it is working on SQL 2000 query analyzer only ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
> Hi
> I could not reproduce the problem. What if you execute first SELECT *INTO
> ... command and then run a cursor, do you still have an error?
>
>
> "Mahmoud Amin" <m_amin33@.hotmail.com> wrote in message
> news:%23tgZFy8tHHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Hi
No it is OK SELECT ID,AUTOID as well
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
news:eKTJMY9tHHA.1768@.TK2MSFTNGP04.phx.gbl...
> do you mean SELECT * instead of SELECT ID,AUTOID only to be replaced or
> what ?
> my problem is why it is working on SQL 2000 query analyzer only ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7LSkG9tHHA.4916@.TK2MSFTNGP05.phx.gbl...
>

No comments:

Post a Comment