Category: GP (Great Plains)

During a recent upgrade of Microsoft Dynamics GP, the client supplied a link to be used for a repurposed Intelligent Edge section on the home page. However, once their Intranet site link had been added, users started getting a script error like the below:

Internet Explorer Script Error

An error has occurred in the script on this page
Line: 320

Char: 6

Error: The system cannot find the file specified
Code: 0
URL: file:///D:/Documents%20and%20Settings/gptestuser/Local%20Settings/Temp/1/tmp10.tmp
Do you want to continue running scripts on this page?

I wasn’t able to get a screenshot of the error at the time, and the above is taken from Microsoft KB article which covered this issue in Q31.

The solution is to create a UserData folder in each users %appdata%\Microsoft\Internet Explorer\. Once we did this, the error did cease to be displayed.

I recently did a Microsoft Dynamics GP upgrade for a client which included Management Reporter. During the upgrade of Management Reporter to the latest hotfix, we found that the upgrade of the legacy connector failed every time. We spent quite a bit of time looking into the problem and eventually got it to install correctly.

When legacy connector was first added, it was added using the FQDN of the SQL Server Instance (SQL01.example.com\GP), the upgrade failed every time we tried to use the FQDN, but when we switched to just the machine name (SQL01\GP), the upgrade was successful.

Management Reporter has been used by this client since the release of the 2012 version and it has been upgraded previously without issue, so I am unsure of what had changed in this version.

That was not the end of the story though. When I tested Management Reporter was working fine, it was. However, I’d logged in using the sa account. When users started logging in they were finding that they were unable to connect to the GP company database:

I’d seen this error message before and it is because the System DSN used by Dynamics GP was using the FQDN, but now the legacy connector in Management Reporter i using the server name so the password encryption will be different.

I didn’t want to remove the legacy connector and try to install it again as I was pretty sure we’d have the same problem as previously. Instead I decided to have a go at updating the connection used by Management reporter which is stored in the Reporting.ControlCompany table and which I noted I’d done in the previous bog post.

However, I didn’t post the SQL I’d used, so I needed to recreate it. The two highlighted sections are the server name which needed to be changed and to what:

/*
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).
*/
UPDATE
	Reporting.ControlCompany
SET
	GLEntityConnectionInformation = REPLACE(CAST(GLEntityConnectionInformation AS VARCHAR(8000)), 'SQL01\GP', 'SQL01.example.com\GP')
GO

Obviously, take a backup of the database before running the script and test that it has worked correctly before allowing users back into Management Reporter.

I’ve been working with a client to create a new standalone Microsoft Dynamics GP test system. We copied the databases to the new SQL Server and were progressing through several tasks making sure everything was aligned and working correctly as they have a number of add-ins which needed to be installed.

One user testing the purchasing module encountered an error when trying to transfer a purchase requisition to purchase order:

The error was produced because the details for the Reporting Services Reports in Reporting Tools Setup had not been updated and the reports deployed; the links were still to the live server SSRS implementation which was inaccessible to the new sandboxed test environment.

Once the links were updated and redeployed the error went away.

I’ve previously posted a script which could be used to update emails in Microsoft Dynamics GP from a tab delimited text file. I’ve recently been working on a project with a client and created a variation on that script which works only for vendor emails, but validates the data before inserting or updating to ensure that the data is valid.

The original script took it on trust that the data was correct, but this tie we had some concerns about the quality of data being provided so I added validation to ensure the vendor and vendor address both existed before any data was inserted or updated; if there were any errors found, the errors were presented to the user to fix and then rerun the script.

The highlighted section is the path to the text file being uploaded; regardless of where you are running SSMS, this path and file must exist on the SQL Server itself.

/*
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 TEMP ERROR TABLE
*/
CREATE TABLE #Errors
	(
		Error VARCHAR(1000)
		,ROW_ID INT IDENTITY
	)
GO

/*
	CREATE TEMP TABLE FOR DATA
*/
CREATE TABLE #SY01200_IMPORT
	(
		VENDORID VARCHAR(100)
		,ADRSCODE VARCHAR(100)
		,EmailToAddress VARCHAR(1000)
		,EmailCcAddress VARCHAR(1000)
		,EmailBccAddress VARCHAR(1000)
	)
GO

/*
	BULK INSERT
*/
BULK INSERT
	#SY01200_IMPORT
FROM
	'c:\temp\email.txt'
WITH
	(
		FIELDTERMINATOR = '\t'
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

/*
	VALIDATE DATA
*/
-- validate creditors
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Vendor does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100))
		FROM
			#SY01200_IMPORT AS SYI
		LEFT JOIN
			PM00200 AS ['PM Creditor Master File']
				ON
					['PM Creditor Master File'].VENDORID = SYI.VENDORID
		WHERE
			['PM Creditor Master File'].VENDORID IS NULL
	)
