Error Posting Journals in One Microsoft Dynamics GP Company

Microsoft Dynamics GPA 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:

Error message
Insert failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on comp

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.

Insert Segments from Text File/CSV into Microsoft Dynamics GP

Microsoft Dynamics GPIt 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 a Dynamics GP Consultant (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.

Management Reporter Currency Does Not Exist Error

Microsoft Dynamics GPI 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:

Currency could not be found error message
Currency XXXX does not exist for company XXXX. No values will be returned.

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.

Deleting a Windows Service

WindowsI recently uninstalled an application to notice a few days later that the related service was still showing in the Services applet.

I’ve previously posted about adding and removing dependencies from a Windows service.

As with adding or removing dependencies, the SC command online program can be used to delete a service:

SC DELETE {service name}

Microsoft Dynamics GP Fall 2020 Release Documentation

Microsoft Dynamics GPAs of the October 2019 release, Microsoft Dynamics GP moved onto the Modern Lifecycle which offers continuous support and servicing, including bug fixes, new features and the latest tax updates. There will be one major release per year along with at last two hotfix releases through the year.

There are a number of resource pages available or updated for the Fall 2020 Release of Microsoft Dynamics GP which has recently been released.

The Microsoft Dynamics GP Directory holds a wealth of information and links which will be useful to you, sorted by version and learning by module.

In addition to the Microsoft resources, I have also done two series on the Fall 2020 Release which you may find useful:

SmartConnect Error: “The delete permission was denied on the object…”

Microsoft Dynamics GPA while back a client using SmartConnect reported an error running one integration; this is one of a set of newly created identical integrations running against a set of companies, but only one of them was failing.

The error produced was:

SmartConnect error
Task xxxx failed. Map run will end reporting failure. The DELETE permission was denied on the object 'xxxx', database 'xxxx', schema 'dbo'

As there was a reference to permssions, I assumed there would be a SQL issue; when looking through database permissions, I found that in one database the user account used by SmartConnect had the db_denydatawriter role membership assigned:

Database User permissions

Once this permission was removed, the integration ran successfully.

SmartConnect could not find data

eOne SolutionsI recently created an integration using SmartConnect for a client as I have many times before; however, when I came to test the integration, my source file was totally ignored and the integration returned an error that no file was found.

The integration was using a folder data source with an Excel template; nothing special and something which I have configured many times. I copied the template file into the source folder and tried the integration again and got the same result.

I asked another consultant on the team to take a look with me and neither of us could see a problem; everything looked correct. Then it occurred to me that the extension of the file was .XLSX which I would not expect to be a problem; but, we couldn’t see anything else, so changed the extension to .xlsx and tried the integration again.

It worked correctly. I didn’t expect the case of the file extension to cause a problem, but is something to definitely bear in mind in the future and to make sure clients are aware.

Missing Security Roles and Tasks After Upgrading to Microsoft Dynamics GP Fall 2020 Release

Microsoft Dynamics GPWhen upgrading Microsoft Dynamics GP, the security roles and tasks are not automatically updated; this is to avoid overwriting or causing problems with any customizations to these roles and tasks which might have been made. Instead Microsoft provide a set of scripts which you can choose to run to add the missing items.

I always recommend clients not directly amend the standard tasks, but make copies and amend the copy (this provides a way to roll back to the standard versions should there be a need).

The update scripts are available from the Dynamics GP Support and Services Blog and I have updated my links on the GP Missing Security Scripts page to link to that blog and the script directly; if you’re using an additional product such as Project Accounting, Enhanced Intrastat, VAT Daybook or HR & Payroll there are additional scripts on the blog post which you can run.