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
   Mobile 3.0.3 for Saleslogix v7.5.4, v8.0.0 & v8.1.0 is now available!
Saleslogix Mobile v3.0.3 is now available in the customer portals. This update addresses all issues int
Posted on Apr 14, 2014 by Scott Weber to SalesLogix Product Blog
 
   Editing Filters in Saleslogix Web Client 8.0
Is it possible to add or remove Saleslogix filters from the List view in Accounts?
Posted on Apr 08, 2014 by SalesLogix Support to SalesLogix Questions & Answers
 
   Creating a Unique Saleslogix table ID in a SQL Function
A couple of months ago Ryan Farley wrote an article about how to use a SQL stored procedure to create a S
Posted on Mar 25, 2014 by Kris Halsrud to Kris Halsrud's Blog
 
   Saleslogix Integration with Google - WOW!
Saleslogix will be releasing their Google Integration this month and from what I have seen, it is fantast
Posted on Mar 18, 2014 by Scott Weber to SalesLogix Product Blog
 
   Global Joins in the Saleslogix Web Client
Question: How can I make a Global Join stick in the Saleslogix Web Client? Currently, when I create a
Posted on Mar 07, 2014 by SalesLogix Support to SalesLogix Questions & Answers
 
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