Uninstalling abandoned or orphan modules from Dynamics GP

Preface

Do not make any changes to your Dynamics GP database without consulting your support partner. Although I used the technique described to clear the products, I have also been working with GP for 23 years and have experience in the nuances about table naming and interdependencies.
The GP application could be rendered INOPERABLE, with little other choice than to restore backups. Even worse, it is possible that issues may not been seen until days or weeks later, by that point it is too late to restore a backup.
These techniques may however help professionals to clean up GP databases.
So to start with, you do have multiple proven backups of data and procedures -right!?

Cleaning up

A GP module is a part of GP that be optionally installed. Fixed assets and the manufacturing module are examples.
There are two parts to a typical GP module;

• GP Dictionary (.DIC) files, these are the client application files referenced by the .SET file.
• Database objects, such as tables, stored procedures, user defined functions, views, triggers data in tables that form the data layer of the module.

When a Dynamics GP installation has been running many years, it may accumulate modules in the database that are no longer in active use by the client application. This takes the form of old tables, stored procedures and other database objects. Indeed in some cases the modules may have been discontinued from by the ISV many versions ago.The corresponding database objects now in the database may be back from a way old version, laying dormant orphaned and abandoned.

This was exactly the case with the GP installation that I was dealing with recently. There were twenty three year old, unused modules cluttering the database. There were other modules such as credit control modules where modules were no longer used, a different product was now used from another vendor.

What was found:

• Manufacturing module only installed to look at (never configured or used, very old version)
• Multiple credit control modules (only one still in use - old versions)
• Fixed assets module (unused)
• Analytical Accounting module (unused)
• Payment Document Management module (unused & old)
• Grant Management (unused)
• Wennsoft PO (no longer unused)
• Advanced Bank Rec (no longer used old version)
• Encore Enhanced Customer Statements (unused)
• plus some other minor tables for small products that I didn't know including PCB and RFQ

The above products had no Dictionary files running in the production deployed application and many were for very old versions of GP, as they had not been upgraded in previous upgrades.

SELECT * FROM  DYNAMICS..DB_Upgrade
SELECT * FROM DYNAMICS..DU000010  
SELECT * FROM DYNAMICS..DU000020  
SELECT * FROM DYNAMICS..DU000030  

The above will list the product IDs and the current versions as the application understands it to be. Every product is assigned a worldwide unique ID number by Microsoft to the vendor that produces it. This means in theory the product ID will identify the product.

This can be a valuable clue to use to look up the product ids to see what products were previously installed. Make a list of the table prefixes used by those products, they will be needed later.
Running this, the clue is,

SELECT * FROM DYNAMICS..DU000020  order by versionMajor, PRODID

anything not sharing the current version of GP is something to look into, it most likely can be removed if not referenced by any legacy systems or reports.

So search up the Dynamics GP product ID on the web, the full list is not published anywhere, one can be compiled.

https://dynamicsgp.wordpress.com/2010/11/30/dynamics-set-list-of-dynamics-gp-products/
https://dynamicsstarter.files.wordpress.com/2011/09/dynamics_2010_table_list.pdf
http://dynamicsconfessions.blogspot.com/2014/09/product-id-and-product-name.html

Product ID Product title
0 Dynamics (GP itself)
1 Dexterity
105 Integration Manager
258 Project Accounting
309 Fixed Assets
346 Manufacturing
414 Human Resources
949 Field Service
1042 Interfund Management
1045 Revenue Expense Deferrals
1058 Cashbook (Bank Management)
1150 Scheduled Installment
1157 Collections Management
1235 Safe Pay
1428 Electronic Reconcile
1493 SmartList
1632 Cash Flow Management
1838 Technical Service Tools
1878 Excel-Based Budgeting
1911 HRM Solution Series
2150 Payment Document Management
2199 Business Activity Statement
2277 Purchase Order Enhancements
2365 Retail Management System Integration
2416 Control Account Management
2547 Enhanced Commitment Management
2788 Enhanced Intrastat
2992 CopierSeries
3096 VAT Daybook
3104 Advanced Security
3107 Extender
3180 Analytical Accounting
3258 Encumbrance Management
3278 Report Scheduler
3830 SmartList Builder
4067 ML Checks
4100 Perceptions
4103 Credit Analysis
4350 Legal Documents
4421 Grant Management
4522 Payroll Integration to Payable
4612 Advanced Go Tos
4621 Analysis Cubes for Excel
4933 Certification Manager
4955 Employee Health and Wellness
4965 Electronic Signatures
4966 Audit Trails
5000 Inflation Adjustment Fixed Assets
5040 Shipping Documents
5064 COA
5373 Taxes and Returns
5597 HITB Report
5967 Fixed Assets Enhancements
5982 Export Financial Data
6499 Dynamics Online
6831 Date Effective Tax Rates
7131 Canadian Payroll
7705 Chile Localization
7712 Monetary Correction FA
8811 Direct Debits & Refunds
9872 Single Account Plan
9874 Inflation Adjustment - Financial
9875 Kardex
9879 Tax Administration - Sales
9880 Tax Administration - Purchasing
9882 Collection and Payment Methods - Withholds
9883 Tax Listinq
9884 Check Printing
9887 Tax Reports
9888 Inflation Adjustment - Inventory
9889 Purchase Vouchers
9890 Certificate of Retention - Ecuador
9990 Inflation Adjustments
10100 Monetary Correction IV

