Business Process Flow Disappears after December Update in Microsoft Dynamics CRM

Microsoft Dynamics CRM is now part of Microsoft Dynamics 365 business solution. For on-premise customers, your CRM will be rebranded with December Update. December Update also brought some new cool function which will improve users and administrators productivity (like an editable grid). As a part of these changes, Microsoft created a completely new editor for Business Process Flows. I really appreciate two things:

1.It is much easier to edit complicated Business Process Flows.
2.You can run specific Workflow for each flow stage.

However, we experienced an error after an update of our development environment. Some of our flows were somehow damaged during the update. After the update, the flows were not visible on entities forms. When I run the “Switch Process” command, I saw an error. I dug in logs to get more information about the error, but I found only Unexpected Exception messages. It was also impossible to deactivate the flows or to export them as a part of a solution. Finally, I was able to run the “Save as” command to create a copy of the flows. I set one of the copies as the highest priority process (using the “Order Process Flow” command). Then a new record for the entity was created successfully with the flow active on its form.

I have found the final solution in a database. I found out primary keys of the old “damaged” flow and its working copy and I run the following SQL command to compare them.

SELECT *
FROM [yourorgname_MSCRM].[dbo].[Workflow]
WHERE workflowid = 'D9CB57B0-90CF-488A-8EFB-1244DC64B6E4' or
workflowid = '5BCFB62B-A7FC-43A8-8050-09A69704F094'


I found out that the old flow has NULL values in UniqueName and BusicessProcessTypes column. The new flow had zero in the BusinessProcessType column and the UniqueName column value consisted of “new_bpf_” prefix and flow WorkflowId. So I run following SQL command. After running it, the flow started to work again.

update [yourorgname_MSCRM].[dbo].[Workflow] set businessprocesstype = 0,
UniqueName = 'new_bpf_D9CB57B090CF488A8EFB1244DC64B6E4'
where workflowid = 'D9CB57B0-90CF-488A-8EFB-1244DC64B6E4'
This is the result of my command.

flow-1

If you are new in Microsoft Dynamics, you may not know how to get the WorkflowId. So you can use the following SQL command.

SELECT [ProcessIdName]
,[OwnerIdType]
,[PrimaryEntityTypeCode]
,[ProcessId]
,[ProcessStageId]
,[StageCategory]
,[StageName]
FROM [yourorgname_MSCRM].[dbo].[ProcessStage]
Then select a value from the ProcessId column. You will see the flow with all its stages so you will be completely sure that you selected the right value. Of course, for one process all rows have the same ProcessId value.

Update: Solution Provided by Microsoft

Microsoft Support has provided us a solution for this problem.The solution has following steps.

First step: Find all business flows wich are affected by this incident. The business flows have a null value in the BusinessProcessType column. Save the result of this query, especially the first column, because you will need this at step three.

-- 1. Identify workflows

SELECT [Name],BusinessProcessType,UniqueName,*
FROM [dbo].[WorkflowBase] W
WHERE Category=4
AND BusinessProcessType is null
AND StatusCode=2

Second step: Run the following update query. This query changes the value of the BusinessProcessType column from null to 0.

UPDATE W SET BusinessProcessType=0, ModifiedOn=getutcdate()
FROM [dbo].[WorkflowBase] W
WHERE Category=4
AND BusinessProcessType is null
AND StatusCode=2

Step three: Deactivate and then activate again all business flows which were listed in the result of the first query.

Fourth step: Run the following script. This SQL script was provided by Microsoft. This script performs the data migration which the updated process failed to accomplish.


 -- 4. "DataMigrationforBusinessProcessFlows.sql"
/*************** Data Migration for Business Process Flows ***************/

