SQL Temp Table Opening As Ctree

Article
07/16/2010

An ISV opened a support incident with me as one of his customers was running across an odd issue with his application. None of his existing customers with the same application were experiencing this issue.

In his application, a SQL temp table was opened and filled via a stored procedure call using code similar to:

Code Example

local long l_ReturnCode, l_ErrorCode;
local string sTableName;

sTableName = Table_GetOSName(table myTempTable);

call sproc "coFillSQLTempTable",
    l_ReturnCode,
    sTableName,
    param1,
    param2,
    l_ErrorCode;

For this customer, an invalid syntax error was being thrown from SQL.

Looking in the Dexsql.log generated, the reason for the error was apparent. Instead of getting the familar name of ##123456 for the temp table name, the name sent to the stored procedure was of the form:

:C:Users/patrick/Appdata/local/temp/TNT7129

Interesting, that is the format that we'd expect a Ctree temp table to use.

We looked at the development dictionary and indeed the Database Type in the Table Definition window was set to SQL.

I speculated that perhaps Dexterity was having trouble generating the SQL to create the table as SQL and instead used the Ctree format. Or perhaps there was a permissions problem on the tempdb that wouldn't allow the table to be created.

Even as I thought of this, I dismissed it. Dexterity wouldn't "fall back" to Ctree if there was an issue creating the SQL temp table, it would throw an error immediately.

After a bit more investigation, we noted that there was a forms dictionary for his application. After renaming the dictionary, the temp table was properly created as SQL.

Knowing the solution but not the reason, I was curious about the forms dictionary. We opened the forms dictionary with Dexterity to inspect the table definition (You knew you could do that right?). While this would likely "damage the dictionary" to make it not useable/reliable, it did prove invaluable in finding the reason this was happening.

In the forms dictionary, the Database Type was set as "Ctree".

So why would it be Ctree and not SQL as the development dictionary shows?

I didn't have the history of the application but I can take a good guess.

Originally the table was a Ctree temp table.

The customer created a forms dictionary for this application which makes a copy of all the resources in that dictionary and puts them in the forms dictionary. So the table definition in the forms dictionary is now set to Ctree.

At some point, perhaps this last update but maybe earlier in the application history, the table was switched to SQL and the code changed to leverage SQL stored procedures.

The new cnk was added on the customer site. The forms dictionary wasn't updated during auto chunk - I suspect the reason is because the main dictionary was deleted after adding the new cnk file. Doing that causes the forms dictionary to not be updated as the new cnk file unchunks itself.

When Dexterity accesses a resource, it pulls it from the forms or reports dictionary first. If it is found, then that version is used. So in this case, the version found was the version that was set to Ctree and that was how it was created by the runtime.

We took the easy route then of solving this by first exporting the modified forms, then renaming the forms dictionary, and lastly importing the modified forms in again.

After doing so, the new table was correctly being created in SQL and the application functioned correctly.

Best Regards,

Patrick Roth
Microsoft GP Developer Support

[Edit] Have a look at the Why won't my Dexterity Application Dictionary update? post as this was also caused by a custom forms dictionary with an old table definition.

SQL Temp Table Opening As Ctree

Additional resources