I have a sproc that has been running for over 2 years. We then alter a tabl
e structure
increasince the size of 3 fields, and correspondingly alter an Insert statem
ent for this
table. Now an 'update tablename where current of mycur' much later in the c
ode, issues an error of
'The cursor is Read Only' and fails.
The cursor syntax were :
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
ORDER BY ld_employee_no, adjusted_hours desc
I changed last line to the following and it works.
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
FOR UPDATE OF adjusted_hours
Does anyone have any idea, or should I post more info? We'd really like to
know
why the DDL change and the Insert change would affect a cursor update.
TIA,
Marc MillerWithout seeing the full repro I'm guessing that you changed something
that caused an implicit conversion to a static cursor. Always specify
cursor options explicitly to avoid this.
Could you explain why you are using a cursor at all? Again just
guessing by your code fragment this looks very like a straight data mod
that ought to be possible in an UPDATE with no cursor at all. If this
is a 2 year code legacy then maybe now would be a good time to review
and replace it.
David Portas
SQL Server MVP
--|||David,
I have a table of salaried employee time entires. Reporting requires,
however, that I only
show 40 hours per employee, even though they report overtime hours. Their
time is reported
in quarter hours increments and I need to loop and decrement/increment the
line items by the amount of
the overtime until I can best adjust each line 'evenly' (sort of an
allocation type basis.) to a total of 40 hours
for each person.
I have no idea in the world how I would use an UPDATE to accomplish this.
Thanks,
Marc Miller
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128538402.789619.48450@.z14g2000cwz.googlegroups.com...
> Without seeing the full repro I'm guessing that you changed something
> that caused an implicit conversion to a static cursor. Always specify
> cursor options explicitly to avoid this.
> Could you explain why you are using a cursor at all? Again just
> guessing by your code fragment this looks very like a straight data mod
> that ought to be possible in an UPDATE with no cursor at all. If this
> is a 2 year code legacy then maybe now would be a good time to review
> and replace it.
> --
> David Portas
> SQL Server MVP
> --
>|||> Reporting requires,
> however, that I only
> show 40 hours per employee
If that's just a reporting requirement why do you need to update the
table? Wouldn't it suffice to put the calc in a SELECT statement?
> I have no idea in the world how I would use an UPDATE to accomplish this.
If you want help with that please post DDL, sample data and required
results as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--
