Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection

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 and also look at the new functionality introduced; the index for this series can be found here.

The second of the new features I’m going to cover is SmartList Favorite Protection.

This feature allows the administrators to define a password which must be entered before a SmartList favorite can be modified. The password is entered in the SmartList Options window (Administration >> Setup >> System >> SmartList Options):

SmartList Options

The password impacts all SmartList favorites. Please do not use ACCESS as the password; I see far too many clients who have this as a password throughout their system. It is in all of the manuals and lots of blog posts so it is pretty much the least secure password you could use.

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: SmartList Favourite Protection”

Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups

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 and also look at the new functionality introduced; the index for this series can be found here.

This is the first of the posts where I will be covering the new functionality introduced to Microsoft Dynamics GP 2016 R2. In this post I am going to cover the addition of SmartList Designer favorites in Advanced Lookups new feature.

To use this feature, create a SmartList Designer object:

SmartList Designer

Continue reading “Hands On With Microsoft Dynamics GP 2016 R2: SmartList Designer Favorites In Advanced Lookups”

Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework”

Microsoft Dynamics GPA bit like Integration Manager, Management Reporter often presents highly generic meaningless error messages to users. The error message below was provided to me by a client a while ago (but I have just stumbled across the screenshots again), which I was then able to reproduce just be ruunning the report:

Management Reporter: The operation could not be completed due to a problem in the data provider framework

Management Reporter

The operation could not be completed due to a problem in the data provider framework.

Continue reading “Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework””

Integration Manager Sales Order Integration – “The Sales Intrastat Entry window is open”

Microsoft Dynamics GPJust before Christmas I was creating an integration for a client in Integration Manager which would integrate Sales Orders, but unfortunately I was receiving an error message:

Integration Log
Integration: Eriks SO Integration (ID: 8)
Action: None
Start Time: 23/12/2016 11:05:38

  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Opening source query...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Initializing Source...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Establishing source record count...
  23/12/2016 11:05:38 Source: IIntegrationEngine_Run, Status Code: 0    Beginning integration...
1:  Order  Insert Failed 5. Seconds 
    DOC 1 ERROR: The Sales Intrastat Entry window is open. - The Sales Intrastat Entry window is open.
  23/12/2016 11:05:43 Source: FinishIntegration, Status Code: 3    Integration Failed
  23/12/2016 11:05:43 Source: FinishIntegration, Status Code: 3    Integration Results
    1 documents were read from the source query.
    1 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        1 failed to integrate.

Finish Time: 23/12/2016 11:05:43

Source Total: 1
Successfully Integrated: 0
Integrated With Warning: 0
Failed: 1

Completion Status: Completed
Integration Status: No Documents Succeeded

Continue reading “Integration Manager Sales Order Integration – “The Sales Intrastat Entry window is open””

Setting Bank Account In ABR To Extract Automatically In Reconcile Bank Transactions

Microsoft Dynamics GPConfiguring Nolan’s Advanced Bank Reconciliation (ABR) to automatically extract transactions when reconciling a bank account is not complicated, but I keep on forgetting where the option is located. That gets a little embarrassing when you tell a client, “yes, you can do that” and then have to spend five minutes looking for the option.

So by writing it down here, I am hoping to be able to commit it to memory.

this option is per bank account and is located on the Bank Account Setup window (Financial >> Setup >> Advanced Bank Reconciliation >> Bank Setup).

Click the Options menu and then select Extract options:

Bank Account Setup

Continue reading “Setting Bank Account In ABR To Extract Automatically In Reconcile Bank Transactions”

Error After Copying Database From Live Server To Test Server

