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

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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)


This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

11 thoughts on ““The stored procedure createSQLTmpTable returned the following results: DBMS: 12” exceptions

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


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


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


  4. 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 …


Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.