A client reported an urgent support call a short time ago that they could not post any journals and were receiving this error when they tried:

This error was produced for all users in only one of their 10+ companies; the other companies could have a journal posted by anyone with no issues. My initial question was to ask what had been changed or deployed in that database, but was told nothing. This meant some investigation.

I fairly quickly found a post from Tim Wappat covering how SQL indexed views are incompatible with Dynamics GP and will result in the exact error the client was seeing. this would make sense as the error referenced indexed views.

I used my find all SQL objects script to look for any object containing the SCHEMABINDING keyword, and found two SQL views in that one database.

After passing this information back to the client they again checked around and found a developer had deployed two SQL views for a report to the database first thing that morning; they hadn’t mentioned them as they didn’t expect them to have caused the problem. Once the views were updated to remove the index, the error was no longer produced when journals were posted.

It seems that while I’ve posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I’ve never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
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 TABLE #Segments
	(
		SGMTNUMB VARCHAR(100)
		,SGMNTID VARCHAR(100)
		,DSCRIPTN VARCHAR(100)
	)
GO

BULK INSERT
	#Segments
FROM
	'C:\Integrations\COA\Segments.txt'
WITH
	(
		FIELDTERMINATOR = '\t'
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

UPDATE
	Segments
SET
	Segments.SGMTNUMB = NewSegments.SGMTNUMB
	,Segments.SGMNTID = NewSegments.SGMNTID
	,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31)
FROM
	GL40200 AS Segments
INNER JOIN
	#Segments AS NewSegments
		ON
			NewSegments.SGMTNUMB = Segments.SGMTNUMB
		AND
			NewSegments.SGMNTID = Segments.SGMNTID
GO

INSERT INTO GL40200
	(
		SGMTNUMB
		,SGMNTID
		,DSCRIPTN
	)
--VALUES
   (
		SELECT
			SGMTNUMB
			,SGMNTID
			,LEFT(DSCRIPTN, 31)
		FROM
			#Segments AS NewSegments
		WHERE
			(
				SELECT
					COUNT(*)
				FROM
					GL40200 AS Segments
				WHERE
					Segments.SGMTNUMB = NewSegments.SGMTNUMB
				AND
					Segments.SGMNTID = NewSegments.SGMNTID
			) = 0
	)
GO

DROP TABLE #Segments
GO

As always with a SQL script which makes changes, I’d make sure you have a good backup and test the script before running it in case you encounter problems.

I was doing some training on Management Reporter for a client the other week and had an issue to follow up on. The issue was around multicurrency conversion which wasn’t working correctly. When doing some testing around the issue, I was able to reproduce the same currency error the client had encountered:

The currency did exist and on most reports was working fine. It took me a little investigation and research to find that this was an issue with the Data Mart connector; the reports using the legacy connector were working correctly.

This issue is covered in Microsoft KB Article 3058400; when using the Data Mart connector, currency lookups are done using the ISO Code of the Currency rather than the Currency Code itself.