IF OBJECT_ID('RetrieveEntityAttribute') IS NOT NULL
DROP FUNCTION RetrieveEntityAttribute
GO
--Function responsible for retrieving the corresponding BPF entity attribute related to the given EntityTypeCode
CREATE FUNCTION RetrieveEntityAttribute
(
       @EntityCode int,
       @TableName NVARCHAR(256)
)
RETURNS NVARCHAR(256)
AS
BEGIN
       -- The following logic provides the right column to insert the entity id into during data migration. E.g.: If the entity type code is 1,
       -- the EntityAttribute variable will have the value 'new_accoundId'
       DECLARE @EntityAttribute NVARCHAR(256);

       SELECT @EntityAttribute = MetadataSchema.Attribute.PhysicalName FROM MetadataSchema.Entity
                    JOIN MetadataSchema.Attribute ON MetadataSchema.Entity.EntityId = MetadataSchema.Attribute.EntityId
                    WHERE BaseTableName = @TableName AND ReferencedEntityObjectTypeCode = @EntityCode
                           AND MetadataSchema.Attribute.LogicalName <> 'createdby' AND MetadataSchema.Attribute.LogicalName <> 'createdonbehalfby'
                           AND MetadataSchema.Attribute.LogicalName <> 'modifiedby' AND MetadataSchema.Attribute.LogicalName <> 'modifiedonbehalfby'

       --If the attribute is not found, then we return NULL
       RETURN @EntityAttribute
END
GO

IF OBJECT_ID('DataMigrationForBusinessProcessFlows') IS NOT NULL
DROP PROCEDURE DataMigrationForBusinessProcessFlows
GO
--Procedure responsible for migrating the BPF instances to the new entity tables
CREATE PROCEDURE DataMigrationForBusinessProcessFlows
       @ProcessId UNIQUEIDENTIFIER,
       @TableName NVARCHAR(256)
