Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

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

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

  • Comments 9

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.

  • David,

    This answers a frequently brought up question very thoroughly - thank you very much!

    Victoria

  • Great summation of the phantom stored procedure and the DBMS error 12.

    Robert

  • Posting from the Dynamics GP Blogster

    http://dynamicsgpblogster.blogspot.com/2009/01/developing-for-dynamics-gp-weekly_24.html

  • 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

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

  • we are getting dbms: - 127 error what is it? help

    thanks

  • Hi Sukhada

    A quick search on the Web for "SQL Error 127" gives

    SQL Server Error Messages - Msg 127

    Error Message

    Server: Msg 127, Level 15, State 1, Line 1

    A TOP N value may not be negative.

    Causes

    As the message suggests, a negative value has been passed to the TOP N clause where a positive value is expected. The TOP clause specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE and DELETE statements.

  • 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 ...

  • 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

Page 1 of 1 (9 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post