Skip to main content

Notifications

Microsoft Dynamics RMS forum

Purging sales transactions in RMS version 2 and later

Posted on by Microsoft Employee

Our RMS database is getting rather large as we have been on RMS for years.  We are currently on version 2.0.

There seems to be no procedure within RMS to purge sales transactions, though I may have missed it.

I did find a procedure to purge RMS sales transactions via a SQL script, but it stated it was good up to version 1.3.

I am not crazy enough to assume that a SQL script for up to version 1.3 will work for version 2.0 and later.

Does anyone know of a method to purge sales transactions in RMS version 2?  I am hoping I have missed a KB article or some other note already published.

Any assistance would be appreciated.

 

 

 

*This post is locked for comments

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Purging sales transactions in RMS version 2 and later

    Here is a long list of queries from an old post - to clean up an old database:

    Backup database first and use carefully.

    Marc

    "Randy" wrote:

    >

    > What is the best practice for purging old transactions?  I have inherited a

    > database that has data that is ten years old

    For a complete purge of old records:

    Order Table Command

    1 RecordDeletedLog DELETE from RecordDeletedLog where WhenDeleted <

    '01/01/2000'

    2 InventoryTransferLog DELETE from InventoryTransferLog where

    DateTransferred < '01/01/2000'

    3 PurchaseOrderEntryDetail DELETE from PurchaseOrderEntryDetail where Date <

    '01/01/2000'

    4 PurchaseOrderEntry DELETE POE FROM PurchaseOrderEntry POE LEFT JOIN

    PurchaseOrder PO ON POE.PurchaseOrderID=PO.ID AND POE.StoreID=PO.StoreID

    WHERE PO.DateCreated<'01/01/2000'

    5 PurchaseOrder DELETE from PurchaseOrder where datecreated < '01/01/2000'

    6 QuoteTenderEntry DELETE QTE from QuoteTenderEntry QTE join [Order] O on

    QTE.OrderID=O.ID WHERE O.Time<'01/01/2000'

    7 OrderHistory DELETE OH FROM OrderHistory OH LEFT JOIN [Order] O ON

    (OH.OrderID = O.ID) AND (OH.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000'

    8 OrderEntry DELETE OE FROM OrderEntry OE LEFT JOIN [Order] O ON (OE.OrderID

    = O.ID) AND (OE.StoreID = O.StoreID) WHERE (O.Time)<'01/01/2000'

    9 Order DELETE from [order] where Time < '01/01/2000'

    (10-11 don't delete accountreceivable as will affect current account balances)

    12 Shipping DELETE S FROM Shipping S LEFT JOIN [Transaction] T ON

    S.TransactionNumber=T.TransactionNumber AND S.StoreID=T.StoreID WHERE

    T.Time<'01/01/2000'

    13 VoucherEntry DELETE from VoucherEntry where Date < '01/01/2000'

    14 TaxEntry DELETE TE FROM TaxEntry TE LEFT JOIN [Transaction] T ON

    TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE

    T.Time<'01/01/2000'

    15 TransactionEntry DELETE TE FROM TransactionEntry TE LEFT JOIN

    [Transaction] T ON TE.TransactionNumber=T.TransactionNumber AND

    TE.StoreID=T.StoreID WHERE T.Time<'01/01/2000'

    17 NonTenderTransaction DELETE from NonTenderTransaction where Time <

    '01/01/2000'

    18 Payment DELETE from Payment where Time < '01/01/2000'

    19 DropPayout DELETE from DropPayout where Time < '01/01/2000'

    20 TenderEntry DELETE TE FROM TenderEntry TE LEFT JOIN [Transaction] T ON

    TE.TransactionNumber=T.TransactionNumber AND TE.StoreID=T.StoreID WHERE

    T.Time<'01/01/2000'

    21 Transaction DELETE from [Transaction] where Time < '01/01/2000'

    22 OldJournal DELETE from OldJournal where ClosingTime < '01/01/2000'

    23 Journal DELETE from Journal where Time < '01/01/2000'

    24 Batch DELETE from Batch where ClosingTime < '01/01/2000'

    25 DailySales DELETE from DailySales where Date < '01/01/2000'

    26 TimeCard DELETE from TimeCard where TimeOut < '01/01/2000'

    27 TimeStampLog DELETE from TimeStampLog where ServerTime < '01/01/2000'

    28 HQRecordDeletedLog DELETE from HQRecordDeletedLog where WhenDeleted <

    '01/01/2000'

    29 Worksheet_DelGlobalCustomers DELETE W from Worksheet_DelGlobalCustomers W

    JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    30 Worksheet_GlobalAccountAdjustment DELETE W from

    Worksheet_GlobalAccountAdjustment W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    31 Worksheet_InventoryTransfer DELETE W from Worksheet_InventoryTransfer W

    JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    32 Worksheet_ItemCost DELETE W from Worksheet_ItemCost W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    33 Worksheet_ItemPrice DELETE W from Worksheet_ItemPrice W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    34 Worksheet_ItemPriceLevel DELETE W from Worksheet_ItemPriceLevel W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    35 Worksheet_ItemPriceLimit DELETE W from Worksheet_ItemPriceLimit W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    36 Worksheet_ItemQuantity DELETE W from Worksheet_ItemQuantity W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    37 Worksheet_ItemRestock DELETE W from Worksheet_ItemRestock W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    38 Worksheet_ItemTax DELETE W from Worksheet_ItemTax W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    39 Worksheet_ItemUpdate DELETE W from Worksheet_ItemUpdate W JOIN Worksheet

    on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    40 Worksheet_PurchaseOrder DELETE W from Worksheet_PurchaseOrder W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    41 Worksheet_SQL DELETE W from Worksheet_SQL W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    42 Worksheet_SupplierList DELETE W from Worksheet_SupplierList W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    43 Worksheet_SupplierUpdate DELETE W from Worksheet_SupplierUpdate W JOIN

    Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    44 WorksheetHeader_PurchaseOrder DELETE W from WorksheetHeader_PurchaseOrder

    W JOIN Worksheet on W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    45 WorksheetHistory DELETE W from WorksheetHistory W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    46 WorksheetStore DELETE W from WorksheetStore W JOIN Worksheet on

    W.WorksheetID=Worksheet.ID WHERE EfectiveDate<'01/01/2000'

    47 Worksheet DELETE from Worksheet WHERE EfectiveDate<'01/01/2000'

  • Ryan Sakry Profile Picture
    Ryan Sakry 3,425 on at
    Re: Purging sales transactions in RMS version 2 and later

    Alvin,

    The script will likely still work as there was little changes to the database between 1.3 and 2.0 especially where the transaction is concerned.  You may also wish to look into Retail Realms Archive utility that provides tools to archive a lot of old RMS data.

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

News and Announcements

Give Back to the Community this Month

Quick Links

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,802 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,133 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,154

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans