I have been asked on several occasions if it is possible to delete the Sage 500 ERP Demo Company Data after it has been created. The answer is yes. It is nice to have it there for initial system review and training, but after you get up and running it is also nice to make it go away.
Well here are the basic steps for you to follow. It goes without saying but I feel it necessary to state the obvious anyway.
First and most importantly, MAKE A BACKUP OF YOUR CURRENT Sage 500 ERP (formerly MAS 500) DATABASE! In fact it would not be a bad thing to verify that you can restore this database to another system and gain access into it before proceeding with this process in your production system. You should also verify these steps in a TEST instance as well.
Second you need to delete some information in SQL Server using the following SQL Script. This information is related to intra-company transactions.
<span style="color: #606060" id="lnum1"> 1:</span> <span style="color: #008000">/*</span>
<span style="color: #606060" id="lnum2"> 2:</span> <span style="color: #008000"></span>
<span style="color: #606060" id="lnum3"> 3:</span> <span style="color: #008000">Run this in Query Analyzer on the Sage 500 ERP database to remove intercompany references prior to deleting the sample companies</span>
<span style="color: #606060" id="lnum4"> 4:</span> <span style="color: #008000"></span>
<span style="color: #606060" id="lnum5"> 5:</span> <span style="color: #008000">DISCLAIMER: This code contains no error checking or validation. </span>
<span style="color: #606060" id="lnum6"> 6:</span> <span style="color: #008000"> It contains no warranties. </span>
<span style="color: #606060" id="lnum7"> 7:</span> <span style="color: #008000"> Use at your own risk!</span>
<span style="color: #606060" id="lnum8"> 8:</span> <span style="color: #008000"></span>
<span style="color: #606060" id="lnum9"> 9:</span> <span style="color: #008000"></span>
<span style="color: #606060" id="lnum10"> 10:</span> <span style="color: #008000">*/</span>
<span style="color: #606060" id="lnum11"> 11:</span>
12: Delete tgltargetcomp where targetcompanyid = 'SGE'
<span style="color: #606060" id="lnum13"> 13:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum14"> 14:</span> <span style="color: #0000ff">Delete</span> tgltargetcomp <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'SLS'</span>
<span style="color: #606060" id="lnum15"> 15:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum16"> 16:</span> <span style="color: #0000ff">Delete</span> tgltargetcomp <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum17"> 17:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum18"> 18:</span> <span style="color: #0000ff">Delete</span> tgljournaldetl <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum19"> 19:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum20"> 20:</span> <span style="color: #0000ff">Delete</span> tglRecurJrnlDetl <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum21"> 21:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum22"> 22:</span> <span style="color: #0000ff">Delete</span> tglAllocateDetl <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum23"> 23:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum24"> 24:</span> <span style="color: #0000ff">Delete</span> taprecurvouchdetl <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum25"> 25:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum26"> 26:</span> <span style="color: #0000ff">Delete</span> tpoReceiver <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'COA'</span> <span style="color: #0000ff">and</span> purchcompanyid = <span style="color: #006080">'SOA'</span>
27: Go
<span style="color: #606060" id="lnum28"> 28:</span> <span style="color: #0000ff">delete</span> tporcvrline <span style="color: #0000ff">where</span> polinekey <span style="color: #0000ff">IN</span> (<span style="color: #0000ff">select</span> polinekey <span style="color: #0000ff">from</span> tpoPOLine <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span>)
<span style="color: #606060" id="lnum29"> 29:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum30"> 30:</span> <span style="color: #0000ff">delete</span> tmfWOShipments_HAI <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum31"> 31:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum32"> 32:</span> <span style="color: #0000ff">delete</span> tpoPOLine <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'COA'</span> <span style="color: #0000ff">and</span> polinekey <span style="color: #0000ff">not</span> <span style="color: #0000ff">in</span> (<span style="color: #0000ff">select</span> d.polinekey <span style="color: #0000ff">from</span> tpoPOlineDist d, tmfwoshipments_HAI w <span style="color: #0000ff">where</span> d.polinedistkey = w.polinedistkey)
<span style="color: #606060" id="lnum33"> 33:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum34"> 34:</span> <span style="color: #0000ff">delete</span> tporeceiver <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum35"> 35:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum36"> 36:</span> <span style="color: #0000ff">delete</span> tpopurchorder <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum37"> 37:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum38"> 38:</span> <span style="color: #0000ff">delete</span> tpopurchorderlog <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'COA'</span>
<span style="color: #606060" id="lnum39"> 39:</span> <span style="color: #0000ff">Go</span>
40: delete tporeceiver where pokey in
(select pokey from tpoPurchOrder where companyid = 'SOA')
<span style="color: #606060" id="lnum41"> 41:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum42"> 42:</span> <span style="color: #0000ff">delete</span> tporcvrline <span style="color: #0000ff">where</span> polinekey <span style="color: #0000ff">IN</span> (<span style="color: #0000ff">select</span> polinekey <span style="color: #0000ff">from</span> tpoPOLine <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'SOA'</span>)
<span style="color: #606060" id="lnum43"> 43:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum44"> 44:</span> <span style="color: #0000ff">update</span> tsoSOLine <span style="color: #0000ff">set</span> polinekey = <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">where</span> polinekey <span style="color: #0000ff">IN</span>(<span style="color: #0000ff">select</span> polinekey <span style="color: #0000ff">from</span> tpoPOLine <span style="color: #0000ff">where</span> targetcompanyid = <span style="color: #006080">'SOA'</span>)
<span style="color: #606060" id="lnum45"> 45:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum46"> 46:</span> <span style="color: #0000ff">delete</span> tpoPendreceiver <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'SOA'</span>
<span style="color: #606060" id="lnum47"> 47:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum48"> 48:</span> <span style="color: #0000ff">delete</span> tpochngOrder <span style="color: #0000ff">where</span> pokey <span style="color: #0000ff">in</span> (<span style="color: #0000ff">select</span> pokey <span style="color: #0000ff">from</span> tpoPurchOrder <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'SOA'</span>)
<span style="color: #606060" id="lnum49"> 49:</span> <span style="color: #0000ff">Go</span>
<span style="color: #606060" id="lnum50"> 50:</span> <span style="color: #0000ff">delete</span> tpopurchorder <span style="color: #0000ff">where</span> companyid = <span style="color: #006080">'SOA'</span> <span style="color: #0000ff">and</span> pokey <span style="color: #0000ff">not</span> <span style="color: #0000ff">in</span> (<span style="color: #0000ff">select</span> pokey <span style="color: #0000ff">from</span> tpochngorder) <span style="color: #0000ff">and</span> pokey <span style="color: #0000ff">not</span> <span style="color: #0000ff">in</span> (<span style="color: #0000ff">select</span> l.pokey <span style="color: #0000ff">from</span> tpoPOLine l, tporeqline r <span style="color: #0000ff">where</span> l.polinekey = r.polinekey)
<span style="color: #606060" id="lnum51"> 51:</span> <span style="color: #0000ff">Go</span>
52: delete tpoPurchOrder where tranno = '0000000200' >and companyid = 'SOA'
<span style="color: #606060" id="lnum53"> 53:</span> <span style="color: #0000ff">GO</span>
<span style="color: #606060" id="lnum54"> 54:</span>
<span style="color: #606060" id="lnum55"> 55:</span> --Proceed <span style="color: #0000ff">in</span> MAS500 <span style="color: #0000ff">to</span> Company Maintenance <span style="color: #0000ff">and</span> <span style="color: #0000ff">delete</span> the companies <span style="color: #0000ff">in</span> the following <span style="color: #0000ff">order</span>:
<span style="color: #606060" id="lnum56"> 56:</span> --CAD
<span style="color: #606060" id="lnum57"> 57:</span> --SLS
<span style="color: #606060" id="lnum58"> 58:</span> --SGE
<span style="color: #606060" id="lnum59"> 59:</span> --DKS
<span style="color: #606060" id="lnum60"> 60:</span> --CON
<span style="color: #606060" id="lnum61"> 61:</span> --COA
<span style="color: #606060" id="lnum62"> 62:</span> --SOA
Third you now go into Sage 500 ERP Company Maintenance and you can delete the Sage 500 ERP Demo Data companies one at a time. I like to delete them in the following order.
- CAD
- SLS
- SGE
- DKS
- CON
- COA
- SOA
