cursor - 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

cursor

Author

Topic

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 02:38:20

I have made a cursor:
DECLARE new_cur CURSOR FOR select TOP 100 percent * from new_iba order by rid, attr, val for update of rid;

now while doing delete operation on new_iba table it give exception that cursor is read only

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-25 : 02:42:01

are you using SQL SEver 2000???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-25 : 02:45:43

you need to delete from the table itself, not on the cursor...

post the tsql for more help...

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 02:50:52

Post your delete statement

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 02:54:11

THATS my delete statement and i am using 2005
delete from new_iba where current of new_cur;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-25 : 03:59:56

Why did you use Cursor?

Madhivanan

Failing to plan is Planning to fail

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 04:39:02

--select * from temp
--select * from iba
select * from new_iba_1

--DECLARE old_cur1 CURSOR FOR (select t2.tracepoint from (select TOP 100 percent t1.tracepoint from temp t1 order by tracepoint) t2) for update ;
--DECLARE old_cur2 CURSOR FOR select TOP 100 percent iba.* from iba,new_obj_3 where iba.rid = new_obj_3.rid and iba.src_link is null order by iba.rid for update of iba.rid;
DECLARE new_cur2 CURSOR FOR select * from new_iba_1 order by rid, attr, val for update of rid;
--open old_cur1;
--open old_cur2
open new_cur2

DECLARE @tracepoint NUMERIC,
@ibaid NUMERIC,
@rid NUMERIC,
@attr NUMERIC,
@val NVARCHAR(1024),
@src_iba NUMERIC,
@src_link NUMERIC,
@rowid NUMERIC,
@new_rec_ibaid NUMERIC,
@new_rec_rid NUMERIC,
@new_rec_attr NUMERIC,
@new_rec_val NVARCHAR(1024),
@new_rec_orig_rid NUMERIC

--FETCH NEXT FROM old_cur1 INTO @tracepoint
--delete from temp where current of old_cur1

FETCH NEXT FROM new_cur2 INTO @new_rec_ibaid,@new_rec_rid,@new_rec_attr,@new_rec_val,@new_rec_orig_rid
delete from new_iba_1 where current of new_cur2;

--FETCH NEXT FROM old_cur2 INTO @ibaid,@rid,@attr,@val,@src_iba,@src_link,@rowid
--delete from iba where current of old_cur2

--close old_cur1;
--deallocate old_cur1;

--close old_cur2;
--deallocate old_cur2;
close new_cur2;
deallocate new_cur2;

--select * from temp
--select * from iba
select * from new_iba_1

this is giving read only cursor

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 07:29:54

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65172 for 'continuation'.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 08:27:37

this code is working for update and not for delete
thats strange
well i think i need to change the logic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 08:32:24

ag_ss, will you be able to post a reproducable sample of the error ? (which means script to create a simple table, insert some simple data etc)

ag_ss
Starting Member

48 Posts

Posted - 2006-04-25 : 09:59:00

CREATE TABLE new_iba_1 (
ibaid NUMERIC,
rid NUMERIC,
attr NUMERIC,
val NVARCHAR(1024),
orig_rid NUMERIC
)

GO

insert into new_iba_1 values (1,7,101,'a',8)
insert into new_iba_1 values (2,7,102,'b',8)
insert into new_iba_1 values (3,7,103,'c',8)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 10:23:59

Your table does not have a primary key ? Try defined a primary key

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-25 : 10:42:09

ag_ss

Did you read the thread I posted in the 'continuation' (which sadly now is not the case)?

The last 2 posts here are relevant - although khtan has now pointed out one of them...

http://sqljunkies.com/Forums/ShowPost.aspx?PostID=1242

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources