Too many temporary tables in connection error

Our customer has an event and a stored procedure sp1 which has only 2 temporary tables declared (declare local temorary table...). After running for some hours it throws an error: Too many temporary tables in connection (SQLCODE -817).
The procedure sp1 calls another procedure sp2 many times (inside a cursor in cursor) and sp2 has 1 temp. table declared itself, but I doubt it can be the reason. From the docs:

Declared local temporary tables within compound statements exist within the compound statement.

There are no triggers in the table which is updated in the procedure.
What reasons may cause this error? What should we check/consider? Thanks.

Version: 11.0.1.2467.

temporary-table sa-11 error

asked 02 Apr '14, 09:36

Arthoor
1.3k●35●52●66
accept rate: 11%

edited 02 Apr '14, 10:02

flat view

One Answer:

active answers oldest answers newest answers popular answers

There is a upper limit of 1,048,576 temporary tables that can be created per transaction. Dropping a temporary table will not allow you to create more than this number. Temporary tables in a transaction are assigned auto-incrementing IDs and currently the server cannot go back and 're-use' IDs of dropped temporary tables.

Is there a place where you could execute a COMMIT or reuse the same temporary table? For example, after each iteration of the cursor you could clear the temporary table and reuse it for the next iteration.

Edit: This is enhanced in version 17. The server can now go back and re-use IDs of dropped temporary tables.

permanent link

answered 02 Apr '14, 11:13

Mikel Rychliski
2.1k●16●41
accept rate: 32%

edited 22 Jul '15, 10:18

Time to switch from DEFAULT AUTOINCREMENT to a SEQUENCE ... CYCLE in the SA server itself?

(02 Apr '14, 11:30) Volker Barth

Thank You for the explanation. The upper limit is too small in this case. :)
Well, we'll try to find a workaround. The last pick (if commits won't be applicable) will be to move temp. table declaration from sp2 to sp1 and clear it everytime in sp2.

(03 Apr '14, 01:06) Arthoor

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-11 ×143
error ×93
temporary-table ×26

question asked: 02 Apr '14, 09:36

question was seen: 3,008 times

last updated: 22 Jul '15, 10:18

SAP SQL Anywhere

Too many temporary tables in connection error

Follow this question

Related questions