I have a stored procedure which uses a cursor. When running this
procedure I get an error message after some time saying that there are
too many temporary tables on the connection. Since I do not create any
temp table I do not understand what this means!

Here the code:

LTER PROCEDURE "DBA"."sp_ONCE_updateOnwerChange"()
begin
declare cur cursor for select DBA.Verkauf.VbIdNr,
DBA.Verkauf.VbWiederZuDat as Zulassungsdatum,
DBA.Fahrzeuge.FzIdNr, row_number()
over (order by fzidnr asc, vbidnr asc) "nummer"
from DBA.Verkauf key join DBA.Fahrzeuge
order by Fahrzeuge.fzidnr asc,Verkauf.vbidnr
asc;
declare @vbidnr integer;
declare @zulassungsdatum date;
declare @fzidnr integer;
declare @nummer integer;

declare @prev_vbidnr integer;
declare @prev_fzid integer;
declare @prev_nummer integer;

set @prev_fzid = 1;
set @prev_nummer = 1;

open cur;
lp: LOOP
fetch next cur into @vbidnr, @zulassungsdatum, @fzidnr, @nummer;
if SQLCODE <> 0 then leave lp end if;
if @fzidnr = @prev_fzid and @nummer <> @prev_nummer then
update verkauf set Halterwechsel =
dateadd(day,-1,@zulassungsdatum), IdFolgeverkauf = @vbidnr where vbidnr
= @prev_vbidnr;
end if;
set @prev_vbidnr = @vbidnr;
set @prev_nummer = @nummer;
set @prev_fzid = @fzidnr;
end LOOP;
close cur;
end


Can anybode help me with this??

many thanks

Tom

at wrote:
What version and build number of SQL Anywhere are you using?
Do you have any statement level triggers or row level triggers on the
verkauf table?

--
Reg Domaratzki, Sybase iAnywhere Solutions
Please reply only to the newsgroup

Documentation : Exercise your WRITE @DocCommentXchange: DCX.sybase.com
SQL Anywhere Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere
-> Optionally set filter to "Display ALL platforms IN ALL MONTHS"

I use the latest available version 11.0.1 2299.

There were no triggers on the verkaufs table.

I had four materialized view, one of them a immediate mv. All of them
hold references to the verkaufs table. When I deleted them, the error
disapeared!

Do MV cause such kind of problems?

Tom


Reg Domaratzki [Sybase iAnywhere] schrieb:

The IMMEDIATE REFRESH materialized view is likely causing this behavior; it will
consume 2 temporary table id's each time a statement updates a table referenced
by the view.

Committing more frequently will avoid the problem. If you are able to re-form
the procedure so that it executes as a single statement rather than a loop, that
would also avoid the problem as well as improving the performance of the procedure.

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




at wrote:

Thanks for your hints! Immediate Refresh mat views are tricky anyway. I
had to remove them because of massive performance drawbacks when
updating large numbers of records. One need to consider very carefully
when and when not to use materialized views.

Tom

Bruce Hay schrieb: