Tuesday, 24 November 2015

CRM Import Data Wizard of Team Failed with "Could not retrieve salesperson role" Error Message

Today, a colleague of mine tried to import the team data exported from Development environment into the UAT environment using CRM out of the box Import Data Wizard.

The import was failed with the following error message “Could not retrieve salesperson role” even though there was no salesperson role assigned to those roles in Development environment. Actually, those unused OOTB security roles like Salesperson role have been deleted in both Dev and UAT environments.
I’ve searched about this issue online a bit and I’ve found out about the behaviour of CRM OOTB Import Wizard in this blog post from Magnetism.

Here are some of the lines quoted from that blog

  • The "Salesperson" role was automatically assigned to all imported Teams.
  • The automatic assignment of the Salesperson Security Role does not happen when you create Team records manually.
  • All teams imported will be automatically assigned a default security role of Salesperson (even if the Security Role has been renamed)
  • The import will fail if you deleted the Salesperson security role.
  • Also you cannot import Access teams (even though you have the option on the Template) - you will get the following error

One comment in that blog mentioned that he also encountered such error and he was able to recreate the Salesperson role through some SQL "magic." (which I believe is updating the GUID values)
Such “SQL magic” will only work on on-premises environment and one must know what values to be updated.
So, the easier solution that I recommend is to export and import the OOTB Salesperson role from one of the CRM environment with the same version. You can create one new organization from deployment manager if that role has been deleted from all environments.
If the security is not being used, you can rename it to something obvious one like “(Do not use. For Team import purpose only) Salesperson” not to confuse about the existence of the role.

So, the moral of the story is not to delete any of the out-of-the-box components even if the whole CRM environment is customised as XRM system and none of the components are being used.
Components like Forms, Views, etc. can be deactivate. Some components like Reports can be hidden away by updating the filter of the default view.
For those components like Security Role which cannot be done either of them, it’s better to rename with obvious prefix to avoid confusion.

Friday, 20 November 2015

Issue After Renaming SQL Server for CRM 2015 (Standalone Instance)

This morning, I was trying to rename the Computer Name of the VM that I copied from my colleague since the computer name was given with his initials. (so that I was trying to rename it to more generic one)

The VM was installed with SQL Server 2014 and MS CRM 2015 all in one machine.
After I've changed the computer name and restarted, I tried to open the Deployment Manager in order to update the SQL DB connection of my environment and update the server names, the Deployment Manager snap-in loading stopped with the following error.

 Unable to access the MSCRM_CONFIG database. SQL Server does not exist or access denied.  

So, I updated the configdb and ServerUrl registry value under MSCRM registry hive (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM)
After updating the registry key and open the deployment manager, I got the Deployment Manager snap-in crashed with the following error while loading.

 Type is not resolved for member 'Microsoft.Crm.CrmException,Microsoft.Crm.Core, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.  
 Exception type:  
 Exceptin stack trace:  
   at Microsoft.ManagementConsole.Internal.IMessageClient.ProcessRequest(Request request)  
   at Microsoft.ManagementConsole.Executive.RequestStatus.BeginRequest(IMessageClient messageClient, RequestInfo requestInfo)  
   at Microsoft.ManagementConsole.Executive.SnapInRequestOperation.ProcessRequest()  
   at Microsoft.ManagementConsole.Executive.Operation.OnThreadTransfer(SimpleOperationCallback callback)  

I referred a couple of blog posts and a forum thread and I tried to run Repair.

At the verification step during the Repair, I got the SQL Server with the error message "The instance name must be the same as computer name.". Since there is no step to update the SQL server instance name during the whole process before the verification step, the Repair process was totally stuck without any option.

I Googled around a bit more and I found a couple of blog posts which suggested to update the MSCRM_CONFIG DB values. So, I updated

  • 4 [NVarCharColumn] values in [DeploymentProperties] table
  • [ConnectionString] ,[SqlServerName] and [SrsUrl] values in [Organization]
  • [FullName] and [Name] values in [Server] table

