working with temporary tables - JOIN

rbuser

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

nicocizik

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