Microsoft Dynamics GPThis particular error is one I have seen a number of times after a client, or inexperienced consultant, has copied a database from the live server to the test server and run the scripts from the Microsoft Knowledge Base and thought everything was fine after logging in with the sa account (which is another good reason for not using sa as it hides a multitude of errors.

However, as soon as a user tries to log in, they receive the error message shown below:

Microsoft Dynamics GP: A get/change operation on table 'syUserDefaults' failed accessing SQL data.

Microsoft Dynamics GP

A get/change operation on table 'syUserDefaults' failed accessing SQL data.

The cause of this error is quite simple; the SID (Security Identifier) of the Server Login ID and of the Database User are different.

There are two ways of fixing it:

  1. Use SQL Server Management studio (SSMS) to delete the server login and database users and then save the user with a new password from within Microsoft Dynamics GP.
  2. Use SSMS to run an ALETR USER command on the database user to link it to the Server Login.

The latter option is by far the quicker, and there are two methods you can use to do this:

  1. Alter each user individually, which is only feasible if you have a small number of users.
  2. Alter all users in Microsoft Dynamics GP using a SQL script, which is by far the easiest method.

Removing Analytical Accounting from Microsoft Dynamics GP

Microsoft Dynamics GPI recently posted a script to remove the Fixed Asset Management tables from Microsoft Dynamics GP. I wrote that script for a client who wanted to start using Fixed Asset Management, but it turned out the module had been installed at some point in the past and then removed and the client wanted to reintroduce it.

We’ve just had a similar instance come up with a client upgrading from Microsoft Dynamics GP 2013 SP2 to 2016 R2 where Analytical Accountijng has been installed at some stage and removed and was stopping the upgrade from going through.

In this case though, there was (operative word was) a Knowledge Base article from Microsoft on how to remove Analytical Accounting which is no longer available (hopefully it will be back as I understand Microsoft are moving to a new KB system).

The script was available when we had the issue, but a couple of weeks later, the question was asked by one of the other MVPs. By the time I saw the question, they’d been emailed the scripts by one of the others.

However, when my colleague downloaded the scripts they had also saved a copy of the web page, which meant I could grab the download links for the scripts.

  1. Company Database Script
  2. System Database Script

After running the Microsoft scripts, you’ve downloaded using the above links, run the below script on the system database to remove the Analytical Accounting entries from the upgrade tables:

DECLARE @PRODID INT = 3180

DELETE DB_Upgrade WHERE PRODID = @PRODID
DELETE DU000020 WHERE PRODID = @PRODID

As always before running scripts, make sure you have a good backup of your databases.

UPDATED 20/01/2018: Download links updated to working ones

Windows 10 Lock Screen Doesn’t Show Background

Microsoft Dynamics GPI had my work laptop rebuilt recently after having numerous faulty components replaced. After the rebuild, the Windows lock screen remains black and doesn’t show the background image. I changed it to a static image rather than the Windows spotlight. Unfortunately, this didn’t resolve the issue.

The answer for fixing this is actually very simple, but not obvious. To correct the problem, open the System properties window by opening Windows Explorer and right clicking on This Computer and then clicking on Properties (or by pressing Win + Pause|Break).

Once the System window is open, click on Advanced system settings:

System

Continue reading “Windows 10 Lock Screen Doesn’t Show Background”

How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio

Microsoft SQL ServerThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In previous versions of Microsoft SQL Server, SQL Server Management Studio (SSMS) was always part of the standard install, but it seems this has changed with SQL Server 2016.

Instead SSMS is now available as a separate download. I’d recommend downloading the GA rather than any release candidate which might be available:

Download SQL Server Management Studio (SSMS)

Continue reading “How To Install Microsoft SQL Server 2016: Installing SQL Server Management Studio”

How To Install Microsoft SQL Server 2016: SSIS Configuration For Named Instances

Microsoft SQL ServerThis is a short series of posts on how to install Microsoft SQL Server 2016; the series index can be found here.

In the last post, I installed SSIS, but if you are using a named Instance of SQL Server, there is a configuration step required.

To make the change, there is a file called MsDtsSrvr.ini which, for SQL Server 2016, is located in C:\Program Files\Microsoft SQL Server\130\DTS\Binn. The ServerName needs to be changed to include the full SQL Server Instance Name (as highlighted below):

MsDtsSrvr.ini file in Notepad

Once the full SQL Server Instance Name was added and the file saved, SSIS is ready to use.