Don't you need a 'FOR' after CURSOR
-----Original Message-----
From: Comperatore Craig
[mailto:mssqldba-ezmlmshield-x76506562.[Email address protected]
Sent: 24 February 2006 14:58
To: LazyDBA Discussion
Subject: RE: The cursor is READ ONLY
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.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
This email and any files transmitted within it are private and confidential.
If you are not the intended recipient, this email and any attachments within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using the information contained within this email.
Nothing in this email message amounts to a contractual or legal commitment on the part of Optilan unless confirmed by a communication signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from viruses. Although every possible care is taken by Optilan, Optilan does not accept any liability whatsoever for any loss or damage which may be caused as a result of the transmission of this message by email.
MS Sql Server LazyDBA home page