Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 2013

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

declare @DeleteRowCount int

Select @DeleteRowCount = 2000

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

declare @continue int, @rowCount int

select @continue = 1

while (@continue = 1)

begin     

begin tran     

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 @rowCount = 0

      Select @rowCount = count(*) from @DeletedAsyncRowsTable

      select @continue = case when @rowCount <= 0 then 0 else 1 end     

        if (@continue = 1)        begin

            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 WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d

 where WS.AsyncOperationId = d.AsyncOperationID

            delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d

            where A.AsyncOperationId = d.AsyncOperationId            

            delete @DeletedAsyncRowsTable     

end      

commit

end

--Drop the Index on AsyncOperationBase

DROP INDEX AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted

Change the MAX file size limit for CRM 2013/2011 Data Import Wizard

----------------------------------------------------------------------------------------------------------------------
Verify the current value for the "Max" file size for data upload. By default the value is set to "8"

 Use MSCRM_CONFIG

SELECT ColumnName, IntColumn FROM ServerSettingsProperties

where ColumnName = 'ImportMaxAllowedFileSizeInMB'

Go

To change the value you would need to apply the below statement. It will change the default value from 8 to 10. As a result the max file size for data import will now be 10 MB.

 Use MSCRM_CONFIG

UPDATE [MSCRM_CONFIG].[dbo].[ServerSettingsProperties]

SET [IntColumn] = '10'

WHERE ColumnName = 'ImportMaxAllowedFileSizeInMB'

 Go
------------------------------------------------------------------------------------------------------------------------

Microsoft Dynamics CRM 2013 New Features

Quick Create form – this very basic form allows you to quickly enter a record. For example, if you’re on the phone with a Lead and want to quickly capture their name and phone number in CRM without having to navigate to the lead entity, this new feature is a great addition. You can customize these forms to include the type of information you would want to quickly enter.

Quick View forms – this feature allows you to bring in data from related entities. For example, if you are working on a Case record and want to view basic Account information on the Case form, you can use a Quick View form to do that. This is a read only mode view, but we anticipate this being an area that Microsoft expands in the future. Wouldn't it be great to update related record information on one form?

Portable Business Logic / Business Rules – this great addition provides an intuitive way to write simple code that was otherwise reserved for someone with development / coding experience. You can do things like set field values, show/hide fields, set required/recommended fields and validate field data. This is a really cool addition to CRM 2013.

Guided Processes – a new visual process display can be made available that guides the user through pre-defined processes. Multiple processes can be added to each record type - think sales process for inside sales vs. outside sales. A sales organization can make sure each required step in each pipeline phase is completed before moving to the next pipeline phase.
Real time / Synchronous Workflows – processes in CRM 2011 always ran in the background requiring a user to reload the form to see the end result of the workflow. CRM 2013 now introduces processes that run immediately and update the form in real time.

Auto Save – once you’ve created a record, this new feature automatically saves information on your form triggering every 30 seconds as well as when you leave the form. This batch updates any changes made to the form without having to remember to hit the Save button. This is a system wide setting, so you either use Auto Save on all forms or you don’t.

MoCA – otherwise known as “Mobile Client Application”. MoCA refers to the (free) mobile app that can be downloaded to your phone, tablet, or even desktop. This new mobile app renders your existing CRM forms as mobile forms. These mobile forms are limited and are restricted to 5 tabs or 75 fields and 10 lists, whichever one comes first. Also, IFrames are not supported. Global search is supported in the mobile client so you can now search across multiple entities, whereas, in the full web client the ‘Quick Find’ only searches within a specific entity.

Controlling Principal Object Access (POA) table growth

You may notice that the size of the SQL table PrincipalObjectAccess has grown in size and could be using a large percentage of entire database size.

A fix to reduce POA table growth was released in CRM 2011 Update Rollup 6: http://support.microsoft.com/kb/2664150.

Apply Update Rollup 6 and then execute the script documented in the KB article. This script needs to only be executed 1 time after applying Update Rollup 6. Update Rollup 6 and future Update Rollups will fix this known issue going forward.

NOTE: If you install any Update Rollup after Update Rollup 6, without first installing Update Rollup 6, and you have not yet ran the script documented in the resolution steps you will need to execute this script after applying that Update Rollup.

USE []
GO

