Top Categories:
Dynamics GP
ClassicPress

Enable the Windows Distributed Transaction Coordinator

Windows ServerOne of the line of business applications used by a few clients performs updates to both it’s own database and also the Microsoft Dynamics GP database. It does this using code in SQL stored procedures which use transaction boundaries.

As the updates are between SQL servers, it does this using BEGIN DISTRIBUTED TRANSACTION rather than the usual BEGIN TRANSACTION; updates of this sort are, as stated in the name, distributed and required the Distributed Transaction Coordinator (DTC) Windows component to be enabled, which it is not by default, on both servers.

However, enabling DTC is straightforward and quick to do. Launch Component Services and expand the nodes (Computers » My Computer » Distributed Transaction Coordinator) and right click on Local DTC; select Properties:

Component Services window

Select the Security tab and mark the following checkboxes:

  1. Network DTC Access
  2. Allow Inbound
  3. Allow Outbound
Local DTC Properties

When you click OK you’ll be prompted to restart the service along with a warning that dependent services will also be stopped, but will need to manually restarted:

MSDTC Service message

MSDTC Service

The MSDTC service will be stopped and restarted. All dependent services will be stopped. Applications using MSDTC may need to be restarted to use the new settings. Please press yes to proceed.

Once the DTC service has been restated, click OK to dismiss the message:

MSDTC restarted confirmation message

Once DTC has been enabled on both servers, distributed transactions can be processed.

Management Reporter Multicurrency Error

Microsoft Dynamics GPI meant to post this a while ago, but apparently didn’t and have just found the screen shots again. This was reported to me by a client who was trying to run a multicurrency report in Management Reporter.

When columns were flagged with currency translations, this error was produced and shown in the Report Queue Status window:

Report Queue Status

An internal error occurred in the provider. Additional information is available in the event viewer on the Management Reporter server. Please contact the administrator.

I checked in the event viewer and found this error:

Event Viewer

Invalid column name 'TranslationDebitAmount'.
Invalid column name 'TranslationCreditAmount'.

After doing some investigation, I realised that this error is one I have seen before. The issue is that there is a longstanding bug in the legacy connection of Management reporter; if you need to use currency translations, then you need to be using the Management Reporter Data Mart.

Replace Expired SSL Certificate Binding with the Web Services for Microsoft Dynamics GP

Microsoft Dynamics GPA few years ago I posted about securing the Web Services for Microsoft Dynamics GP which is something I would recommend that everyone does, most especially if they are exposed externally.

However, when the certificate expires and needs to be replaced, there are a few steps you need to follow to do this and they are mainly done at the command line as it is the certificate binding which needs to be updated.

First, you need to delete the certificate (the highlighted section is the port used when the certificate was originally bound):

netsh http delete sslcert ipport=0.0.0.0:48666

Second, you need to add the new certificate; the certhash is the certificate thumbprint:

netsh http add sslcert ipport=0.0.0.0:48666 certhash=7c69255774b41dd0f4edc3a34514e5f08a526443 appid={8EFC5047-33C1-438D-B76B-3634DDCF585F}

Once the above steps have been completed, the Microsoft Dynamics GP Service Host service should be restarted.

Historical Inventory Trial Balance VS the Historical Stock Status Report in Microsoft Dynamics GP

Microsoft Dynamics GPI’m a little late, but there was a good article fromAngela Eckman Ebensteiner on the Dynamics GP Support and Services Blog on the differences between the Historical Inventory Trial Balance and the Historical Stock Status Report and why the HITB (Historical Inventory Trial Balance) is the one you should be using to determine the value of your stock on a given day and to reconcile with the GL.

If you’re a user of the Inventory Control module, then the article is definitely worth a few minutes of your time.

Clear Pending Batches from Microsoft Dynamics GP Generate EFT File Window

Microsoft Dynamics GPBack in Microsoft Dynamics GP 10 and before, the ability to produce EFT files was not available in UK language installs. This meant a lot of clients used other methods for producing the EFT files and many of them are still using those alternative methods. That said, I’ve done work with a few recently where they had changed banks or otherwise needed to mke changes to the EFT format, so we hve switched them to using the standard functionality so the format can be maintained esily through the EFT File Format window ((Financial area page » Cards » EFT File Format)).

