Home DADE

working with temporary tables - JOIN

edited August 2006 in DADE
Hi,
I made change to daADO.pas modified the method,
TdaADOSession.GetTableNames to add a list of temp tables.

In Data tab of the report it is working with just the temporary table,
alone. Whenever I tried to add a join table (double click anther table), I
got an error 'Invalid object name '#tmpXXX' in module TdaMetaData, procedure
GetFieldsFromDataSet. I am designing the report with RAP.

I know the RBuilder is trying to get all the fields from temporary table,
so that join can have a list of fields. But is there any way to make the
join work?

Thanks

Bin

Comments

  • edited August 2006
    Hi Bin,

    I can think of two items that may be causing this behavior.

    1. If you look at the TdaMetaData.GetFieldsFromDataSet procedure, the error
    is probably occuring after the lDataSet.Active := True; line is called. You
    can see that the lDataset.DataName is set before this and if you take a look
    at the TdaADODataset.SetDataName procedure in daADO.pas you can see the SQL
    that is causing the error. I am unsure what changes you made to the
    GetTableNames routine but you may need to make some changes to this one as
    well. I understand all temp tables in SQL Server are stored in the tempdb
    system database. Could this be what's causing a problem?

    2. It is my understanding that local SQL Server temporary tables are
    dropped once the initial connection is closed. Since these tables should be
    accessable from your SQL Server database, your temp tables might be getting
    dropped before you have finished using them. Try changing them to global
    temporary tables (by using two hash marks at the beginning of the table name
    "##TempTable").



    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.