Refreshable Excel report for historical GL trial balance in Dynamics GP

Want to see your historical General Ledger trial balance in Excel? Yes, you can always print out the one from GP and dump it to Excel and manipulate the results, but there is an easier way. Below is a stored procedure you can use to create this. As an added bonus, if you’re using GP Reports Viewer you can […]

More:
Refreshable Excel report for historical GL trial balance in Dynamics GP

August 13, 2015 · Victoria Yudin · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 130

Solving Performance Problems with Microsoft Dynamics GP and Windows 10 – Part 2

David Meego - Click for blog homepageFollowing on from my previous post: Solving Performance Problems with Microsoft Dynamics GP and Windows 10 – Part 1, I was still not happy with the performance of Dexterity and Microsoft Dynamics GP on my newly updated Windows 10 development machine.

This article takes you through the steps to get your Dexterity and/or Dynamics system running fast on Windows 10.

I normally don’t post multiple articles in one day, but it was worth posting this one and the previous two as soon as possible to help others out there facing the same issues.

Performance Problem

After resolving the pause on exit issue discussed in the previous post, I noticed that Dexterity and Microsoft Dynamics GP where still very sluggish when running. It was taking much to long to log in and everything was like I was running through molasses.

Checking the Task Manager, I could see fairly high CPU usage. Sorting by the CPU column, the culprit was the Antimalware Service Executable. This Windows process is the real-time processing component for Windows Defender.

I have seen issues before with anti virus or malware solutions causing performance issues for Microsoft Dynamics GP and Dexterity, and the easiest way to resolve them is to add some exclusions to the settings for the application.

Part of the issue is that the Dynamics dictionary is a very large single file and the anti virus solution often wants to check the entire file when only a small part of it is being read. So you want to exclude the file type with the extension .DIC.

This is the same issue with the database and log files maintained by SQL Server. So you want to exclude the folder where SQL Server stores its .mdf and .ldf data files.

As a final step (that delivered some of the best performance improvement), I also excluded the executables for Dexterity, Dexterity Utilities and Dynamics. I have five versions of each installed, so that needed 15 entries.

Solution

To make the changes on your Windows 10 system running Windows Defender follow the steps below. If using a different anti virus or malware solution, you can add the same exclusions anyway.

  1. Select Start >> Settings
  2. Select Update & Security >> Windows Defender
  3. Under Exclusions, click Add an exclusion
  4. Under Folders, click Exclude a folder
  5. Locate the SQL Server Data folder and exclude it. On my system it was
    C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATA
  6. Under File Types, click Exclude a file extension
  7. Add .dic as an exclusion
  8. Under Processes, click Exclude a .exe, .com or .scr process
  9. Navigate to the Dex.exe, Dexutils.exe for each version of Dexterity and add it
  10. Also navigate to the Dynamics.exe for each version of Microsoft Dynamics GP and add it

Now test out how fast your system runs Dexterity and/or Microsoft Dynamics GP. Zoom Zoom. :-)

Hope you are happy with your Windows 10 upgrade now.

David

PS: I know adding exclusions weakens your protection, but it does stop you pouring a liquid into the machine in the hopes of dissolving the molasses.

This article was originally posted on http://www.winthropdc.com/blog.

Filed under: Development, Dexterity, Dynamics, GP, Microsoft, Windows Tagged: Application, Development, Dexterity, GP 2010, GP 2013, GP 2015, Windows 10

Read More:
Solving Performance Problems with Microsoft Dynamics GP and Windows 10 – Part 2

August 13, 2015 · WinthropDC · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 281

Solving Performance Problems with Microsoft Dynamics GP and Windows 10 – Part 1

David Meego - Click for blog homepageNow that I am running Windows 10 and am starting to get back to work with developing for Microsoft Dynamics GP and Dexterity, I have been noticing some serious performance issues.

This article takes you through the steps I used to identify the issue and what I did to resolve the issues. After making the changes listed in this and the next article I am happy with my Windows 10 development environment.

