Login / Register  search  syndication  about

SalesLogix Support

SalesLogix Cloud / Web / LAN Support Topics from Mark Duthoy

A Script to Clear Out a SalesLogix SQL Database

There may be a time that you want to upload a database but would prefer not to share all of your data.  This script will leave all of the SalesLogix customizations intact but will remove all but 500 accounts.  There is a section at the end of this script where you can add your custom tables.  I have seen this script run for more then 2 hours so plan to wait quite a while.

This script is designed for SalesLogix prior to version 7.2.2 - proceed at your own risk.

--Custom Tables to be cleared need to be added at the bottom of the script

--********************************************************************************

--**---*** WARNING THIS QUERY CAN TAKE A LONG TIME TO RUN ***---***

--**---*** WARNING THIS QUERY IS NOT SLX SYNC AWARE ***---***

--********************************************************************************

--********************************************************************************

--**This query is what is used to limit what accounts will be kept on the DB.

--**All tables will drill down from this query.

--**To modify, put your condition(s) in the nested select statement in place of

--**(select top 500 accountid from account).

--********************************************************************************

--********************************************************************************

delete from account where accountid not in (select top 500 accountid from account)

Go

--********************************************************************************

--********************************************************************************

delete from contact where accountid not in (select accountid from account)

Go

delete from opportunity where accountid not in (select accountid from account)

Go

delete from address where addressid not in (select addressid from address where

entityid in (select accountid from account) or

entityid in (select contactid from contact) or

entityid in (select userid from userinfo) or

addressid in (select addressid from branchoptions))

Go

delete from history where historyid not in(select historyid from history where

accountid in (select accountid from account) or

contactid in (select contactid from contact) or

opportunityid in (select opportunityid from contact))

Go

delete from activity where activityid not in(select activityid from activity where

accountid in (select accountid from account) or

contactid in (select contactid from contact) or

opportunityid in (select opportunityid from contact))

Go

Delete from user_activity where activityid not in (select activityid from activity)

Go

Delete from accountsummary where accountid not in (select accountid from account)

Go

Delete from accountproduct where accountid not in (select accountid from account)

Go

Delete from ACT_Userdef where contactid not in (select contactid from contact)

Go

Delete from association where associationid not in (select associationid from association where

fromid in (select accountid from account) and

toid in (select accountid from account))

Go

Delete from attachment where attachid not in(select attachid from attachment where

accountid in (select accountid from account) or

contactid in (select contactid from contact) or

opportunityid in (select opportunityid from contact))

Go

Delete from campaigncontact where contactid not in (select contactid from contact)

Go

Delete from competitor where accountid not in (select accountid from account)

Go

Delete from competitor_contact where contactid not in (select contactid from contact)

Go

Delete from competitor_details where competitorid not in (select competitorid from competitor)

Go

Delete from competitorcustomer where competitorid not in (select competitorid from competitor)

Go

Delete from competitorproduct where competitorid not in (select competitorid from competitor)

Go

Delete from contact_leadsource where contactid not in (select contactid from contact)

Go

Delete from contactresponse where contactid not in (select contactid from contact)

Go

Delete from contract where accountid not in (select accountid from account)

Go

Delete from contractincident where contractid not in (select contractid from contract)

Go

Delete from contractitem where contractid not in (select contractid from contract)

Go

Delete from GM_Profile where gm_profileid not in (select gm_profileid from gm_profile where

accountid in (Select accountid from account) or

contactid in (select contactid from contact))

Go

Delete from litrequest where contactid not in (select contactid from contact)

Go

Delete litrequestitem where litreqid not in (select litreqid from litrequest)

Go

Delete from opportunity_campaign where opportunityid not in (select opportunityid from opportunity)

Go

Delete from opportunity_contact where opportunityid not in (select opportunityid from opportunity)

Go

Delete from opportunity_product where opportunityid not in (select opportunityid from opportunity)

Go

Delete from opportunity_salesstep where opportunityid not in (select opportunityid from opportunity)

Go

Delete from opportunity_user where opportunityid not in (select opportunityid from opportunity)

Go

Delete from resourceschedule where activityid not in (select activityid from activity)

Go

Delete from RMA where accountid not in (select accountid from account)

Go

Delete from rmareceivedproduct where rmaid not in (select rmaid from rma)

Go

Delete from rmashippedproduct where rmaid not in (select rmaid from rma)

Go

Delete from ticket where accountid not in (select accountid from account)

Go

Delete from ticketaccountproduct where ticketid not in (select ticketid from ticket)

Go

Delete from ticketactivity where ticketid not in (select ticketid from ticket)

Go

Delete from ticketactivityitem where ticketactivityid not in (select ticketid from ticket)

Go

Delete from tickethistory where ticketid not in (select ticketid from ticket)

Go

Delete from ticketproblem where ticketid not in (select ticketid from ticket)

Go

Delete from ticketsolution where ticketid not in (select ticketid from ticket)

Go

 

 

--Custom Tables

Delete from account2 where accountid not in (select accountid from account)

Go

Delete from contact2 where contactid not in (select contactid from contact)

Go

Delete from contactextension where contactid not in (select contactid from contact)

Go

Print "All Finished, please run a full Database Mainteneance Rountine to Shrink DB"

What's This?
  
Bookmark and Share

About Mark Duthoy

  


Related Content
   Unicode Character Sets for SalesLogix Web 8.1
If you have the need to use unicode character sets so international users can view the web client in thei
Posted on Oct 29, 2014 by Mark Duthoy to SalesLogix Support
 
   Video: Demystifying Infor CRM (Saleslogix) Updates
Watch it now! If you missed yesterdays demo, "Demystifying Infor CRM (Saleslogix) Updates" ,
Posted on Sep 26, 2014 by Brianna Ojard to The Inbox
 
   Thoughts on the Future and Involvement in the Saleslogix/Infor CRM Community
Over the last year and a half I've been working on another product named Contatta. It's a great p
Posted on Sep 25, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Fixing the Orphaning of Salesfusion Data when Converting Leads to Contacts in Infor CRM (Saleslogix)
We have had a couple of client recently come to other with the same problem-When you convert a Lead to a
Posted on Sep 19, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Official Infor CRM Acquisition FAQ
What to expect now that Saleslogix is Infor CRM. Like many of you, it will take some time (probably a
Posted on Sep 18, 2014 by Brianna Ojard to The Inbox
 
Comments

No Comments

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register