Month: November 2022

Four years ago I wrote a script which changed server and database names embedded within SQL views after a live Microsoft Dynamics GP company had been copied into a test one. This script is used by a client as one of several which are all run when they refresh live into test, but they also sometimes use it when copying databases between a live server and a test one.

They recently ran it when creating a new test server, but found afterwards that it hadn’t worked correctly. Or more accurately, it had worked correctly as it was created to do. However, the new SQL Server had a different Instance name than the original.

The script below is an update of the original which adds in a change of Instance alongside the existing server and company.

The three highlighted sections are for the old values which need to be changed; the new values are all determined automatically based on the database in which the script is run.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @ViewDefinition AS NVARCHAR(max)

DECLARE @OldServer AS VARCHAR(50) = '2018SQL1'
DECLARE @NewServer AS VARCHAR(50) = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(50))

DECLARE @OldDB AS VARCHAR(50) = 'FINANCE'
DECLARE @NewDB AS VARCHAR(50) = CAST(SERVERPROPERTY('InstanceName') AS VARCHAR(50))

DECLARE @OldCompany AS VARCHAR(5) = 'TWO'
DECLARE @NewCompany AS VARCHAR(5) = DB_NAME()

CREATE TABLE #ViewDefinitions(
	ViewDefinition NVARCHAR(MAX)
	,ROW_ID INT IDENTITY
)

INSERT INTO #ViewDefinitions
	(ViewDefinition)
--VALUES
	(SELECT
		REPLACE(
			REPLACE(['SQL Modules'].definition, 'CREATE VIEW', 'ALTER VIEW')
		,'Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany,'Db=' + @NewDB + '&Srv=' + @NewServer + '&Cmp=' + @NewCompany)
	FROM
		sys.all_views AS ['All Views']
	JOIN
		sys.sql_modules AS ['SQL Modules']
			ON
				['SQL Modules'].object_id = ['All Views'].object_id
	and
		['SQL Modules'].definition LIKE '%Db=' + @OldDB + '&Srv=' + @OldServer + '&Cmp=' + @OldCompany + '%')

DECLARE
	cursor_Views Cursor
FOR
	SELECT
		ViewDefinition
	FROM
		#ViewDefinitions
	
	Open cursor_Views

	FETCH NEXT FROM
		cursor_Views
	INTO
		@ViewDefinition
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			EXEC (@ViewDefinition)
			FETCH NEXT FROM
				cursor_Views
			INTO
				@ViewDefinition
		END
	CLOSE cursor_Views
DEALLOCATE cursor_Views

DROP TABLE #ViewDefinitions
GO

As always before using a script, make sure you understand wat it is going to do and also ensure you have a good backup.

A little while ago I did some work with a client who was migrating their Microsoft Dynamics GP VBA modifications into C# so that they could make more use of the web client. We had the C# code created, and the VBA-mess modified forms and reports loaded into Dynamics GP. The next step is to use dag.exe to create the managed code assembly which provides access to resources in an application dictionary.

On one of the machines we installed Dexterity in order to get access to dag.exe and then ran the command required to create the managed code assembly. Unfortunately, the following error was produced:

Unhandled exception: System.IO.FileNotFoundException: Could not load file and assembly 'Microsoft.Dynamics.GP.BusinessObjects, Version=18.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad64e35' or ne of its dependencies. The system cannot find the file specified.

It was only seeing the error message that I remembered having been told about this by the developer. The Microsoft.Dynamics.GP.BusinessObjects.dll file needs to be copied from the Dynamics GP install folder (typically %ProgramFiles(x86)%\Microsoft Dynamics GP) into the Dexterity install folder (typically %ProgramFiles(x86)%\Microsoft Dexterity\Dex 18.0).

Once this has been done, the dag.exe command can be run successfully.

I created the script in this post for a client a while ago who wanted to use workflow approvals for purchase orders, but didn’t quite understand how a workflow process should be built.

He needed approximately thirty different approvals and build a step for each approver, all correct so far. Unfortunately, he created them each as a single step under a separate workflow process instead of thirty steps under one workflow process. Asking him to manually recreate all of the steps correctly under one workflow process was not going to be feasible so I did a little table exploring and came up with the below script which will move all steps for a specified workflow type under a single specified workflow process.

The two highlighted parameters at the top are where you need to specify a valid workflow type and destination workflow process. because of how the client had named his workflow processes and steps this script will change the name and description of each step to be the workflow process name and step name concatenated together and separated with a hyphen.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @WorkflowType VARCHAR(50) = 'Purchase Order Approval'
DECLARE @WorkflowName VARCHAR(50) = 'PO Approval v1';

WITH WFUpdate AS(
	SELECT
		['Workflow Step Table'].Workflow_Name
		,['Workflow Step Table'].Workflow_Step_Name
		,ROW_NUMBER() OVER(ORDER BY ['Workflow Step Table'].Workflow_Name,['Workflow Step Table'].Workflow_Step_Sequence) * 10 AS ROWNUMBER
	FROM
		WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
	INNER JOIN
		WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
			ON
				['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
	WHERE
		['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType
	AND
		['Workflow Step Table'].Workflow_Step_Order = 0
)

UPDATE
	['Workflow Step Table']
SET
	Workflow_Name = @WorkflowName
	,Workflow_Step_Name = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].Workflow_Step_Name), 50)
	,WF_Step_Description = LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Description), 100)
	,Workflow_Step_Sequence = ISNULL(WFUpdate.ROWNUMBER, ['Workflow Step Table'].Workflow_Step_Sequence)
	,WF_Step_Predecessor = CASE WHEN LEN(['Workflow Step Table'].WF_Step_Predecessor) > 0 THEN
								LEFT(RTRIM(['Workflow Master - Step Parent'].Workflow_Name) + - + RTRIM(['Workflow Step Table'].WF_Step_Predecessor), 100)
							ELSE
								''
							END
