Friday, December 19, 2014

Dynamics GP - Payables Transaction Inquiry error "The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Problem:
When opening a transaction inquiry window to view transactions for a vendor, you get the following error for some vendors.
"The stored procedure createSQLTmpTable returned the following results: DBMS: 2627, Microsoft Dynamics GP: 0"

Cause:
This is caused by a record existing in both the Work AND History Tables.
This generally happens when a batch gets stuck, or half-posts and has to be recovered using the script to force-recover it. Some transactions may post to history, but not get cleaned out of the Work Table.

Solution:
Run this


select *
--delete
from pm20000
where
VENDORID in ('MyAffectedVendorID') and
DOCNUMBR in (
select DOCNUMBR  from pm30200)

2 comments:

  1. Hi, our finance team is running into this problem and i found this solution online. However, I am not too sure how it works, do I run this command on the database server?

    Thank!
    Eric

    ReplyDelete
    Replies
    1. Hi Ngok. Yes, you have to log into the SQL server, click on query, select your company database, paste this code in and run it. Change the "MyAffectedVendorID to your Vendor ID that is having the problem.

      Delete