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

Dynamic CRM Hide/Show ribbon button by OnChange lookup field

1)create a javascript (HideShow.js), 2 functions

var HideShowButton = {
//refresh the ribbon when lookup field on change.
OnChangeLookup: function () {
        Xrm.Page.ui.refreshRibbon();
  },

//Show and Hide when form load
EnableCustomRule: function () {
if (Xrm.Page.data.entity.attributes.get("contactid") != null) {
   var contactId = Xrm.Page.data.entity.attributes.get("contactid").getValue()[0].id;
   var req = new XMLHttpRequest();
   var url = Xrm.Page.context.getClientUrl() + "/XRMServices/2011/OrganizationData.svc/ContactSet(guid'" + contactId + "')?$select=photoonfile";
   req.open("GET", encodeURI(url), false);
   req.setRequestHeader("Accept", "application/json");
   req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
   req.send();
   var data = JSON.parse(req.responseText).d;
   if (data.photoonfile == null || data.photoonfile == false) {
return true;
   }
   return false;
}
},
};

SQL Server - Common Table Expressions (CTE)

It's a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query.

Syntax

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

SELECT <column_list> FROM expression_name;

Multiple cTE

WITH expression_name1 [ ( column_name [,...n] ) ],
     expression_name2 [ ( column_name [,...n] ) ]   

select <column_list> FROM expression_name1
union all
select <column_list> FROM expression_name2

select <column_list> FROM expression_name1 en1 inner join expression_name2 en2 on en1.column_name= en2.column_name

SQL Server - Shortcut to add alias for all the columns

In SSMS, for example

I have a sql query,

select
    BusinessEntityID,
    Bonus,
    SalesLastYear
from
    Sales.SalesPerson

 now  i added alias Sales.SalesPerson sp, want to apply alias name to all the columns quickly, press ALT+Shift button and hold them down while i move my cursor to the lower right of the block.

Now i change to SalesPerson alias name to all column name

select
    SalesPerson.BusinessEntityID,
    SalesPerson.Bonus,
    SalesPerson.SalesYTD,
    SalesPerson.SalesLastYear
from
    Sales.SalesPerson

SQL Server Last executed query history

SELECT

    deqs.last_execution_time AS [Time],

    dest.TEXT AS [Query]

FROM

    sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY

    deqs.last_execution_time DESC

http://msdn.microsoft.com/en-us/library/ms188068.aspx

 

Dynamics CRM - Passing crm records id and Display report in the "Run on Current Record" menu section

1) Created SSRS report
2) Created 2 DataSets
    i) DSContact

          Query
              declare @sql as nVarchar(max)
              set @sql = 'SELECT fullname, contactid FROM (' + @CRM_FilteredContact+ ') as fc'
              exec(@sql)

          Field
             fullname - fullname
             contactid - contactid
          Parameters
             @CRM_FilteredContact - [@CRM_FilteredContact]

     ii) DSAuditHistory - sql query passing parameters @Contactid [@ContactID]

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))

Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"

Convert Dynamics CRM Entity GUID in SSRS

When i create hyperlink i passed  GUID (Fields!contactid.Value) in the query string, the link is not working. I tried to convert GUID to string, still the link is not working.

Finally i used CType function to concatenation with the querystring, it works.

 = Parameters!CRM_URL.Value + "/tools/audit/audit_details.aspx?_CreateFromId=&_CreateFromType=2&id={" & CType(Fields!contactid.Value, GUID).ToString  & "}"

MS Dynamics CRM - Change Grid view background color

My client wants to change the grid view color based on the contact entity address type value is "Ship To". So i added a button in the contact entity "<ribbondiffxml>", but i didn't make it display, you can see there is no command in the "<displayrules>". I added new <customrule> to call the javascript("dns_contact") function ("BackGroundColorGrid") to change the color. Make sure you added jquery in your webresources.

Same way you can change color for the grid.