AS
BEGIN

       /*************** Data Migration Phase 1 - Ranking the Different Types of Instances ***************/

       CREATE TABLE #SourceData (
       Entity1ObjectTypeCode int,
       Entity2ObjectTypeCode int,
        Entity3ObjectTypeCode int,
        Entity4ObjectTypeCode int,
        Entity5ObjectTypeCode int,
        GroupNumber int
       )

       INSERT INTO #SourceData (Entity1ObjectTypeCode,Entity2ObjectTypeCode, Entity3ObjectTypeCode, Entity4ObjectTypeCode, Entity5ObjectTypeCode,GroupNumber)
       SELECT DISTINCT Entity1ObjectTypeCode, Entity2ObjectTypeCode, Entity3ObjectTypeCode, Entity4ObjectTypeCode, Entity5ObjectTypeCode,
                    DENSE_RANK() over (order by Entity1ObjectTypeCode,Entity2ObjectTypeCode, Entity3ObjectTypeCode, Entity4ObjectTypeCode, Entity5ObjectTypeCode ) as GroupNumber
                           FROM BusinessProcessFlowInstanceBase WHERE ProcessId = @ProcessId

       /*************** Data Migration Phase 2 - Migrating the Data Based on the Ranks ***************/

       DECLARE @Count int;
       DECLARE @i int;
       DECLARE @InsertQueryColumns NVARCHAR(max);
       DECLARE @InsertQueryValues NVARCHAR(max);
       DECLARE @Entity1ObjectTypeCode NVARCHAR(100);
       DECLARE @Entity2ObjectTypeCode NVARCHAR(100);
       DECLARE @Entity3ObjectTypeCode NVARCHAR(100);
       DECLARE @Entity4ObjectTypeCode NVARCHAR(100);
       DECLARE @Entity5ObjectTypeCode NVARCHAR(100);
       DECLARE @EntityAttribute NVARCHAR(256);
       DECLARE @Name NVARCHAR(200);
       DECLARE @ProcessName NVARCHAR(200);
       DECLARE @SystemUserId UNIQUEIDENTIFIER;
       DECLARE @CurrentDate DATETIME;
       DECLARE @OrganizationId UNIQUEIDENTIFIER;

       --DisplayMask for DisplayMasks.PrimaryName | DisplayMasks.ValidForForm | DisplayMasks.ValidForGrid | DisplayMasks.ValidForAdvancedFind | DisplayMasks.RequiredForForm
       DECLARE @PrimaryNameDisplayMask int;
       SET @PrimaryNameDisplayMask = 256

       --The process name is a default attribute of the BPF entities. The attribute will have the format <solution prefix>_name.
       --That being said, the below query retrieves the right column name for the process name.
       SELECT TOP 1 @Name = MetadataSchema.Attribute.PhysicalName FROM MetadataSchema.Attribute
       JOIN MetadataSchema.Entity ON MetadataSchema.Entity.EntityId = MetadataSchema.Attribute.EntityId
       WHERE MetadataSchema.Entity.BaseTableName = @TableName AND ((DisplayMask & @PrimaryNameDisplayMask) > 0)

       /*Get the BPF Name from the Workflow Table. Get the Top 1 value since there will be same process name in
        Solution layering scenarios for same process */
       SELECT TOP 1 @ProcessName =  Name From dbo.WorkflowBase WHERE WorkflowId=@ProcessId

       --Get the Current Datetime in yyyy-mm-dd hh:mi:ss.mmm (24h) format to fill CreatedOn and ModifiedOn Fields
       SELECT @CurrentDate = CONVERT(DATETIME, GETUTCDATE(),121)

       --Get the SystemUser Id from SystemUserBase Table
       SELECT @SystemUserId = SystemUserId, @OrganizationId = OrganizationId FROM OrganizationBase

       /*As the BusinessProcessFlowInstanceBase table supports up to 5 entities, there'll be up to 24 possible ranks*/
       SELECT @Count = Count(*) FROM #SourceData;

       SET @i =1;
       WHILE @i<=@Count
       BEGIN
             SET @InsertQueryColumns = '';
             SET @InsertQueryValues = '';

             SET @Entity1ObjectTypeCode = NULL;
             SET @Entity2ObjectTypeCode = NULL;
             SET @Entity3ObjectTypeCode = NULL;
             SET @Entity4ObjectTypeCode = NULL;
             SET @Entity5ObjectTypeCode = NULL;

             SELECT @Entity1ObjectTypeCode = t.Entity1ObjectTypeCode,
                           @Entity2ObjectTypeCode = t.Entity2ObjectTypeCode,
                           @Entity3ObjectTypeCode = t.Entity3ObjectTypeCode,
                           @Entity4ObjectTypeCode = t.Entity4ObjectTypeCode,
                           @Entity5ObjectTypeCode = t.Entity5ObjectTypeCode
             FROM #SourceData t WHERE t.GroupNumber = @i

             IF (@Entity1ObjectTypeCode IS NOT NULL)
              BEGIN
                    SET @EntityAttribute = dbo.RetrieveEntityAttribute(@Entity1ObjectTypeCode,@TableName);
                    SET @Entity1ObjectTypeCode = '= ' + @Entity1ObjectTypeCode;
                    IF (@EntityAttribute IS NOT NULL)
                    BEGIN
                           SET @InsertQueryColumns = @InsertQueryColumns + @EntityAttribute + ',';
                           SET @InsertQueryValues = @InsertQueryValues + 'Entity1Id,';
                    END
             END
             ELSE
              BEGIN
                    SET @Entity1ObjectTypeCode = 'IS NULL';
             END

             IF (@Entity2ObjectTypeCode IS NOT NULL)
              BEGIN
                    SET @EntityAttribute = dbo.RetrieveEntityAttribute(@Entity2ObjectTypeCode,@TableName);
                    SET @Entity2ObjectTypeCode = '= ' + @Entity2ObjectTypeCode;
                    IF (@EntityAttribute IS NOT NULL)
                    BEGIN
                           SET @InsertQueryColumns = @InsertQueryColumns + @EntityAttribute + ',';
                           SET @InsertQueryValues = @InsertQueryValues + 'Entity2Id,';
                    END
             END
             ELSE
             BEGIN
                    SET @Entity2ObjectTypeCode = 'IS NULL';
             END

             IF (@Entity3ObjectTypeCode IS NOT NULL)
              BEGIN
                    SET @EntityAttribute = dbo.RetrieveEntityAttribute(@Entity3ObjectTypeCode,@TableName);
                    SET @Entity3ObjectTypeCode = '= ' + @Entity3ObjectTypeCode;
                    IF (@EntityAttribute IS NOT NULL)
                    BEGIN
                           SET @InsertQueryColumns = @InsertQueryColumns + @EntityAttribute + ',';
                           SET @InsertQueryValues = @InsertQueryValues + 'Entity3Id,';
                    END
             END
             ELSE
             BEGIN
                    SET @Entity3ObjectTypeCode = 'IS NULL';
             END

             IF (@Entity4ObjectTypeCode IS NOT NULL)
              BEGIN
                    SET @EntityAttribute = dbo.RetrieveEntityAttribute(@Entity4ObjectTypeCode,@TableName);
                    SET @Entity4ObjectTypeCode = '= ' + @Entity4ObjectTypeCode;
                    IF (@EntityAttribute IS NOT NULL)
                    BEGIN
                           SET @InsertQueryColumns = @InsertQueryColumns + @EntityAttribute + ',';
                           SET @InsertQueryValues = @InsertQueryValues + 'Entity4Id,';
                    END
             END
             ELSE
             BEGIN
                    SET @Entity4ObjectTypeCode = 'IS NULL';
             END

             IF (@Entity5ObjectTypeCode IS NOT NULL)
              BEGIN
                    SET @EntityAttribute = dbo.RetrieveEntityAttribute(@Entity5ObjectTypeCode,@TableName);
                    SET @Entity5ObjectTypeCode = '= ' + @Entity5ObjectTypeCode;
                    IF (@EntityAttribute IS NOT NULL)
                    BEGIN
                           SET @InsertQueryColumns = @InsertQueryColumns + @EntityAttribute + ',';
                           SET @InsertQueryValues = @InsertQueryValues + 'Entity5Id,';
                    END
             END
             ELSE
             BEGIN
                    SET @Entity5ObjectTypeCode = 'IS NULL';
             END

              IF (@InsertQueryColumns <> '')
             BEGIN
                      SET @InsertQueryColumns =SUBSTRING(@InsertQueryColumns,0,LEN(@InsertQueryColumns))
                      SET @InsertQueryValues =SUBSTRING(@InsertQueryValues,0,LEN(@InsertQueryValues))

                      SET @InsertQueryColumns = 'BusinessProcessFlowInstanceId,StateCode, StatusCode, '+ @InsertQueryColumns + ',' + @Name + ',ActiveStageId, TraversedPath, ProcessId, CreatedOn, CreatedBy'
                           +',CreatedOnBehalfBy,ModifiedOn, ModifiedBy, ModifiedOnBehalfBy, ActiveStageStartedOn, OrganizationId';

             DECLARE @SQL NVARCHAR(MAX), @Param NVARCHAR(MAX);

             SET @Param = N'@ProcessNameIN NVARCHAR(256),
                                        @CurrentDateIN DATETIME,
                                        @SystemUserIdIN UNIQUEIDENTIFIER,
                                        @ProcessIdIN UNIQUEIDENTIFIER,
                                        @OrganizationIdIN UNIQUEIDENTIFIER';

             SET @SQL = N' INSERT INTO ' + @TableName + ' ( ' + @InsertQueryColumns  + ' )

                    SELECT BusinessProcessFlowInstanceId,0,1,' + @InsertQueryValues + ', @ProcessNameIN ,CONVERT(VARCHAR(50),ProcessStageId), TraversedPath, CONVERT(VARCHAR(50),ProcessId), @CurrentDateIN, @SystemUserIdIN,
                    CONVERT(VARCHAR(50),CreatedOnBehalfBy), @CurrentDateIN, @SystemUserIdIN, CONVERT(VARCHAR(50), ModifiedOnBehalfBy), @CurrentDateIN, @OrganizationIdIN from BusinessProcessFlowInstanceBase

                    WHERE ProcessId = @ProcessIdIN AND Entity1ObjectTypeCode ' +  @Entity1ObjectTypeCode + ' AND Entity2ObjectTypeCode ' +  @Entity2ObjectTypeCode
                    + ' AND Entity3ObjectTypeCode ' + @Entity3ObjectTypeCode + ' AND Entity4ObjectTypeCode ' + @Entity4ObjectTypeCode + ' AND Entity5ObjectTypeCode ' + @Entity5ObjectTypeCode;

                    --Migrating instances to the BPF entity table
                    BEGIN TRY
                                  EXECUTE sp_executesql @SQL, @Param,
                                        @ProcessNameIN=@ProcessName,
                                        @CurrentDateIN=@CurrentDate,
                                        @SystemUserIdIN=@SystemUserId,
                                        @ProcessIdIN=@ProcessId,
                                        @OrganizationIdIN=@OrganizationId;
                    END TRY
                    BEGIN CATCH
                                  --In case of failure, we want to log the insert query used during data migration
                                  DECLARE @GenericErrorCode int = 50000;
                                  DECLARE @MSG NVARCHAR(max) = ' Error Message: ' + (SELECT ERROR_MESSAGE() as ErrorMessage) + ' Insert Query: ' + @SQL;
                                  THROW @GenericErrorCode, @MSG, 1;
                    END CATCH

             END
             SET @i = @i + 1;
       END

       /*************** Data Migration Phase 3 - Cleaning Up Auxiliary Table ***************/
       DROP TABLE #SourceData;

       /**************** Update BusinessProcessFlowInstance Table- Since these are new fields in Centaurus these will be null after upgrade ****************/
       SET @Param = N'@ProcessNameIN NVARCHAR(256),
                                        @CurrentDateIN DATETIME,
                                        @SystemUserIdIN UNIQUEIDENTIFIER,
                                        @ProcessIdIN UNIQUEIDENTIFIER';

       SET @SQL = 'UPDATE BusinessProcessFlowInstanceBase SET Name = @ProcessNameIN, CreatedOn = @CurrentDateIN,CreatedBy = @SystemUserIdIN,
                    ModifiedOn = @CurrentDateIN,ModifiedBy = @SystemUserIdIN, ActiveStageStartedOn = @CurrentDateIN WHERE ProcessId = @ProcessIdIN';

       EXECUTE sp_executesql @SQL, @Param,
                                        @ProcessNameIN=@ProcessName,
                                        @CurrentDateIN=@CurrentDate,
                                        @SystemUserIdIN=@SystemUserId,
                                        @ProcessIdIN=@ProcessId;
