Hi,

I've upgraded to v12 and am encountering a strange problem.

I run a watcom sp which loads about 30 text files into local temp tables and processes the data before adding it to the database. All temp tables are declared "not transactional".

I'm now getting random "Table '%1' not found" errors at random places in the code. The procedure hasn't been changed. It does call an external DLL but it's not failing at this point, only on a select into the temp.

I'm at a loss!

TC

asked 19 Sep '11, 12:34

TimC's gravatar image

TimC
1515610
accept rate: 0%

edited 15 Mar '13, 22:07

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273

From what version are you upgrading?

IIRC, in v9 there was a change w.r.t. local temporary tables: Before that, they could be declared with an owner name, so it was possible to declare local temporary names with the same table name (but a different owner name). Might this be an issue here?

Though according to the v12 docs that would imply a failure already in the upgrade process...

(19 Sep '11, 13:08) Volker Barth

Thanks for you answer.

V10 to 12. No owner names on temp tables. No temp tables duplicate main table names.

Code has always worked before

(19 Sep '11, 13:36) TimC

Is it possible that you have declared the temporary tables in a scope-specific way that you were not aware of previously? (e.g. CREATE LOCAL TEMPORARY TABLE vs. DECLARE LOCAL TEMPORARY TABLE )?

Can you post a code sample of your stored procedure as a new comment to this question?

(19 Sep '11, 14:07) Jeff Albion

What specific version and build of 12.x are you running? (SELECT @@version)

(19 Sep '11, 14:31) Glenn Paulley

Running Version 12.0.1.3436 which is the latest EBF. Using DECLARE LOCAL TEMPORARY TABLE. The sp has not changed.

It seems to fail at various places but occasionaly runs perfectly. Most recently failed on this statement: "delete from zt_temp;"

zt_temp had been referenced in various inserts and updates previously in the sp.

I've tried running this maually and using an event but both fail.

(19 Sep '11, 15:37) TimC
Replies hidden

Can you try to put MESSAGE statements (or separate error handlers) in the SP to have more control on where or when the control flow stops?

(19 Sep '11, 15:41) Volker Barth
showing 5 of 6 show all flat view

There were some recent changes to made to the logic that encompasses stored procedure execution in order to correct a defect related to the inlining of SELECT statements within procedures and SQL user-defined functions. It is possible that your issue may be related, but we'll need more information (preferably a repro, which it sounds like you have) in order to diagnose the problem.

Do you have a support plan?

permanent link

answered 20 Sep '11, 08:09

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Sorry for the late reply but have been doing a lot of testing.

I've been running the routine every 30 mins using an event and finding that it fails 2 times out of 6 on accessing different local temp tables.

I've also found that if I substitute an external CLR DLL which loads a file the fault clears.

Neither the DLL, or the call to the DLL is failing; it seems to fail randomly thoughout the SP but only when accessing a temp tabel.

We don't have a support plan.

(21 Sep '11, 16:06) TimC
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:

×412
×85
×20

question asked: 19 Sep '11, 12:34

question was seen: 2,743 times

last updated: 15 Mar '13, 22:07