From BOL:
If a DECLARE CURSOR using Transact-SQL syntax does not specify READ_ONLY,
OPTIMISTIC, or SCROLL_LOCKS, the default is as follows:
If the SELECT statement does not support updates (insufficient
permissions, accessing remote tables that do not support updates, and so
on), the cursor is READ_ONLY.
STATIC and FAST_FORWARD cursors default to READ_ONLY.
DYNAMIC and KEYSET cursors default to OPTIMISTIC.
-----Original Message-----
From: Comperatore Craig
[mailto:mssqldba-ezmlmshield-x71709734.[Email address protected]
Sent: Thursday, February 23, 2006 3:36 PM
To: LazyDBA Discussion
Subject: The cursor is READ ONLY
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.
The statement has been terminated.
Here is the query:
/*
Inactivate patients who are in Active status but only have discharged
assignments
*/
DECLARE @inactivate_DI_assign CURSOR
SET @inactivate_DI_assign = CURSOR FOR
(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
/******************************************************************
The following section finds patients that have discharged assignments
but do not have active assignments
******************************************************************/
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
-- Only select where the last discharge was more
than 40 days ago
having (datediff(d, max(pa.date_discharged),
getdate())) > 40) z
on p.patient_id = z.patient_id
and p.case_status = 'A'-- Only active patients
and p.patient_id <> '00000000') -- Ignore NONCLIENT
OPEN @inactivate_DI_assign
FETCH NEXT FROM @inactivate_DI_assign
WHILE (@@FETCH_STATUS = 0)
BEGIN
update p
set p.case_status = 'I', -- Inactivate patient
p.case_substatus = 'DI', -- Set substatus to Discharged
p.episode_close_date = z.last_discharge, -- Set episode close = date of
last assignment discharge
p.user_id = '120DAYS', p.entry_chron = getdate(),
comments = 'Episode discharged via the 120 day rule.'-- Mark records
changed by this script
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
/******************************************************************
The following section finds patients that have discharged episodes
but do not have active episodes
******************************************************************/
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
-- Only select where the last discharge was more
than 40 days ago
having (datediff(d, max(pa.date_discharged),
getdate())) > 40) z
on p.patient_id = z.patient_id
and p.case_status = 'A'-- Only active patients
and p.patient_id <> '00000000' -- Ignore NONCLIENT
WHERE CURRENT OF @inactivate_DI_assign
FETCH NEXT FROM @inactivate_DI_assign
END
CLOSE @inactivate_DI_assign
DEALLOCATE @inactivate_DI_assign
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
I have another query that uses the same format (only does not use an inner
join) and it is working fine. I don't get it.
Thanks for your help,
Craig Comperatore, CNE, MCP, CCNA, A+, Net+
IT Site Coordinator, Baycare - the Harbor BHCI
Phone: (727) 841-4200 x 248
Fax: (727) 841-4203
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
MS Sql Server LazyDBA home page