These will be found in the .SET files in the application directory too. That defines which modules are loaded at run time (discussion of SET files is outside scope of this post).

By reconciling the modules that are being loaded in the SET file and those in the DYNAMICS database DU* tables, it is possible to derive an idea of what are active products in the system now are.
*Microsoft Dynamics GP>>Tools>>Setup>>System>>Edit Launch File *
will also give a view of the current .SET file products.

Naming convention for database objects is that objects in the database have a prefix to their name that relates to the main product to which they belong. For example, Fixed Asset database objects have FA prefix on them. This is a convention but not a rule, the prefix may also not related to the current product name, as products have been acquired and re-branded. Sometimes a vendor name prefix then product is used, for example Nolan Business Solutions, Advanced Bank Rec has "NCABR" as a prefix to object names. The company used to be called Nolans Computers (NC), Advance Bank Rec (ABR).

Prepare a new company

This is a vital step in the process. Prepare a new company with your current client application configuration. You can use GP utilities to drop and recreate the sample company (TWO) database. This is important as you will get the set of tables GP expects to be installed, as they are in the current configuration of the application, hence why create a new company instance. You must bear in mind that some products require a install into each company that they are used in. So for those product the database objects will not exist until you run the installer against the sample company. Do this for all products, or just be very aware database objects you later might consider dropping are maybe part of these products.

Compare objects between the sample company and your production databases

For each database compare each of the object types (procedures/triggers/tables/functions), this will start to flush out the database objects later targets for removal.
Here, where DBNAM is the company database to compare with.

SELECT s.[name] schemaname, t.[name] FROM 
DBNAM.SYS.tables t
JOIN DBNAM.SYS.schemas s
ON t.schema_id =s.schema_id
WHERE 
    s.[name] NOT IN ('ourschema', 'pricing') 
AND t.[name] not like 'CA%'
AND t.[name] not like 'EXT%'
AND t.[name] not like 'EN%'
AND t.[name] not like 'CHG%'
AND t.[name] not like 'CN%'
AND t.[name] not like 'sys%'
AND t.[name] not like 'VAT%'
AND t.[name] not like 'ASI%'
AND t.[name] not like 'CB%'
AND t.[name] not like 'ELZ%'
AND t.[name] not like 'EDCV%'
AND t.[name] not like 'BU%'
and t.[name] not like '%to delete%'
EXCEPT  
SELECT s.[name], t.[name] FROM 
TWO.SYS.tables t
JOIN TWO.SYS.schemas s
ON t.schema_id =s.schema_id

This will list all the tables that are in the company database but not in the sample database, excluding any starting with the "known list of prefixes" that you gathered in one of the earlier stages. It will also suppress any that contain the name "to delete". By convention renaming objects to something unique like that, in this example, appending "to delete" to the name indicates they are going to get dropped later.

Change out SYS.tables for: SYS.Procedures, SYS.Views, SYS.Triggers etc to compare all the various object types with the sample company. You will see tables and views and procedures that have been created on purpose for bespoke reasons, just add them to the WHERE clause of the query. The idea is to build up a list of object that are to be dropped.

Script out all the objects and keys, indexes, permissions etc before dropping anything

It is wise to use a tool such as powershell to script out all the create SQL for the objects that are about to be dropped from a database. You can not protect data this way, but certainly if a script, trigger, procedure needs putting back again later, this make it a super easy, vs restoring databases into another database to get hold of a missing views, procedures or whatever.
I do this using powershell, creating a file for each object in a folder of that object type, within a folder for each database.
That process is outside scope of this post, but scripts can be found via search engines to do this.

Rename the objects

Generate rename scripts for all the objects no longer needed, take those scripts and run them against each database.

select 'EXECUTE sp_rename '''+  t2.name +'.'+t1.name +''' , ''' + t1.name +' to delete gm' +''';' from sys.tables t1
join sys.schemas t2 
on t1.schema_id=t2.schema_id  where t1.name like'GTM%'

The above will generates the SQL that needs to be executed against each database.
I prefer to do this work by hand and execute each script myself rather than have the generation script run it. Just gives more visibility to what it will do.
Add the where clauses from the comparison script to exclude all objects to be protected.
After running the script for each database and object type, the databases will now have all the objects there but named differently.
Running for a week or so in this state or in testing will ensure it is safe to drop those objects, assuming no errors have been encountered.

Rebuild zDP procedures

Dynamics GP generates proxy stored procedures prefixed with zDP for every table defined in the system. More information is here:
https://winthropdc.wordpress.com/2008/10/06/what-do-the-zdp_-auto-generated-stored-procedures-do/

A heap of these will no longer be required as the objects have been removed.

• Using the Object Explorer Details window in SQL Server Management Studio, drop ALL the zDP named procedures from all the GP databases.
• As sa user, use SQL maintenance window to recreate them for the current set of products. (Microsoft Dynamics GP>>Maintenance>>SQL)
• Select each company and each module in turn, select all the tables with ALT+A and then check the create auto procedures check box.
• Click process
  

This is very important as many thousands of procedures will removed from this
stage.

Remove the product from the DU tables

If we are happy the products objects have been cleared, also remove the product from the upgrade and versions tables in DYNAMICS.

-- change XXX for the product ID
Delete DB_Upgrade where PRODID = XXXX 
Delete DU000010 where PRODID = XXXX 
Delete DU000020 where PRODID = XXXX 
Delete DU000030 where PRODID = XXXX 

Check the for scripts

Also it is wise to check for scripts to modules by the vendor of that module. Microsoft provide scripts to remove triggers from the manufacturing module and also a script to help remove the analytical accounting.
These scripts can deal with things like removing from smart lists and objects that may be named badly.

Beware of names

It is also important to not remove any scripts that are defined in the sample company. An example of this is the aag procedures for analytical accounting. Not all aag prefix objects should be deleted, some always exist in any GP system. Simply removing everything with the aag prefix would delete these, if reference is not made to the sample company.

This kind of hazard is why it is important to take great care.
It is also worth noting it is wise to enforce a rigid naming convention around new object in the databases. This allows easy filtering for these kind of maintenance tasks, also keeps objects grouped together.

Example for Grant Management

Grant management uses GTM table prefix. Having checked there are no exclusions to this rule (no customer added objects or other modules stepping over that prefix, then the drop scripts can be generated, copied out of the results window and then executed in SQL management studio. Do this for each company database. Also remember to check DYNAMICS database too.
The SQL that generates the scripts can be tweaked with extra WHERE clauses to exclude and object that can be identified as not in scope of the clean up.
Be very risk aware in this work. Running such scripts most certainly could make your installation of GP unsuported by your support partner, which is a big deal if things go wrong.

select 'EXECUTE sp_rename '''+  t2.name +'.'+t1.name +''' , ''' + t1.name +' to delete gm' +''';' from sys.tables t1
join sys.schemas t2 
on t1.schema_id=t2.schema_id  where t1.name like'GTM%'

EXECUTE sp_rename 'dbo.GTM01100' , 'GTM01100 to delete gm';
EXECUTE sp_rename 'dbo.GTM01101' , 'GTM01101 to delete gm';
EXECUTE sp_rename 'dbo.GTM01200' , 'GTM01200 to delete gm';
EXECUTE sp_rename 'dbo.GTM01300' , 'GTM01300 to delete gm';
EXECUTE sp_rename 'dbo.GTM01400' , 'GTM01400 to delete gm';
EXECUTE sp_rename 'dbo.GTM41000' , 'GTM41000 to delete gm';
EXECUTE sp_rename 'dbo.GTM41400' , 'GTM41400 to delete gm';


select 'EXECUTE sp_rename '''+  t2.name +'.'+t1.name +''' , ''' +t1.name +' to delete gm' +''';' from sys.procedures t1
join sys.schemas t2 
on t1.schema_id=t2.schema_id  where t1.name like'GTM%'
and t2.[name] ='dbo'
EXECUTE sp_rename 'dbo.GTM_BudgetOutsideRangeForGrant' , 'GTM_BudgetOutsideRangeForGrant to delete gm';
EXECUTE sp_rename 'dbo.GTM_ClearBudgetAccountBalanceForGrant' , 'GTM_ClearBudgetAccountBalanceForGrant to delete gm';
EXECUTE sp_rename 'dbo.GTM_ClearBudgetBalanceForGrant' , 'GTM_ClearBudgetBalanceForGrant to delete gm';
EXECUTE sp_rename 'dbo.GTM_DeleteFromGLWorkError' , 'GTM_DeleteFromGLWorkError to delete gm';
EXECUTE sp_rename 'dbo.GTM_DeleteFromSubLedgerError' , 'GTM_DeleteFromSubLedgerError to delete gm';
EXECUTE sp_rename 'dbo.GTM_InsertIntoGLWorkError' , 'GTM_InsertIntoGLWorkError to delete gm';
EXECUTE sp_rename 'dbo.GTM_InsertIntoSubLedgerError' , 'GTM_InsertIntoSubLedgerError to delete gm';


select 'EXECUTE sp_rename '''+  t2.name +'.'+t1.name +''' , ''' + t2.name +'.'+t1.name +' to delete gm' +''';' from sys.views t1
join sys.schemas t2 
on t1.schema_id=t2.schema_id  where t1.name like'GTM%'
and t2.[name] ='dbo'

EXECUTE sp_rename 'dbo.GTM20001V' , 'dbo.GTM20001V to delete gm';
EXECUTE sp_rename 'dbo.GTM20002V' , 'dbo.GTM20002V to delete gm';
EXECUTE sp_rename 'dbo.GTM20003V' , 'dbo.GTM20003V to delete gm';
EXECUTE sp_rename 'dbo.GTM20004V' , 'dbo.GTM20004V to delete gm';
EXECUTE sp_rename 'dbo.GTM20005V' , 'dbo.GTM20005V to delete gm';
EXECUTE sp_rename 'dbo.GTM20006V' , 'dbo.GTM20006V to delete gm';
EXECUTE sp_rename 'dbo.GTM20007V' , 'dbo.GTM20007V to delete gm';
EXECUTE sp_rename 'dbo.GTM20008V' , 'dbo.GTM20008V to delete gm';
EXECUTE sp_rename 'dbo.GTM20009V' , 'dbo.GTM20009V to delete gm';
EXECUTE sp_rename 'dbo.GTM20010V' , 'dbo.GTM20010V to delete gm';
EXECUTE sp_rename 'dbo.GTM20011V' , 'dbo.GTM20011V to delete gm';
EXECUTE sp_rename 'dbo.GTM20012V' , 'dbo.GTM20012V to delete gm';
EXECUTE sp_rename 'dbo.GTM20013V' , 'dbo.GTM20013V to delete gm';
EXECUTE sp_rename 'dbo.GTM20014V' , 'dbo.GTM20014V to delete gm';
EXECUTE sp_rename 'dbo.GTM20015V' , 'dbo.GTM20015V to delete gm';
EXECUTE sp_rename 'dbo.GTM20016V' , 'dbo.GTM20016V to delete gm';
EXECUTE sp_rename 'dbo.GTM20017V' , 'dbo.GTM20017V to delete gm';
EXECUTE sp_rename 'dbo.GTM20018V' , 'dbo.GTM20018V to delete gm';

select 'EXECUTE sp_rename '''+  t2.name +'.'+t1.name +''' , ''' + t2.name +'.'+t1.name +' to delete gm' +''';' from sys.views t1
join sys.schemas t2 
on t1.schema_id=t2.schema_id  where t1.name like'GTM%'
and t2.[name] ='dbo'

In the above I also had checked there were no user defined functions or triggers relating to this module. Remember to also remove the product from the DU tables.

Regression

Obviously fully test this, ensuring you post and perform normal transactions in a test copy of your production environment first.
Once the renames have been applied to production, run with those objects in the database for a while, once confident you may use the "Object Explorer Details" (F7 key) in SQL Server Management Studio to filter object name by "to delete". Select all the objects and drop them via the user interface.
Double check before dropping objects that nothing has been caught up in the filter results that should not be dropped (common sense I know).
If an object not found error should happen in production, simply rename the object back to what it was, this is a super quick resolution compared to restoring backups.
This is a very risky task, please be aware a posting could be damaged if objects are missing, so everything should be properly tested out in a test copy of your environment first.

Results

For the work just completed, it resulted in over 1000 tables per company removed, about 10,000 stored procedures removed per company, 200+ views per company, etc
As triggers left over from old instllations of manufacturing and other products are no longer writing into tables that will never be queried, it saves back up space an helps performance and storage requirements.

The information in this post is for consultants or for customer to work with support partners, do not delte objects from the database without proper testing.