Temp table problem in ASA 12 . SQLE_TEMP_TABLE_DEFINITION_CHANGED

[Former Member]

Hi / I got this error after moving to asa 12 from asa 10 . Recompile of all procedure did not help . Store procedure just failing from time to time ( in 20 % cases on the different data ) / Can anybody help ? Error reference suggest to recompile procedures . It dos not help . Thanks

[Breck_Carter]

I've never seen that message, so this is just guesswork: At runtime, the definition of some temporary table used inside a procedure has changed between one call to that procedure and the next, it such a way that the procedure can't run. Recompiling doesn't work because it is a dynamic problem, occurring or not occurring at runtime depending on which path is taken through the SQL code.

Please show us the the code for the stored procedure, and try to locate the exact statement that is raising the error... that's a starting point.

[Former Member]

Something like that Create Table #SSpec(....)

Exec SP1 ......
Select From #SSpec Inner join ........ .....

Exec SP2 .....

Update #SSpec

.....

Exec SP3 .....................

    Create Table #SSpec(....)

    Exec SP1
    Exec SP3

Sometimes it fall on update ? sometimes on select . Failure occurs sometimes ( 20% smthing). Failure began when we moved from asa 10 to 12 . It happens in dozens of procedures that use Temporary tables . And it was never happend in ASA 10 . We made standart transfer of ASA 10 source to ASA 12 by internal SYbase Transformer . We commonly using temp tables for all multiString operations . Create Table / Insert into this table choosen list of positions and make different things on it ( orders ? invoices etc ) / It was standart coding in our company for more then decade i think ////

[Breck_Carter]

Please show us ALL the code, not SOME of the code.

We understand that you are frustrated, but repeating that is just a waste of YOUR time... it is not going to get you an answer any faster at all, and since this is a VOLUNTEER forum, some folks might not react too well to being yelled at.

Please trust me when I say (1) your error message is very rare (try Google), and (2) temporary tables work OK in version 12. There may be some behavior change between Version 10 and Version 12 that is affecting your stored procedures, but if you don't want to show us the code then it's hard for anyone to help.

Maybe you can help yourself, by reading all the "behavior change" sections in the V12 Help, from 10.0.1 through 12.0.1...

http://dcx.sybase.com/index.html#1201/en/sachanges/new1001-s-3634293.html

http://dcx.sybase.com/index.html#1201/en/sachanges/newpanorama-sectb-3789082.html

http://dcx.sybase.com/index.html#1201/en/sachanges/new1101-s-3634293.html

http://dcx.sybase.com/index.html#1201/en/sachanges/newinnsbruck-sectb-3789082.html

http://dcx.sybase.com/index.html#1201/en/sachanges/new1201-sectb-3789082.html

Good luck!

[Former Member]

We have some different case --first we create procedure using temp table

Create Proc dbo.Test_TempTable as Insert Into #sSpec(ID, tt) Select 1,'Test'
go      -- then cod always make an error

Create Table #sSpec(ID Integer, tt VarChar(20),p bit Null) 
Exec dbo.Test_TempTable 
Select * From #sSpec
Drop Table #sSpec

-- then we define some different structure table with the same name
Create Table #sSpec(ID Integer, tt VarChar(20), p Integer Null) 
Exec dbo.Test_TempTable -- Error occurs in second procedure call 
Select * From #sSpec 
Drop Table #sSpec 
-- the same code in Asa-10 did't make any problem

Sorry for Ugly formatting

Please give us some advice !!!!

[jeff_albion]

Pavlun,

I've edited your answer to add formatting to it. (Tip: SQL code should be indented by 4 spaces and it will "auto-format").

As per Hartmut's answer, try this re-write of the stored procedure.

(Note: 'EXECUTE IMMEDIATE WITH RESULT SET ON' is a Watcom SQL statement and isn't available in T-SQL - I've re-written the procedure in Watcom SQL below):

create or replace proc dbo.Test_TempTable()
no result set
begin
   execute immediate with result set on 'Insert Into #sSpec(ID, tt) Select 1,''Test''';
end;

[Former Member]

If you use a temporary table inside a procedure and there is a chance that the table definition is different in each procedure call, then you must use the temporary table with execute immediate statements only (e.g EXECUTE IMMEDIATE WITH RESULT SET ON "select * from tmp2"). All procedure statements except "execute immediates" are parsed and annotated when they are used the first time. So a "select * from tab2" has a fixed parse tree after the 1st run and if the table definition is different during the next procedure call you see the error. Hope this helps.

[Former Member]

I add this post only because of the error code. We are migrating from SQLA7 to 12 and get the same error now when calling a stored procedure twice. The second run causes the error. Imagine the following: 1. Create some data into #temptable1_first 2. Now group this data by "select col1,sum(col2) as col2 into #temptable1 from #temptable1_first group by col1"

The problem here is that the column types of col1 and col2 are not well-defined (although #temptable1_first has data in both calls). To avoid this problem you have to make sure that the column types are known before grouping the data:

Select cast(null as integer) as col1, cast(null as integer) as col2 into #temptable1; delete from #temptable1; insert into #temptable1 select... from #temptable1_first;