END
GO

/*************** Data Migration Phase 4 - Executing Data Migration ***************/
DECLARE @MajorBuildNumber INT, @MinorBuildNumber INT;
SELECT @MajorBuildNumber = MajorVersion, @MinorBuildNumber = MinorVersion FROM BuildVersion

-- Only Perform for broken Centaurus Orgs
IF ((@MajorBuildNumber = 8) AND (@MajorBuildNumber = 8 AND @MinorBuildNumber = 2))
BEGIN
       DECLARE ProcessCursor CURSOR FOR
             (SELECT DISTINCT WorkflowId, UniqueName FROM WorkflowBase WHERE Category = 4 AND BusinessProcessType = 0);

       OPEN ProcessCursor;
             DECLARE @WorkflowId VARCHAR(50), @UniqueName VARCHAR(256);
             FETCH NEXT FROM ProcessCursor INTO @WorkflowId, @UniqueName;

       WHILE @@FETCH_STATUS = 0
       BEGIN
             IF (@UniqueName IS NOT NULL)
             BEGIN
                    DECLARE @TableName VARCHAR(256);
                    --Migrating data for each business process flow
                    SELECT @TableName  = BaseTableName FROM MetadataSchema.Entity WHERE LogicalName = @UniqueName

                    --Execute the data migration only if the table was successfully created
                    IF (@TableName  IS NOT NULL)
                    BEGIN
                           --Bug 350098 - If the data migration fails for online orgs, the upgrade infrastructure will re-try this action.
                           --Thus, we need to clean up the BPF entity tables before trying it again.
                           EXEC ('DELETE FROM ' + @TableName )
                           EXEC DataMigrationForBusinessProcessFlows @WorkflowId ,@TableName;
                    END
             END
             FETCH NEXT FROM ProcessCursor INTO @WorkflowId, @UniqueName;
       END

       CLOSE ProcessCursor;
       DEALLOCATE ProcessCursor;

END

--Cleaning up procedures
DROP FUNCTION RetrieveEntityAttribute;
DROP PROCEDURE DataMigrationForBusinessProcessFlows;

As this is direct Database change l would recommend a database back up before implementation.