FROM
	WF100003 AS ['Workflow Step Table'] -- Workflow Step Table (WF100003)
INNER JOIN
	WF100002 AS ['Workflow Master - Step Parent'] -- Workflow Master (WF100002)
		ON
			['Workflow Master - Step Parent'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
LEFT JOIN
	WFUpdate
		ON
			WFUpdate.Workflow_Name = ['Workflow Step Table'].Workflow_Name
		AND
			WFUpdate.Workflow_Step_Name = ['Workflow Step Table'].Workflow_Step_Name
WHERE
	['Workflow Master - Step Parent'].Workflow_Type_Name = @WorkflowType

As always with scripts, before you run the one above please make sure you understand what the script will do and have a good backup of your company database.

A common request from clients using Microsoft Dynamics GP workflow was a way to easily be able to review to whom workflow steps were assigned; this was often a request from auditors.

I produced a SQL view which could easily be deployed for use in any reporting tool (SmartList Designer or SmartList Builder, SSRS, refreshable Excel, etc.) and which returned who the step was assigned as well showing the predecessor step.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
CREATE VIEW uv_AZRCRV_GetWorkflowSetupStepAssignment AS
SELECT
       ['Workflow Step Instance Table'].Workflow_name
       ,['Workflow Step Instance Table'].WF_Step_Predecessor
       ,['Workflow Step Instance Table'].Workflow_Step_Name
       ,['Workflow Step Instance Table'].WF_Step_Description
       ,['Workflow Step Instance Table'].EmailMessageID
       ,['Workflow Users'].ADLogin
       ,['Workflow Users'].ADDisplayName
FROM
       WF100003 AS ['Workflow Step Instance Table']
LEFT JOIN
       WF40200 AS ['Workflow Users']
ON
       ['Workflow Step Instance Table'].Workflow_Step_Assign_To = ['Workflow Users'].UsersListGuid
GO

GRANT SELECT ON uv_AZRCRV_GetWorkflowSetupStepAssignment TO DYNGRP
GO