The first issue I noticed was that when I exited Dexterity Test mode, there was a significant delay (about one minute) before I was returned to the Dexterity IDE (Integrated Development Environment). I tested this on multiple Dexterity versions and it still occurred. To make sure that it was nothing in my code, I tested using a clean Dynamics.dic and it still occurred.

To test if it was a Dexterity issue or Microsoft Dynamics GP in general, I started testing with multiple versions of Microsoft Dynamics GP and each time there was a minute pause when exiting. The problem was occurring for all current versions (GP 2010, GP 2013 and GP 2015) in both the runtime and development environments.

Note: I did not have this issue on my other Windows 10 machine, so it does not occur for all machines. It just occurred on my primary development machine. :-(

So now I put my Microsoft Escalation Engineer hat back on to troubleshoot and identify the cause. Using a source dictionary in Dexterity, I used the Script Log to capture the scripts running when you exit GP. The excerpt from the script.log file below shows two calls to the script exitDynamics of form Main Menu with a significant 30 second plus time before the next script.

14:12:04  'Main_Menu FORM POST on form Main Menu'
14:12:04      'exitDynamics of form Main Menu'
14:12:40          'SQLPath', 0, 7, 31, ""
14:12:40          'GetClientUIType()', 0
14:12:40          'Add_Successful_Logout_Record'
14:12:40          'DeleteUserMessages of form sySendMessage', "sa"
14:12:40              'SQLPath', 0, 7, 0, ""
14:12:40          'IsCreated() of form syDeferredPassword', 0
14:12:40      'isDynUtils() of form LibSystem', 0
14:12:40      'exitDynamics of form Main Menu'
14:13:12          'GetClientUIType()', 0
14:13:12          'Add_Successful_Logout_Record'
14:13:12          'DeleteUserMessages of form sySendMessage', "sa"
14:13:12          'IsCreated() of form syDeferredPassword', 0
14:13:12      'smDEX_Clear_Locks', 0, 15
14:13:12          'SQLScriptPath', 0, 7, 3069, ""
14:13:13      'ST_CleanupBeforeLogout'

Looking at this script, the issue had to occur between the start of the script and the first call to the GetClientUIType() function. So I placed a breakpoint at the top of the script and stepped through the script until it hit the pause. Below are the lines of code and the command causing the pause was the DDE_IsAppRunning(CONTAIN) call.


if (TEMPCloseOLE of globals) then
    if DDE_IsAppRunning(CONTAIN) then
        set fDummy to OLE_Exit();
    end if;
end if;

Looking at the DDE_IsAppRunning(CONTAIN) call, I could see that it was inside a conditional statement and so if we changed the value of the global variable TEMPCloseOLE of globals it should be possible to avoid the pause on exit issue.

Using the trusty References facility in Dexterity, I located the TEMPCloseOLE global field and asked what it was referenced by. In the global procedure Setup_System_Variables, I found the following lines.


set sFlag to Defaults_Read(DEXINI_OLECLOSE);
set TEMPCloseOLE of globals to (sFlag <> FALSE_STRING); 

This showed that the TEMPCloseOLE of globals variable was set once based on the Dex,ini setting referred to by the constant DEXINI_OLECLOSE (which evaluates to OLEClose).

With this information in hand I added the following Dex.ini setting to my system and tested again:

OLEClose=FALSE

Et Voila, the delay on exit disappeared. First problem solved. I forwarded my research to Microsoft Support in case anyone else was having this issue…. and it turned out that others had seen it.

The delay on exit issue was reported on the partner forum and my “workaround” solution was provided, then my friend Mariano blogged about it. Here are the links:

Hope this information helps take away the frustration I was having.

David

PS: I don’t really speak French, even though I did study it (and Latin) in primary school in the UK before I moved to Australia. I have even been to France, but that did not help.

PPS: Before you ask, I don’t speak Latin either… it is a dead language and even the Italians don’t speak it any more.

PPPS: OK, yes I know that even though Latin is dead, its influence lives on in many European languages.

This article was originally posted on http://www.winthropdc.com/blog.

Filed under: Development, Dexterity, Dynamics, GP, Microsoft, Windows Tagged: Application, Development, Dexterity, GP 2010, GP 2013, GP 2015, Windows 10

Visit site:
Solving Performance Problems with Microsoft Dynamics GP and Windows 10 – Part 1

August 13, 2015 · WinthropDC · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 150

Microsoft Edge says “Hmm, we can’t reach this page.”

David Meego - Click for blog homepageI have finally taken the plunge and updated my primary machine, a Surface Pro 3, to Windows 10.

I delayed the installation of Windows 10 for a little while primarily because I wanted to get GP Power Tools released and did not want anything to mess up my development environments.

Then my friend Bill Marshall seemed to have some issues with Dexterity and Windows 10, so I did some testing on another Windows 10 machine to ensure that everything should work.

While doing some initial testing after the upgrade, I started receiving errors from new Microsoft Edge internet browser.

Error
Hmm, we can’t reach this page.

This would be expected if I did not have internet access, but I did. Internet Explorer worked fine and my mail worked perfectly via outlook.

While checking my settings on the Network and Sharing Center window, I noticed that my system had both a private and a public network showing. Even though my WiFi and Ethernet were connected to the same router.

I was able to solve the issue by cleaning up my network adaptors. I had a number of virtual drivers because I had been using a Hyper-V virtual machine to test the Windows 10 preview. Disabling the Hyper-V Virtual switch and making sure that the remaining Ethernet and WiFi adapters had all checkboxes selected on the Networking tab of propertues (except Microsoft Network Adapter Multiplexor Protocol and Hyper-V Externsible Virtual Switch) stabilised my connections.

Now both connections show as a Private connection to the same network and my internet connection is now fast and stable.

It seems that Microsoft Edge does not like it when there are both Private and Public networks at the same time.

Hope this helps.

David

This article was originally posted on http://www.winthropdc.com/blog.

Filed under: Microsoft, Windows Tagged: Exception, Microsoft Edge, Windows 10

Read the article:
Microsoft Edge says “Hmm, we can’t reach this page.”

August 13, 2015 · WinthropDC · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 553

You experience slow performance exiting Microsoft Dynamics GP 2015 on Windows 10

Today I ran across a very interesting partner forum post, in which the consultant described a slow performance issue when closing (exiting) Microsoft Dynamics GP 2015, which started happenning after his client upgraded their Windows workstations to the newly minted Windows 10 operating system.

A Microsoft support engineer acknowledge that “there have been a few people experiencing this issue“. The recommendation to bypass the slow application closing is to change or add the following Dex.ini setting to see if it resolves the issue on exit. The setting changes a Microsoft Dynamics GP global variable and avoids the pause that seems to be occurring during the exiting process.

OLEClose=FALSE

This Dex.ini setting controls whether the Microsoft Dynamics GP application checks for the OLE Container application being opened at the time of shutdown — a “shutdown” can occur if the user legitimately exits Dynamics GP or simply chooses to open either the Report Writer or Modifier tools. If the Container is open during the shutdown operation, Dynamics GP will attempt a graceful closure of it.

The default value of this setting is TRUE, whether the setting is present in the Dex.ini with a value of true or empty, or simply not present at all. This indicates to GP that it should always attempt to close down the Container if it is found to be open.

Given the fact that most customers are now taking advantage of Document Attachment (Doc Attach) and moving away from the Container application, there should be less and less dependency on Container, which would almost require that the default value for our flag is set to FALSE, rather than it’s current state.

As usual, you must apply these changes to your environment only if you are experiencing this issue.

Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.intelligentpartnerships.com/

Excerpt from:
You experience slow performance exiting Microsoft Dynamics GP 2015 on Windows 10

August 13, 2015 · Mariano Gomez · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 157

Management Reporter 2012 CU 13 – NOW AVAILABLE! | Dynamics …

Subscribe. Enter your E-mail address in the box below and subscribe to our blog feed. Email Address … Search Dynamics GP Blog. ← CustomerSource. How can you use it as a resource?

See the article here:
Management Reporter 2012 CU 13 – NOW AVAILABLE! | Dynamics …

August 12, 2015 · tidestone · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 105

#GPPT What’s New: Database Validation, copying databases and upgrades

David Meego - Click for blog homepageThe Database Validation feature was previously demonstrated but never released. GP Power Tools now releases Database Validation for general availability.

Database Validation is a tool designed to ensure that the user, company and table settings at the SQL Server level match what the Microsoft Dynamics GP application says they should be.

Database Validation does not replace any of the tools available already from Microsoft. It performs different functions to Dynamics Utilities, DB Maintenance, or Check Links.

Database Validation ensures that the user, company and access information in the application tables matches the logins, databases and database users in SQL Server. It checks for the correct database owner and permission settings for users. It also checks for invalid data in the Dynamics GP Utilities version tables and checks that the account framework settings match between the application, system database and company databases.

Database Validation can also be used to identify SQL tables and views which don’t appear to be used by any of the installed product dictionaries as well as Dexterity table definitions for which the SQL table or view has not been created. For tables that exist in both the Dexterity product dictionaries and in SQL, Database Validation can compare the tables structures to ensure that the columns and datatypes match.

When Database Validation is opened it performs the following checks on the system.

Users and Databases:

  • Confirm that the ‘DYNSA’ SQL Server Login exists
  • Confirm that ‘DYNSA’ is assigned as dbo for the SQL Databases
  • Confirm that the ‘DYNSA’ GP User ID exists
  • Confirm that ‘DYNSA’ is assigned access to all GP Companies
  • Confirm that ‘sa’ is assigned access to all GP Companies
  • Identify GP Companies for which there is no SQL Database
  • Identify GP User IDs for which there is no SQL Login
  • Identify GP Users not assigned to the DYNGRP SQL Role
  • Identify Company Access records for missing Users or Companies
  • Identify missing Database Users as per Company Access records

Dynamics GP Utilities:

Using the records in DB_Upgrade and DU00020 tables in the System database.

  • Identify records for companies that are not installed
  • Identify records for product dictionaries that are not installed

Account Framework:

  • Identify Account Framework from Application Dictionary
  • Identify Account Framework from setup tables in System Database
  • Identify Account Framework from GL_Account_MSTR (GL00100) table in each Company Database

This information is then displayed in the top half of the window. The red crosses and yellow exclamation marks highlight the various issues found.

Database Validation 1

After running the Fix Users, Fix Utilities and Fix Framework processes to fix any issues found during the initial checks, you can click Process to read the dictionaries and compare the tables and views identified against the data in the SQL databases. Then you can click Validate to compare the column and datatype structures for the tables existing in both the product dictionaries and the SQL databases.

Database Validation 4

If any tables are found with incorrect structures, the details are available as a report or via a window to show you the differences.

Database Validation Structure Errors

You can then click Fix Tables. Database Validation provides options to fix the tables depending on whether they contain data and if Dynamics Utilities has a conversion script available.

Examples of Use

Here are some common examples of how using Database Validation makes your life easier:

  1. Before upgrading you can ensure that there are no invalid users or companies in your system, that Dynamics Utilities has no “junk” in its tables, that the account framework settings match everywhere and that all the tables have the correct column structure and security permissions. This will help ensure a smooth upgrade without errors.
  2. When transferring all the databases to a new server, GP Power Tools can recreate all the logins and database users including using the application level encryption and marking the passwords to be changed on next login.
  3. When creating test environments, GP Power Tools can remove all the references to the users and databases that will not be used in the test environment.

For more information on using Database Validation, please review the section in the GP Power Tools User Guide manual PDF, which includes a step by step guide to usage. The manual is installed with the product and is also available as a separate download.

Enjoy

David

This article was originally posted on http://www.winthropdc.com/blog.

Filed under: 2010, 2013, 2013 R2, 2015, 2015 R2, Dynamics, GP, GP Power Tools, Microsoft, Products Tagged: GP 2010, GP 2013, GP 2015, GP Power Tools, GPPT, What’s New

Excerpt from:
#GPPT What’s New: Database Validation, copying databases and upgrades

August 12, 2015 · WinthropDC · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 130