After all those updates, the CRM environment is working without any Repair required.

 SELECT [ColumnName], [NVarCharColumn]  
 FROM [MSCRM_CONFIG].[dbo].[DeploymentProperties]  
 WHERE [ColumnName] = 'ADDeploymentSdkRootDomain' OR   
      [ColumnName] = 'ADDiscoveryRootDomain' OR   
      [ColumnName] = 'ADSdkRootDomain' OR   
      [ColumnName] = 'ADWebApplicationRootDomain'  

 SELECT [FriendlyName]  
 FROM [MSCRM_CONFIG].[dbo].[Organization]  

 SELECT [FullName] ,[Name]  
 FROM [MSCRM_CONFIG].[dbo].[Server]  

Even though CRM environment is working fine, you may still need to fix the DB connection string in Reporting Services from Reporting Services Configuration Manager.

This blog post also suggested to Fix SQL Server and Restart Services. (a good thing to take note)

  1. In SQL Server Management Studio run the following SQL:
 sp_dropserver <old_name>;  
 sp_addserver <new_name>, local;  
  1. Restart SQL Server Services.
  2. Run SELECT @@SERVERNAME to confirm the rename has succeeded.
See http://technet.microsoft.com/en-us/library/ms143799.aspx for further details on
renaming instances of SQL Server.

