CRM Related SQL Queries 1

SQL Queries Version 1

Get Privilege by Role name

select priv.* 

from Privilege priv

join RolePrivileges rolePrivileges on (rolePrivileges.PrivilegeId = priv.PrivilegeId )

join Role role on (rolePrivileges.RoleId = role.RoleId)

where role.Name = 'system administrator' 

Get the Users By RoleName

select sysuser.FirstName,sysuser.DomainName,sysuser.AccessMode, sysuser.ActiveDirectoryGuid,sysuser.CALType, sysuser.*

from SystemUser sysuser

join SystemUserRoles userRoles on (userRoles.SystemUserId = sysuser.SystemUserId )

join Role role on (userRoles.RoleId = role.RoleId )

where role.Name ='system administrator' 

and sysuser.AccessMode=0 and sysuser.CALType=0 and sysuser.IsDisabled=0

Get All Privilege By Role with formatting

SELECT DISTINCT
FilteredRole.name,
EntityView.PhysicalName AS [Entity Name],
EntityView.IsCustomEntity AS [IsCustom],
displayname.Label as[Entity Display Name] ,


CASE Privilege.AccessRight
WHEN 1 THEN 'READ'
WHEN 2 THEN 'WRITE'
WHEN 4 THEN 'APPEND'
WHEN 16 THEN 'APPENDTO'
WHEN 32 THEN 'CREATE'
WHEN 65536 THEN 'DELETE'
WHEN 262144 THEN 'SHARE'
WHEN 524288 THEN 'ASSIGN'
END AS [Access Level],
CASE PrivilegeDepthMask
WHEN 1 THEN 'User'
WHEN 2 THEN 'Business Unit'
WHEN 4 THEN 'Parent: Child Business Unit'
WHEN 8 THEN 'Organisation'
END AS [Security Level]
FROM  RolePrivileges
INNER JOIN
FilteredRole ON RolePrivileges.RoleId = FilteredRole.roleid
INNER JOIN
PrivilegeObjectTypeCodes ON RolePrivileges.PrivilegeId = PrivilegeObjectTypeCodes.PrivilegeId
INNER JOIN
Privilege ON RolePrivileges.PrivilegeId = Privilege.PrivilegeId
INNER JOIN
EntityView ON EntityView.ObjectTypeCode = PrivilegeObjectTypeCodes.ObjectTypeCode
inner join LocalizedLabelLogicalView displayname
on EntityView.EntityId = displayname.ObjectId
and displayname.ObjectColumnName = 'LocalizedName'
ORDER BY FilteredRole.name, [Entity Name]

Get Picklist values details from an Entity

SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.AttributeTypes t
ON a.AttributeTypeId = t.AttributeTypeId
AND t.Description = 'picklist'
INNER JOIN MetadataSchema.AttributePicklistValue pl
ON a.OptionSetId = pl.OptionSetId
INNER JOIN MetadataSchema.LocalizedLabel l
ON pl.AttributePicklistValueId = l.ObjectId
where e.Name ='Account'   -- change entity as per reuirement
ORDER BY e.Name, a.Name

Attribute DisplayName

SELECT DISTINCT e.Name as Entity, a.Name as Attribute, pl.Value, l.Label
FROM MetadataSchema.Attribute a
INNER JOIN MetadataSchema.Entity e
ON a.EntityId = e.EntityId
AND YEAR(e.OverwriteTime) = 1900
INNER JOIN MetadataSchema.AttributeTypes t
ON a.AttributeTypeId = t.AttributeTypeId
AND t.Description = 'picklist'
INNER JOIN MetadataSchema.AttributePicklistValue pl
ON a.OptionSetId = pl.OptionSetId
INNER JOIN MetadataSchema.LocalizedLabel l
ON pl.AttributePicklistValueId = l.ObjectId
where e.Name ='Account'   -- change entity as per reuirement
ORDER BY e.Name, a.Name

Difference between 2 organizations 

SELECT  

    T.[name] AS table_name, AC.[name] AS column_name,  

        TY.[name] AS system_data_type,

        Ty.max_length as maxLength into FullCRMSchema_165

FROM    [<DBName>].sys.[tables] AS T  

    INNER JOIN <DBName>.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 

    INNER JOIN <DBName>.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

EXCEPT

SELECT  

    T.[name] AS [table_name], AC.[name] AS [column_name],  

        TY.[name] AS system_data_type,

         Ty.max_length as maxLength  into FullCRMSchema_164

