Freigeben über

"The stored procedure createSQLTmpTable returned the following results: DBMS: 12" exceptions

  • Artikel

David MeegoMany 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) Secure Link

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) Secure Link

David

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

Comments

  • Anonymous
    January 19, 2009
    David, This answers a frequently brought up question very thoroughly - thank you very much! Victoria

  • Anonymous
    January 20, 2009
    Great summation of the phantom stored procedure and the DBMS error 12. Robert

  • Anonymous
    January 28, 2009
    Posting from the Dynamics GP Blogster http://dynamicsgpblogster.blogspot.com/2009/01/developing-for-dynamics-gp-weekly_24.html

  • Anonymous
    November 13, 2009
    Thanks David, great write up on this.  Had a client running into this sporadic error.  Found they were leaving their system up while out a lunch and getting the errors when they returned.   Also, I really enjoyed your and Mariano's sessions at the Developers conference this week. Grady

  • Anonymous
    May 07, 2012
    We have received a DBMS: -127 error messages several times when trying to run the purchasing report "pmHistoricalAgedTrialBalance". Any ideas for this one?

  • Anonymous
    September 30, 2013
    we are getting dbms: - 127 error what is it? help thanks

  • Anonymous
    September 30, 2013
    The comment has been removed

  • Anonymous
    October 29, 2013
    Hello, Having the same issue in GP 2013 SP2.  As automated fixes aren't available for GP 2013 (and Fixit 50406 doesn't want to work), will try this shortly. Re-starting the service, and server did not help. Time to comb thru the DB ...

  • Anonymous
    October 30, 2013
    Hi Parminder Also have a look at the blog post below as you can get error 12 from unexpected disconnections cause by the TCP Chimney issue: blogs.msdn.com/.../why-does-smartlist-not-return-all-of-the-expected-data-for-large-queries.aspx David