This gives me the error:

Server: Msg 16957, Level 16, State 4, Line 2
FOR UPDATE cannot be specified on a READ ONLY cursor.


Craig

-----Original Message-----
From: Devin Ben-Hur
[mailto:mssqldba-ezmlmshield-x55932940.[Email address protected]
Sent: Friday, February 24, 2006 12:48 PM
To: LazyDBA Discussion
Subject: Re: The cursor is READ ONLY

Comperatore Craig wrote:
> I changed my declare statement to:

You're syntax is incorrect check
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_de-dz_31yq.asp>

You need a FOR statement and have to loss the outer parens around your
select to get the syntax right. I suggest FORWARD_ONLY SCROLL_LOCKS
cursor mode options too for your usage.

Also, you may not specify any columns from alias Z in the FOR UPDATE
clause. Z's fields are derived values, you can not SET them (this was
likely the source of your earlier read_only error message). The columns
you specify in FOR UPDATE OF should be the same columns you target
(assign) with the SET clause of your UPDATE... FOR CURRENT OF.

Here's what it should look like:

DECLARE @inactivate_DI_assign CURSOR
FORWARD_ONLY SCROLL_LOCKS FOR
SELECT p.*, z.last_discharge
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


--
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