You are mixing the syntax up a bit. The declaration looks like a hybrid
of the DECLARE CURSOR and the cursor variable syntax.


Comperatore Craig wrote:
> 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.
>
>

MS Sql Server LazyDBA home page