February 17, 2002 at 9:44 pm
Hi all,
Trying to declare cursor with a for update statement something like below.
Gives an error numbered:16957 Error Description: FOR UPDATE cannot be specified on a READ ONLY cursor.
While the same code works fine for SQL 7.0 it gives a problem with SQL 2000.
Declare xx cursor for
select column_1, column_2, column_3 from some_table
where column_1 = @some_var for update of column_3;
TIA,
Kaushik.
February 17, 2002 at 9:58 pm
I don't believe SQL2K defaults to READ ONLY unless you are lacking update permissions to the table in question. Is that an issue?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 2:19 am
Checked on the permission issues. User has permission to execute the procedure from where this cursor is being used and the user also has permission to perform dml statements.
February 18, 2002 at 7:12 am
Also be sure that your cursor is really needed to accomplish the job. I have in the past used cursor too often and recently reevaluated and got rid of 95% and am still working on the others. Cursors just have too much overhead to justify unless there is no way around. But for your question I think if you make the following change:
Declare xx cursor FORWARD_ONLY for
February 18, 2002 at 10:49 am
This works because the default for a FORWARD_ONLY cursor is DYNAMIC which allows updates. If you declare a DYNAMIC cursor (without the FORWARD_ONLY) you'll get the ability to update and the ability to scroll in both directions. However, if you don't need that ability, go with the FORWARD_ONLY as Antares suggests.
What's throwing me for a loop is the following from Books Online:
quote:
READ ONLYPrevents updates made through this cursor. The cursor cannot be referenced in a WHERE CURRENT OF clause in an UPDATE or DELETE statement. This option overrides the default capability of a cursor to be updated.
So by default it should be an updatable cursor.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 19, 2002 at 12:11 am
Doesn't really work. This is a funny situation here. While it works perfectly with SQL 7.0 and Personal/Desktop Edition of SQL 2000 the same code doesn't seem to work in the Enterprise edition. Tried all combinations with/without FORWARD_ONLY same result.
February 19, 2002 at 4:39 am
Looking around I found a reference I want to verify. Does the table have a unique index? Also so I know what is up with the table could you please post the table def from production and your 2000 PE server (to obtain right click on the table in Enterprise Manager and choose copy then paste somewhere)?
February 19, 2002 at 8:29 pm
The core of my problem I have posted as a different topic but wouldn't mind understanding the reason behind codes working differently in various versions. In my both environments the table structure is as belows:
CREATE TABLE [CODE_ATTRIB] (
[ENTITY] [varchar] (4) NOT NULL ,
[CODE_ID] [varchar] (10) NOT NULL ,
[ATTRIB_ID] [varchar] (10) NOT NULL ,
[ORDRD_ID] [numeric](3, 0) NOT NULL ,
[ATTRIB_VAL] [varchar] (10) NULL ,
[END_VAL] [varchar] (10) NULL ,
[CURR_VAL] [varchar] (10) NULL ,
[CREATED_BY] [varchar] (10) NULL ,
[CREATED_DATE] [datetime] NULL ,
[UPDATED_BY] [varchar] (10) NULL ,
[UPDATED_DATE] [datetime] NULL ,
CONSTRAINT [CODE_ATTRIB_KEY] PRIMARY KEY CLUSTERED
(
[ENTITY],
[CODE_ID],
[ATTRIB_ID],
[ORDRD_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply