Temporary tables in report

[hamzajosh]

I see that some reports use fields from temporary tables. Where can i see the SQL and structure of fields from where these temporary tables are created? I mean i want to see the structure and original source of data which feeds these temporary tables and which field is which?
for e.g. i want to see all the fields in RM_Applied_Temp table and from where these fields are pulling data and what is the mapping used between these temporary table and original table fields. Any help would be appreciated, thanks.

Learn everything but implement only what is needed.

 

[winthropdc]

Just look at the Resource information for that table under Tools >> Resource Descriptions >> Tables.

RM_Applied_TEMP is a ctree temp table, which means the actual table is created as a ctree file in your TEMP folder and is never available on the SQL server. Temp tables with a database type of SQL or Default will be created in the Tempdb on your server.

You can't see the original source tables as this depends on the code which builds the contents of that table. That code is not visible unless you are a Source Code Partner.

Looking at the source, that table is populated from RM_Applied_OPEN and RM_Applied_History.

David Musgrave
Senior Development Consultant
Asia Pacific Professional Services

Microsoft Business Solutions

http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@nospam-microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.

 

[GreatPains]

You can kinda fudge the tables together... SQL Server version...

Create the report in GP and make a DEXSQL.LOG of the transactions.

You can the edit the DEXSQL.LOG and turn the temp tables into real tables ( by removing the ## in front of the table name ). Then remove the 'DROP TABLE ##xxxxx' lines from the end of the DEX.LOG

You can then execute the DEXSQL.LOG in query analyser against a test company and it will recreate the tables for you.

Be careful however to only include the code for creating the report in the DEXSQL.LOG . You may also need to remove some of the DYNAMICS SP's so not to create locks.

As far as where the data comes from, you will have to have a scan through the DEXSQL.LOG and get a list of the source tables...

Have fun..!!

An expert is one who knows more and more about less and less until eventually he knows absolutely everything about nothing.