This largely works, but as the clients had been running EFT payment runs, there was a multi-year backlog of entries in the Generate EFT File window ((Financial area page » Transactions » Generate EFT File)) which needed to be cleared. While it is possible to hit the generte button and have them cleared by creating the files, for some cients we were looking at hundreds of files which would need to be created.

Instead I explored the database and found two tables holding entries which would need to be cleared down to remove the pending batches:

/*
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). */
DELETE FROM CM20202 --Checkbook Transaction Electronic Funds Transfer (CM20202) DELETE FROM CM20203 --Checkbook EFT Transaction Batch (CM20203)

This script removes all entries from the Generate EFT File window; before running the script make sure you don’t need any of the entries, test the script on a test company or system and make sure you have a good backup before running the script on live.

Script to Update Purchasing Unit of Measure in Microsoft Dynamics GP

Microsoft Dynamics GPWhile I am a big advocate of making changes to data via the front end or using an officially supported integration tool, there are times when a SQL script is the quicker way of making a small update.

I recently needed to update the purchasing unit of measure on 60,000 inventory items. The below SQL script was created to make this update:

/*
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). */
UPDATE ['Inventory Item Master'] SET ['Inventory Item Master'].PRCHSUOM = ['Inventory U of M Schedule Detail Setup'].UOFM FROM IV00101 AS ['Inventory Item Master'] --Item Master (IV00101) INNER JOIN IV40202 AS[/sqlgrey] ['Inventory U of M Schedule Detail Setup'] --Inventory U of M Schedule Detail Setup (IV40202) ON [/sqlgrey] ['Inventory U of M Schedule Detail Setup'].UOMSCHDL = ['Inventory Item Master'].UOMSCHDL

This script was suitable for the client’s data against which it was run, but may not be suitable for all data configurations.

As always before running a script, make sure you understand what the script does, test it on a test company/system and have a good backup before running on live and verify the results.

What NETBIOS Is The Machine On?

Windows ServerI was recently doing some work for a client installing some software and realised that I didn’t know what their NETBIOS was and the application didn’t have a lookup option for the service account so I needed to type the domain and username directly. The IT people I was working with were in a meeting and not going to be available for a while so I couldn’t ask.

I did some checking around and found a command which can be run to get the NETBIOS:

nbtstat -n

There may be other ways of doing this and I’d be interested in hearing about them.

Microsoft Dynamics 365 BC Table Reference Website Now Available

Microsoft Dynamics 365 Business CentralA few years ago there was a GP Table Reference website which disappeared with no warning; it had been very helpful, so I created my own version of it.

Well, we’ve recently started doing work with Microsoft Dynamics 365 Business Central and there didn’t seem to be a similar website freely available, so I have created my own. The main reason for creating the site is that it will help me understand data structures which in turn should help my understanding of data entry through the front end. As I expect to be using Jte Reports with Dynamics BC at some point, knowing how data hangs together will help me there too.

The azurecurve | Dynamics 365 BC Table Reference is, and will remain, free to use. It is currently up-to-date as of 2020 Wave 2 and will be kept up-to-date for new releases (updates may not be immediate, but will happen).

I’ve started adding table joins and will continue to do so; when a join exists, an example SQL statement will be available showing how the tables are joined.

If you see any errors or omissions, or have suggestions as to how the site can be improved, please contact me with details and I’ll see about making an update.

MySQL Snippets: Update Statement

MySQL LogoThis post is part of the series on MySQL Snippets.

The following MySQL snippet shows an example UPDATE statement for MySQL; I’m posting this as I have trouble remembering that the order of the MySQL statement is different to that of Microsoft SQL Server; in MySQL the SET clause comes after the JOIN clause, whereas in Microsoft SQL Server, with which I am more experienced, the order is reversed.

/*
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). */
UPDATE bct_custom_fieldnotes AS cfn INNER JOIN bct_custom_fields AS cf ON cf.TableName = cfn.TargetTableName SET cfn.TargetDBTableName = cf.DBTableName WHERE cf.TableName <> cfn.TargetTableName;

MySQL Snippets: Series Index

MySQL LogoThis post is part of the series on MySQL Snippets.

Like the other …Snippets series, this one may not get many posts as I’ll be using it to record things I need to remember. In thie series, I’ll be posting MySQL snippets; usually ones which highlight a difference between the MySQL and Microsoft SQL query syntax.

MySQL Snippets
Update Statement