"The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions error in Dynamics GP

• "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions error in Dynamics GP
  
  https://community.dynamics.com/blogs/developingforgp/comments/16090.aspx
  
  Developing for Dynamics GP
  Developing for Dynamics GP by David Musgrave (Australia) and the Microsoft Dynamics GP Developer Support Team (USA
  
  Syndicated From: http://blogs.msdn.com/DevelopingForDynamicsGP/
  
  "The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions
  Many of inquiry windows in Microsoft Dynamics GP have been optimized to leverage SQL Server to increase performance. This is especially common with transaction inquiry windows which can combine data from more than one table (ie. Work, Open and History).
  
  The method that is usually used is based on a temporary table. This temporary table (created in the TempDB) is where the data displayed in the scrolling window of the inquiry window comes from. When the search criteria are entered and the window is redisplayed, the Dexterity code grabs the physical name for the temporary table and uses it to generate the SQL commands to insert the data from the appropriate tables. It then uses pass through SQL to run the commands and populate the temporary table. Once the temporary table is populated the data is displayed in the scrolling window.
  
  If an error occurs while the pass through SQL script is being executed it will look similar to the error messages below (using Dexterity message ID 18060):
  
  The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0.
  
  The stored procedure createSQLTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
  
  The stored procedure createTmpTable returned the following results: DBMS: 12, Microsoft Dynamics GP: 0.
  
  These error messages refer to a createSQLTmpTable or createTmpTable stored procedure. However, the stored procedures referenced do not actually exist. The message is in fact referring to pass through the SQL script called from the Dexterity code and the name is just an arbitrary string assigned by the developer.
  
  You can usually look up the DBMS (Database Management System) error codes or SQL Server error codes on the Internet and find out the exact meanings. Two common DBMS error codes seen from the pass through SQL are 2627 and 12.
  
  DBMS: 2627 refers to a duplicate key error and is covered in more detail in the article: Identifying Duplicate Transactions.
  
  DBMS: 12 is a little bit more complex as there is little or no documentation on what error code 12 really means. Error code 12 is a syntax error and is caused by the SQL script attempting to reference a resource that does not exist. The easiest way to explain the error is by describing a scenario:
  
  1. User logs into Microsoft Dynamics GP. SQL Server assigns a Server Process ID or SPID to the connection.
  
  
  2. User opens inquiry window and Dexterity creates private temporary table in TempDB associated with the current SPID.
  
  
  3. User leaves inquiry window open.
  
  
  4. Connection to SQL Server is lost. Reasons I have heard of for losing the connection include:
  
  
  o Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
  
  o Timeout settings on SQL Server, disconnecting idle sessions.
  
  o Faulty network infrastructure causing the connection to the server to be dropped.
  
  o Not installing critical updates, causing a faulty network driver to drop the connection.
  
  5. User comes back to inquiry window and starts using the window again.
  
  6. Dexterity realizes the connection is dropped and automatically re-establishes a new connection.... with a new SPID.
  
  7. The new SPID cannot "see" the temporary table created when the window was opened and so when the pass through SQL script executes and uses the name of the temporary table it expects to be there, an error 12 is generated.
  
  Most people find that when they close the window or the application and try again that it works fine. Which is exactly what we would expect:
  
  · Restarting the application will create a new SPID and the temporary table will be created associated with that SPID and all is fine.
  
  · Closing and re-opening the window will create a new temporary table associated with the current SPID and all is fine.
  
  This explains why the error is so hard to reproduce and why no obvious causes can be found.
  
  The Knowledge Base (KB) Article below discusses a similar error and how stopping and restarting the SQL Server service will fix the error and clean-up the TempDB:
  
  Error message when you double-click the "Journal Entry" field in the Detail Inquiry window in General Ledger in Microsoft Dynamics GP: "Stored Procedure Creating SQL TMP Table DBMS: 12 eEnterprise: 0" (KB 852594)
  
  Please let me know if you find this information useful.
  
  
  ALSO FOUND ON THIS ISSUE:
  
  http://groups.google.com/group/microsoft.public.greatplains/msg/fc7ce61f9d965c8e
  
  
  The two most memorable cases when it took some time to find the cause for
  this:
  1. User's network cable was cramped between the wall and a filing cabinet
  almost cutting it in half by the time we found it. Amazingly, while she was
  getting other intermittent errors, the biggest issue was GP. However, this
  was also someone who is in GP all day every day entering a lot of
  transactions.
  
  
  2. Customer had ordered new computers for the entire accounting department
  and they all came with the network card set to automatically go to sleep
  after 15 minutes of inactivity.