BEGIN TRY
 BEGIN TRAN t1

  create table #ToDeletePoaEntries
  (
   ObjectId uniqueidentifier,
   Otc int
  )

  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]

  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 @totalDeleted int = 0

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

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

   declare @deletestatement nvarchar(max)

   -- Insert records to be deleted in [#ToDeletePoaEntries]
   set @deletestatement = '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 @deletestatement

   exec(@deletestatement)
   set @totalCollected = @@ROWCOUNT
   print CAST(@totalCollected as nvarchar(20)) + ' records collected for deletion for ' + @baseTableName

   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

  -- delete all records of the current entity type which don't have corresponding object in the base table
  delete from PrincipalObjectAccess
  from PrincipalObjectAccess poa
  join #ToDeletePoaEntries e on poa.ObjectId = e.ObjectId and poa.ObjectTypeCode = e.Otc

  -- delete PrincipalObjectAccess records

  set @totalDeleted = @@ROWCOUNT
  print CAST(@totalDeleted as nvarchar(20)) + ' records deleted for ' + @baseTableName

 COMMIT TRAN t1
 PRINT 'EXECUTION SUCCEED'
END TRY
BEGIN CATCH
 ROLLBACK TRAN t1
 PRINT 'EXECUTION FAILED :' + ERROR_MESSAGE()
END CATCH

Enable WCF compression to Improve CRM2011 Netowrk Performance

Enable compression using a command line.

1. Open the Command Prompt on the CRM Server.

2. Run the following command:    

%SYSTEMROOT%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpCompression /+"dynamicTypes.[mimeType='application/soap%u002bxml; charset=utf-8',enabled='true']" /commit:apphost

3. Reset IIS for the setting to take effect.

Enable compression by manually updating the ApplicationHost.Config

1. On the CRM Server Navigate to: C:\Windows\System32\Inetsrv\Config\applicationHost.config and open it with notepad.

2. Search for the Section: “” and in that section you should fine an entry that looks like this: 

3. Below that, add the following line: 

4. Save the file and reset IIS for the setting to take effect.

Note: You may notice an increase in CPU utilization with compression enabled so be sure to monitor CPU accordingly

Dynamics CRM 2011 JavaScript “Access is Denied” Error with OData call

Dynamics CRM 2011 JavaScript “Access is Denied” Error with OData call

In CRM 2011 on load of JS users geting ""Access is Denied" error if they have OData call
when they use the Xrm.Page.context.getServerUrl();

Use the below JS
document.location.protocol + "//" + document.location.host + "/" + Xrm.Page.context.getOrgUniqueName();

CRM 2011 User does not have send-as privilege

CRM 2011 User does not have send-as privilege

When you have workflows in CRM 2011 that create e-mails for other users, like say, send an e-mail from the person who last modified an account to the owner of the account, you’re probably going to end up with e-mails staying in Draft.

When you want to send the e-mail afterwards you’re greeted with a nice: “User does not have send-as privilege.” (ErrorCode: -2147203059) exception.

When you look up the privilege, it’s nowhere to be found in the UI.

You can however set it (as suggested on the Microsoft Forum) in the Personal Settings.

The downside, you only get this option when you have the System Administrator role and no other.

When looking in the database, there is actually a prvSendAsUser privilege, so after assigning it to a general role that all users get like this:

AddPrivilegesRoleRequest addPrivilegesRequest = new AddPrivilegesRoleRequest

{

RoleId = new Guid(generalroleid),

Privileges = new[]

{

// Grant prvSendAsUser privilege.

new RolePrivilege

{

PrivilegeId = new Guid("6FD3EB4F-66E3-4587-B4AB-C064F03AD783"),

Depth = PrivilegeDepth.Global

}

}

};

service.Execute(addPrivilegesRequest);

If you afterwards have a look at the user’s privileges in the database with this query:

SELECT p.name

FROM SystemUser u

JOIN filteredSystemUserRoles sur ON sur.systemuserid = u.systemuserid

JOIN RolePrivileges rp ON rp.roleid = sur.roleid

JOIN FilteredPrivilege p ON p.privilegeid = rp.PrivilegeId

You’ll see that the user has in fact got the prvSendAsUser role.

But when you try to send an e-mail the problem stays the same.

The solution however is very simple, check for this privilege is not done in through privileges, despite what the exception makes you suggest, but is checked in the user settings.

Running an easy update query will fix this for you:

UPDATE UserSettings

SET IsSendAsAllowed = 1

WHERE IsSendAsAllowed = 0

Logical, no; but does it work, yes