Hands On With Microsoft Dynamics GP 2016 R2: Web Services Runtime SQL Server Prerequisite

Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components; the index for this series can be found here.

I’ve encountered this particular issue around the prerequisites so rarely, that I usually forget to mention it.

When installing the Web Services for Microsoft Dynamics GP there is a step where the SQL Server Connection Information is entered. This is the server where the Dynamics Security data will be stored.

You will receive the following message when clicking Next if the Windows user account you’re logged in as, is not a SQL Server sysadmin:

Invalid SQL Information - The current user must be a member of the sysadmin SQL role.

Invalid SQL Information

The current user must be a member of the sysadmin SQL role.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: Web Services Runtime SQL Server Prerequisite”

Error Creating A New Company: Cannot Insert Duplicate Key In Object ‘dbo.SY60100’

Microsoft Dynamics GPOne of our clients was creating a new company recently when GP Utilities produced an error:

Microsoft Dynamics GP: Cannot insert duplicate key in object 'dbo.SY60100'

Microsoft Dynamics GP

[Microsoft][SQL Server Native Client 11.0]p[SQL Server]Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'. The duplicate key value is (sa, 114).

The client clicked the OK button and GP Utilities ran through to completion. However, when the user logged into Microsoft Dynamics GP using the sa account, the company was not available for selection.

This is when they called us asking for help.

When we connected in we found that the company did have a record in Company Master (SY01500), but did not have an entry in User-Company Access (SY60100).

The company was listed in the Delete Company window (Administration >> Utilities >> Delete Company). However when the company was deleted, the database remained behind.

I’ve had problems deleting databases this way before, so when I was called over, I directed my colleague to delete the database directly in SQL Server Management Studio and run the Clear Companies script available in Microsoft KB 855361.

A quick search for the error message, revealed a Microsoft Knowledge Base article covering this exact issue.

At some point, the system database’s owner had been changed away from DYNSA. Running the following script set the owner back to DYNSA, allowing the company to be created:

sp_changedbowner 'DYNSA'

Integration Manager Error: Cannot Open Database “GPLIV” Requested By The Login

Microsoft Dynamics GPWe’re in the middle of large upgrade project at the moment for a client and encountered an error when trying to run an integration.

This particular integration is an Account one, which has been extended with VB Script which checks to see if the segments exist, and if not, it inserts them. When a user tried to run the integration, they get the following error:

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
DOC 2 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
DOC 3 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
Integration Failed
Integration Results
    3 documents were read from the source query.
    3 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        3 failed to integrate.

I did some double checking and it turned out that the user who was doing the testing had two Domain accounts (one with full name and one with initial and surname) and were not using the one I had configured originally.

All of the other users I had worked with had been using Domain accounts of initial and surname, which was the one I had configured, but this particular user was using the account with their full name.

Adding this Domain user account to the database with the DYNGRP role resolved the problem.

List View Action Pane Error During Upgrade

Microsoft Dynamics GPWe have been doing a number of upgrades for clients recently, the majority of which went fine. However, we did have one, being done by one of my team, where there was an error produced on the table List View Action Pane when upgrading to Microsoft Dynamics GP 2015 R2:

Company Detail

Continue reading “List View Action Pane Error During Upgrade”

Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME'”

Microsoft Dynamics GPThis came up a few days ago at a client site during an upgrade being done by a colleague and then again when I was running a training session.

When I was doing the training I was explaining what a Temporary Creditor (or Vendor for the American readers) was and why I hated them so much. As I was talking I was showing that even through the temporary creditor itself had been deleted, the history for it remained.

It was when I tried to open the Payables Transactions navigation list that I got the below error:

Cannot insert the value NULL into column 'VENDNAME'

Microsoft Dynamics GP

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert the value NULL into 'VENDNAME', table 'tempdb.dbo.##181163', column does not allow nulls. INSERT fails.[Microsoft][SQL Server Native Client 11][SQL Server]Cannot insert the value

Continue reading “Navigation List Error: “Cannot insert the value NULL into column ‘VENDNAME’””

Management Reporter Period 13 Is Not Valid For Company

Microsoft Dynamics GPThis is an issue I came across a few months ago and misplaced my screenshots. Having just stumbled across them on my test Hyper-V server I decided it is still worth posting about.

I was doing some testing on a system with multiple companies created and was looking at a report consolidating figures from all of them into one report. When the report was generated it failed and gave the error message below for several of the companies in the Report Queue Status window:

Period 13 for fiscal year 2013 is not valid for company <company name>. No data will be returned for this company.
Period 13 for fiscal year 2013 is not valid for company <company name>. No data will be returned for this company.

I checked the Fiscal Period Setup in Microsoft Dynamics GP for all reported companies and all of them had the same period 13 defined. Management Reporter, as well as reporting errors in the Report Queue Status window, logs errors in the Windows Event Viewer.

Continue reading “Management Reporter Period 13 Is Not Valid For Company”

VBA Compile Error – Expected: expression

Microsoft Dynamics GPI realise that the use of VBA in modifications and customisations is something that needs to be carefully considered with Microsoft Dynamics GP 2013 including a Silverlight based Web Client which cannot use VBA. I had a requirement from a client for a modification to the Debtor Enquiry (Customer Inquiry to the Americans reading) to include the sum of the displayed transactions on the window and as this was a small client with GP installed locally on each PC there is no requirement for the web client, I felt able to perform this change using Modifier using some VBA.

I added the required fields to the VBA project but encountered an error message when writing the VBA code to concatenate the fields into the SQL statement I was going to use to get the data;

Microsoft Visual Basic: Compile error: Expected: expressionMicrosoft Visual Basic: Compile error: Expected: expression

Continue reading “VBA Compile Error – Expected: expression”

Management Reporter – An Unknown Error Has Occurred

Microsoft Dynamics GPAfter dealing with an obscure and unhelpful Integration Manager error recently, I’ve also had a Management Reporter one; when trying to generate a report the following error was consistently produced;

An unknown error has occurred while processing reportAn unknown error has occurred while processing report

Continue reading “Management Reporter – An Unknown Error Has Occurred”

Integration Manager Error – Object Reference Not Set To An Instance Of An Object

Microsoft Dynamics GPSometimes I find Integration Manager to be very trying. It can be a very useful tool but it can also be incredibly frustrating.

A client we have taken over support for logged a call about an integration they couldn’t run and hadn’t been able to run for a couple of months after their upgrade to Microsoft Dynamics GP 2010. The error message they were receiving was this;

Integration Manager - Object reference not set to an instance of an objectIntegration Manager – Object reference not set to an instance of an object

Continue reading “Integration Manager Error – Object Reference Not Set To An Instance Of An Object”

Area Page Error – Value Cannot be Null

Microsoft Dynamics GPA client who upgraded to Microsoft Dynamics GP 2013 a few weeks ago began encountering a problem when switching between modules. In particular when they loaded the Purchasing area page they received the following message;

Value cannot be null. Parameter  name: contentValueValue cannot be null. Parameter name: contentValue

Fortunately, there are two solutions to this problem outlined in the Microsoft Support KB Article 2843273. The first is to upgrade to Microsoft Dynamics GP 2013 which at very short notice isn’t really an option, or to use the script in the KB article to both remove the corrupt entry in SY07140 and add a trigger to the table to prevent another corrupt entry being created.

As we needed the client up and running without the error quickly, we opted for the second approach and will look to schedule in the upgrade from 2013 RTM to SP1.