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

This question is marked "community wiki".

asked 01 Aug '12, 10:08

Pavlun's gravatar image

Pavlun
1114
accept rate: 0%

edited 01 Aug '12, 10:20

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.

(01 Aug '12, 10:32) Breck Carter

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

(01 Aug '12, 10:45) Pavlun
1

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!

(02 Aug '12, 14:26) Breck Carter

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

(13 Sep '12, 02:13) Pavlun
Replies hidden
1

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;
(13 Sep '12, 15:20) Jeff Albion

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.

permanent link

answered 09 Aug '12, 07:58

Hartmut%20Branz's gravatar image

Hartmut Branz
37629
accept rate: 0%

Comment Text Removed

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;

permanent link

answered 18 Oct '12, 10:45

obredy's gravatar image

obredy
31113
accept rate: 0%

Your answer
toggle preview

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

Question tags:

×19

question asked: 01 Aug '12, 10:08

question was seen: 2,173 times

last updated: 18 Oct '12, 10:45