Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Deleting Transactions ...
Small and medium business | Business Central, N...
Unanswered

Deleting Transactions on Hold via SQL

(0) ShareShare
ReportReport
Posted on by eBank 80

Hello,

I've been playing with a customer's database that had about 7 items on hold.  The owner changed some promotions which broke the pricing relationships and when you clicked on one of these transactions it would shut down POS.  So I started playing with a backup of his, looking at any relationships these on hold transactions may have.

1) Does it commit inventory - No

2) Does it show anything on the customer's end? - No

I couldn't think of any other transactional possibilities so I went with thinking, "this is just a single spot in the db that commits nothing and is really just there until you take it off hold and process it fully".

I deleted the transactions in SQL and went on my merry way w/o any errors on this db after doing so.

Am I wrong in assuming that which I have?

  • eBank Profile Picture
    eBank 80 on at
    RE: Deleting Transactions on Hold via SQL

    Awesome!  Thanks Todd!

  • ToddB Profile Picture
    ToddB on at
    RE: Deleting Transactions on Hold via SQL

    Hi eBank,

    The Select script provided should show all records, in the TransactionHoldEntry table, where the associated TransactionHoldID references a TransactionHold record that no longer exists.

    After validating that the associated TransactionHoldEntry records are in fact the records where the TransactionHold was deleted, you could modify this script to delete these records, and it should delete any records shown in the Select script. However, do not change any of this part of the select script "TransactionHoldEntry where TransactionHoldId not in (select id from TransactionHold)"

    NOTE: Always make a backup before running any Delete, Update, or Insert scripts.

  • eBank Profile Picture
    eBank 80 on at
    RE: Deleting Transactions on Hold via SQL

    Would this clear them out one by one, Todd?

  • ToddB Profile Picture
    ToddB on at
    RE: Deleting Transactions on Hold via SQL

    Thank you eBank for posting your question about deleting transactions on hold; and thank you Mark for recommending this be done within the UI.

    Were you also able to clean up the associated records in the TransactionHoldEntry table?

    Select * from TransactionHoldEntry where TransactionHoldId not in (select id from TransactionHold)

    _______________________________________________

    Just a note that Mainstream support for Microsoft Dynamics RMS 2.0 ended on July 10, 2016; and extended support ends on 7/13/2021:

    community.dynamics.com/.../microsoft-dynamics-rms-2-0-mainstream-support-discontinuation-july-10-2016

    support.microsoft.com/.../search

    pastedimage1606756653159v1.png

    This is less than 9 months from now; and you can continue using RMS after this date but technical support will not be available.

    I would recommend checking with your partner in regard to what they may recommend as a replacement system after the extended support end date.

  • eBank Profile Picture
    eBank 80 on at
    RE: Deleting Transactions on Hold via SQL

    If we could have done that, we would of.  He couldn't recreate the promotions because he couldn't remember them.

    I deleted the on hold transactions from dbo.TransactionHold.  I don't believe anything goes to Transaction or TransactionEntry until the sale is committed.

  • mcons Profile Picture
    mcons 390 on at
    RE: Deleting Transactions on Hold via SQL

    Depends on what tables you deleted from.  There are multiple tables that relate to each other,  the main ones are Transaction and TransactionEntry, but there are others as well.   It is best to recall those transactions and delete the items from them and complete them or save them again, which should remove them from the On Hold list, and it do it cleanly.       If your referring to work orders on hold, that is whole different set of tables.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans