SQL Server – Page 4 – Winthrop Development Consultants Blog

Dynamics, GP, Microsoft, News, Public Service Announcement, SQL Server

#MSDynGP New NACHA Data Security Requirements coming up on 30-Jun-2022

May 26, 2022 Tagged with Application, News, SQL No Comments

You might be aware of the new Security requirements for Bank Account Numbers which applies to ACH Originators and Third-Parties with more than 2 million ACH payments annually and becomes effective on 30-Jun-2022.

NACHA, the governing body for ACH transactions in the United States, is rolling out updated security requirements which are actually best practice whether you meet the transaction level targets or not.

Read on for how you handle the requirements on your Microsoft Dynamics GP system.

Development, Dexterity, Dynamics, GP, GP Power Tools, Microsoft, Products, SQL Server

#GPPT Obscuring Bank Account Numbers in Microsoft Dynamics GP

May 25, 2022 Tagged with Application, GP Power Tools, GPPT No Comments

This article covers the latest “Must Have” free sample project for the GP Power Tools – Developer Tools module (requires Build 28 or later).

Back in 2012, I created code to obscure customer credit card details (see more information below). Using the same concepts, I have now created a project to obscure Bank Account Numbers for Checkbooks, Customers, Vendors and Employees.

Whether you have a requirement (see NACHA Security Requirements article) or not, best practice would be to make sure that Bank Account Numbers in your system are not human readable while stored in the database.

Development, Dexterity, Dynamics, GP, GP Power Tools, Microsoft, Products, SQL Server

#GPPT Tracking changes to Customer and Vendor Cards

Aug 20, 2021 Tagged with Application, Development, Dexterity, GP Power Tools, GPPT, SQL No Comments

As promised in my previous article, this article contains awesome example GP Power Tools – Developer Tools projects for tracking changes to customer and vendor cards records.

Thanks to Eric Gernan from Amaysim Mobile whom I worked with to create this custom functionality and gave me permission to publish generic versions of the projects.

Development, Dexterity, Dynamics, GP, GP Power Tools, Microsoft, Products, SQL Server

#GPPT Using GP Power Tools to track specific changes Revisited

Aug 18, 2021 Tagged with Application, Development, Dexterity, GP Power Tools, GPPT, SQL No Comments

Back in 2017 I wrote an article which demonstrated how to get GP Power Tools to send an email every time the Credit Limit Field on the Customer Maintenance window was edited by a user and saved.

#GPPT Using GP Power Tools to track specific changes

This article revisits this article by enhancing the functionality with the options to log changes to a text file or a SQL table instead of sending an email.

Development, Dexterity, GP Power Tools, Products, SQL Server

#GPPT Building Self Service SQL Scripts

Aug 16, 2021 Tagged with Dexterity, GP Power Tools, GPPT, SQL No Comments

This example shows the different methods that can be used with GP Power Tools to create Self Service SQL Scripts which can be made available to all users or just specific users.

The actual SQL Script used for this demonstration is a simple select query on the Customer Master (RM00101) RM_Customer_MSTR table, but can be replaced by any SQL statements to achieve any manipulation of the data desired.

Development, Dexterity, Dynamics, GP, GP Power Tools, Microsoft, Products, Report Writer, SQL Server

#GPPT Modifying Reports based on Temporary Tables

Jul 6, 2021 Tagged with Application, Dexterity, GP Power Tools, GPPT, Report Writer, SQL No Comments

The recent Build 28.8 hotfix of GP Power Tools incorporated some Developer Tools module changes to enable so very cool customization options for updating reports based on temporary tables. The big feature enhancement is the ability to store a Table Reference as a Memory parameter.

References in Dexterity are like pointers to a particular resource, so the Table Reference allows access to the particular instance table buffer without needing to pass the table as a parameter to a script.

Development, Dexterity, GP Power Tools, Products, Report Writer, SQL Server

#GPPT Reporting for Minnesota’s Wage Theft Law

Feb 28, 2020 Tagged with Development, Dexterity, GP Power Tools, GPPT, SQL No Comments

This article is comes about thanks to my friend, Melissa Brown, who approached me for a solution to a reporting requirement she had for a customer due to new legislation brought in by the US state of Minnesota.

The requirement was not possible to solve with the Dynamics Report Writer alone as it involved an array in a table which prevented a static relationship from working to retrieve the data needed.

Development, Dexterity, GP Power Tools, Products, SQL Server

#GPPT Parsing Returned SQL Data into Rows and Columns

Feb 24, 2020 Tagged with Development, Dexterity, GP Power Tools, GPPT, SQL No Comments

When writing customizations using GP Power Tools as your development tool, there may be times where you need to return a SQL query as a data set to your Trigger Setup script or Runtime Execute Setup script.

It is possible to display a data set to the end user in a SQL Results window using the MBS_SQL_Results helper function. If you have “Goto” actions defined, the MBS_SQL_Results_Goto helper function will display the data and enable the SQL Goto functionality.

But what if we don’t want the data displayed to the user and just need to use the data in your code.

Development, Dexterity, Dynamics, GP, GP Power Tools, Microsoft, Products, SQL Server

#GPPT Renumbering SOP Transaction Lines

Jun 6, 2019 Tagged with Dexterity, GP Power Tools, GPPT, SQL No Comments

Earlier this week, a forum post by my friend Abra Gilman on the GPUG Open Forum brought up the problem where a user was getting an error when attempting to insert a new line into a SOP Transaction.

SOP Line Items – Running out of Line Item Sequence (cannot add line items)

This problem can be reproduced by attempting to insert lines in the same location more than 14 times, on the 15th attempt an error will be generated.

Development, Dexterity, SQL Server

#Dexterity Creating Large SQL Store Procedures

Mar 2, 2019 Tagged with Dexterity, SQL No Comments

Today I was asked about how you can use Dexterity to create SQL Stored Procedures when the stored procedure itself exceeds 32,768 characters.

This 32K limit is because the largest datatype supported by Dexterity is the Text field which is limited to 32,768 characters. This is the maximum size of a signed 16 bit integer (-32767 to 32768).