Question Re: Table not found in Technology Q&A

Table not found

Former Member — Mon, 19 Sep 2011 12:34:12 GMT

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!

Re: Table not found

VolkerBarth — Mon, 19 Sep 2011 13:08:06 GMT

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

Re: Table not found

Former Member — Mon, 19 Sep 2011 13:36:16 GMT

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

Re: Table not found

jeff_albion — Mon, 19 Sep 2011 14:07:37 GMT

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?

Re: Table not found

Former Member — Mon, 19 Sep 2011 14:31:09 GMT

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

Re: Table not found

Former Member — Mon, 19 Sep 2011 15:37:02 GMT

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.

Re: Table not found

VolkerBarth — Mon, 19 Sep 2011 15:41:45 GMT

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?

Re: Table not found

Former Member — Tue, 20 Sep 2011 08:09:18 GMT

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?

Re: Table not found

Former Member — Wed, 21 Sep 2011 16:06:56 GMT

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.