FROM    <DBName>.sys.[tables] AS T  

    INNER JOIN <DBName>.sys.[all_columns] AC ON T.[object_id] = AC.[object_id] 

    INNER JOIN <DBName>.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]

    

    

    

    select * from FullCRMSchema_165

    except

    select * from FullCRMSchema_164

Find Present Active Users in CRM who are logged in

SELECT login_name, COUNT(session_id) AS [session_count] 

FROM  sys.dm_exec_sessions

GROUP BY login_name

ORDER BY login_name;

-- Run multiple times

SELECT LTRIM (st.[text]) AS [Command Text],[host_name], der.session_id AS [SPID],

       der.[status], db_name(database_id) AS [Database Name], 

       ISNULL(der.wait_type, 'None') AS [Wait Type], 

       der.logical_reads, der.cpu_time, der.total_elapsed_time 

FROM sys.dm_exec_requests AS der

INNER JOIN sys.dm_exec_connections AS dexc

ON der.session_id = dexc.session_id

INNER JOIN sys.dm_exec_sessions AS dexs

ON dexs.session_id = der.session_id

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE der.session_id >= 51

AND der.session_id <> @@spid -- eliminate this connection

ORDER BY der.[status];

Find Duplicates records LOGIC only. You should change the table names accordingly

drop table #Temp1

CREATE TABLE #Temp1 (Id int,  Name Char( 30 ), state1 Char( 30 ) )

insert into #Temp1 values ( 50, 'Sujith_ND1','India_ND1');

insert into #Temp1 values ( 51, 'Sujith_ND2','India_ND2');

insert into #Temp1 values ( 52, 'Sujith_ND3','India_ND3');

DECLARE @cnt INT = 0;

Declare @id INT=0

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith','India');

   SET @cnt = @cnt + 1;

    SET @id = @id + 1;

END;

set  @cnt  = 0;

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith1','India1');

   SET @cnt = @cnt + 1;

     SET @id = @id + 1;

END;

set  @cnt  = 0;

WHILE @cnt < 10

BEGIN

   insert into #Temp1 values ( @id, 'Sujith2','India2');

   SET @cnt = @cnt + 1;

     SET @id = @id + 1;

END;

--select * from #Temp1

---------------------------------------------------

SELECT  id, name,state1,  DENSE_RANK()  over (ORDER BY state1 DESC) as Ranking into #Temp2

FROM #Temp1 

select * from  #Temp2 t join

(select  COUNT(*) as counting,Ranking from  #Temp2 group by ranking having COUNT(*)>1) rankingtable

on t.Ranking=rankingtable.Ranking

Cleanup Async Table:

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

GO

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

GO

while(1=1)

begin

declare @DeleteRowCount int = 2000

declare @rowsAffected int

declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)

insert into @DeletedAsyncRowsTable(AsyncOperationId)

Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase

where

  OperationType in (1, 9, 12, 25, 27, 10)

  AND StateCode = 3

  AND StatusCode in (30, 32)

select @rowsAffected = @@rowcount

 delete poa from PrincipalObjectAccess poa

   join WorkflowLogBase wlb on

    poa.ObjectId = wlb.WorkflowLogId

   join @DeletedAsyncRowsTable dart on

    wlb.AsyncOperationId = dart.AsyncOperationId

delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d

where

  W.AsyncOperationId = d.AsyncOperationId            

 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d

where

  B.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d

where

  O.AsyncOperationId = d.AsyncOperationId

delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d

where

  WS.AsyncOperationId = d.AsyncOperationID

 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

where

  A.AsyncOperationId = d.AsyncOperationId

/*If not calling from a SQL job, use the WAITFOR DELAY*/

if(@DeleteRowCount > @rowsAffected)

  return

else

  WAITFOR DELAY '00:00:02.000'

end

CleanUp a Specific Async Plugin Name

IF EXISTS (SELECT name from sys.indexes

WHERE name = N'CRM_AsyncOperation_CleanupCompleted')

      DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

GO

CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

GO

while(1=1)

begin

declare @DeleteRowCount int = 2000

declare @rowsAffected int

declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)

insert into @DeletedAsyncRowsTable(AsyncOperationId)

Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase

where

 StateCode in ( 0,20, 21,10)

  and   

-- Use any of the option

--Name='SamplePluginName'

--owningextensionid ='20C25FE6-0F37-E311-AEEA-0050569A063F'

