Errors from Dynamics GP Local Temp Tables

This week I received an e-mail from a client regarding an error they received when posting SOP Invoice batches.

A hundred batches a day post fine, but recently, two batches have failed with the following errors:

An open operation on table 'IV_Cost_Variance_TEMP' failed because the path does not exist.

An open operation on table 'IV_Cost_Variance_TEMP' has an incorrect record length.

My first thought was that maybe there is an issue with a third party module or GP version where GP is somehow trying to write data to a temp table that has a slightly different schema.  But this is a vanilla GP install with the proper service pack.

So today I Googled the error, and I found some puzzling blog articles that mentioned C-Tree files.  When I saw dates of 2004, I thought okay, those are just old.  But then I saw more from 2009 and 2010, still mentioning C-Tree files.

In a comment posted in 2010 to one of his 2009 blog posts, David Musgrave directs someone with a similar error to these two Microsoft blog posts:

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/10/an-open-operation-on-table-temp-table-errors.aspx

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2010/04/21/unexplained-temp-table-errors.aspx

As David explains in the first post, "Not all temporary tables used by Microsoft Dynamics GP are SQL tables created in the tempdb system database.  Many temporary tables used for quick processing in windows use local ctree temporary tables."

Say what???  This may be common knowledge for a lot of folks, but I naively thought that GP had been fully transitioned over to SQL Server.  Apparently there are still some vestigial pieces of code that rely on local file system temp tables.  I would have thought that the MCP at Microsoft would have squashed those rogue programs, but apparently they are still riding their Light Cycles around in the GP code.  Naturally, there are probably some performance benefits to being able to perform temp table operations locally, but I am hoping that there are plans to wean GP off of the C-Tree implementation of those tables using the file system.

As the second article by Allan Cahill explains, they found that a local anti-virus application was taking just enough time to scan the temp files that it was locking them, preventing GP from deleting them.  Excluding those files from the scan seemed to resolve the issue.

I have forwarded this information to my client, and I don't yet know whether this is the cause of my client's errors, but I found this pretty interesting.

So keep those Temp folders nice and clean!  (feels like advice for a Windows 3.1 user...)