"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:
    
   • Workstations set to Stand By, Sleep or Hibernate when not used for a period of time.
   • Timeout settings on SQL Server, disconnecting idle sessions.
   • Faulty network infrastructure causing the connection to the server to be dropped.
   • 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, it can now be found in the following KB article:

Error message when you select a vendor in the Payables Transaction Inquiry window in Microsoft Dynamics GP: "The stored procedure createSQLTmpTable returned the following results, DMBS: 2627, Microsoft Dynamics GP: 0" (KB 961576)

David

10-Feb-2009: Added link to KB 961576.