serving the solutions day and night

Pages

Tuesday, May 26, 2015

Unsupported Dynamics CRM database delete operations


Before delete, run SQL Profiler, just delete a single delete operation in CRM and watch all queries involved. 

In this example i am deleting contact entity records, before delete all their related entity records

1.Delete related entities

for example contact related dns_mailings entity (object type code is 12345)
declare  @c table(c varchar(max))

insert into @c select  dns_mailingsid from dns_mailings where dns_name = 'Test 2015'
--select * from @c

delete from dns_mailingsExtensionBase where (dns_mailingsid in (select c from @c));
delete from dns_mailingsBase
OUTPUT DELETED.dns_mailingsid , 12345
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where (dns_mailingsid in (select c from @c))

delete from [EmailSearchBase]
OUTPUT DELETED.[EmailSearchId], 4299
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where ([ParentObjectId] in (select c from @c))

 update [ActivityPartyBase] set [IsPartyDeleted]=1 where ([PartyId] in (select c from @c)) and [PartyObjectTypeCode] =12345


2.Delete Contact entity

declare  @c table(c varchar(max))

insert into @c
select  contactid  from contact where description = 'Test 2015' and dns_myear = 2015

delete from [ContactExtensionBase] where ([ContactId] in (select c from @c));
delete from [ContactBase]
OUTPUT DELETED.[ContactId], 2
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where ([ContactId] in (select c from @c))

 delete from [EmailSearchBase]
OUTPUT DELETED.[EmailSearchId], 4299
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where ([ParentObjectId] in (select c from @c))

update [ActivityPartyBase] set [IsPartyDeleted]=1 where ([PartyId] in (select c from @c)) and [PartyObjectTypeCode] =2



1 comment:

prethikarajesh said...

Thanks for sharing such a great information..Its really nice and informative.
Dotnet Training in Chennai