How to view data in a temporary table using SQL Server Management Studio

Temporary tables are used quite often in Dynamics 365 Finance and Operations (F&O) and you can find them everywhere in the standard code and forms. There are different types of temporary tables but in this article we are using the TempDB type. The main advantage of the TempDB type is that you can join them with regular tables because they physically exist in the database.

Every time a temporary table is instantiated in F&O, an actual table is created in the SQL Server tempdb database. The name of the table is unique each time. When the temporary table buffer in F&O goes out of scope, the data in the temporary table is discarded and the table is automatically deleted at some point.

Sometimes when you are troubleshooting code that's using a temporary table, you need to view the contents of a temporary. This cannot be done easily like a regular table using the F&O table browser or SQL Server Management Studio (SSMS). So this begs the question, how do you actually view the data of a temporary table?

The answer is by using the X++ getPhysicalTableName method of the intantiated temporary table. This retrieves the physical SQL Server table name of the temporary table. The most basic method is to temporarily add a line of code using the info method to display the table name. You can then use the table name to query the database with SSMS and view the data as long as the temporary table buffer is still in scope in F&O.

Warning: don't do this in production! The above is only applicable for dev and sandbox environments. This article is for education purposes and does not explain the best possible way to get the table name for your situation. To make thing a bit more elegant, you could write the table name to a log table (e.g. SysExceptionTable) instead of displaying a message in the infolog and you could do execute the line of code for your user account only. This way users won't notice it's there.

Get the physical SQL table name in code

public class MyTmpTableTest
{
    public static void main(Args _args)
    {
        TmpRecIdFilter tmpRecId; //note: TableType must be TempDB
    
        //add some dummy records to the temp table.
        for (Counter cntr = 1; cntr < 50; cntr++)
        {
            tmpRecId.RefRecId = cntr;
            tmpRecId.insert();
        }

        //show the temp table name. this is the only line of code you need,
        //the rest of the code here is just for this test class.
        info(tmpRecId.getPhysicalTableName());

        //for this test class we use a work around to keep temp table bufffer
        //in scope so that the data can be viewed using SSMS.
        //as soon he main method exists and the temp table buffer is out of
        //scope, the data will be lost in the table.
        Box::infoOnceModal('Table name', 'Table name', tmpRecId.getPhysicalTableName(), curUserId());
    }
}

Viewing the data in SQL Server Management Studio

• Get the table name.
• Connect to the F&O database using SSMS.
• Open a new query with the table name form the first step e.g. "select * from [your_temp_table_name_here]"