Stored procedure cannot read temp table

Question

I'm using an ODBC dsn for Informix database to be used in BDC.  When I tried to execute procedure it's run well for normal stored procedure, but when I tried to use a temporary table in the stored procedure, the error of [42S02] came out; the specified() table is not in the database.   This thing also happen when I tried from Visual Basic.  

This stored procedure is working well in Informix Engine, I've tested it!   Is there any limitation or contraint or did I miss something? Pls help.

---

application developer

Answer 1

Hi,

I'm assuming that the stored procedure is both creating and consuming the temp table in the same.  Since it seems to work fine when you call the it directly (presumably from a query window), and it also does not work when calling from a VB app, my guess is there is something with the ODBC driver or with how the procedure is being called through the driver.  You might look to see if there is an updated driver.  You might also try to post for assistance on an Informix forum (if you can find one).

---

Randy - http://sharepointhawaii.com/randywilliams

Answer 2

Hi Randy,

I did upgrade my ODBC driver to the latest version, but I still got the same error.   Any other idea?

---

application developer

Answer 3

I'm having the same problem

This works within SQLServer 2005

SELECT * INTO #temp FROM TblObject
    SELECT * FROM #temp

When called from an access mdb this works.
When called from an access project this fails.

But when I change the stored procedure to:

SELECT * FROM TblObject

It works.