Steven Rao: 2017

The following is a handy query example to look for entity/attribute/option set lookup value:

select
e.Name as Entity
,os.IsGlobal
,a.Name as Attribute
,aop.DisplayOrder
,aop.Value as OptionValue
,ol.Label as OptionLabel
,osl.Label as OptionSetLabel
,al.Label as AttributeLabel
,el.Label as EntityLabel
,e.IsAudited as EntityIsAudited
,os.Name as OptionSet
,e.IsAuditEnabled as EntityIsAuditEnabled
,al.Label as AttributeLable
,a.IsAuditEnabled as AttributeIsAuditEnabled
,atp.Description as AttributType
--,a.*
--,ol.*
--,al.*
from MetadataSchema.Entity e
inner join MetadataSchema.LocalizedLabel el on el.ObjectId=e.EntityId and el.LanguageId=1033 and el.ObjectColumnName='LocalizedName' and year(el.OverwriteTime)=1900
inner join MetadataSchema.Attribute a on a.EntityId=e.EntityId and a.IsCustomField=1 and year(a.OverwriteTime)=1900
inner join MetadataSchema.LocalizedLabel al on al.ObjectId=a.AttributeId and al.LanguageId=1033 and al.ObjectColumnName='DisplayName' and year(al.OverwriteTime)=1900
inner join MetadataSchema.AttributeTypes atp on atp.AttributeTypeId=a.AttributeTypeId
inner join MetaDataSchema.OptionSet os on os.OptionSetId=a.OptionSetId and os.IsCustomOptionSet=1
inner join MetadataSchema.LocalizedLabel osl on osl.ObjectId=os.OptionSetId and osl.LanguageId=1033 and osl.ObjectColumnName='DisplayName' and year(osl.OverwriteTime)=1900
inner join MetadataSchema.AttributePicklistValue aop on aop.OptionSetId=os.OptionSetId and year(aop.OverwriteTime)=1900
inner join MetaDataSchema.LocalizedLabel ol on ol.ObjectId=aop.AttributePicklistValueId and ol.ObjectColumnName='DisplayName' and year(ol.OverwriteTime)=1900 and ol.LanguageId=1033
where year(e.OverwriteTime)=1900
--and e.Name='new_entity'
and e.Name='opportunity'
--and e.IsCustomEntity=1
order by 1,2 desc,3,4

year(...)=1900 can be replaced by =0

Thursday 2 February 2017

T-SQL to clean up PrincipleObjectAccess table of Microsoft Dynamics CRM

-- https://support.microsoft.com/en-us/help/2664150/how-to-control-principalobjectaccess-table-growth-in-microsoft-dynamics-crm-2011

-- ??
--Delete from SubscriptionTrackingDeletedObject where TimeStamp <= convert(timestamp, 55527527);

BEGIN TRY
BEGIN TRAN t1

IF NOT EXISTS (SELECT * FROM sys.sysobjects
WHERE id = object_id(N'[dbo].[ToDeletePOAEntries]')
AND ObjectProperty(id, N'IsUserTable') = 1)

create table ToDeletePoaEntries
(
ObjectId uniqueidentifier,
Otc int
)

IF NOT EXISTS (SELECT * FROM sys.sysindexes si
INNER JOIN sys.sysobjects so ON si.id = so.id
WHERE so.id = OBJECT_ID(N'[dbo].[ToDeletePoaEntries]')
AND OBJECTPROPERTY(so.id, N'IsUserTable') = 1
AND si.name LIKE '%mainindex%')

CREATE UNIQUE NONCLUSTERED INDEX [mainindex] ON [dbo].[ToDeletePoaEntries]
(
[ObjectId] ASC,
[Otc] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

----- Insert records to be deleted in ToDeletePoaEntries

-- go through all user-owned entities which are not replicated and don't support duplicate detection
declare entity_cursor cursor local FORWARD_ONLY READ_ONLY
for select distinct e.ObjectTypeCode, e.BaseTableName, a.PhysicalName from EntityView e
inner join AttributeView a on e.EntityId = a.EntityId and a.IsPKAttribute = 1
where e.IsReplicated = 0 and e.IsDuplicateCheckSupported = 0 and e.OwnershipTypeMask & 1 = 1

open entity_cursor

declare @baseTableName sysname
declare @otc nvarchar(20)
declare @primaryKey sysname
declare @totalCollected int = 0
declare @currentCollected int

declare @tempRowCount int = 0
declare @collectstatement nvarchar(max)

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey

while @@FETCH_STATUS = 0
begin
print 'Cleaning up POA for ' + @baseTableName

set @currentCollected = 0

set @collectstatement = 'insert into ToDeletePoaEntries(ObjectId, Otc)
select distinct poa.ObjectId, poa.ObjectTypeCode
from PrincipalObjectAccess poa
left join ' + @baseTableName + ' e on poa.ObjectId = e.' + @primaryKey +
' where e.' + @primaryKey + ' is null and poa.ObjectTypeCode = ' + @otc;

print @collectstatement

exec(@collectstatement)
set @tempRowCount = @@ROWCOUNT
set @currentCollected = @currentCollected + @tempRowCount
print CAST(@currentCollected as nvarchar(20)) + ' records collected for ' + @baseTableName
set @totalCollected = @totalCollected + @currentCollected

fetch next from entity_cursor
into @otc, @baseTableName, @primaryKey
end

close entity_cursor
deallocate entity_cursor

print CAST(@totalCollected as nvarchar(20)) + ' total records collected'

-- Delete query

-- This scripts cleans up orphaned POA records for selected entities

declare @deleteBatchSize int = 50000
declare @deleteBatchSizeNVarChar nvarchar(10) = CAST(@deleteBatchSize as nvarchar(10))
declare @totalDeleted int = 0
declare @currentDeleted int
declare @deletestatement nvarchar(max)

set @currentDeleted = 0
set @tempRowCount = 0

-- delete all records of the current entity type which don't have corresponding object in the base table
set @deletestatement = 'delete top (' + @deleteBatchSizeNVarChar + ') from PrincipalObjectAccess
from PrincipalObjectAccess poa
join ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc'

print @deletestatement

-- delete PrincipalObjectAccess records in batches
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount

while @tempRowCount = @deleteBatchSize
begin
exec(@deletestatement)
set @tempRowCount = @@ROWCOUNT
set @currentDeleted = @currentDeleted + @tempRowCount

print CAST(@currentDeleted as nvarchar(20)) + ' records deleted ' + cast(getUtcdate() as nvarchar(50))
--cleanup

end

COMMIT TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]
PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
ROLLBACK TRAN t1

-- Cleanup

DROP Table [dbo].[ToDeletePoaEntries]

PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH

T-SQL script to clean up SyncOperationBase table of Microsoft Dynamics CRM

-- https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large-in-microsoft-dynamics-crm

/*
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 = 10000
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
;