Ask a questionAsk a question
 

QuestionStored procedure cannot read temp table

  • Monday, December 01, 2008 5:41 AMNorani Yaakub Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    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

All Replies

  • Monday, December 01, 2008 6:03 AMRandy.WilliamsMVPUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    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
  • Monday, December 01, 2008 9:44 AMNorani Yaakub Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Hi Randy,

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


    application developer
  • Friday, October 30, 2009 9:26 AMSjuul Janssen Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    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.