select @rowsAffected = @@rowcount

 delete poa from PrincipalObjectAccess poa

   join WorkflowLogBase wlb on

    poa.ObjectId = wlb.WorkflowLogId

   join @DeletedAsyncRowsTable dart on

    wlb.AsyncOperationId = dart.AsyncOperationId

delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d

where

  W.AsyncOperationId = d.AsyncOperationId            

 delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d

where

  B.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteOperationBase From BulkDeleteOperationBase O, @DeletedAsyncRowsTable d

where

  O.AsyncOperationId = d.AsyncOperationId

delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d

where

  WS.AsyncOperationId = d.AsyncOperationID

 delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

where

  A.AsyncOperationId = d.AsyncOperationId

/*If not calling from a SQL job, use the WAITFOR DELAY*/

if(@DeleteRowCount > @rowsAffected)

  return

else

  WAITFOR DELAY '00:00:02.000'

end

What is Difference between Retrieve VS Retrieverequest in SDK - use Retrieverequest to get related records using single server call

Which method will you apply to get related records of an entity record? 

Could we get it using service.retrieve? Probably you cannot... It will return single record but you may not get related records.

So how do you get it 

You can utilize Retrieverequest for this purpose. You can pass Relatedentity query as part of retrieve request which will return related entities as part of the response. 

Let us see some sample

if you want one specific incident record with its notes you can get it in a single server call.

Retrieve Method

Retrieve method used to get specific record by Id

C#

public override Entity Retrieve (             string entityName,             Guid id,             ColumnSet columnSet )

Parameters

^entityName

^{Type: String. The logical name of the entity specified in the entityId parameter.}

^id

^{Type: Guid. The ID of the record you want to retrieve.}

^columnSet

^{Type: ColumnSet. A query that specifies the set of columns, or attributes, to retrieve.}

Return Value

Type: Entity
The requested record. 

  

Sample Syntax:

Entity entity = crmservice.Retrieve("incident", new Guid("5181F7EA-914F-E411-91C6-0050569A023C"), new ColumnSet(true));

Retrieverequest

Retrieve method used to get specific record by Id including related entity instance in a single server call

Step 1: Define Related Entity request query. You can create multiple queries if required

QueryExpression OQueryExpression = new QueryExpression();

OQueryExpression.EntityName = "annotation";

OQueryExpression.ColumnSet = new ColumnSet(true);

Step 2:  Define relationshipquerycollection and add the Query expressions into it.

RelationshipQueryCollection OrelatioshipQueryCollection = new RelationshipQueryCollection();

Relationship Orelationship = new Relationship("Incident_Annotation");  //Schema Name of relationship

OrelatioshipQueryCollection.Add(Orelationship, OQueryExpression);

Step 3:  Define retrieve request object. define the Id of Case[incident] which you want to retrieve

RetrieveRequest OretrieveRequest = new RetrieveRequest();

OretrieveRequest.Target = new EntityReference("incident", new Guid("5181F7EA-914F-E411-91C6-0050569A023C"));

OretrieveRequest.ColumnSet = new ColumnSet(true);

Step 3:  Attach relationshipquerycollection to request

OretrieveRequest.RelatedEntitiesQuery = OrelatioshipQueryCollection;

Step 3:  Call service to retrieve

RetrieveResponse response=(RetrieveResponse)  service.Execute(OretrieveRequest);

How to read related entity records 

foreach (KeyValuePair<Relationship, EntityCollection> relatedEntitiesCollection in response.Entity.RelatedEntities)

   {

Relationship relationship = relatedEntitiesCollection.Key;

EntityCollection entityCollection = relatedEntitiesCollection.Value;

  for (int i = 0; i < entityCollection.Entities.Count;i++ )

     {

  Entity Oentity=  entityCollection.Entities[i];

    // Do your actions

                

      }

   }

   

Happy Coding!

CRM Chanage Management Process

CRM Data Purging or Data Managment

Hi

Looking for a data management solution in CRM?

Every body knows, if active data load is less, the transactions will process faster. But probably the purging policy of customer for data may not allow you to remove or delete history data. :-(

May be you will reach some situation where we cannot achieve better performance after doing all technical/hardware changes in CRM and infrastructure level.

In  such situation, this approach may help you.

Here is the basic idea. 

Below items has to take care:

1.       Keep the same GUID & Created on dates for integrity

2.       Sync Customization Changes in Original entity to duplicate Entity

I    We are in the process of making this tool. I will post as per its progress....Welcome your input

     

      Thanks. Great Day Ahead!