Hands On With the GP Power Tools – Administrator Tools: Deny Based Security – Enhanced Security

● Ian Grieve ●  ● 2 Comments   ● 

Winthrop DCThis post is part of the Hands On With the GP Power Tools (GPPT) – Administrator Tools series in which I am taking a hands on look at the various tools offered by GPPT.

The Enhanced Security window (GP Power Tools area page >> Reports >> Enhanced Security) allows you to view the security resources and/or operations via the navigation (menus and area pages) or dictionary model:

Enhanced Security

Continue reading → Hands On With the GP Power Tools – Administrator Tools: Deny Based Security – Enhanced Security

● Categories: Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL Snippet: Split String By Delimiter

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft SQL ServerIf you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.

This third example, shows how to use the new in SQL Server 2016 string_split command:

/*
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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	value
	,ITEMNMBR
	,ITEMDESC
	,ITMCLSCD
FROM
	IV00101
 CROSS APPLY
	string_split(RTRIM(ITEMNMBR), '-')
WHERE
	value = 'SHP'

The example is part of the code I used when working on a client project a while ago; the client had a large number of Inventory Items and I needed to select a subset of the Items from the Inventory Master (IV00101).

When the clioent created their items they did so using a hyphen delimiter. Using the string_split command, I was able to separate out the segments of the Item Number and select only one of them in the WHERE clause.

● Categories: Microsoft, SQL Server, Uncategorized ● Tags: , , , ,  ● Permalink ● Shortlink ●

Fixing Reports Not Implemented Error After Upgrade

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPWe’ve been doing a number of upgrades recently and I encountered an old error when trying to deploy the SQL Server Reporting Services (SSRS) and Excel Reports through the Reporting Tools Setup window (Administration >> Setup >> System >> Reporting Tools).

This particular client is being upgraded from Microsoft Dynamics GP 2013 SP2 to 2016 R2.

The problem is that some of the companies are showing as Not implemented. The companies showing this way are the oldest; TEST2016… is actually the main company renamed for testing.

Reporting Tools Setup

Continue reading → Fixing Reports Not Implemented Error After Upgrade

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: Bank Reconciliation Tracks History

● Ian Grieve ●  ● 3 Comments   ● 

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 tenth Feature of the Day I’m going “hands on” with is the Bank Reconciliation Tracks History one which introduced a new window on the Financial Routines menu:

Reconciled Transaction Maintenance

To use this feature, decide if all, a range or one Checkbook should have its reconciliation history moved to history.

Mark the checkbox next to the type of transactions which should be moved and enter a Cut Off date; although the date field is aligned with the Reconciled Deposit Transactions, it apples to all of the transaction types.

Click Process to perform the move of reconciled transactions to history.

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: Receivables & Payables Distribution Line Display UI Change

● Ian Grieve ●  ● 3 Comments   ● 

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 feature I am going “hands on” with is the ninnth Feature of the Day, GL Distribution Line Display UI change.

The status of the scrolling window on the Payables or Distribution Zoom window are remembered:

As with the GL Distribution Line Display UI Change feature, the settings for this feature are stored in the SY01402 table (System User Defaults) in the system database, which is typically called DYNAMICS.

Again is closed and 1 is expanded; the Payables Management setting is where the syDefaultType column is equal to 107 and Receivables Management is 111.

Click to show/hide the Hands On With Microsoft Dynamics GP 2016 R2 Series Index

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change

● Ian Grieve ●  ● 0 Comments   ● 

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 feature I am going “hands on” with is the seventh Feature of the Day, GL Distribution Line Display UI change.

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: GL Distribution Line Display UI change

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions

● Ian Grieve ●  ● 3 Comments   ● 

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.

In this post, I’m going “hands on” with the sixth of the Microsoft Dynamics GP 2016 R2 Feature of the Day posts, Display Tax Percent for Historical Sales Transactions.

To test this feature, I created a new Tax Detail in Tax Detail Maintenance (Administration >> Setup >> Company >> Tax Details) for a Sales series Standard Rated UK VAT at 20% and created a related Tax Schedule:

Tax Detail Maintenance

Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: Display Tax Percent for Historical Sales Transactions

● Categories: Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

Error After Copying Database From Live Server To Test Server

● Ian Grieve ●  ● 0 Comments   ● 

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.
● Categories: Uncategorized ● Permalink ● Shortlink ●

A Diary of reIMAGINE 2016: Part 4 – At Heathrow Airport

● Ian Grieve ●  ● 1 Comment   ● 

reIMAGINE2016The plane arrived at Heathrow slightly late, apparently due to the airport being busy.

The flight from Newcastle was pleasant enough. I now have three-and-a-quarter hours until my flight to Chicago is due to take-off. The downside is once I get to Chicago, the next connection departs from a different terminal and I have to collect my luggage before going through customs and re-checking my luggage.

And I have to do all of that in one-and-a-half hours.

Slightly concerned, which I voiced on Twitter, but American Airlines responded saying their staff will be happy to help and not to worry. Yeah, still worrying.

As it is my flight is due to arrive in Fargo at 2205 and I am due to start work tomorrow at 0930. I don’t exactly have a lot of latitude in timings if I am going to get any sleep tonight. Why did we book a flight like this, you ask? All of the flights available had similar timings, so there was little actual choice.

Well, I’m going to try to stop borrowing trouble and worrying about it and have a nose about Heathrow Terminal 5 while I am here.

● Categories: Conferences & Events, Dynamics, GP, Microsoft, Uncategorized ● Tags: , , , , ,  ● Permalink ● Shortlink ●

wfDeployClrAssemblies Error When Deploying The System Database

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPThis post is a slight diversion from my Hands On With Microsoft Dynamics GP 2016 R1 series, but I am posting it as it came up while I was deploying the system database.

Towards the end of the deployment an error occurred while the wfDeployClrAssemblies was being executed (actually there were three error messages produced):

Microsoft Dynamics GP Utilities: The following SQL Statement produced an error: EXEC wfDeployClrAssemblies

Microsoft Dynamics GP Utilities

The following SQL Statement produced an error:
EXEC wfDeployClrAssemblies

Continue reading → wfDeployClrAssemblies Error When Deploying The System Database

● Categories: Uncategorized ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●