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

Comments

Post a Comment

Popular posts from this blog

How to integrate Angular with CRM

Image
WHY CRM? Today’s consumers are literally bombarded with all sorts of messages and offers that spring out from every corner. Think about it for a minute, how many times per day do we receive promotional emails from a page you signed up for? And more importantly, how often do you actually read it!! These days everyone is on social media, on blogs and anyone can comment, read or share. We cannot go about a one size fits all approach, as these days customers have become more discerning and tech savvy. They are more aware of what they want and the array of options available to them. They demand personalized and diverse means of engaging with products and services. Most marketers around the world believe that personalization of the web experience is most critical, 74% of respondents claim to get frustrated with websites when content, offers, ads and promotions appear to have nothing to do with their interests and this study results, paint a picture of a more eager consum...
Read more