Error:The cursor is READ ONLY - 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

Error:The cursor is READ ONLY

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-09-28 : 05:44:42

Dear All,

I trying delete record from table with the help of following cusror.
I am getting error: "The cursor is READ ONLY" while deleting record.

Can anybody solve this issue.

ALTER PROCEDURE [dbo].[abc]
AS
BEGIN
DECLARE @a varchar(36)

DECLARE xyzcursor CURSOR FOR
SELECT TOP 10 R_D_C_R_ID
FROM dbo.tb_l_r_d_l_c_c
FOR UPDATE;

OPEN xyzcursor;

FETCH NEXT FROM xyzcursor INTO @a;

WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS ( SELECT '1' FROM dbo.tb_l_r_c_r
WHERE ID=@a)
BEGIN
PRINT 'Deleting ID=' + @a + ' DT=' + convert(varchar, getdate(), 14);
DELETE FROM tb_l_r_d_l_c_c
WHERE CURRENT OF xyzcursor;
END
FETCH NEXT FROM xyzcursor INTO @a;
END;
CLOSE xyzcursor;
DEALLOCATE xyzcursor;
END

Thanks and Regard's
Harish Patil

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 06:48:42

Why do you need a cursor and what are you trying to do with it? Are you trying to delete a record at a time within this cursor?

Best Regards
Vadivel

http://vadivel.blogspot.com

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-09-28 : 06:55:45

Hello Friend,

Yes. I am trying to delete record through this cursor.
Because in cusrsor i am getting millions of record to be deleted.
So instead of delete one by one I am trying to delete at a time.

If there is any other solution can you please provide me ?

Thanks,
Harish

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-28 : 07:42:39

Can you explain little more like based on what condition you wanted to delete the records? Do you want all records in that table to removed?

Best Regards
Vadivel

http://vadivel.blogspot.com

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-09-28 : 08:36:40

Hello Friend,

In my dbo.tb_l_r_d_l_c_c table having millions of record and in dbo.tb_l_r_c_r also. So when i trying to delete with the R_D_C_R_ID
= @a id insted of WHERE CURRENT OF xyzcursor; It's taking so much of time and coz of that may be table lock problem will arries.

So as performance optimize perspective I want to delete record with minimum time.

Please solve my issue why the error is comming while running this SP.
Error: The cursor is READ ONLY

Where i did forget to declare somthing or what ?

Thanks,
Harish

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-09-30 : 04:28:25

Hello Friends,

Anyone can help me how to resolve this issue ?

Thanks and Regard's
Harish Patil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources