Instantly share code, notes, and snippets.

Embed
What would you like to do?
--Specific customer data like email info, credit cards info, etc.
update PreferencesSecurity set PasswordMinLength = 3, PasswordComplexity = 0, PasswordSecurityType = 0
update users set password = '123', PasswordChangeOnNextLogin =0, PasswordNeverExpires = 1 where source = 0
delete from userfilter
Update EMailAccount set Password = '123'
UPDATE AUSchedule SET IsActive = 0
update CustomerPaymentMethodDetail set value = '' where detailid in ('CCDNUM', 'ADDLN1', 'ADDLN2', 'NAMEONCC', 'CVV')
update ccproctran set PCResponse = ''
update CCProcessingCenterDetail set Value = '1111' where DetailID='LOGINID' or DetailID = 'TRANKEY'
UPDATE TXAvalaraSetup SET Account = NULL, Licence = NULL WHERE CompanyID > 0
--Clear Buisness Account information
update BAccount set
AcctCD = right(concat(REPLICATE('0',10), convert(nchar(10), BAccountID)), 10),
AcctName = concat(case Type
when 'CU' then 'Customer'
when 'PR' then 'Prospect'
when 'VE' then 'Vendor'
when 'EP' then 'Employee' else 'Company' end ,'#' ,convert(nchar(10), BAccountID))
where Type in ('CU', 'VE', 'VC', 'EC', 'EP', 'CP','PR');
update Location set TaxRegistrationID = null, Descr = LocationCD;
update Address set AddressLine1 = null, AddressLine2 = null, AddressLine3 = null, PostalCode = null;
update c
set EMail = null, Website = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null, Salutation = null,
FullName = concat(case b.Type
when 'CU' then 'Customer'
when 'PR' then 'Prospect'
when 'VE' then 'Vendor'
when 'EP' then 'Employee' else 'Company' end, '#', convert(nchar(10), b.BAccountID))
from Contact c
inner join BAccount b on b.CompanyID = c.CompanyID and b.BAccountID = c.BAccountID and ContactType = 'AP'
update SOContact set EMail = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null, Salutation = null,
FullName = concat('Customer#', convert(nchar(10), CustomerID ));
update POContact set EMail = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null, Salutation = null,
FullName = concat('Vendor#', convert(nchar(10), BAccountID));
update ARContact set EMail = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null, Salutation = null,
FullName = concat('Customer #', convert(nchar(10), CustomerID));
update APContact set EMail = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null, Salutation = null,
FullName = concat('Vendor#', convert(nchar(10), VendorID));
update c
set FirstName = '', LastName = '', MidName = '', DisplayName = concat('Contact#', convert(nchar(10), ContactID)), FullName = concat('Contact#', convert(nchar(10), ContactID)), Salutation = null, EMail = null, Website = null, Phone1 = null, Phone2 = null, Phone3 = null, Fax = null
from Contact c
inner join BAccount b on b.CompanyID = c.CompanyID and b.BAccountID = c.BAccountID and ContactType != 'AP'
update SOAddress set AddressLine1 = null, AddressLine2 = null, AddressLine3 = null, PostalCode = null;
update POAddress set AddressLine1 = null, AddressLine2 = null, AddressLine3 = null, PostalCode = null;
update ARAddress set AddressLine1 = null, AddressLine2 = null, AddressLine3 = null, PostalCode = null;
update APAddress set AddressLine1 = null, AddressLine2 = null, AddressLine3 = null, PostalCode = null;
update SalesPerson set SalespersonCD = right(concat(REPLICATE('0',6), convert(nchar(6), SalesPersonID)), 6), Descr = concat('Salesperson#',convert(nchar(6), SalesPersonID));
--Clear all uploaded files that are linked to Business Account
delete UploadFileRevision
from NoteDoc
inner join BAccount on NoteDoc.CompanyID = BAccount.CompanyID and NoteDoc.NoteID = BAccount.NoteID
inner join UploadFileRevision on NoteDoc.FileID = UploadFileRevision.FileID and NoteDoc.CompanyID = UploadFileRevision.CompanyID
delete UploadFile
from NoteDoc
inner join BAccount on NoteDoc.CompanyID = BAccount.CompanyID and NoteDoc.NoteID = BAccount.NoteID
inner join UploadFile on NoteDoc.FileID = UploadFile.FileID and NoteDoc.CompanyID = UploadFile.CompanyID
--Clear Project/Contract information
--NOTE: Verify that there are no RateTables with wildcards, current script do not handle such situations.
DECLARE @companyID int
DECLARE @contractID int
DECLARE @contractCD nvarchar(10)
DECLARE ProjectCursor CURSOR FOR
SELECT CompanyID, ContractID, ContractCD FROM Contract
OPEN ProjectCursor
FETCH ProjectCursor INTO @companyID, @contractID, @contractCD
WHILE @@FETCH_STATUS >= 0 BEGIN
UPDATE PMProjectRate SET ProjectCD = right(concat(REPLICATE('0',10), convert(nchar(10), @contractID)), 10) WHERE ProjectCD=@contractCD AND CompanyID=@companyID
UPDATE Contract SET ContractCD = right(concat(REPLICATE('0',10), convert(nchar(10), @contractID)), 10),
Description = concat(case BaseType when 'P' then 'Project' when 'C' then 'Contract' end, '#', convert(nchar(10), @contractID))
WHERE ContractID=@contractID AND CompanyID=@companyID
FETCH ProjectCursor INTO @companyID, @contractID, @contractCD
END
CLOSE ProjectCursor
DEALLOCATE ProjectCursor
--Clear all uploaded files that are linked to Contract/Project
delete UploadFileRevision
from NoteDoc
inner join Contract on NoteDoc.CompanyID = Contract.CompanyID and NoteDoc.NoteID = Contract.NoteID
inner join UploadFileRevision on NoteDoc.FileID = UploadFileRevision.FileID and NoteDoc.CompanyID = UploadFileRevision.CompanyID
delete UploadFile
from NoteDoc
inner join Contract on NoteDoc.CompanyID = Contract.CompanyID and NoteDoc.NoteID = Contract.NoteID
inner join UploadFile on NoteDoc.FileID = UploadFile.FileID and NoteDoc.CompanyID = UploadFile.CompanyID
--Clearing data from emails
UPDATE [E]
SET [Subject] = '',
[Body] = NULL,
[MailFrom] = NULL,
[MailReply] = NULL,
[MailTo] = NULL,
[MailCc] = NULL,
[MailBcc] = NULL,
[Exception] = NULL
FROM [SMEmail] E
UPDATE [E]
SET [Address] = NULL,
[ReplyAddress] = NULL,
[LoginName] = NULL,
[Password] = NULL,
[OutcomingHostName] = NULL,
[OutcomingLoginName] = NULL,
[OutcomingPassword] = NULL,
[OutcomingPort] = NULL,
[IncomingHostName] = NULL,
[IncomingPort] = NULL
FROM [EMailAccount] E
DELETE FROM [EMailSyncServer]
DELETE FROM [EMailSyncPolicy]
DELETE FROM [EMailSyncLog]
DELETE FROM [EMailSyncAccount]
DELETE FROM [EMailSyncAccountPreferences]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment