I changed my declare statement to:

DECLARE @inactivate_DI_assign CURSOR
(select *
from Patient p
inner join
-- Find last discharge date
(select p.patient_id, max(pa.date_discharged) as
last_discharge
from patient p
join patient_assignment pa
on p.patient_id = pa.patient_id
and p.episode_id = pa.episode_id
where pa.patient_id in(select patient_id from
patient_assignment where date_discharged is not null)
and pa.patient_id not in (select patient_id from
patient_assignment where date_discharged is null)
and p.case_status = 'A'-- Only active patients
and pa.patient_id <> '00000000' -- Ignore NONCLIENT
group by p.patient_id, p.case_status
having (datediff(d, max(pa.date_discharged), getdate())) >
40) z
on p.patient_id = z.patient_id
where p.case_status = 'A'
and p.patient_id <> '00000000')
FOR UPDATE OF p.case_status, p.case_substatus, p.episode_close_date,
p.user_id, p.entry_chron, p.comments, z.last_discharge

And that gives me the error:

FOR UPDATE clause allowed only for DECLARE CURSOR.

Craig

-----Original Message-----
From: Devin Ben-Hur
[mailto:mssqldba-ezmlmshield-x44163097.[Email address protected]
Sent: Thursday, February 23, 2006 6:50 PM
To: LazyDBA Discussion
Subject: Re: The cursor is READ ONLY

Comperatore Craig wrote:
> Hey Gurus - can someone help me figure out why I am getting this error:
>
> Server: Msg 16929, Level 16, State 1, Line 70
> The cursor is READ ONLY.
....
> I've tried adding the FOR UPDATE clause in my set query, but then I get:
> FOR UPDATE cannot be specified on a READ ONLY cursor

Did you use just
FOR UPDATE
or
FOR UPDATE OF p.case_status, p.case_substatus, p.episode_close_date,
p.user_id, p.entry_chron, p.comments
?

The former implies you're asking to update all the fields including the
result fields of your z select, which is obviously impossible as z's
fields are derived aggregate values.

To update with a cursor, you have to have a FOR UPDATE clause in the
declaration and all the targetted fields must be updateable (non-derived
and from a table which you have update permissions on).

As an aside, you should probably use FETCH NEXT INTO (set of local
variables) and use the variables in your UPDATE SET ... WHERE CURRENT OF
statement instead of repeating that FROM clause from the cursor
definition. What you've expressed may be allowed syntax, but I've never
seen it used before and it looks like an invitation for future error.

--
Devin Ben-Hur | President / CTO
eMarket Group, Ltd | http://www.emarket-group.com
Zat.com | http://www.Zat.com
503/445-8028 | mailto:[Email address protected]

At least when a friend shoots you in the face,
you know where he stands.


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidential: This electronic message and all contents contain information
from BayCare Health System which may be privileged, confidential or otherwise
protected from disclosure. The information is intended to be for the addressee
only. If you are not the addressee, any disclosure, copy, distribution or use
of the contents of this message is prohibited. If you have received this
electronic message in error, please notify the sender and destroy the original
message and all copies.

MS Sql Server LazyDBA home page