FOR UPDATE clause in cursor

I'm refactoring a cursor written by another employee. It includes a cursor
that uses the FOR UPDATE clause to update records as the cursor iterates
through the records. Please assume that the cursor cannot/should not be
replaced.
When executing the snippet of code below, I get the SQL Server error below.
What syntax would allow me to make the cursor read/write rather than read
only? I tried making the cursor DYNAMIC, but this did not appear to help.
Thanks in adavnce.
-Mark
Server: Msg 16957, Level 16, State 4, Procedure p_om_daily_reload, Line 402
FOR UPDATE cannot be specified on a READ ONLY cursor.
********************** SNIPPET OF CODE ****************************
declare @.audit_note varchar(200)
declare reg_curs_mentorhip_cancelled CURSOR DYNAMIC
FOR
-- Look for existing payment records using the reg_num that have been
marked as canceled in informix.
SELECT DISTINCT mp.mentorship_id, im.hours
FROM t_om_informix_mentorship im
INNER JOIN t_om_app_mentorship_payment mp
ON im.reg_num = mp.reg_num
WHERE im.reg_status = 'C'
FOR UPDATE -- MARK: Added to allow cursor data to be updated. Default is
read-only
OPEN reg_curs_mentorhip_cancelled
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
PRINT'* 4a , subtract the amount of time refunded'
UPDATE t_om_app_mentorship
SET time_purchased = time_purchased - @.add_purchase
WHERE mentorship_id = @.mentorship_id;
PRINT'* 4a , remove payment record'
DELETE t_om_app_mentorship_payment
WHERE CURRENT OF reg_curs_mentorhip_cancelled
PRINT'* 4a ,add audit trail'
SET @.audit_note = 'mentorship_id = ' + convert(varchar(15),
@.mentorship_id) + ', Hours affected: ' + CAST(@.add_purchase as varchar)
EXEC p_om_data_audit_trail_i @.user_key = 'unknown', @.priority = 1,
@.create_user = 'Reload_Script', @.category = 'Payments',
@.issue = 'Mentorship Payment either disappeared or was cancelled',
@.note = @.audit_note
END
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
END
CLOSE reg_curs_mentorhip_cancelled
DEALLOCATE reg_curs_mentorhip_cancelledThe cursor cannot be updateable because of the DISTINCT modifier in the
query.

> Please assume that the cursor cannot/should not be
> replaced.
No chance of me assuming that! Cursors bad. Updateable cursors worse.
;-)
David Portas
SQL Server MVP
--|||>> Please assume that the cursor cannot/should not be replaced. <<
Is this a teaching exercise of some kind? "Look Igor, screwdriver
better than rocks for cabinet making!!" "Igor not like screwdriver!
Igor have rock!"
Based on ~20 years with SQL, ten years on the ANSI Standards committee
of SQL, and six books on the language, I find Kosher Pork easier to
assume -- Hey, we got genetic engineering and can fix those feet!
I have written five cursors in my career and I know that at least three
of them could have been done with a CASE expression. They would have
run **orders of magnitude** faster and been easierr to maintain.
Also, when you post code could you consistently uppercase reserved
words? Learn about semi-colons in SQL? ISO_11179 rules? Not use PRINT
in application code (it is for debugging only)? What about the basic
rule that audit trails are external to the procedures, so you know that
they valid?
You have code like an OO or COBOL program. The basic problem; this is
procedural code written in SQL, a declarative language. Your entire
approach and vocabulary are non-relational (Records in SQL').
Dave was nicer than me (who isn't?). EVERYTHING needs to be re-done
whiel there is still time. This is so crappy it is not worh saving and
might have screwed up data integrity already.

For each Loop Container do nothing inside

Hi,

A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?

Your help is much welcome. Thanks in Advance.

Jo?o Cruz

Not enought nformation to tell really, but likely related to the code in the script task. Without knowing what the package looks like, what precedence contraints you have etc., it's really tough to know.

More information please...

|||

I have found the solution . Thanks very much for your time.