The Dynamics GP support team has seen an increase in cases regarding the following error when working with Workflow 2.0:

 

[Microsoft][SQL Server Native Client 10.0][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate “GetAssignedUsers”: System.IO.FileLoadException: Could not load file or assembly ‘System.DirectoryServices,Vers

 

As noted by my colleague Dan in a past article, the error usually occurs after moving the GP databases to a different SQL instance. In most cases, this can be resolved by running the wfDeployCLRAssemblies stored procedure.

However, there may be instances where you need to drop all procedures, functions and assemblies deployed by the wfDeployCLRAssemblies stored procedure. As some customers may feel uncomfortable dropping those objects themselves, the support team has developed scripts to automate the task.

A few items to note before running the following scripts:

  1. **Ensure you have a viable, working database backup before proceeding with any scripts**

  2. The 2015 and 2016 versions of the scripts will work with 2015 R2 and 2016 R2, respectively.

  3. The scripts are provided AS IS

  4. You will need to execute the wfDeployCLRAssembles stored procedure again after running the scripts, which will redeploy the procedures, functions and assemblies.

                                              

If the error still occurs after running the script and the wfDeployCLRAssemblies stored procedure, please open a support case for assistance.

 

GP 2016:

/*Begin_GP2016dropAllwfObjectsandAssemblies*/

  IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetAssignedUsers')
        DROP PROCEDURE [GetAssignedUsers];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'SendWorkflowAssignmentEmail')
        DROP PROCEDURE [SendWorkflowAssignmentEmail];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'SendWorkflowCompletionEmail')
        DROP PROCEDURE [SendWorkflowCompletionEmail];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'IsValidUserByObjectGuid')
        DROP FUNCTION [IsValidUserByObjectGuid];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'IsValidUserByUser')
        DROP FUNCTION [IsValidUserByUser];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetUserByObjectGuid')
        DROP FUNCTION [GetUserByObjectGuid];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetObjectGuidByUser')
        DROP FUNCTION [GetObjectGuidByUser];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'TestEmail')
        DROP PROCEDURE [TestEmail]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine')
        DROP ASSEMBLY [Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.IdentityModel.Clients.ActiveDirectory')
        DROP ASSEMBLY [Microsoft.IdentityModel.Clients.ActiveDirectory];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Azure.ActiveDirectory.GraphClient')
        DROP ASSEMBLY [Microsoft.Azure.ActiveDirectory.GraphClient];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Newtonsoft.Json')
        DROP ASSEMBLY [Newtonsoft.Json];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.Web')
        DROP ASSEMBLY [System.Web]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.IdentityModel')
        DROP ASSEMBLY [System.IdentityModel]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.Runtime.Serialization')
        DROP ASSEMBLY [System.Runtime.Serialization]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.DirectoryServices')
        DROP ASSEMBLY [System.DirectoryServices]

IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Dynamics.GP.DocAttachEngine')
        DROP ASSEMBLY [Microsoft.Dynamics.GP.DocAttachEngine] /*End_GP2016dropAllwfObjectsandAssemblies*/


 
GP 2015:

/*Begin_GP2015dropAllwfObjectsandAssemblies*/
  
  IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetAssignedUsers')
        DROP PROCEDURE [GetAssignedUsers];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'SendWorkflowAssignmentEmail')
        DROP PROCEDURE [SendWorkflowAssignmentEmail];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'SendWorkflowCompletionEmail')
        DROP PROCEDURE [SendWorkflowCompletionEmail];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'IsValidUserByObjectGuid')
        DROP FUNCTION [IsValidUserByObjectGuid];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'IsValidUserByUser')
        DROP FUNCTION [IsValidUserByUser];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetUserByObjectGuid')
        DROP FUNCTION [GetUserByObjectGuid];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'GetObjectGuidByUser')
        DROP FUNCTION [GetObjectGuidByUser];

      IF EXISTS (SELECT [NAME]
                 FROM   SYSOBJECTS
                 WHERE  [NAME] = 'TestEmail')
        DROP PROCEDURE [TestEmail]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine')
        DROP ASSEMBLY [Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.IdentityModel.Clients.ActiveDirectory')
        DROP ASSEMBLY [Microsoft.IdentityModel.Clients.ActiveDirectory];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Azure.ActiveDirectory.GraphClient')
        DROP ASSEMBLY [Microsoft.Azure.ActiveDirectory.GraphClient];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Newtonsoft.Json')
        DROP ASSEMBLY [Newtonsoft.Json];

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.Web')
        DROP ASSEMBLY [System.Web]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.IdentityModel')
        DROP ASSEMBLY [System.IdentityModel]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.Runtime.Serialization')
        DROP ASSEMBLY [System.Runtime.Serialization]

      IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'System.DirectoryServices')
        DROP ASSEMBLY [System.DirectoryServices]

IF EXISTS (SELECT [NAME]
                 FROM   sys.ASSEMBLIES
                 WHERE  [NAME] = 'Microsoft.Dynamics.GP.DocAttachEngine')
        DROP ASSEMBLY [Microsoft.Dynamics.GP.DocAttachEngine] /*End_GP2015dropAllwfObjectsandAssemblies*/



GP 2013 R2:

/*Begin_GP2013R2dropAllwfObjectsandAssemblies*/

USE [Dynamics]
GO

/****** Object:  UserDefinedFunction [dbo].[IsValidADUser]    Script Date: 08/28/2014 09:13:32 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IsValidADUser]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[IsValidADUser]
GO


USE [Dynamics]
GO

/****** Object:  UserDefinedFunction [dbo].[GetDomainAliasByOjbectGuid]    Script Date: 08/28/2014 09:13:45 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetDomainAliasByOjbectGuid]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetDomainAliasByOjbectGuid]
GO


USE [Dynamics]
GO

/****** Object:  UserDefinedFunction [dbo].[GetObjectGuidByDomainAlias]    Script Date: 08/28/2014 09:13:58 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetObjectGuidByDomainAlias]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetObjectGuidByDomainAlias]
GO


USE [Dynamics]
GO

/****** Object:  StoredProcedure [dbo].[GetAssignedUsers]    Script Date: 08/28/2014 09:14:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAssignedUsers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAssignedUsers]
GO


USE [Dynamics]
GO

/****** Object:  StoredProcedure [dbo].[SendWorkflowAssignmentEmail]    Script Date: 08/28/2014 09:15:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SendWorkflowAssignmentEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SendWorkflowAssignmentEmail]
GO


USE [Dynamics]
GO

/****** Object:  StoredProcedure [dbo].[SendWorkflowCompletionEmail]    Script Date: 08/28/2014 09:15:24 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SendWorkflowCompletionEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SendWorkflowCompletionEmail]
GO


USE [Dynamics]
GO

/****** Object:  StoredProcedure [dbo].[TestEmail]    Script Date: 08/28/2014 09:15:38 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestEmail]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestEmail]
GO

USE [Dynamics]
GO
DROP ASSEMBLY [Microsoft.Dynamics.GP.WorkflowGP.WorkflowEngine]
GO
USE [Dynamics]
GO
DROP ASSEMBLY [System.DirectoryServices]
GO

/*End_GP2013R2dropAllwfObjectsandAssemblies*/