GO

--validate creditor addresses
INSERT INTO #Errors
	(Error)
	
	(
		SELECT DISTINCT
			'Vendor address does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) + ' / ' + CAST(SYI.ADRSCODE AS VARCHAR(100))
		FROM
			#SY01200_IMPORT AS SYI
		LEFT JOIN
			PM00300 AS ['PM Creditor Addresses']
				ON
					['PM Creditor Addresses'].VENDORID = SYI.VENDORID
				AND
					['PM Creditor Addresses'].ADRSCODE = SYI.ADRSCODE
		WHERE
			['PM Creditor Addresses'].VENDORID IS NULL
	)
GO

/*
	UPDATE EXISTING DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	UPDATE
		SY
	SET
		SY.EmailToAddress = ISNULL(SY_I.EmailToAddress, '')
		,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '')
		,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '')
	FROM
		SY01200 SY
	INNER JOIN
		#SY01200_IMPORT AS SY_I
			ON
				SY_I.VENDORID = SY.Master_ID
			AND
				SY.Master_Type = 'VEN'
			AND
				SY.ADRSCODE = SY_I.ADRSCODE
GO

/*
	INSERT NEW DATA IF NO ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) = 0
	INSERT INTO SY01200
		(
			Master_Type
			,Master_ID
			,ADRSCODE
			,EmailToAddress
			,EmailCcAddress
			,EmailBccAddress
			,INETINFO
		)

		(
			SELECT
				'VEN'
				,VENDORID
				,ADRSCODE
				,ISNULL(EmailToAddress, '')
				,ISNULL(EmailCcAddress, '')
				,ISNULL(EmailBccAddress, '')
				,''
			FROM
				#SY01200_IMPORT 
			WHERE
				(SELECT
					COUNT(Master_ID)
				FROM
					SY01200
				WHERE
					Master_Type = 'VEN'
				AND
					Master_ID = #SY01200_IMPORT.VENDORID
				AND
					ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0
				)
GO

/*
	OUTPUT ERRORS
*/
IF (SELECT COUNT(*) FROM #Errors) > 0
	SELECT Error FROM #Errors ORDER BY ROW_ID
GO

/*
	DROP TEMP TABLES
*/
DROP TABLE #SY01200_IMPORT
GO

DROP TABLE #Errors
GO

I try to do as much through the front-end of Microsoft Dynamics GP as possible in order that the correct business logic be applied to the changes as possible. However, sometimes doing it that way takes far too much time. Recently when working with a particular client, we needed to assign all bins to all items.

Bins were being being introduced so there were no bins currently assigned, which meant a simple SQL script could be created using CROSS JOIN which produces a resultset where each item umber in the Item Master (IV00101) table was paired with the location code/bin number combination in the Site Bin Master (IV40701) table and inserted into Item Site Bin Priorities (IV00117).

/*
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).
*/
INSERT INTO IV00117 --Item Site Bin Priorities (IV00117)
	(
		ITEMNMBR
		,LOCNCODE
		,Priority
		,BIN
		,MINSTOCKQTY
		,MAXSTOCKQTY
	)
--VALUES
	(
		SELECT
			['Item Number'].ITEMNMBR
			,['Site Bin Master'].LOCNCODE
			,ROW_NUMBER() OVER(PARTITION BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE ORDER BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE, ['Site Bin Master'].BIN) AS Priority
			,['Site Bin Master'].BIN
			,0 AS MINSTOCKQTY
			,0 AS MAXSTOCKQTY
		FROM
			IV00101 AS ['Item Number'] --Item Master (IV00101)
		CROSS JOIN
			IV40701 AS ['Site Bin Master']--Site Bin Master (IV40701)
	)
GO

If any assignments exist in the IV00117 table the script will fail with a duplicate key error; the script also assigns priority based on the alphabetical order of the site/bin number combinations which may not be suitable in all contexts.

I’ve been involved with a number of upgrade projects recently where we were both upgrading the client to the latest version of Microsoft Dynamics GP, but also introducing some new functionality such as workflow for approvals.

Quite a few of these clients are long time users of Dynamics GP who started on version 9 or before. This means that in most cases, when introducing workflow, we find that the Not Submitted and Pending Approval navigation lists don’t work. By this, I mean they do not display any data.

This issue was covered back in 2017 by Derek Albaugh on the Dynamics GP Support and Services Blog.

The blog post includes scripts to clear down the List View Master (SY07220) and List View Filters (SY07230) tables whether you have custom navigation lists or not. These tables are like the Menu Master (SY07110) table which repopulate when you log back into Dynamics GP.

For most of the clients we have found that even if there are custom navigation lists, we need to delete the contents of the two tables and recreate the customisations rather than removing only the defaults, as Dynamics GP crashes whenever we tried to access a navigation list after removing the defaults.