Read Only Cursor

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

Reached nvarchar(4000) limit in building SQL statement.

SQL Server 2000 SP3a
Im using a sproc to make a sql statement. The statement is built up and
assigned to @.SQL1 nvarchar(4000), then at the end of the sproc its runs:
exec sp_executesql @.SQL1
The problem is that I've reached the 4000 limit!! How do other people get
round this, bearing in mind that its full of inner joins with sub selects, s
o
I dont think I can split it out. And because the sproc can create a view
which is updateable, I can't turn those sub selects into views as this makes
the newly created view non-updateable.
How can I build up a SQL string that is bigger than 4000 please.Split it up into multiple char/varchar variables and do:
EXEC( @.var1 + @.var2 + ...+ @.varn )
Anith|||Off the top of my head, can you incrementally build this using temp
tables and/or table variables?
Maybe something like...
procedure MyProc
as
-- do something with #temp1
-- do something with #temp2
select * from #temp1
join #temp1 on #temp2.something = #temp1.something
go
Just a thought.
Bryce|||If that means I can link several statements, which each on their own don't
make sense, together and execute in one go that would be perfect!!!
I won't be able to check til Monday, so many thanks in advance, I was
getting REALLY worried that many hours of trying to get get a one sproc does
all approach was going to fall at the last hurdle.
I shall read some more on exec / sp_executesql as it sounds like more can be
done than I had assumed.
Many thanks!
"Anith Sen" wrote:

> Split it up into multiple char/varchar variables and do:
> EXEC( @.var1 + @.var2 + ...+ @.varn )
> --
> Anith
>
>