ReadOnly Cursor

4 Nov 2005 6:17 PM

Li

DECLARE CP_Cursor CURSOR FOR
SELECT InsID, DateID
FROM dbo.vw_CP
ORDER BY InsID, DateID
FOR UPDATE OF ToDateID

OPEN CP_Cursor
FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE dbo.vw_CP SET ToDateID = 0
WHERE CURRENT OF CP_Cursor

FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
END
CLOSE CP_Cursor
DEALLOCATE CP_Cursor

In one environment this script works, but in another environment has error
message 'Msg 16957, Level 16, State 4, Server , FOR UPDATE cannot be
specified on a READ ONLY cursor', I can't access another environment. Do you
know why the cursor is readonly?

Thanks

4 Nov 2005 7:42 PM

Barry

The only thing I can think off is that you do not have sufficient
permissions to execute UPDATES. If this is the case then the cursor
would revert to a Read Only Cursor.

HTH

Barry

4 Nov 2005 8:29 PM

David Portas

Why the cursor? Instead you can do:

UPDATE dbo.vw_cp
SET todateid = 0

I assume this was just a simplified example but it's too simple for us
to help you properly. Usually there's an easy answer that doesn't
require a cursor.

--
David Portas
SQL Server MVP
--

6 Nov 2005 12:03 AM

Erland Sommarskog

Li (L*@Li.com) writes:
Show quote

> DECLARE CP_Cursor CURSOR FOR
> SELECT InsID, DateID
> FROM dbo.vw_CP
> ORDER BY InsID, DateID
> FOR UPDATE OF ToDateID
>
> OPEN CP_Cursor
> FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
> WHILE @@FETCH_STATUS = 0
> BEGIN
> UPDATE dbo.vw_CP SET ToDateID = 0
> WHERE CURRENT OF CP_Cursor
>
> FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
> END
> CLOSE CP_Cursor
> DEALLOCATE CP_Cursor
>
> In one environment this script works, but in another environment has
> error message 'Msg 16957, Level 16, State 4, Server , FOR UPDATE cannot
> be specified on a READ ONLY cursor', I can't access another environment.
> Do you know why the cursor is readonly?

Judging from the name vw_CP is a view. There may be differences between
the definition of the view on the two environments, so that the view is
not updatable in the other environment. You need to check view, triggers
on tbe view, and the underlying tables.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

3 Jan 2006 5:26 PM

Emma

I had a similar problem in two different environments with cursors. It turned
out that the table I am updating in the environment with the error does not
have an index. After manually creating the index, the stored procedure run
without the error message.

Show quote

"Li" wrote:

> DECLARE CP_Cursor CURSOR FOR
> SELECT InsID, DateID
> FROM dbo.vw_CP
> ORDER BY InsID, DateID
> FOR UPDATE OF ToDateID
>
> OPEN CP_Cursor
> FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
> WHILE @@FETCH_STATUS = 0
> BEGIN
> UPDATE dbo.vw_CP SET ToDateID = 0
> WHERE CURRENT OF CP_Cursor
>
> FETCH NEXT FROM CP_Cursor INTO @iInsID, @iDateID
> END
> CLOSE CP_Cursor
> DEALLOCATE CP_Cursor
>
> In one environment this script works, but in another environment has error
> message 'Msg 16957, Level 16, State 4, Server , FOR UPDATE cannot be
> specified on a READ ONLY cursor', I can't access another environment. Do you
> know why the cursor is readonly?
>
> Thanks
>
>
>