SA9 to SA16 Migration issue: temporary table error

[Former Member]

We are migrating to SA16 from SA9. Some of our stored procedures have temporary tables created as:

CREATE PROCEDURE proc1(IN var1 varchar(10)) BEGIN CREATE TABLE #TEMP (field1 varchar(10)); ...... END

Please note that there is no explicit DROP TABLE #TEMP in these stored procedures.

Now there are several other stored procedures with the same temporary table name in the same db:

CREATE PROCEDURE proc2(IN var2 varchar(20)) BEGIN CREATE TABLE #TEMP (field2 varchar(20)); .... END

After SA16 migration these stored procedures are throwing an error after they are called in a sequence from the application.

"Database error code: -1397 Select Error: SQLSTATE = S1000 [Sybase][ODBC Driver][SQL Anywhere]The definition of temporary table '#TEMP' has changed since last used."

I understand that we need to explicitly DROP the tempory table or use DECLARE to create temporary tables as the scope of these tables is within the stored procedure only. My question is this actually worked in SA9 without any error.

There are hundreds of such stored procedures and this is preventing us to migrate to a new version. Adding explicit DROP temporary table in these procedures is a massive exercise.

[Breck_Carter]

Try this, it might help:

SET OPTION PUBLIC.MAX_PLANS_CACHED = '0';

[Former Member]

Thanks for your response Breck.

We however need to cache the plans otherwise performance on SA16 will be a major problem.

[Former Member]

Even SET OPTION PUBLIC.MAX_PLANS_CACHED = '0'; doesn't work.

The scenario is: There are 2 stored procedures with same named temporary tables. These temporary table are referenced from a function also. Both these stored procedures call this function. Even if I explicitly DROP these temporary tables from stored procedures it still doesn't work, probably because the temporary table is referenced from this function.

I guess the SP cache has nothing to do with this. This could be a ASA bug introduced in version 12.0.

To confirm this if I rename the function and call it from one of the stored procedures it works fine without any error.

[VolkerBarth]

A very wild guess: Possibly a different option "max_client_statements_cached" may be of influence here. Cf. the docs and note, it has been introduced with v10.0.1.

So I'd suggest to try to set that option to 0.

[Former Member]

Thanks for your input Volker. I tried setting max_client_statements_cached to 0 and confirmed that the value was set on the db. However this didn't work either. I am still getting the same error after this change.

[justin_willey]

We've come across the same problem -it's only intermittent and doesn't effect every usage of select ... into #temptable from ... Currently trying a workaround not using #temptable, but its a bit worrying as we have that construct in a lot of places.

Did you ever get to the bottom of the problem?

This is a v11.0.1 format db running under the latest 16.0.0 ebf.

[justin_willey]

Using DECLARE TEMPORARY TABLE does solve the problem. In our scenario there were two potential risky aspects to our usage - a) the procedure could be called multiple times simultaneously b) sometimes values returned for some columns would be null (making it rather hard for SQLA to work out a type).

Give that SELECT ... INTO #TEMPTABLE is working fine elsewhere where b) doesn't apply - I wonder if that is the problem. At some point between 11.0.1 and 16.0.0 has SQLA become less tolerant of having the data type changed, or has got better at caching the structure of # temp tables? I can't see anything in the docs, but a rewriting of some code might have introduced a more explicit treatment.

Explicitly casting every member of the select list might work as well if I'm right. However with a hundred or so columns I'm not sure I'm going to test it!