The same blog post suggested to Update CRM Security Group Membership in Active Directory. (it was necessary for my case since my standalone VM consists of the Active Directory service too.
It is also not necessary if you renamed your server whilst it was connected to the Domain Controller (you might want to check though just to be sure).
a. Log onto your Domain Controller as a Domain Admin
b. Open up Active Directory Users and Computers and navigate to the Security Groups created by the original CRM installation process for your server. Eg. PriveReportingGroup, PrivUserGroup, SQLAccessGroup.
c. Open each group and add your new server as a member to each.
NB. You will need to select "Computers" as an Object Type

Wednesday, 16 September 2015

Save & Publish Button for Forms and WebResources for CRM 2015

Development for my previous projects, I've been using Save and Publish button by Jonas Rapp for customization of the forms. When I started the new project in CRM 2015, I cannot import the same solution anymore since CRM 2011 solutions are not allowed to import in CRM 2015 anymore.

So, I just copied all the items from CRM 2011 solution and create a new managed solution for CRM 2015. You can download from the following link if you need that button for your CRM 2015 as well.
All credit goes to the original solution creator, Jonas Rapp.

Wednesday, 26 August 2015

Fix for CRM 2013 SP1 UR3 The product key is not compatible with the installed version of Microsoft Dynamics CRM

Today, when I setup the secondary front end server of the existing CRM 2013 system with SP1 UR3, I encounter the following error message.

The product key is not compatible with the installed version of Microsoft Dynamics CRM.

When I searched around the web with the error message, this is the first article in the results and the issue is quite similar to mine.

But trying out the resolution in the post didn't actually resolve my issue. May be it is the separate issue for CRM 2011.

After searching a bit further, I came across the following forum thread.
In the forum thread, Abacuse mentioned about the solution to integrate rollup to the RTM version.

Since my existing CRM 2013 system was installed with SP1 UR3, I extracted the UR3 of CRM 2013 SP1 file "CRM2013-Server-KB3016464-ENU-amd64.exe" with Winrar in one of the folders.
After extracting, find the only .msp file in the extracted folder and note it down for next step.

Then, create the config XML file with the name "SetupConfig.xml" in the same location as SetupServer.exe of the CRM Server installation folder.
This is the content of my config XML file and the file path of the .msp file need to be changed accordingly.
  <Patch update="true">C:\CRM Installer\SP1 UR3\CRM2013-Server-KB3016464-ENU-amd64\server_kb3016464_amd64_1033.msp</Patch>  

After that, open the command line and change directory to the CRM Server installation folder and run the following command based on the name of the .xml file which was created in the previous step.
SetupServer.exe /config SetupConfig.xml

If you follow the steps correctly, the setup should start with the following screen instead of normal setup first page.

If the Setup is using the patch from the same rollup as the existing CRM instance, the product key compatibility error will not be shown anymore.

Microsoft Dynamics CRM 2013 Notebook

In this post, I am going to note down all the useful add-onsinformationtips and tools regarding with MS CRM 2013.

Start Menu style navigation
Tool Tip Manager
tuneMultiCheckBox (CodePlex)

BPFHelper Library (CodePlex)
CrmRestKit.js (CodePlex) (How to)
PFE Core Library for Dynamics CRM (CodePlex)
SparkleXrm - Features (GitHub) (Code Snippets)
XrmServiceToolkit (JavaScript Library for MSCRM 2011 & 2013) (CodePlex)


Depreciated Features

  • 2007 WebService Endpoint
  • CRM 4.0 Custom Workflow Tools
  • CRM 4.0 Client Side Scripting
  • CRM 4.0 Plugins
  • Duplicate Detection
  • ExtensionBase tables
  • Get Started Pane
  • ISV Folder
  • Read-Optimized Forms
  • Solution Down Level Tool
  • Workplace and Personalization Options

New Features
9 Nifty New CRM 2013 Features
Top 10 New Features of CRM 2013
TOP 15 New Features of CRM 2013
Top 7 New Features in CRM 2013
New Features & Comparison of Microsoft Dynamics CRM 2011 versus CRM 2013
Upgrading to the CRM 2013 User Experience
What’s changed in Dynamics CRM 2013
What’s new in CRM 2013 for Developers
What’s new for CRM Online Fall '13 and CRM 2013
Microsoft Dynamics CRM 2013 What's New slide

Changes in CRM 2013 Service Pack 1
Configuration Migration Tool
Case Creation and Routing (Details)
Number of Command Bar Buttons
Queue Enhancements (Part 1, Part 2)
Server-Based Sharepoint Integration
Service & Case Changes
Service Level Agreements
Service Management module
Status Reason Transitions (Part 1, Part 2, Part 3) (another blog Custom State Model Transitions)
Timer Control
Unified Service Desk aka USD

PowerObjects Spring ’14 Wave Update
Close a Case with Open Activities
Create Holiday Scheduling
Create Parent and Child Case Relationship
Parent and Child Case Settings Configuration
Plug-in Registration Tool Gets New Look
Service Level Agreements

New Feature blogs by cRM Musings (Service Management, Service Queues and Routing, Service Management SLA's)
New Features in Dynamics CRM 2013 Service Pack 1
New Help Center for Dynamics CRM Online Spring ’14 and Microsoft Dynamics CRM 2013 SP1
Top 10 Spring ’14 Wave Updates for Dynamics CRM
What’s new for CRM Online Spring '14 and CRM 2013 Service Pack 1
What’s New in the Spring ‘14 Release / Service Pack 1 (Case Hierarchies, Entitlements, SLAs, Insights)
What's New With Queue

Microsoft Dynamics CRM 2013 White Papers & Technical Documentation
Microsoft Dynamics CRM 2013 Implementation Guide
Microsoft Dynamics CRM 2013 Software Development Kit (SDK)
Microsoft CRM Online Data Migration to Microsoft Dynamics CRM on-premises
Configuring Claims-based Authentication for Microsoft Dynamics CRM Server
Process Enablement with Microsoft Dynamics CRM 2013
Microsoft Dynamics CRM 2013 for E-mail Router Installing Guide for use with Microsoft Dynamics CRM Online
Microsoft Dynamics CRM 2013 Logical Entity Relationship Diagrams
Manage Your Microsoft Dynamics CRM Online Subscription
Access Teams with Microsoft Dynamics CRM 2013
Scalable Security Modeling with Microsoft Dynamics CRM 2013
Microsoft Dynamics CRM 2013 Performance Counters
Connectivity and Firewall Port Requirements for Microsoft Dynamics CRM 2013
Delivering an Extensible CRM Solution Using Agile Development
Using multi-tenancy in Microsoft Dynamics CRM 2013 to address challenges in enterprise business environments
Microsoft Dynamics CRM Online security and service continuity guide
Microsoft Dynamics CRM Online security and compliance planning guide

Microsoft Dynamics CRM 2013 Resources
Useful Dynamics CRM 2013 Documentation
Videos & eBooks
Video resource knowledge base
New Videos for Microsoft Dynamics CRM 2013 SP1 and Spring '14

Access Teams in a Nutshell
Common queries regarding Dynamics CRM Asynchronous Service
Compatibility with Dynamics CRM 2013
CRM admin best bets
CRM for tablets browser and app support
Default status and status reason values
Explaining the built-in SYSTEM and INTEGRATION users
Keyboard shortcuts
Maintenance Jobs
Microsoft Dynamics CRM 2013 Resources
MSCRM Videos & eBooks
Multiple Currencies
Owner Teams vs Access Teams
Save Event Arguments
Upgrade Tips and Considerations for CRM 2011 to 2013 (Part 1, Part 2)and other information

Manage Appointment Status Reasons (Adding Optionset Values for System Attributes through API)

Business Process Flow
Enable Business Process Flows for Custom Entity
Command Bar
Create a command bar button for your custom activities
Showing "Save and New” button
Resend stuck emails
Restore Add Activity buttons
Restore Hidden button on the Command Bar

Access Team View in Dynamics CRM
Add Mail Merge Button
Adjust Field Label Sizes
Controlling Access to Access Team Templates
Create a Customer Service Schedule for SLA
Create a 1:1 relationship
Create new Exchange Email Server Profile
Design and scalability considerations when using Access Teams
Embed Charts into Forms
Expand the Email Description Field in Dynamics CRM
Make a subgrid “+” button launch a new record form
Make the SLA timers change colour
Manage Auto-Save
Pass Custom Parameter with query string
SharePoint Integration
Use Hyperlinks & Images in KB Articles

All you want to know about Auto-Save
Capture a signature in a CRM form using html5
Change Navigation Bar Colours and Sandbox Text (Dynamics CRM On-premise Sandbox Instances)
Colorizing Service Calendar
Color Code Your Calendar for Tracked Appointments in Microsoft Dynamics CRM
Create Lookup Attributes
Data Encryption
Data Migration Tips
Disable Notes Section
Disable Welcome Screen
Display a CRM Dashboard in an HTML Page
Enable Text Selection on CRM Web Resources in Dynamics CRM
Extend the 10000 Record Limit when exporting to Excel
Increase the performance of crm form
JavaScript Bookmark to Enter Debug Mode of CRM Form (Show Logical Names of the Attributes) in CRM 2011/2013
Logging and Handling Microsoft Dynamics CRM 2013 Exceptions (Part 1, Part 2)
Migrate to the cloud
Monitor CRM System
Move reports to new SSRS server
Performance Report
Show Overlay Rather than open a new window
Test CRM for Tablets from a PC browser
Use Custom Actions as Storing the Value for Configuration
User-Owned or Organization-Owned Entities?
Utilize GUID’s for Importing Data to Multiple Orgs
What to Do with Unwanted Activity Types

Add a button on Form
Attach OnClick event in Sub grid Records Using JavaScript
Block click-to-call in CRM
Bookmarklets to aid Microsoft Dynamics CRM 2013 Development
Change color for text fields
Change Grid view background color
Create dialog windows
Disabling all fields
Dynamically Set the Document Location with JavaScript
Filter data grid values
Filter Lookup using addCustomFilter
Form Notification With Timeout
jQuery and Dynamics CRM 2013
Hide Post Tab in Social Pane
Highlight lookup Border in case of error
Leverage Actions to get around JavaScript cross-domain challenges
Lock Read-only users from Form ribbon click
Manage Auto-Save for individual forms of entity
Manipulate Look up to show Multiple Entity Record
Open Dialog box like CRM 2013
Open Entity Form in new Window
Open pre-populated child record form
Populate the Email “FROM” field with the Email address of the user’s Queue
Put Icons in Sub-grids
Read SubGrid Records using JavaScript
Regex for JavaScript mobile phone validation
Retrieve image using JS and ODATA
Retrieve Single Record with ODATA in CRM with Java Script
Retrieving Multiple Records with ODATA in CRM with Java Script
Retrieve record using OData/REST endpoint without jQuery
Script Loading Deep Dive
Show your dialog in CRM 2013 modal style
Simulate OnLoad Event for Form Updates
Update Entity Image using Rest endpoint without jQuery

Portable Business Logic (PBL)
Business Rule Scope Defaults
Create If Then Else Logic in Business Rules
Hide a Field with Portable Business Logic
Hide a Tab using Portable Business Logic
Understanding Portable Business Logic
Show Language specific error messages using Business Rules/PBL

Create folder in sharepoint site from CRM record via plugin
Create word file from html and send it as attachment
Difference between setting OwnerId and AssignRequest in Create Plugin
Download the attachment of a note within a plugin
Generate SSRS Report as PDF Using Plugin
Trigger Plugin from Ribbon Button Using Custom Actions
Update Opportunity on 'Close as Won' or 'Close as Lost'
Use of Shared Variables in Plugin

Automatically Triggered Background Workflow Processes Scope
Execute a Workflow Upon Change of Business Process Flow Stage in Dynamics CRM
Reminder Workflows Done Right

Access "Special" Activity Data with CRM Report Wizard
Audit Report in SSRS
Convert Dynamics CRM Entity GUID in SSRS
CRM Report Scheduler
Right and Centre Align Images in a Table
Use SSRS To Show The Report Hierarchy (Part 1, Part 2)

Sample Codes
.NET Sample Codes
Add Notes Attachment in Email
Attach SSRS Report in Email Attachments
Change process and stage programmatically
Create Custom Actions
Create Email
Duplicate Detection for Qualify a lead to an Account
Get Business Closure Dates
Get label and value for OptionSet in CRM
Read all Notes having attachment related to case entity
Usage of Custom Actions in .NET and JavaScript

JavaScript Sample Codes
Basic JavaScript Reference
Clone/Copy Existing Record by Auto Populating Values without Saving the Record
Get the Object Type Code of an EntityPrevent a record from being saved
Lock Form using script along with locked panel in footer
Refresh Form using script
Set Unresolved Email Id's in party List from Java Script
Show Business Process Area expanded on form
SOAP Authentication to CRM Online using JavaScript
SOAP Authentication to CRM On Premise (ADFS) using JavaScript
Usage of Custom Actions in .NET and JavaScript


Third Party Libraries
Bootstrap and Dynamics CRM 2013
JqGrid Theme
Resco javascript Fetch all account example
Simple Dynamics CRM Lookup using Kendo UI

Unsupported Customizations
Change UI, CSS and Navigation Bar

Configuration Migration Tool
Custom Code Validation Tool (Download)
CRM DevTools (Chrome Extension)
CRM Early Bound Generator (CodePlex)
Dynamics CRM 2013 Easy Solution Importer
EasyPlugins - Setting up Plugins in Graphical Way (CodePlex)
Legacy Code Checker Tool (Download)
Local to Global Option Set Converter (CodePlex)
Migrating Images from Notes to EntityImage (XrmToolBox Plugin)
PFE CRM Trace Tool
Security Role Browser (CodePlex)Trace Reader (CodePlex)

Demian_Rasko's Toolbox for Dynamics CRM 2011/2013 (CodePlex)
  • Currency Exchange Rates

MSCRM ToolKit (CodePlex)

  • Reference Data Transporter
  • N:N Associations Transporter
  • Data Export Manager
  • Export Entities Structure
  • Deployment Properties
  • Solutions Transporter
  • Solutions Import Jobs Viewer
  • Workflow Execution Manager
  • Records Counter
  • Audit Export Manager

XrmToolBox (CodePlex)
  • Access Checker
  • Assembly Recovery Tool
  • Attribute Bulk Updater
  • Audit Center
  • Easy Translator
  • FetchXml Tester
  • Form Libraries Manager
  • Form Parameter Manager
  • Iconator
  • Metadata Doc Generator
  • Privileges Discovery
  • Role Updater
  • Script Finder
  • SiteMap Editor
  • Solution Import
  • Solution Transfer Tool
  • Sync Filter Manager
  • View Layout Replicator
  • Web Resources Manager

Dev VM and Connectivity(Hyper-V)
Install Email router
Installation steps of Microsoft Dynamics CRM 2013
Setting up a Microsoft Dynamics CRM 2013 development server on Windows Azure
Setting up Visual Studio with the Developer Toolkit for Microsoft Dynamics CRM
Update Plugin Tutorial using the CRM 2013 Development Toolkit

Solution Samples
Add Title and Case Number to the Subject Line of an Email sent from CASE entity
Automated Deployment of Solutions and Data Automatically (Application Lifetime Management Part 1, Part 2, Part 3, Part 4)
Dashboard Samples
Display Microsoft CRM records with no recent Activities
Field Inspection for Dynamics CRM 
Financial Services Demo
Find Open Opportunities with NO Activities (without having to use FetchXML)
Measure KPI’s with a Custom Entity (Video Tutorial)
Set Ticket Aging with Workflow
Simple Approval Process

Workaround Solutions
Context Sensitive Sub-Grid Commands (SubGrid to Associated View button missing)
CRM will not work if IE Add-on blocking is enabled via Group policy
Custom Code creating a report as a PDF attachment in SP1
Custom Filter Error There was an error processing your request
Dynamics CRM service accounts when Reports created by report wizard fail during execution
Field that has an enabled event is disabled in bulk edit
rsProcessingAborted Error Fix: Setting up Service Principal Names SPN values for Microsoft

Real Time Workflow or Plugin?

CRM Book - PowerObjects

MB2-703 – Business Units and Security Roles Study Information
MB2-703 – Customization and Configuration Certification Solution Exam Cram Notes

Installation and Configuration
Required Components Direct download links
Setup Document Management with SharePoint Online and Microsoft CRM Online (2013)

Integration with Others
Card Scanner in Dynamics CRM
Conditional Formatting In Outlook for Dynamics CRM Data
Microsoft SharePoint 2013/2010 Document Management Integration (Part 1, Part 2, Part 3)
Simple CRM Lead Capture using Azure & Web API
Stop Automatic CRM to Outlook Syncing for Contacts
Use Google Maps in Dynamics CRM
Yammer Configuration On-Premise

Update Rollups and Service Packs
Update Rollup 1 (Build Number 6.0.0001.0061)
Update Rollup 2 (Build Number 6.0.0002.0046)

Service Pack 1 (Build Number 6.1.0000.0581)

Useful Libraries
CSV Helper

Fiddler2: The tool that gives you Superpowers – Part 1, Part 2, Part 3, Part 4
JavaScript Fundamentals for Absolute Beginners

Tuesday, 30 June 2015

Async Workflows and System Jobs Stop Working and Stuck with Status Reason "Waiting for Resources"

Today, I encountered one issue at the client site where none of the asynchronous workflows are working any more. Any new workflow triggered on-demand or automatically by the event just go straight into the "Waiting for Resources" status.

I tried practically everything that I've heard about the solution for the async workflow issues.
Restarted the Microsoft Dynamics CRM Asynchronous Processing Service
Restarted the rest of the CRM services
Restarted the SQL Server service
Restarted both CRM application server and database server
Went through several blog posts and forum thread related to that issue. (like thisthis, this, this and this)

The earliest time of the system jobs being stuck in that status was also some how coincident with the server restart after Windows security update on last Sunday so that we tried to uninstall those updates as well.
We also encountered the following error message when we try to restart the async service "Error 1053: The service did not respond to the start or control request in a timely fashion" so that we even thought about installing async service in another machine as a server role.

And finally, I've found the root cause of the issue after trying out different things.


Symptom 1

When you stop or restart a Microsoft Dynamics CRM Asynchronous Processing Service, the process of stopping or restarting the service takes more time than the default configured time, you receive the following error message:
Could not stop the Microsoft Dynamics CRM Asynchronous Processing Service on Local Computer.
Error 1053: The service did not respond to the start or control request in a timely fashion.

Symptom 2

Found a lot of Warning logs in the event viewer of CRM application server from the Source MSCRMPlatform with the following message.
Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 85; Database: OrgName_MSCRM; Server:DBServerName; Query: 
    DECLARE @res INT
    EXEC @res = sp_getapplock                 
                @Resource = 'AsyncService_app_lock',
                @LockMode = 'Exclusive'
    IF @res NOT IN (0, 1)
        RAISERROR ( 'Unable to acquire App Lock', 16, 1 )

ModifiedOn = '02/20/2015 13:41:04',
ModifiedBy = CreatedBy
StateCode = 2
and HostId = 'server.MSCRMAsyncService.737ea3a3-ca4d-4500-a0b0-f4b94f00e442'
EXEC @res = sp_releaseapplock 
                            @Resource = 'AsyncService_app_lock'
Note OrgName is a placeholder for the name of your organisation and DBServerName is a placeholder for the name of your database server.

Symptom 3

All of the async system jobs are stuck with Status Reason "Waiting for Resources" with a few jobs with "In Progress"


Some of the initial async system jobs are stuck with Status Reason "In Progress" due to SQL timeout while being processed. Those system jobs encountering SQL timeout were also not being changed into Failed so that the rest of the system jobs "Waiting for Resources" cannot be processed.


To resolve this problem, stop the MS CRM Async service. Then, all of the system jobs being stuck with Status Reason "In Progress" will be changed back to "Waiting for Resources". After that, patch the statecode and statuscode of those system job to "Canceled" based on the values in in the table below.
0 Waiting For Resources
10 Waiting
20 In Progress
21 Pausing
22 Canceling
30 Succeeded
31 Failed
32 Canceled
0 Ready
1 Suspended
2 Locked
3 Completed

(You may update the [Message] column with certain value in order to re-trigger those workflows at the later stage)
Here's the code that I used to patched for your reference.
 UPDATE AsyncOperationBase SET StateCode = 3 -- Completed  
 , StatusCode = 32 -- Canceled  
 , [Message] = 'WorkflowErrorPatched'  
 WHERE StatusCode = 20 -- In Progress  
 OR StatusCode = 0 -- Waiting For Resources  

After that, start the MS CRM Async service again and see if the workflows are running back again. If so, update the statecode and statuscode of those system job that we cancelled in the earlier step back to "Waiting for Resources" batch by batch in descending order of [CreatedOn] column since the initial ones are those system jobs which caused the SQL timeout issue. After changing them back to "Waiting for Resources", those normal ones will be processed and finally changed to "Succeded". And finally when you change the problematic ones back to "Waiting for Resources", all of the async system jobs will be stuck again just like the initial state.

Once you are able to pinpoint the affected async system jobs, just keep them in "Canceled" status and let the rest of the workflows complete.


For my issue, the affected workflows are those related to the note (annotation) records which has attachment files > 10MB. I'm not sure the size of the attachment file is anything to do with the simple On Create workflow which just update the flag of the case entity based. But at least we are able to identify the root cause of the issue and I'll update in this blog post if I happen to investigate further and find out the link between those SQL timeout workflows and the size of the attachment in the annotation record.